Exporting Power BI Data to SQL Server

 

Exporting Power BI Data to SQL Server

In the previous blog posts I explained how to export Power BI data to Excel and CSV here and here. As promised in this post I explain how to export data from Power BI Desktop to SQL Server.

Hans Peter Pfister has already explained how to export data from Power BI Desktop to SQL Server using R scripts. Although Hans has done a brilliant job, it might be a bit hard to make it work if you don’t have any R experience and you don’t even know how to install and call R libraries. That’s so true about myself, I’m NOT an R guy, but, who knows, maybe I will be. Smile 

But, what if you don’t want to go with R? If you are more involved with BI than analytics, then using R might not really be your cup of tea. Luckily, there is another way to export your Power BI data to SQL Server which is more BI friendly. You can export Power BI data to SQL Server using SSIS (SQL Server Integration Services). So if you are familiar with SSIS, then it might be your your preferred choice.

With respect to Hans, in this post, I explain his method of exporting data from Power BI Desktop to SQL Server more in details so that anyone who is not that familiar with R can make it work. I also explain how to export data from Power BI Desktop to SQL Server using SSIS. If there is any other methods you’re aware of please let me know in the comment section below.

Exporting Data from Power BI Desktop to SQL Server with R

As stated before, Hans has already explained this method here. So I don’t explain exactly what he did, but, I use his method to export data from existing Power BI Desktop model to SQL Server and I explain it step-by-step.

Requirements

To make this method work you need to:

  • Latest version of Power BI Desktop, you can download it from here
  • Have access to an instance of SQL Server, either on your own machine or on a server in your local network to export the data to
  • Either install R for Windows, you can download it from here OR using an existing R-Server OR install SQL Server 2016 R Services
  • Install RODBC library for R, you can download the library from here

Note: I haven’t installed R Studio and nothing went wrong.

Installing RODBC Library for R and SQL Server R Services

As mentioned earlier, you can install R OR SQL Server R Services OR R-Server, but, as I haven’t tried R-Server myself I just explain how to install RODBC in R and SQL Server R Services.

You have to download the library from the link provided above, then extract the contents of the zip file which contains a “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder exists in either R or SQL Server 2016 folders in your “Program Files” folder.

Library folder in R

Library folder in SQL Server 2016

How Does It Work?

Open an existing Power BI Desktop model that you’re willing to export its data to a SQL Server table and follow the steps below: (I use “Internet Sales” model created on top of AdventureWorksDW2016CTP3. You can download my Power BI Desktop model at the end of this post.)

 

  • Open the Power BI Desktop model
  • Click “Edit Queries”
  • Right click on any desired Table you want to export to SQL Server and click “Reference”

Duplicate a query in Power BI

  • Rename the duplicated table to “Internet Sales Export”

Query Editor in Power BI Desktop

  • Now click on “Run R Script” from “Transform” tab from the ribbon
  • Copy and paste the R script below:

  • Replace the highlighted parts with your object names  then click OK

Exporting Power BI Data to SQL Server with R

Note: If you have more than one SQL Server instance then you need to add an extra “\” (backslash) in the server name. The first backslash is considered as an escape character. So your script should look like the below figure.

R Script for Exporting Power BI Data to SQL Server

If everything is fine then you’ll see an empty table in Query Editor without any error messages. Now open SSMS and query the “Exported_From_PowerBI” table too see the exported data.

SQL Server Management Studio

Note: As you see in the “Run R Script” window, there is a description after the script block showing your current R home directory.

R Home Directory in Power BI Desktop

You can change it from Power BI desktop Options as below:

  • Click “File” menu
  • Click “Options and settings”
  • Click “Options”

Power BI Desktop Options

  • Click “R Scripting”
  • Select a “Detected R home directory” from the dropdown list then click OK

Changing R Home Directory in Power BI Desktop Options

Note: As you see we’re modifying the query. This is why I created a duplicate from the base query. You may want to put the R script to the base query then remove it after the data is exported to SQL Server.

Hans briefly explained the R script, so I encourage you to have a look at his post to get a better understanding of the R script. For more detailed information check this out.

I tried the above method on a bigger table containing more than 11 million rows and I should say that the performance was not too good. It took more than 15 minutes to export about 1.5 million rows which is not that impressive.

Exporting Power BI Desktop Data to SQL Server Using SSIS

In this method I connect to Power BI Desktop from SSIS to export data to SQL Server. I explained how to connect to Power BI Desktop more in details here.

Requirements

  • Latest version of Power BI Desktop
  • Accessing an instance of SQL Server
  • Accessing SSIS (SQL Server Integration Services)
  • SSDT (SQL Server Data Tools), you can download it here. I use SSDT 2015

How Does It Work?

As stated before we’ll connect to a Power BI Desktop using it random local port from SSIS package then we export data from a desired table to SQL Server.

