Feb 3, 2022
This demo will how you how DataDirect can connect to multiple BI and analytics sources. First it will show ODBC connectivity using our Autonomous REST Connector, along with Power BI, connecting to a REST Endpoint and second, it will also show our Hybrid Data Pipeline product to achieve OData connectivity to that same REST Endpoint.
----
Today I will be showing two demos. The first showing ODBC connectivity using our Autonomous REST Connector, along with Power BI and connecting to a REST Endpoint. And I will also show our Hybrid Data Pipeline product to achieve Odata connectivity to that same REST Endpoint. When you install our Autonomous REST Connector for ODBC, and you go to the start program window, it will create and open our user interface in order to create a configuration file. Our Autonomous REST Connector uses a REST configuration file in order to access that backend API. And that file is just a JSON document and it determines how the data can be formed into a relational model. It can be used with your traditional BI tools using ODBC or JDBC connectivity. The products will sample the response from many REST API that we can consume, and then turns that into a relational data model.
And as I said, we provide this user interface here to help you build that configuration file. Or you can bring in a recipe that we have prebuilt, kind of a template that we've prebuilt for you to various sources. And some of those sources that Abbott who'd mentioned such as Aha!, Jira, Twitter, some of the beta sources that we support GitHub and HubSpot and coming soon, such as PayPal. For today's presentation or demo I'll show the Open Brewery, API. And if you click this, it will automatically bring in that recipe that we have prebuilt for you. And Open Brewery is the public API that anybody can access. This is their website, Open Brewery Database. Their projects are available on GitHub. And if you go to list breweries here on the left, you'll see, this is the base URL that we can use with the driver, in order to access that REST Endpoint.
If I go back to the user interface here, the host name or the base URL has already brought in for me, showing that Open Brewery URL, and the only other thing I need to do here for authentication, we do support multiple authentication methods with Autonomous REST Connector, such as [inaudible 000217], Baro Token. We also support custom authentication but for this brewery, API does require authentication. I will just choose, None, here. Next, when you go to configure Endpoints, when you are connecting to your own API, you would put in the endpoint here along with the table name. And at that point you can hit send, and it will sample that REST Endpoint and bring back the endpoint here on the left-hand side because we are using that template. It brought it in for us automatically. It found the Brewery's Endpoint. And if I expand that and it's already been formed into that relational model of rows and columns.
It's showing the column names that are available to us within that Endpoint. And if I click on breweries, it will automatically validate that data in real time within this UI as well, and show you that the SQL view here and also that JSON view, if needed, and with this, you can also modify that JSON file, if there's anything that is not quite right. For example, if I go to this, you can go to modify attributes. This is set to primary key. I'm going to uncheck that. I will make this one, the primary key instead, and change this to change the data type.
It's updating that model for me automatically. Everything is how I want it to be now. You can even create or add your own query parameters for that backend API, if needed. Include pagination, all this has already been done for me because of the template that I uploaded. Once I have everything here, it creates the connection string, whether you're using a DSMs connection string, if I were using JDBC, it would have the JDBC URL here, or you can go ahead and download that REST Configuration File and use that when creating your ODBC connection. I will open that here. And it's created that Open Brewery, API REST file or JSON file for me. So now if I go to the ODBC administrator and create a data source to the Autonomous REST Connector, and it's opening the configuration manager now to create that data source, and you have to give it a data source name, and the only other thing here that you need to add is the location of the REST Configuration file that was created. I'm going copy that location, put it in here and add that REST file name.
And since this API does not have authentication, I will leave that to none. And that is all I need to do and save that. Now it's created that data source name for me. I go back to the ODBC administrator. It's created that [Ado 000524], breweries data source for me. Now you can go to your favorite BI tool of choice. I will use Power BI and go to get that data and you can select other and ODBC and connect. And I will choose that Ado breweries data source that I created in the ODBC administrator. And since this data source does not have authentication, I will choose default or because system.
And it will read that data source and bring back the data for me. There it is floating. I will load it in here. This is going to bring back several thousand rows from that brewery's API. And once it completes floating, it will add the information here on the right-hand side. And there I have access to my Brewery's data.
Let me go ahead and add a couple of those in here. I'm going to add it as a map. Let me add the name of the brewery, the city it's located in the state and the type of brewery. And let me change this and change the name, just so it makes a little more sense in here. You can rename their field names and okay, and then I'll change this and now it will bring back the visual for the brewery's API. You'll see, this is for worldwide. There's several breweries here in London, several in Alaska. And I'll scroll in here a little bit closer to the US and for the bubbles. It gives me the information that they're available, obviously this is only the information that is available in that API. That gives you the type of brewery. It is here, Springfield, Missouri, White River Brewing.
And this is your visual for that brewery API. With Power BI, it gives you the ability of bringing in the data two different ways. You can import the data, which is what we just did and it will load that data into memory. And the user can only refresh that data once a day. They also give you the ability of using direct query. It gives you the results that are needed in real time, and you can configure refreshes for that data to refresh every 15 minutes, every hour, every couple of hours, but it's a little bit more flexibility of how current that data is.
In order to use direct query. They require you to use what's called a custom connector. There are several of our ODBC drivers that we provide the custom connector for, Autonomous REST Connector is one of them. We do support custom connector with our high than green plum and other drivers as well. But if you navigate, if you go to the installation of the ODBC driver and we provide the Power BI custom connector for you in there, you just need to incorporate that into your Power BI application, which we have documentation on how to do that.
And when I select the product, instead of using ODBC, I will use the Auto REST Connector, the custom connector, and I will put in the same data source, thing and choose direct query instead of import. Now it's bringing back the data the same way it did before. Select breweries and load, and I will do everything the same as last time. All right, and I'll just select a couple of these here again, and oops. Now it'll bring back the data and there, the data has been returned, and then I can save this report.
And once that is saved, I can then publish this report to my Power BI and the cloud and Power BI and the clouds where you can issue those that refresh intervals. I'll save it in my workspace, in the cloud and once that's done, I can open it in Power BI and the same report will open here or anyone. You can send a link for anyone to access that report. And there, the report has been brought in here into Power BI cloud, and then you can go to that dataset. And this is where you can configure the refresh, whether you have one hour, every 15 minutes daily, weekly.
That is Power BI and using the Autonomous REST Connector. The other demo I would like to show real quick is using our Hybrid Data Pipeline with Odata connectivity. As Abbott had mentioned, Hybrid Data Pipeline can give you the ability to secure your data sources behind your corporate authentication. I can give you the ability to access On-Prem Data Sources behind a firewall, and also gives you the ability to decide which tables and columns you want to expose to your end-users.
You can also share the data source to On-Prem Sources or cloud-based sources, or even a REST API by providing a single ODBC or JDBC driver. You don't even have to share a driver, the ODBC or JDBC driver, and you can provide a no data endpoint to any of those sources. Quickly, going to show you how to achieve Odata connectivity to that same REST API that we used in the last demo. If I click on new data sources here, these are the sources that we support out of the box today with Hybrid Data Pipeline and all of these sources you can connect to using a single ODBC driver or JDBC driver or Odata Endpoint. We also support the ability of bringing your own JDBC driver for use within Hybrid Data Pipeline. Or you can also use our... We've recently incorporated our Autonomous REST Connector for use within Hybrid Data Pipeline as well. I will click on that and give it a name.
And I'm going to use that same endpoint that we used in the last sample right here and import that. And it automatically brought that data in, if click on generate configuration, this is that same JSON REST file that was generated before. You can see the data type and column names, you can even edit this file, if there's anything in here that you want change or update. And now if I click on the Odata tab, I can configure my SEMA map to connect to that REST Endpoint. I will select auto REST and I want to remove the configuration because we just want the brewery's information.
So here's all the column names that we have access to. You can remove the column names if you don't want some of these for your end users to have access to and save that mapping. And once that is done, you can save the data source and the mapping was already created for me for that Odata map. I can click on that. This is my URI, that I can use with any application that supports Odata. I'll quickly show you, you may have noticed in one of hoot slides, that Excel is still even one of the number BI tools of choice, that customers are using. I'm going to use Excel because you can use our ODBC driver or Odata with Excel. If I go to data and get data from other sources and select Odata, you can just put in that Odata URL, hit okay and it will bring back that list of brewery right there, pull that data in and there you go. And everything's brought back into Excel.
Contact us to learn more on these features.
