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.
To see the dataset settings:
- Click “Datasets” tab then click on the desired dataset
- Expand “Gateway connection”
You can download On-premises Data Gateway from here. The installation is super easy you just have to keep in mind to
- Select “Personal Mode” when you’re installing the gateway.
- Now go back to the “Dataset settings” and expand “Gateway connection”. The Gateway should be online on Personal Mode.
- Tick the “Use your data gateway (personal mode)
- Expand “Data source credentials” and click “Edit Credentials” for all data sources and pass the credentials accordingly
- To test the solution just navigate to your workspace and refresh the dataset
- At this point you’ll get a warning message as below. Click OK
- Now query the underlying table(s) in SQL Server database to see if the data has been imported successfully
- Expand “Schedule refresh” and change the configuration as desired the click “Apply”