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.
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.)
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.
In some cases we need to do a single task for lots of SQL Server instances. Assume that we have a web based programme. The programme’s database is distributed across the country and we have 10 different virtual (VM) servers to host the programme’s databases. The programme is working based on some configurations that are stored in a CONFIG database. The CONFIG databases are hosted by 20 different SQL Server instances to serve 20 different clients. The SQL server instances are all named SQL server instances hosted by those 10 virtual servers. We need to update the CONFIG database for all regions on a monthly basis. The database structure of all CONFIG databases is the same. In this case a simple way is to create an SSIS package for each source server to collect the data from all source databases one-by-one. This means that we will have 10 copies of the same SSIS package that each package is pointing to a server as a source server. We need 10 packages because we can retrieve the CONFIG database list by writing a T-SQL script or using an extra Foreach Loop Container. So we need a SSIS package per server.
In the previous article we discussed about how migrating a database diagram into another database. In this article I’ll quickly show you how to install database diagram support. As mentioned in the” How to copy or migrate database diagrams into another database” article, we need to install database diagram support to be able to see the migrated database diagrams. We also explained a very easy way to install database diagram support from SSMS in the “How to store a SQL Server database diagram into a file and share it with others?” article. Now, assume that we want to migrate the database diagrams into several SQL Server instances. It seems that it might be better if we can implement the whole process programmatically. This was exactly my question when I wanted to deploy several database diagrams from a database hosted in development environment into a copy of that database hosted by test or UAT (User Acceptance Test) environments. For instance, just assume that there are a bunch of database diagrams created by developers in development environment. So, you’ll have an exact copy of the database structure in test environment. Your testers need to use the database diagrams created by the developers. So far, so good. This part of the challenge is covered in the previous articles. However, we still need to install database diagram support manually and this is what we don’t like! The solution is really easy. Run the following code and you are done! You can also add the following code to the execute SQL task from your SSIS package if you decided to implement the solution in an SSIS package (take a look at “Migrating database diagram by creating a simple SSIS package” No. 8).
First of all you need to read my previous article called “How to send SSIS logs (errors) through email” as the processes are pretty the same. However, you need to create some changes in the Execute SQL Task (ref.: section “J” number 5 and 12 of “How to send SSIS logs (errors) through email”). As an Execute SQL Task is used to collect the logs stored in SQL Server you need to be familiar with parameter mapping in Execute SQL task and know how it works. Assume that we need to just send the “Error” logs that are happened between “Event Handler Start Time” and the current time for the current package execution. As you can imagine it is slightly different from what we did in the previous article to email the SSIS logs to the system administrators. As there was just one system variable that we mapped in parameter mapping section in the Execute SQL Task. But, here we need to have one more system variable mapped to a parameter. Please note that we are using OLEDB connection to connect to the SSIS log database that we created before to store SSIS logs. So there are some important points with OLEDB Connection and Execute SQL Task parameter mapping and its SQL statement.
First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure is occurred. Just assume that we have several SSIS packages and we are storing the logs in a SQL Server database. Almost all of the packages are running over night. Now, what if a package or some packages failed? Generally one of the most essential activities is that a notification email should be sent to the system administrators to let them know that something’s wrong with the package execution. It could be more helpful to send them the relevant information about the failure. Reading the following lines you can handle this important part of the process in your organisation.
SENSITIVE DATA! It’s an interesting topic! In this post I’m trying to explain how to hash data to increase security during ETL. Assume that we have sensitive data stored in several secured source systems. The source systems are located in different countries and different regions. As the source systems themselves are secured, how we can cover data security needs during ETL process to read data from source systems and load into staging area? Apart from using secured network infrastructure, VPN, network tunnelling etc. we need to cover data layer security to extract sensitive data. One of the best ways is hashing data when it is extracting from source databases. Hashbytes is a T-SQL function that is available in SQL Server 2005 and later. As you might know there are many hashing algorithms, but, different SQL Server versions are supporting different range of hashing algorithms. For instance SHA1 is supported by SQL Server 2005 and later, but, if you are looking more secure hashing systems like SHA2, 256 (32 bytes) or 512 (64 bytes), you should use SQL Server 2012. Actually the hashbytes function will return null in earlier versions of SQL Server. If you are looking for a higher level of security like SHA3 that is originally known as “Keccak” you should wait for it for a long time as based on my investigations it is not supported even in SQL Server 2014 OR you can write your own SHA3 code OR just rely on some third party codes available on the Internet! So let’s get our hands dirty with using hashbytes in different versions of SQL Server.