You can now export the data directly from Power BI Desktop using our free external tool, Power BI Exporter. Read more here. Show Update 2019 April: If you want to export the data model from either Power BI Desktop or Power BI Service to CSV or SQL Server check this out. 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 me, I’m NOT an R guy, but, who knows, maybe I will be. 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 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 detail 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 RAs 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 the existing Power BI Desktop model to SQL Server and I explain it step-by-step. RequirementsTo make this method work you need to:
Note: I haven’t installed R Studio and nothing went wrong. Installing RODBC Library for R and SQL Server R ServicesAs 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 an “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder that exists in either R or SQL Server 2016 folders in your “Program Files” folder. 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 the “Internet Sales” model created on top of AdventureWorksDW. You can download my Power BI Desktop model at the end of this post.)
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. 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 to see the exported data. Note: As you see in the “Run R Script” window, there is a description after the script block showing your current R home directory. You can change it from Power BI desktop Options as below:
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 SSISIn 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 detail here. Requirements
How Does It Work?As stated before we’ll connect to a Power BI Desktop using its random local port from the SSIS package then we export data from the desired table to SQL Server. Note: Keep in mind that the random port number will change if you close your Power BI Desktop file and reopen it. Therefore, this method is a temporary solution for exporting a larger amount of data from Power BI Desktop to SQL Server. First of all, we need to find the port number of the local SSAS instance created by the Power BI Desktop. To learn more about the Power BI Desktop port number check this out. To quickly find the port number:
%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
Now we need to create an SSIS project in SSDT.
This method works well even when exporting a 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. |