An adventure on SQL Server performance and features

How to populate a PowerBI Dataset from a REST API, using PowerBI’s REST API, without any code (via Microsoft Flow)

H

I was wondering if PowerBI could be used for reporting on Live data, not coming from my Database using DirectQuery (or the new super neat composite mode) with with a Streaming Dataset, which I never used before, so, I got to work.

The Proof Of Concept is to take live data from Cryptocurrency value using REST API and pushing them into a PowerBI report.
I’m using the API from CryptoCompare¬†with this super basic call to get the price of BTC in EUR:

https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=EUR

This will return a very simple JSON result shaped like this:

{"EUR":5685.01}

Now that we have the source data, let’s talk about destination; I’ve created a new streaming dataset in PowerBi (Your Workspace -> Datasets -> Create new Streaming Dataset

Then, it’s time to configure the API Streaming Dataset, I’ve added only two fields, a BTC value and a datetime timestamp to keep track of the moment in time which the data was fetched:

Once created, you’ll see that PowerBI will give you the API URL to call for pushing the data in the dataset, and a few code examples to use right away:

We’re simply going to use the raw input, so basically our JSON data to push needs to be in the format that you see above, pretty simple.

Going straight to data pushing, you could either write your own app (or let your dev build it for you) in a pretty simple way, as they are just two API calls, but we’re lazier than that, and for this POC we’re going to use Microsoft Flow wich will work just fine:

Step 1: schedule the flow to launch every *whatever*
Step 2: Get the data from the API

Pretty straightforward until now, were just getting the JSON data every 1 minute, now we have to push in into the PowerBI Dataset.
This data needs a little bit of manipulation, because as you’ll surely remember the JSON data from Cryptocompare gets you only the EUR value and not the timestamp (maybe I couldn’t find the right API, whatever, this is neat for a demo); Remember that we’ll be pushing the data in a specific format, as seen above.
To do so, we’ll use another HTTP step and build the POST data to send to the REST API:

We’re basically buidling the input JSON manually by using the “EUR” value from the previous step (using the triggerBody() expression to extract the value, google it up it’s easy) and the expression-equivalent of a GETDATE()

If everything goes well, your flow will run and start pushing the data to the PowerBI dataset:

Once the dataset has some data, you can create a new report from that dataset, and accomplish something like this:

Ad that’s it, a very simple exampe on how to get data from a REST API and pushing it to a Streaming Dataset in PowerBI, all without basically writing any code.

You’re welcome!

About the author

Emanuele Meazzo

Add comment

An adventure on SQL Server performance and features

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you