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
- A Custom ODBC Driver for your REST API
- 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.
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.,
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.
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.
- Download the Power BI Data Connector for OpenAccess drivers from GitHub
- On your PC, Go to Documents and create a folder named Microsoft Power BI Desktop.
- Under the folder Microsoft Power BI Desktop, create another folder Custom Connectors
- Copy the OpenAccessODBC.mez to the folder Custom Connectors
- 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.