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.
Hi, how did you make a direct query connection to the push dataset? For me, it’s a live connection which prevents any other data sources to be added to the file. This is a limitation documented here https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#limitations-and-considerations
LikeLiked by 1 person
are you aware of this https://powerbi.microsoft.com/en-us/blog/directquery-for-power-bi-datasets-and-azure-analysis-services-preview/
LikeLike
Hey mim – good stuff here but whenever I connect to the push dataset and try to use DQ over PBI Datasets to add dimensions and tables from other sources, I get this error – does this solution still work for you 18 months later?
Error Message:
Either the database ‘WatcherProd’ does not exist, or you do not have permissions to access it.
I am a workspace admin and have all the permissions I need, I suspect that push datasets have been blocked from using DQ over PBI Datasets but not sure
LikeLike