Update 2021 March:
You can now export the data directly from Power BI Desktop using my tool, Power BI Exporter. Read more here.
Update 2019 April:
If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.
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:
- Login to Power BI Service
- Click the gear icon (
- Click “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”
All done!
Nice to read about possibility to get data from Power BI Service dataset to SQL. We need it so much.
I installed Data Gateway in personal mode, but I cannot see in PBI Service Datasets / Gateway connection at top line : Setting for Export Power BI … to SQL Server with R.
Why?
Thank you for help.
Hi Vaclav,
Welcome to BIInsight.com.
There are a lot of possibilities to be checked.
Are you getting any error messages/warnings in Power BI Service? If so please share it with us.
Have you checked the gateway service in your machine is up and running?
Open the gateway desktop app and make sure it is connected.
Have you installed the gateway in personal mode on you organisation machine? If yes, then make sure it is not blocked by your corporate firewall.
I newly posted a new blog that you might find it helpful: https://www.biinsight.com/quick-tips-export-power-bi-desktop-and-power-bi-service-model-data-as-a-whole-with-dax-studio/
Cheers.
I couldn’t get it to work. I was trying to export an aggregated table in Power BI service that has DAX calculations in it. But it didn’t work for me. It works through desktop though but my requirement is to refresh it everyday. Still a great article. Any help is appreciated on my issue though. Thanks.
Hi Seikant,
What sort of issue(s) are you facing?
Are you getting any errors?
Please elaborate.
Cheers
Hi i tried exporting the data to sql sever using Authentication = ActiveDirectoryIntegrated;
but got error that this method can’t be used then tried with interactive method and resulted in expression error not enough enumerator. Whereas while I used the same interactive authentication method to run the script in Rstudio it worked.
Anythoughts on that…?
Hi
I set this up successfully and it wrote to my SQL DB without any issues. But I noticed the data in SQL Table isn’t being updated after Power BI Service Refresh. I opened the PBI Desktop again to make slight change before the R script step and opened the script and ran it again. The changes weren’t reflected in the SQL table. I don’t think it is related to Gateway issue, but I could be wrong. Thought of asking here if I missed a step…
Thank you,
SJ