Exporting Energy BI Knowledge to SQL Server

Replace 2021 March:

Now you can export the info straight from Energy BI Desktop utilizing our free exterior software, Energy BI Exporter. Learn extra right here.

Replace 2019 April:

If you wish to export the info mannequin from both Energy BI Desktop or Energy BI Service to CSV or SQL Server verify this out.

Within the earlier weblog posts I defined the best way to export Energy BI information to Excel and CSV right here and right here. As promised on this put up I clarify the best way to export information from Energy BI Desktop to SQL Server.

Hans Peter Pfister has already defined the best way to export information from Energy BI Desktop to SQL Server utilizing R scripts. Though Hans has finished a superb job, it could be a bit onerous to make it work when you don’t have any R expertise and also you don’t even know the best way to set up and name R libraries. That’s so true about myself, I’m NOT an R man, however, who is aware of, perhaps I might be. Smile

However, what when you don’t wish to go along with R? If you’re extra concerned with BI than analytics, then utilizing R may probably not be your cup of tea. Fortunately, there may be one other solution to export your Energy BI information to SQL Server which is extra BI pleasant. You’ll be able to export Energy BI information to SQL Server utilizing SSIS (SQL Server Integration Providers). So if you’re aware of SSIS, then it could be your your most well-liked alternative.

With respect to Hans, on this put up, I clarify his technique of exporting information from Energy BI Desktop to SQL Server extra in particulars in order that anybody who is just not that aware of R could make it work. I additionally clarify the best way to export information from Energy BI Desktop to SQL Server utilizing SSIS. If there may be another strategies you’re conscious of please let me know within the remark part under.

As said earlier than, Hans has already defined this technique right here. So I don’t clarify precisely what he did, however, I exploit his technique to export information from present Energy BI Desktop mannequin to SQL Server and I clarify it step-by-step.

Necessities

To make this technique work you must:

Newest model of Energy BI Desktop, you may obtain it from right here
Have entry to an occasion of SQL Server, both by yourself machine or on a server in your native community to export the info to
Both set up R for Home windows, you may obtain it from right here OR utilizing an present R-Server OR set up SQL Server 2016 R Providers
Set up RODBC library for R, you may obtain the library from right here

Word: I haven’t put in R Studio and nothing went incorrect.

Putting in RODBC Library for R and SQL Server R Providers

As talked about earlier, you may set up R OR SQL Server R Providers OR R-Server, however, as I haven’t tried R-Server myself I simply clarify the best way to set up RODBC in R and SQL Server R Providers.

You need to obtain the library from the hyperlink offered above, then extract the contents of the zip file which accommodates a “RODBC” folder. Then all you really want to do is to repeat the “RODBC” to the “library” folder exists in both R or SQL Server 2016 folders in your “Program Information” folder.

How Does It Work?

Open an present Energy BI Desktop mannequin that you just’re prepared to export its information to a SQL Server desk and observe the steps under: (I exploit “Web Gross sales” mannequin created on prime of AdventureWorksDW. You’ll be able to obtain my Energy BI Desktop mannequin on the finish of this put up.)

Open the Energy BI Desktop mannequin
Click on “Edit Queries”
Proper click on on any desired Desk you wish to export to SQL Server and click on “Reference”

Rename the duplicated desk to “Web Gross sales Export”

Now click on on “Run R Script” from “Remodel” tab from the ribbon
Copy and paste the R script under:

library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server; server=SQL_SERVER_INSTANCE; Database=SQLDB")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="TABLE_NAME",rownames=FALSE, safer=FALSE, append=TRUE)
shut(conn)

Change the highlighted components together with your object names then click on OK

Word: You probably have multiple SQL Server occasion then you must add an additional “” (backslash) within the server identify. The primary backslash is taken into account as an escape character. So your script ought to seem like the under determine.

If all the things is ok then you definitely’ll see an empty desk in Question Editor with none error messages. Now open SSMS and question the “Exported_From_PowerBI” desk too see the exported information.

Word: As you see within the “Run R Script” window, there’s a description after the script block exhibiting your present R residence listing.

You’ll be able to change it from Energy BI desktop Choices as under:

Click on “File” menu
Click on “Choices and settings”
Click on “Choices”

