Export Power BI Service Data to SQL Server

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!

 

http://biinsight.com/export-power-bi-service-data-to-sql-server/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s