Replace 2021 March:

Now you can export the info straight from Energy BI Desktop utilizing our free exterior device, 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 how you can export Energy BI knowledge to Excel and CSV right here and right here. As promised on this submit I clarify how you can export knowledge from Energy BI Desktop to SQL Server.

Hans Peter Pfister has already defined how you can export knowledge from Energy BI Desktop to SQL Server utilizing R scripts. Though Hans has completed an excellent job, it is likely to be a bit exhausting to make it work in the event you don’t have any R expertise and also you don’t even know how you can set up and name R libraries. That’s so true about me, I’m NOT an R man, however, who is aware of, perhaps I shall be.  

However, what in the event you don’t need to go together with R? In case you are extra concerned with BI than analytics, then utilizing R may not likely be your cup of tea. Fortunately, there may be one other strategy to export your Energy BI knowledge to SQL Server which is extra BI pleasant. You possibly can export Energy BI knowledge to SQL Server utilizing SSIS (SQL Server Integration Companies). So in case you are acquainted with SSIS, then it is likely to be your most well-liked selection.

With respect to Hans, on this submit, I clarify his methodology of exporting knowledge from Energy BI Desktop to SQL Server extra intimately in order that anybody who shouldn’t be that acquainted with R could make it work. I additionally clarify how you can export knowledge from Energy BI Desktop to SQL Server utilizing SSIS. If there may be some other strategies you’re conscious of please let me know within the remark part beneath.

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

Necessities

To make this methodology work it is advisable to:

• The most recent 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 current R-Server OR set up SQL Server 2016 R Companies
• 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 mistaken.

Putting in RODBC Library for R and SQL Server R Companies

As talked about earlier, you may set up R OR SQL Server R Companies OR R-Server, however, as I haven’t tried R-Server myself I simply clarify how you can set up RODBC in R and SQL Server R Companies.

It’s important to obtain the library from the hyperlink offered above, then extract the contents of the zip file which comprises an “RODBC” folder. Then all you really want to do is to repeat the “RODBC” to the “library” folder that exists in both R or SQL Server 2016 folders in your “Program Information” folder.

How Does It Work?

Open an current Energy BI Desktop mannequin that you simply’re prepared to export its knowledge to a SQL Server desk and observe the steps beneath: (I exploit the “Web Gross sales” mannequin created on prime of AdventureWorksDW. You possibly can obtain my Energy BI Desktop mannequin on the finish of this submit.)

• Open the Energy BI Desktop mannequin
• Click on “Edit Queries”
• Proper-click on any desired Desk you need 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 the “Rework” tab from the ribbon
• Copy and paste the R script beneath:

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)

• Exchange the highlighted components along with your object names  then click on OK

Word: When you’ve got multiple SQL Server occasion then it is advisable to add an additional “” (backslash) within the server title. The primary backslash is taken into account as an escape character. So your script ought to appear to be the beneath determine.

If every thing is okay 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 to see the exported knowledge.

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

You possibly can change it from Energy BI desktop Choices as beneath:

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

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

Word: As you see we’re modifying the question. Because of this I created a replica from the bottom question. It’s possible you’ll need 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 submit to get a greater understanding of the R script. For extra detailed info verify this out.

I attempted the above methodology 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 methodology, I connect with Energy BI Desktop from SSIS to export knowledge to SQL Server. I defined how to connect with Energy BI Desktop extra intimately right here.

Necessities

• The most recent model of Energy BI Desktop
• Accessing an occasion of SQL Server
• Accessing SSIS (SQL Server Integration Companies)
• SSDT (SQL Server Information Instruments), you may obtain right here. I exploit SSDT 2015

How Does It Work?

As acknowledged earlier than we’ll connect with a Energy BI Desktop utilizing its random native port from the SSIS bundle then we export knowledge from the specified desk to SQL Server.

Word: Take into account that the random port quantity will change in the event you shut your Energy BI Desktop file and reopen it. Subsequently, this methodology is a short lived resolution for exporting a bigger quantity of knowledge from Energy BI Desktop to SQL Server. 

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

To shortly discover the port quantity:

• Navigate to the listing beneath:

%LocalAppDatapercentMicrosoftPower BI DesktopAnalysisServicesWorkspaces

Now we have to create an SSIS mission in SSDT.

• Open SSDT
• Create a brand new “Integration Companies” mission
• Put a “Information Circulation Process” on Management Circulation
• Within the knowledge circulate 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” another time
• Set “Supplier” to: “Native OLEDBMicrosoft OLEDB Supplier for Evaluation Companies 13.0”
• Set “Location:” to “localhost:XXXXX” which XXXXX is your native Energy BI port quantity
• Choose “Preliminary catalog” from the dropdown record
• Click on “Check Connection” to verify the connection is profitable
• It’s important to click on OK a number of occasions to get again to “OLEDB Supply Editor”

• Thus far we created a connection supervisor you can see within the “OLEDB Supply Editor”
• You possibly can see all tables exist within the Energy BI Desktop mannequin by clicking on “Identify of the desk or the view” dropdown record

• As you see there’s a bunch of “LocalDate Desk” within the mannequin that you simply can not 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 record as we’ll get an error message on the finish.
• Set “Information 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 circulate
• 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
• The supplier needs to be “Native OLE DBSQL Server Native Consumer”
• Enter the “Server title” that you simply want to export knowledge to
• Choose or enter the database title
• Check connection
• You then have to click on OK a number of occasions to get again to “SQL Vacation spot Editor”

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

• Copy/paste the code again to the “Create Desk” window then click on OK

• Click on “Mappings” then map all “Enter Columns” to “Vacation spot Columns” then click on OK

• That’s it. You simply have to run the bundle now

This methodology works effectively even when exporting a bigger variety of rows.

When you’ve got some other concepts, suggestions, and so forth. I’d like to find out about it. So please go away your ideas within the remark part beneath.

Obtain Energy BI Desktop mannequin used on this article