First of all we need to find the port number of local SSAS instance created by Power BI Desktop. To learn more about Power BI Desktop port number check this out.

To quickly find the port number:

  • Navigate to the directory below:

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

Power BI Desktop Temp Folder

  • There should be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random number. Open that folder
  • Open “Data” folder
  • Find “msmdsrv.port.txt” in the folder and open it. It contains the random local SSAS instance port number used by Power BI Desktop

Finding Power BI Desktop Local Port Number in Temp Dir

Now we need to create an SSIS project in SSDT.

  • Open SSDT
  • Create a new “Integration Services” project
  • Put a “Data Flow Task” on Control Flow
  • In the data flow add an “OLEDB Source”
  • Double click OLEDB Source to open “OLEDB Source Editor”
  • Click “New” to create a new OLEDB connection manager
  • Click “New” one more time
  • Set “Provider” to: “Native OLEDB\Microsoft OLEDB Provider for Analysis Services 13.0”
  • Set “Location:” to “localhost:XXXXX” which XXXXX is your local Power BI port number
  • Select “Initial catalog” from the dropdown list
  • Click “Test Connection” to make sure the connection is successful
  • You have click OK several times to get back to “OLEDB Source Editor”

Creating SSIS Connection Manager

  • So far we created a connection manager that you can see it in the “OLEDB Source Editor”
  • You can see all tables exist in Power BI Desktop model by clicking on “Name of the table or the view” dropdown list

Browsing Power BI Desktop in SSIS

  • As you see there are a bunch of “LocalDate Table” in the model that you cannot see in Power BI Desktop. They are hidden Date tables created by Power BI Desktop automatically to support time intelligence. We are NOT going to select a table from the list as we’ll get an error message at the end.
  • Set “Data access mode” to “SQL Command” then write the following simple DAX code in the “SQL command text” box. I know, it says SQL command, but, we put DAX query. Believe me, it works Smile

Running DAX Queries in SSIS

  • Click “Columns” to see the table’s columns then click OK

OLE DB Source Editor

  • Put a “SQL Server Destination” on the data flow
  • Connect the “OLE DB Source” to “SQL Server Destination”
  • Double click “SQL Server Destination”
  • Click “New” to create a new connection manager to our SQL database
  • Click “New” again
  • Provider should be “Native OLE DB\SQL Server Native Client”
  • Enter the “Server name” that you desire to export data to
  • Select or enter the database name
  • Test connection
  • You then need to click OK several times to get back to “SQL Destination Editor”

Creating SSIS Connection Manager

  • Click “New” to create a new table to land our data
  • Looking at generated T-SQL you’ll quickly understand that the create table statement won’t work. Copy the code and paste in a text editor OR in SSMS and tidy it up. You may also want to use a meaningful name for the new table.

Creating New Table in SQL Server from SSIS

  • Copy/paste the code back to “Create Table” window then click OK

Creating New Table in SQL Server from SSIS

SQL Destination Editor

  • Click “Mappings” then map all “Input Columns” to “Destination Columns” then click OK

Mapping Columns in SQL Destination Editor

  • That’s it. You just need to run the package now

SSIS Package Run

This method works well even when exporting larger number of rows.

If you have any other ideas, feedback, etc. I’d love to know about it. So please leave your thoughts in the comment section below.

Download Power BI Desktop model used in this article

5 thoughts on “Exporting Power BI Data to SQL Server”

  1. Muito bom dia!
    Achei incrível seu artigo e me ajudou muito a resolver um enorme problema.
    Gostaria de abusar um pouco mais de seus conhecimentos.
    Veja! Fiz tudo da forma como você explicou e deu tudo certo, no entanto, quando efetua a consulta no SQL Server algo acontece e, os dados são triplicados. Imagine que no exemplo que fiz, minha tabela original tinha apenas 10 linhas, mas quando faço o procedimento via Power BI, o arquivo da consulta do SQL resulta em 30 linhas.
    Muito estranho! Nunca vi isso!
    Se puder me ajudar ficarei grato.
    E novamente parabéns pelo artivo.

    1. Hi Cleiton,

      Thanks for your comment.
      Well, I do not speak Portuguese, so I used google translate to understand your comment.
      If google translated correctly, you’re getting tripled the results than you have in PowerBI.
      If that’s right, please elaborate which method you used, R script method or SSIS method?
      Please note that the R script will append the results to the current data exist in the SQL Server table. This means if you refresh your model 3 times then you get tripled number of records in SQL Server table. So if you have 10 rows in PowerBI and if you refresh it 3 times then you get 30 rows in SQL Server side.
      Cheers.

  2. Meu Deus!
    Tenho um novo problema!
    Veja se pode me ajudar, por favor!
    Ocorre que, os valores da coluna DATA não aparecem, mas uma mensagem “Microsoft.OleDb.Date” é repetida em todos os registros.

Leave a Reply