Forum Discussion
Automate Export of PowerBI table to SQL Server
I have a powerBI calculated table based off a number of data sources and transformations and on a scheduled refresh and want to now automate exporting this data into MS SQL Server on an automated, scheduled frequency. I would have thought there would be an easy way to do this using Flows or Power Automate but haven't found any easy way without coding R or Python. Before I head down that path, has anyone come across a way to do this inherent in the product?
5 Replies
Power BI datasets are exposed as XMLA endpoints. You can address it just like any other SSAS source.
Pushing data from a Power BI dataset into a SQL Server feels like the tail wagging the dog. Do you have no option to feed the SQL server from the original source of the data?
Thanks for the reply. The objective is to take a dataset that is a complilation of many data sources (web services data, Google Analytics, Oracle transactional data, other data feeds) and use the features of PowerBI to feed the cleansed, transformed, consolidated data into one SQL table in an automated fashion so it can be consumed by other services. I have the automation of all the feeds and transformation complete, the last step is the automated load in SQL.
I understand I can code a solution to automate this using the API set or SSIS, looks like that is my best option. I was hopeful Flows or Power Automate could be used and I was missing something. I did notice a number of threads asking for the samer feature. I'll suggest it as well.
You will find that the Power Automate SQl connector is a premium connector. It has no support for bulk operations. They expect you to pull and push individual rows. Good luck with that.
To add insult to injury, Power Automate has some ridiculous message and file size limitations. Any normal sized Power BI dataset is too much for these limits.
