DirectQuery your custom REST API in Power BI

If you have a custom REST API and would want to Direct Query the API from Power BI instead of an importing data, this tutorial will guide you on how you can do that for any of your public or internal REST API’s.

To enable Direct Query for your REST API, the pieces of puzzle you need would be

1. A Custom ODBC Driver for your REST API
2. Power BI Data Connector that enables Direct Query

In this tutorial, I will be using REST API from NYC Open Data to demonstrate the Direct Query capabilities.

Why

First, why build an ODBC driver for enabling Direct Query on REST API? The only way that Power BI currently enables Direct Query is using Data Connector SDK provided by Microsoft for Power BI. The Data Connector SDK will allow you to enable Direct Query on ODBC driver only. So, building a custom ODBC driver for your REST API helps not only to enable Direct Query your API from Power BI, but you can also use this ODBC driver with any other tool like Tableau, Qlik etc.,

How

Building a custom ODBC driver for REST API is easy with Progress DataDirect OpenAccess SDK, that lets you build ODBC/JDBC drivers on a single code base and you are free to use C#, Java or C++ for developing the driver.

Getting Started

I have already created a tutorial that helps you build a ODBC/JDBC driver for NYC Parking Violations data offered by NYC Open Data. You can follow through this easy tutorial to build your own ODBC driver. You can find the code to this project in GitHub, if you just want to test it out before you can dive in.

Power BI Data Connector that enables Direct Query

On to second part of the puzzle, you now have a ODBC driver but by default ODBC drivers won’t have Direct Query enabled in Power BI. You would need a custom extension that enables Power BI to get to enable the Direct Query and know the capabilities of ODBC driver as there are many drivers in the wild that don’t follow the ODBC standards and are not fully compliant. The drivers that you build using Progress DataDirect OpenAccess SDK and any other drivers are fully compliant with ODBC standards. For drivers built using Progress DataDirect technology, we will provide you with that extension file, which you can plug it in to Power BI and start using the Power BI.

1. Download the Power BI Data Connector for OpenAccess drivers from GitHub
2. On your PC, Go to Documents and create a folder named Microsoft Power BI Desktop.
3. Under the folder Microsoft Power BI Desktop, create another folder Custom Connectors
4. Copy the OpenAccessODBC.mez to the folder Custom Connectors
5. Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)

Note: This extension is not production ready, please contact us for the stable version.

1. If your Power BI is already running, save your work and Restart Power BI for the extension to show up.
2. In Power BI, click on Get Data -> All -> OpenAccessODBC. Choose it and you should now see a form as shown below where it will ask for DSN name. Below are my DSN Config(Find more about this in the tutorial for building ODBC driver) and Power BI Config. Also under Data Connectivity mode -> Choose DirectQuery
   
   
   
3. Click on OK and you should now see Authentication for the driver. As the API is open, it has no authentication. Click on Connect.
   
   
4. This will fetch all the tables from your custom ODBC driver you built for your REST API
   
   
   
   
5. Click on Load and you should now see that DirectQuery has been enabled for querying your REST API in Power BI footer as shown below
   
   
   

I hope this article helped you on how you can Direct Query your REST API using the ODBC driver built on Progress DataDirect OpenAccess SDK.  All the Progress DataDirect ODBC drivers and the drivers you can build using OpenAccess SDK are fully compliant to ODBC standards, and are internally certified to work with Power BI. Feel free to try these for your projects and if you have any questions or issues, feel free to contact us.