Update 2019 April: If you’re interested in exporting the data model from Power BI Desktop or Power BI Service to SQL Server check this out.
Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.
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.
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.
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.
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.)