You must make this process work, you must follow this.
- You can download the most recent edition of Power BI Desktop from here.
- Access a SQL Server case, either on your computer or on a server on your local network to export the data to the server.
- You may either install R for Windows, download it from here or install SQL Server 2016 R Services from an existing R-Server OR install it from here.
- You can download the library from here by downloading the RODBC library for R.
Note: I did not have R Studio built and nothing went wrong.
To learn Power BI Go through ITGuru’s Power BI Online Course
RODBC Library Installation for R and SQL Server R Services
You must download the library from the above link and then extract the contents of the zip file containing the folder “RODBC.” Then all you need to do is copy the “RODBC” folder to the “library” folder that resides in your “Program Files” folder in either the R or SQL Server 2016 directories.
How is it working?
Open an existing Power BI Desktop model that you want to export your data to a SQL Server table and follow the steps below: (I use the “Internet Sales” model built on top of AdventureWorksDW. At the end of this article, you can download my Power BI Desktop model.)
- Open a model of the Power BI Desktop
- Tap ‘Modify Queries’
- Right-click any table you want to export to SQL Server, and then right-click ‘Reference’
- Now from the “Transform” tab on the ribbon, press “Run R Script”
- Copy the R script below and paste it:
Library (RODBC)
Driver=SQL Server; server=SQL SERVER INSTANCE; Database=SQLDB) conn <- odbcDriverConnect()
OdbcClearErrorFailed to (conn)
SqlSave(conn, dataset, tablename=’TABLE NAME’,rownames=FALSE, safer=FALSE, add=TRUE)
Proximity (conn)
- Replace the highlighted sections with the names of your items and click OK.
Note:
If you have more than one instance of SQL Server, then an additional ‘\’ (backslash) must be applied to the server name. An escape character is known to be the first backslash. So your script should look like the figure shown below.
R The Power BI Data Export Script to SQL Server
If all is all right, you’ll see an empty table without any error messages in the Question Editor. Now open SSMS and scan the “Exported from PowerBI” table to see the data being exported.
Management Studio of SQL Server
Note:
There is a summary after the script block showing your current R home directory, as you see in the Run R Script window.
R In Power BI Desktop Home Directory
You can change it as below from Power BI Desktop Options:
- Tap the menu for “File”
- Tap “Options and Configurations”
- Tap on ‘Options’
- Options for Power BI Desktop
- “Click “Scripting for R
- In the drop-down list, pick the “Detected R home directory” and then press OK
- Changing R Home Directory in Desktop Power BI Options
Note:
We’re changing the query, as you can see. This is why, from the base question, I produced a duplicate. After the data is exported to SQL Server, you might want to apply the R script to the base query and then delete it.
To get a better understanding of the R script, Hans briefly clarified the R script, so I encourage you to take a look at his article. Check it out for more comprehensive details.
On a larger table containing more than 11 million rows, I attempted the above method and I should admit that the result was not too good. It took more than 15 minutes to export approximately 1.5 million lines, which is not that impressive.
Power BI Desktop Data Export to SQL Server Using SSIS
I link to the Desktop from SSIS to export data to SQL Server using this process. I have discussed in more depth how to connect to Power BI Desktop here.
- Accessing a SQL Server instance
- SSIS Accessing (SQL Server Integration Services)
- You can download SSDT (SQL Server Data Tools) here. I’m using SSDT 2015
How is it working?
We export data from the desired table to SQL Server as indicated before connecting to a Power BI Desktop using its random local port from the SSIS package.
Note:
Bear in mind, if you close your Power BI Desktop file and reopen it the random port number will change. This method is therefore a temporary solution for exporting larger volumes of data to SQL Server from Power BI Desktop.
First of all the port number of a local SSAS instance generated by Power BI Desktop needs to be found. Check this out to find out more about the Power BI Desktop port number.
To find the port number quickly:
- Navigate to the following directory:
- Percentage of the percentage of LocalAppData \Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
Temp folder for Desktop
The folder “AnalysisServicesWorkspaceXXX” should be open, with XXX being a random number. Open the folder from the folder.
- “Open folder “Information
- In the folder, find “msmdsrv.port.txt” and open it. It includes the random number of the local SSAS instance port used by Power BI Desktop.
- Local Port Number Finding Power BI Desktop in Temp Dir
- Now we need to build a project for SSIS in SSDT.
- Open up SSDTTS
- Build a new initiative for “Integration Services”
- On Control Flow, put a “Data Flow Task”
- Add “OLEDB Source” to the data flow.
- To open ‘OLEDB Source Editor’, double click OLEDB Source.
- To create a new OLEDB link manager, click “New”
- One more time, press “New”
- Set ‘Provider’ to: ‘Native OLEDB\Microsoft OLEDB Analysis Services Provider 13.0’.
- Set “Location:” to “localhost: XXXXX” with the local Power BI port number XXXXX.
- “Choose from the drop-down list “Original Catalogue.
- To ensure the connection is successful, click “Test Connection”.
- Click on the “OK” button continuously many times to return to the “OLEDB Source Editor” option.
Creating a Link Manager for SSIS
We have developed a connection manager so far, which you can see in the “OLEDB Source Editor”
In the Power BI Desktop model, you can see all the tables that exist by clicking on the “Table Name or View” drop-down list.
Power BI Desktop Browsing in SSIS
In the model, as you see, there are a bunch of “LocalDate Tables” that you can’t see in the Power BI Desktop. They are secret date tables automatically generated to support time intelligence by Power BI Desktop. We will NOT pick a table from the list as we will receive an error message at the end.
Set “Data Access Mode” to “SQL Command” and in the “SQL Command Text” window, write the following simple DAX code. I know, the SQL command says, but, uh, we put the DAX query here. Believe me, Smile works
- In SSIS, running DAX Queries
- To see the table columns, click ‘Columns’ and then click OK
- DB Source Editor for OLE
- “Double click “Destination SQL Server
- To build a new link manager for our SQL database, click ‘New’.
- “Click again “New
- Enter the “Server Name” that you want data to be exported to
- Choose or enter the name of a database
- Link checking
- Then to get back to “SQL Destination Editor” you need to click OK multiple times
Creating a Link Manager for SSIS
To create a new table for our results, click New”
Looking at the developed T-SQL, you will quickly realize that the table statement produced will not work. Copy the code and paste it into SSMS OR in a text editor and clean it up. For the new table, you will want to use a meaningful name as well.
Creating a New Table from SSIS on SQL Server
- Copy/paste the code back into the window “Create Table” and press OK.
- Creating a New Table from SSIS on SQL Server.
- Editor of SQL Destination.
- Click ‘Mappings’ to map all ‘Input Columns’ to ‘Destination Columns’ and then click ‘OK’.
- SQL Destination Editor Mapping Columns
- It’s that. Just now you need to run the kit.
- Package Run for SSIS
- Even when exporting larger numbers of rows, this method functions well.
Conclusion
I hope you conclude writing data to SQL server from Power BI. You can learn more about writing data through Power BI online training.
