I recently spoke in Tampa Dev Azure Meeting in a webinar form on 31st Jan 2018. We received interesting questions during the presentation. My aim was to introduce basic concepts of Big Data, Azure Data Lake, Azure Data Lake Store (ADLS), Azure Data Factory (ADF) and Power BI.
I would like to thank Tampa Dev organisers and all attendees for giving the opportunity to speak in this session.
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 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 the existing Power BI Desktop model to SQL Server and I explain it step-by-step.
Requirements
To make this method work you need to:
The 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 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.)
In this post I’m explaining how you can deploy a developed SSIS project to several different environments. It might have happened to you that there are several environments that you need to deploy the SSIS projects to. Assume that you have DEV, QA, UAT and PROD environments. Some organisations might have even more environments. Also, there are many cases that you might have several PRODs that the SSIS packages should be deployed to all of them. So the scenario is that whenever you create a new SSIS project in DEV area or you may modify the existing projects, you need to deploy each SSIS project to QA for testing purposes. So, if you have 3 new SSIS projects or you’ve just modified 3 existing projects, you’ll need to deploy each project separately. It is the same story for QA guys after finishing the test cases and after the SSIS projects pass all the test cases. They’ll need to deploy all projects to UAT. Again it is the same story with UAT and PROD. It is getting harder when you need to deploy all the projects in several different PROD environments.
Using the solution below, you can easily deploy all SSIS projects from an environment to another environment or even several different environments.
