Export Power BI Service Data to SQL Server

image

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:

  • Login to Power BI Service
  • Click the gear icon (image)on top right of the page
  • Click “Settings”

image

  • Click “Datasets” tab then click on the desired dataset
  • Expand “Gateway connection”

image

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.

image

  • 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)

image

  • 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

image

  • 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”

image

All done!

2 thoughts on “Export Power BI Service Data to SQL Server

  1. 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.

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.