Automate Export of PowerBI table to SQL Server

[Chewsi]

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? 

[lbendlin]

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?

[Chewsi]

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. 

[lbendlin]

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.

[Chewsi]

Thank again. Yes it does seem strange that a product with such rich capabilities to automate intake and transformation has very limited ability to automate export for consumption downstream. I'm still a big fan of the product. 

[lbendlin]

Power BI was positioned as a reporting tool. That is purely a one way street - ingest data and display it.  There was never a plan to make it an ETL tool, like SSIS. (Incidentally the same is true for VBA - it was never intended for ETL but it's being used for that since forever)

Only very recently have the Power BI designers caught on to the "new" trend of business decision support which doesn't stop at looking at pretty charts - it requires you to take your insights and do changes in your process based on them, ie "write back".

There's a crude way of doing it with the Power Apps visual, and there are even more crude ways of abusing the R scripting abilities to do these writebacks. 

It's a big question - should Power BI stay focused on data consumption and reporting, or should it become part of the business decision cycle?