Push Dataset in PowerBI Composite Model, Near real Time made easy !!!

I was always intrigued by the Push dataset in PowerBI but it did had a big showstopper, you can’t mix it with other tables, which made is of little use for me, until Dec 2020, where it become possible to use it in a composite Model, see this excellent introduction

To see how it works, I tried it with this dataset, the Power Generation in Australia every 5 minutes.

1- Create Dataset in PowerBI service

2- Get the URL

3- Use some tool to start streaming data

You need some tool to start pushing data into that Table, for example you can use Easymorph, Personally I have an existing python script that download the data and push it to a DB, I had to add only a couple of line of codes to make it push to PowerBI dataset directly, I copied the code from this function

Please make sure the datetime format is correct, otherwise it will not work.

###### post to PowerBI
            
            df['SETTLEMENTDATE'] = [datetime.strftime(item, "%Y-%m-%dT%H:%M:%SZ") for item in df['SETTLEMENTDATE']]
            
            REST_API_URL = "https://api.powerbi.com/beta/XXXXXXXXXXXXXXXXXXXXXX"
            body = bytes(df.to_json(orient='records'), encoding='utf-8')
            req = urllib2.Request(REST_API_URL, body)
            response = urllib2.urlopen(req)  
            print("PowerBI: HTTP {0} {1}\n".format(response.getcode(), response.read()))
            ################################################

4- Build a Composite Model in PowerBI Desktop

DirectQuery to the Push dataset and a dimension Table as Import, we need the dimension table to know the fuel source of every generator ( wind, Coal etc), The Dimension Table change very rarely, less than 20 new rows per year !!!!

And here is the report

Maybe not a big deal for a lot of users, but Export to web does not work currently with composite Model as of 5-Jan-2021

5- Refresh the Page

Please notice, even if the data source refresh, you still needs to click refresh to get the Latest data on the visuals, when I try to add Automatic page refresh I get this error, I am using a pro license not PPU !!!!

Edit : Fred Kaffenberger suggested a very clever workaround by using a hidden Play Axis custom viz to force the refresh, and indeed it works very well !!!

Take away

To be very clear, Pushing a 5 minutes dataset is not something new , but it was a a relatively complex task, with the new composite model it become nearly trivial, and that’s the magic of PowerBI.

3 thoughts on “Push Dataset in PowerBI Composite Model, Near real Time made easy !!!”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s