Click on “R Scripting”
Choose a “Detected R residence listing” from the dropdown listing then click on OK

Word: As you see we’re modifying the question. Because of this I created a reproduction from the bottom question. You could wish to put the R script to the bottom question then take away it after the info is exported to SQL Server.

Hans briefly defined the R script, so I encourage you to take a look at his put up to get a greater understanding of the R script. For extra detailed data verify this out.

I attempted the above technique on a much bigger desk containing greater than 11 million rows and I ought to say that the efficiency was not too good. It took greater than quarter-hour to export about 1.5 million rows which isn’t that spectacular.

On this technique I hook up with Energy BI Desktop from SSIS to export information to SQL Server. I defined how to connect with Energy BI Desktop extra in particulars right here.

Necessities

Newest model of Energy BI Desktop
Accessing an occasion of SQL Server
Accessing SSIS (SQL Server Integration Providers)
SSDT (SQL Server Knowledge Instruments), you may obtain it right here. I exploit SSDT 2015

How Does It Work?

As said earlier than we’ll hook up with a Energy BI Desktop utilizing its random native port from SSIS package deal then we export information from a desired desk to SQL Server.

Word: Have in mind, the random port quantity will change when you shut your Energy BI Desktop file and reopen it. Due to this fact, this technique is a brief answer for exporting bigger quantity of knowledge from Energy BI Desktop to SQL Server.

To begin with we have to discover the port variety of native SSAS occasion created by Energy BI Desktop. To study extra about Energy BI Desktop port quantity verify this out.

To shortly discover the port quantity:

Navigate to the listing under:

%LocalAppDatapercentMicrosoftPower BI DesktopAnalysisServicesWorkspaces

There needs to be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random quantity. Open that folder
Open “Knowledge” folder
Discover “msmdsrv.port.txt” within the folder and open it. It accommodates the random native SSAS occasion port quantity utilized by Energy BI Desktop

Now we have to create an SSIS mission in SSDT.

Open SSDT
Create a brand new “Integration Providers” mission
Put a “Knowledge Circulation Job” on Management Circulation
Within the information movement add an “OLEDB Supply”
Double click on OLEDB Supply to open “OLEDB Supply Editor”
Click on “New” to create a brand new OLEDB connection supervisor
Click on “New” yet one more time
Set “Supplier” to: “Native OLEDBMicrosoft OLEDB Supplier for Evaluation Providers 13.0”
Set “Location:” to “localhost:XXXXX” which XXXXX is your native Energy BI port quantity
Choose “Preliminary catalog” from the dropdown listing
Click on “Take a look at Connection” to ensure the connection is profitable
You’ve got click on OK a number of instances to get again to “OLEDB Supply Editor”

Up to now we created a connection supervisor you can see it within the “OLEDB Supply Editor”
You’ll be able to see all tables exist in Energy BI Desktop mannequin by clicking on “Title of the desk or the view” dropdown listing

As you see there are a bunch of “LocalDate Desk” within the mannequin that you just can’t see in Energy BI Desktop. They’re hidden Date tables created by Energy BI Desktop robotically to help time intelligence. We’re NOT going to pick a desk from the listing as we’ll get an error message on the finish.
Set “Knowledge entry mode” to “SQL Command” then write the next easy DAX code within the “SQL command textual content” field. I do know, it says SQL command, however, we put DAX question. Imagine me, it really works

Click on “Columns” to see the desk’s columns then click on OK

Put a “SQL Server Vacation spot” on the info movement
Join the “OLE DB Supply” to “SQL Server Vacation spot”
Double click on “SQL Server Vacation spot”
Click on “New” to create a brand new connection supervisor to our SQL database
Click on “New” once more
Supplier needs to be “Native OLE DBSQL Server Native Shopper”
Enter the “Server identify” that you just need to export information to
Choose or enter the database identify
Take a look at connection
You then have to click on OK a number of instances to get again to “SQL Vacation spot Editor”

Creating SSIS Connection Manager

Click on “New” to create a brand new desk to land our information
generated T-SQL you’ll shortly perceive that the create desk assertion received’t work. Copy the code and paste in a textual content editor OR in SSMS and tidy it up. You might also wish to use a significant identify for the brand new desk.