DirectQuery Any On-premises REST API from Power BI Service

REST APIs are very common in enterprise environments today for exposing and consuming data. Although it simplifies data access, access control and platform dependency, it gets tricky if you want to access data from these REST APIs via Power BI. You have to write custom code to handle authentication, pagination, and normalization, not to mention keep it up-to date whenever the REST API changes.

With Progress DataDirect Autonomous REST Connector, you will be able to easily connect to any REST API in your enterprise from Power BI without having to write any code. In this tutorial, we will walk you through how you can connect to any REST API from Power BI, use DirectQuery to get data from your REST API to Power BI and publish the report to Power BI Online which can refresh the dataset by using DirectQuery. Let’s get started.

You can use the Autonomous REST Connector for ODBC directly with Power BI; however, to use the DirectQuery functionality, you must install the Power BI connector that is included with the Autonomous REST Connector. To install the Power BI connector:

1. Navigate to the \tools\Power BI subdirectory of the Progress DataDirect installation directory; then, locate the installation batch file install.bat.
2. Run the install.bat file. The following operations are executed by running the install.bat file:
   • The Power BI connector file, DataDirectAutoREST.pqx, is copied to the following directory.

     %USERPROFILE%\Documents\Power BI Desktop\Custom Connectors

   • The following Windows registry entry is updated.

     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Power BI Desktop\TrustedCertificateThumbprints

3. Open or restart Power BI to use the connector.

1. In this tutorial, we will be connecting to an API called AlphaVantage, which provides real-time stock data. You can check out their documentation here.
2. Get the API Key for AlphaVantage by registering your information.
3. Open a new file and add all the endpoints you want to connect to as shown below.
   {

   "StockIntraday":"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=MSFT&interval=5min&outputsize=full",

   "StockDaily":"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MSFT&outputsize=full"

   }

    

4. Save the above file as alphavantage.rest file.
5. Open the ODBC Administrator, go to System DSN tab, and click on Add to create a New Data Source. Choose DataDirect 8.0 Autonomous REST Connector.
   
   
   
   
6. On the Setup form, provide the below details under General Tab
   1. Data Source Name: {Any name}

      Note: Remember this name, as we will use it later.

   2. REST Config File: Path to .rest file you created above.

      
      

7. Go to Authentication Tab and configure the authentication to the AlphaVantage API as below. AlphaVantage API uses apikey for authentication, which you need to send in as a query parameter with every request.
   1. Authentication Method: UrlParameter
   2. Auth Param: apikey
   3. SecurityToken: Your AlphaVantage API Key

      
      
      

8. Click on Test Connect. Autonomous REST Connector will now try to connect to all the endpoints in your .rest configuration and if everything is configured properly you should see a success message.

1. Download and install Autonomous REST ODBC connector from our website.
2. Install the connector by running the setup executable file on your machine.

1. Open Power BI, go to Get Data -> Other and choose Progress DataDirect Autonomous REST Connector.
   
   
   

   
   Note: If you don’t see the connector, go to File -> Options and Settings -> Security -> Data Extensions -> Choose Allow any extension to load without validation or warning. Restart Power BI.
   
   

2. On the next screen, fill out the DSN Name from ODBC Administrator you have created in the previous section and choose DirectQuery.
   
   
   
   
3. Click on OK and on the next screen choose Anonymous as we have already configured the Authentication in ODBC Administrator. Click on Connect to continue.

    

4. You should now see Navigator representing your REST API as tables. Click on TimeSeries5MIN or TIMESERIESDAILY tables to preview the Stock data.
   
   
   
   
5. Click on Load. Create your Report using the DirectQuery connection to your REST API.

   
   
   

6. Save and publish your report to Power BI Online Service so others can access it. Click on Publish in the toolbar to do this.
7. Choose your destination. In our example, we chose My workspace.

Your report should now be published online.

1. Once you have published your reports online for sharing, you will also want to refresh the reports with latest dataset to keep the information up to date. But the source of your data (REST API) is accessible from your on-premises environments only. To enable refreshing data from on-premises databases and REST API’s you need to install the on-premises data gateway from Microsoft on the machine where you configured the ODBC Connector.
2. Download Data GateWay from Power BI Service. You should find the download option as shown below.

   
   
   

3. Install Data Gateway and log in to your Microsoft account during the setup process. Make sure you use the same account you have used to publish the report online from Power BI Desktop.
4. Go to C:\Users\{username}\Documents\Power BI Desktop\Custom Connectors where you copied the connector in the previous section and open the properties of Custom Connectors.
5. Go to Security Tab -> Advanced-> Add -> Select a New Principal to add the Service Account NT SERVICE\PBIEgwService to access the Custom Connectors folder. The Data Gateway uses this service account to run the services, so we need to make sure this service account has access to Custom Connectors folder.
6. Change the Location to the machine name and add NT SERVICE\PBIEgwService. Click on Check Names to validate the service account and click on OK.

   

7. Save the security permissions and make sure NT SERVICE\PBIEgwService has access to the Custom Connectors folder before you leave.

   

8. Now open On-premises Data gateway. Go to the Connectors tab and replace the path with the below:

    

   C:\Users\{username}\Documents\Power BI Desktop\Custom Connectors

    

   This is the same Custom Connectors folder we have added our connector to and made sure there is access to the service account NT SERVICE\PBIEgwService.
   
   
   

9. Click on Apply and you should now see all the connectors in the folder displayed in the Data Gateway.

   

10. Go to Power BI Service, Settings -> Manage Gateways and you should see the gateway you just installed. Under the Gateway settings, Check the option Allow User’s custom data connectors to refresh through this gateway cluster as shown below. Click on Apply to save the settings.

    
    

11. Go to the Settings -> Data Sets tab and you should see the dataset you published to Power BI Online in the previous section. Under Gateway connection, you should see an error “Not configured Properly”. Click on Actions and you should now see an option to “Manually add to gateway”. Click on it.

    
    

12. This should take you to Gateway Settings to Add a New Data Source under the gateway. Under DSN Name, enter the ODBC DSN Name you have created on your machine and click on Add. This validates the connection and if it’s successful, the data source gets created.

    

13. Now go to Settings -> Data Sets -> Your Data Set -> Gateway Connection. You should see Status as running and there should be a new dropdown Maps to choose the data source that you have just created above. Click on Apply.

    

14. Now you can schedule refresh for your data set under Scheduled Cache Refresh as shown below.

    

15. Or you can refresh your report on-demand from the report settings. The data set will get refreshed by sending a query to your REST API in real-time with the help of Progress DataDirect Autonomous REST Connector.

We hope this tutorial helped you to DirectQuery your REST API from Power BI Desktop as well as Power BI Online Service using Progress DataDirect Autonomous REST Connector. If you have any questions, please contact us and we will be happy to help you.