DirectQuery your custom REST API in Power BI

Introduction

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.

Building a Custom ODBC Driver for your REST API

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)
Enable Custom data connectors

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

Using the DataDirect Extension to DirectQuery your REST API

  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
    openaccess odbc config

    power bi dsn config
  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.

    authentication
  4. This will fetch all the tables from your custom ODBC driver you built for your REST API

    preview data in power bi

  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

    direct query enabled

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.


ODBC TUTORIAL

DirectQuery your custom REST API in Power BI

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers