Copy Power BI dataset tables to SQL Server (using XMLA endpoint connectivity)

[anselmojg]

Hello,

We have a Power BI dataset/reports in the Service refreshed daily with the latest Supply Chain plan from SAP. This is working great.  But we have the need to take “snapshots” at regular intervals to be used later as a reference to compare and understand how changes in the plans develop over time.

The data source (SAP in this case) is not providing that “snapshot” capability and is just giving us the current planning data that is consumed in the existing Power BI solution.

With the recent announcement of the XLMA endpoint connectivity (https://powerbi.microsoft.com/en-us/blog/power-bi-open-platform-connectivity-with-xmla-endpoints-pub...) we are now able to see the Power BI dataset tables in SSMS v18.4. What we would like to do is to “copy” some of those tables over to a SQL Server database where tables with the same fields would serve to create the needed “snapshot” repository. In these tables there would be one additional column to tag each record with the corresponding “snapshot” timestamp.

Here is the challenge we are now facing. With the strict policy we have in place regarding software installation we can only use Visual Studio 2015, SSDT 2015 .. which appear to be not able to connect to Power BI to create an Integration package; no surprise I guess given that XLMA endpoint connectivity is recent.

We have now got a “special permission” to test with Visual Studio 2017 and still no luck. And this is quickly becoming a bureaucracy 'rabbit hole'…

So here is the question that we are trying to answer: is the described intent (copy Power BI dataset tables to a SQL Server database tables) at all possible? If so, would which Visual Studio version would support that? Version 2019? Any other hint, suggestion, alternative..... will be highly appreciated!

Thanks!

Anselmo

[anselmojg]

hi @Anonymous 

I ended up taking a completely different path using Paginated Reports connected to the Power BI dataset containing the data we want to "copy"... With Power Automate, we schedule an expòrt action using the Paginated Report and save the file (excel or csv) in a SharePoint library.. It works well enough.... 

[JirkaZ · Accepted solution]

To my knowledge it's not possible yet (tried VS2019 too). The support for SQL Server Data Tools should come with the introduction of read/write XMLA endpoints.

[anselmojg]

Thank you @JirkaZ ,

Finally got the chance to test with VS2019 as well and found no way to make a copy over to a SQL Server database...

Will see if the announced read/write XMLA announced for later this year (2020) allows this... I would have thought though that "write" was referring to "write back to Power BI", but maybe you are right and it means "write (Power BI data) elsewhere", which is what we are after...

thanks!

[Anonymous]

Hi @anselmojg 
Could you please let me know if you were able to copy the data from Power BI datasets to SQL Server database?

I have tried with Power BI Desktop and i was able to load it into SQL tables, however with Power BI Desktop the port keeps of changing so we can't automate that process.
Any guidance would be appreciated!