Some time ago, I had an intake with a new client; he wanted to analyze his financial data coming from Exact Online, using Power BI. After a quick search, I thought there was no problem. There were many other companies who offered this service and the API documentation was comprehensive. I even found a step-by-step tutorial.
But, during development, there were many problems to overcome in order to make it work. To share my experience with the rest of the world, I decided to write this blog (my first one!). I’ll show you how to connect an API which uses OAuth2 authentication to PowerBI, refresh the data online and probably the most beautiful part: for free*. Key to this solution is the use of Azure Functions, to act as a gateway between the PBI Service and the API.
Note: all assumptions and conclusions about (the lack of) technical features are subject to change. Power BI is developing very fast, and by the time you read this, it could be obsolete.
The Problem
The first problem is that PowerBI out-of-the-box doesn’t support OAuth2 API sources. It can’t handle the authentication flow, using the standard Get Data > From Web option. There are some options get the data via complex M (PowerQuery) code, but then online refresh would still be an issue. If you want to use OAuth2, you must write (or buy) a Custom Connector. Unfortunately, with a custom connector, you can only refresh the data with an On-Premise Data Gateway, which requires a (windows) pc online at refresh time. I was looking for a solution to connect a SaaS application to Power BI, without the need of any resource on-premise.
The Solution
To refresh the data directly and get rid of the OAuth2 auth flow, I’ve used Azure Functions. In short: with Azure Functions, you can execute code in the cloud (Azure). More beautiful; it can act as an API which you can use to send or receive data.
In plain text: the original request will not be sent directly to the API, but to the Azure Function. This piece of code will handle the authentication and sends the original request to the API. The response will be returned as result of the Azure Function. The Azure Function acts as a gateway between the caller (PowerBI) the service.
The Code
The Azure Function contains three parts; the main function call with the logic, a separate access code routine to perform the authentication request and a code block to store the response refresh code into the Table Storage.
To call the function, you must provide the original API call in the name query parameter. So the call to https://yourfunctionurl/function?name=/financial/GLAccounts
will be translated tohttps://start.exactonline.nl/api/v1/division/financial/GLAccounts
These steps will follow:
- Get latest refresh token from Table Storage
- Request new AccessCode (with previous refresh token, client id & secret)
- Perform request to the given API (base_url), with the division code AND provided API call (name)
- Return response
To perform the API request, I’m using the RestSharp library.
Function Call:
Access Token part:
Add Token part:
The Result
To test the function, I’m calling the function from the browser.
Please note that in order to make this work, you have to send the client id & secret with the request.
Check the function logging from the Azure Portal. The translated request is red marked.
Power BI
Now (finally!) we can build our report. No complex M code, just a simple Web Request.
And, after publishing the file to the Power BI Service, we hit Refresh to check if it’s working as expected:
Voila 🙂
Now, we can even make use of the new DataFlows in the Power BI Service:
Considerations
Of course, security is an issue. Since you’re no longer using the OAuth2 flow (between Power BI and Function), you must implement your own. Please do not save the sensitive credentials (client id, secret, division) hardcoded in the Function, but sent them in the (secured) header. You can force the Function to use only SSL. To secure the Azure Functions, there are multiple options, please refer to the following resources:
- https://www.linkedin.com/pulse/whitelisting-azure-functions-mike-douglas/
- https://vincentlauzon.com/2017/12/04/azure-functions-http-authorization-levels/
Besides the security, the Function is not ‘production ready’; there are several features that need to be implemented, like error handling and API paging. You could handle the API paging inside the Function, but its runtime is limited to 10 minutes (5 by default, see below) so it might fail when there’s a lot of data. You could handle paging by using M code (PowerQuery), I suggest reading this blog from Chris Webb: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
Another, very important issue; you have to make sure your Function is only running once instance a time. When you request an access code, your previous refresh code gets invalid — that’s why you must store it immediately for the next request. If you run two or more Functions at the same time, you end up in doing requests with old refresh tokens. To make sure you can only run one Function, you can set some settings in the host.json file:
{
"version": "2.0",
"functionTimeout": "00:10:00",
"extensions": {
"http": {
"routePrefix": "api",
"maxOutstandingRequests": 50,
"maxConcurrentRequests": 1,
"dynamicThrottlesEnabled": true
}
}
}
As you can see, this is also the place to increase the default timeout from 5 to 10 minutes (max). If you want to implement paging and need a bigger timeout, you must switch from Consumption Plan to App Service Plan. See pricing below.
Really free?
Well, if you run your function in the Consumption Plan, then it’s free up to 1 million (!!) calls. There is a small fee (almost nil) for the used table storage. See the Azure Function Pricing here, and this for the Table Storage.
To wrap it up; by using an Azure Function as a gateway, you can connect and refresh Power BI Online / Dataflow with any OAuth2 source you’d like, without the need of a custom connector, or on-premise server for (almost) free.
Of course, the code used in this example only works for Exact Online, but I hope you get the point.
This was my first blog. Thanks for reading! If you have any complaints or suggestions (both technical and/or about my writing skills), please let me know!