Hi guys and welcome to ZappySys ODBC PowerPack 101. We continue
where we left off. In the previous video triplet authenticating using OAuth
paginating through the results and then using queries in applications. Today in
addition to making HTTP GET requests we are going to POST data to a REST API
using ZappySys ODBC JSON driver and then use the results in a power bi report so
let's imagine that I have these companies in the same hub spot see
online crm tool and I want to have a Power BI report where I could search by
the main name and get all the companies that are related to that domain let's
say Boeing or Netflix comm so here let me bring the report.
I already made so I have this
parameter domain which I could select and as you see this there are already
the main names here listed so basically these are retrieved from hotspot API and
so let's let's say once I select at the main I want here to have a table with
all the company names and next to them I want to have a phone number so we let me
show you the queries I also built so basically here is the parameter and it
uses the query domains I have this domains list which is basically the
company's query and then I just remove the columns and get this single column
list and the companies is the query to ODBC query
I have hotspot companies let me show you that
HubSpot companies here so as you see data here I will be making a preview and
will get yes companies same companies here
HubSpot widely test etc yeah and then I have this dummy dummy query will just
selects the current domain parameter value so that I can use it in this
what's that name card yeah okay yeah so I guess we can start and you know think
how to get those company names based on the domain name okay so the first thing
I had to do is go to API documentation and find this request I have to make so
this time this is post you have the URL and then this column tells that this is
the parameter of a domain which you want to get companies off right so we are
ready to create a new data source let's get bring back the data source
administrator let's add a new data source I will name in HubSpot search for
companies right yeah okay so now I am ready to copy the URL so the sample URL
so this sample URL will retrieve all the companies of hotspot as you see here and
this time let me show you how to authenticate using a simple URL
parameter and HubSpot uses this h api key so let me go back to my account
where I where I have my company's company is listed as you see from the
previous video we have this integration and integration apps registered here
showing that we are using them and we can disconnect the usage and now let's
press this HubSpot api key copy it and use it here okay let's go back here to
documentation and there are also several parameters we need to pass like limit
properties of set so let's again take the sample data from
here and let's set the request method to post because it's here post let's
configure limit we can remove the limit because we don't want to limit the
company names that will be retrieved for us and these properties the property is
an array of of properties we want to be returned for us so the main will be
returned then I will change this to phone then name will be retrieved for us
and I don't need this one and this is offset for paging which we will not be
using now okay we also have to set the body content type and it says here
application JSON this is kind of important option because if you will use
any other kind most probably the you will you won't get the response you want
you will get an error yeah and also in this place we could enter the parameters
let's say you know any my header my value and this could be passed as well
but but none of them are required to be passed so we are not going to be to be
passing any you can also modify them using this button in this editor so what
you enter here will appear here so let me delete those and let me show you
another way of authenticating this HTTP connection type you basically use it
when you have windows credentials windows authentication or basic ntlm
kerberos or when you are making soap requests so
when making soap requests you should use then ZappySys xml driver and use this
authentication type now there is also static token authentication where you
just write the header name and here and the key here it's and also there is
dynamic token where you have to login into the website and retrieve the token
and then it can be passed along all with other requests okay so we won't be using
any now okay let's make an let's test connection let's make a preview
company's results let's write results so that this area this column would be put
in two rows okay in only one row because HubSpot has only one company so the
domain name was retrieved and company name and also somewhere will be phone
number okay okay so we are ready to use this query in power bi so let's let me
remove all the unnecessary options I'm copying this clicking oh okay getting
back to power bi and let's create a new data source from ODBC let's connect
let's select hotspot search for company's Advanced Options and now let's
enter the sequel statement okay let's click OK
load okay let's open query editor and let me rename this search for companies
and rename the columns I want to use I will rename this to phone this to
company okay so as you see we got back the results of HubSpot come to a close
and apply report okay now I can include the company name and phone okay so it
kind of works but not yet we still have to make this query parametric so when we
select the parameter the disco this table updates as well okay so yeah let's
select to Boeing now and go back to query editor and now we will make this
query dynamic okay let's right click on the search for companies advanced editor
and you scroll to the right and when you find hot spot they remove it close the
string open the string now put two ampersands and write down the parameter
name which is domain right here okay done oh it's already retrieved us back
the data of Boeing okay close and apply
voila you have Boeing and you have all the companies of Boeing we can check in
here to boeing.com boom aircraft engines and the phone number is the same as here
let's select something else let's say Netflix my changes okay you have here
Netflix companies as well together with phones so that's it that's how you
configure ODBC data source based on ZappySys json driver to post data to a
rest api and use the response in whatever application you want
we just used power bi to demonstrate you know the concept you also learn how to
make post requests dynamic by using applications parameters and be aware
that ZappySys xml driver UI is very similar to the episodes json
drivers UI so you can set up data source based on it and then get xml files
instead of json files from urls or call soap web service methods in the next
parts we will explore how to use the pieces or the busy drivers in other
applications thanks for watching