How to use Power BI DirectQuery to build reports for data returned from a custom REST API

If you want to live query your custom REST API using Power BI rather than importing data, you can use the DirectQuery functionality from Power BI with an ODBC driver (connector). This tutorial helps you select the ODBC driver that is most appropriate for your REST API, and then guides you through using a custom OpenAccess driver to connect with DirectQuery to a REST API.

To use DirectQuery to query your REST API, you will need the following:

• An ODBC driver for your REST API. See ODBC drivers for REST APIs for details.
• A Power BI Data Connector that enables DirectQuery. See Using the DataDirect Extension to use DirectQuery.

There are a number of ways in which a REST API can be implemented. To support the different implementations of REST APIs, Progress DataDirect offers multiple solutions to connect to REST APIs.

ODBC drivers for standard REST APIs

For straightforward REST APIs that directly access the entities and their attributes, the Progress DataDirect Autonomous REST Connector for ODBC is the best option. The Autonomous REST Connector is a codeless solution that samples JSON responses from REST APIs and normalizes them to expose them as relational tables. After configuring your endpoints and authentication with the driver, you are ready to connect to your REST API and begin querying your data.

Refer to DirectQuery Any On-premises REST API from Power BI for step-by-step instructions on building a custom connector for Power BI to use with the Autonomous REST Connector. To learn the basics of the Autonomous REST Connector, refer to Query any REST API using SQL in 5 minutes.

Build custom ODBC driver for complex REST APIs

We have observed that REST APIs are often an afterthought of the integration workflow. Organizations will implement REST API as a layer over backend relational databases and enable the API to pass on the SQL or SQL-like queries as payload while invoking REST APIs. In scenarios such as this, the Autonomous REST Connector is not a good fit.

To connect to implementations like this, you can use Progress DataDirect OpenAccess SDK to build the custom ODBC Connector. OpenAccess SDK allows users to intercept queries coming from an ODBC/JDBC/ADO.NET driver and transform them to a structure or format supported by the backend data service, which could be a REST API, SOAP-API, or simply a file.

For step-by-step instructions on creating a custom ODBC driver using OpenAccess SDK, refer to the Build your own custom ODBC Driver for a custom REST API - Advanced tutorial.

In this section, we will use a custom built connector to connect with DirectQuery in Power BI. For the purpose of demonstration, we will use the NYC Parking Violation SODA APIs as our data source for this tutorial:

https://data.cityofnewyork.us/resource/ati4-9cgt.json 

Note that we used this same endpoint when creating a custom driver with OpenAccess SDK in the Build your own custom ODBC Driver for a custom REST API - Advanced tutorial. Refer to this tutorial for details on how the OpenAccess SDK ODBC client and Server retrieve data from endpoints.

The following is the sample data source configuration used to connect to the NYC Parking Violation endpoint:

To connect to your REST API using a DirectQuery connection for Power BI:

1. Download the Sample Power BI Custom Connector code for OpenAccess SDK from this Github project.
2. Build and deploy the Sample Power BI Custom connector using your Power BI Desktop installation. For instructions on building and deploying your connector, refer to Develop a connector using the Power Query SDK in the Microsoft documentation.
3. Open the Power BI desktop application. If your Power BI is already running, save your work and restart Power BI.
4. Select the Get data from other sources button.
5. From the Get Data window, navigate to All > OpenAccessODBC. Click Connect. The OpenAccessODBC connector window opens.

   

6. Provide values for the following; then, click OK:
   • DSN Name: Enter the name of the data source you created. For example, nycdata.
   • Data Connectivity Mode: Select the DirectQuery radial.
7. Enter authentication information if prompted. Because our example does not require authentication, this step is skipped when accessing the sample endpoint.
8. Once connected, the Navigator window displays schema and table information. Select your tables and click Load. This will fetch all the tables from your custom ODBC driver you built for your REST API.

   

9. Select and load tables. Then, prepare your Power BI dashboard as desired.

   

You are now able to query your REST API using DirectQuery for Power BI.

In this tutorial, we demonstrated how you can connect to your REST API with DirectQuery for Power BI and a driver built with Progress DataDirect OpenAccess SDK. In addition, all Progress DataDirect for ODBC drivers are fully ODBC compliant and internally certified with Power BI. We encourage you to try these drivers for your next project. Please contact us if you have any questions.