Update 2019 April: If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.
Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.
A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.
How It Works?
This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to
- Publish the existing Power BI Desktop solution to Power BI Service
- Install On-premises Data Gateway in PERSONAL MODE
Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.
After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.