Today, I will talk about a cool feature of Power BI where you can fix your data from the Power BI desktop or Power BI web service to SQL server. We encountered a situation where we had to pull data from Power BI desktop to SQL server and use R script.
In order to demonstrate this functionality, I have already opened Power BI desktop over here, which we can connect to our test data.
We also have a SQL Server Management Studio opened over here. You can see that under MyDB, we don’t have any table at this moment.
We’ll connect to this SQL server using Power BI desktop R script, and create a table based out of our data model. In order to begin, let’s connect to one of the data sources.
We have this file called Test Data in Excel format. It has some data which we need to create a table in SQL server. Let’s just select this particular sheet to see a preview of the type of data we have, then click on Transform Data.
As you can see, there are multiple fields over here. Now, out of these fields, we will be inserting some of this data into the SQL server table. Let’s choose some of these fields to load into the table.
We’re going to choose some columns and create a table from there. These fields we have identified will be loaded into our SQL server table.
In order to create this table, we are going to utilize a functionality known as R script and run this into our Power BI desktop.
This is the script that we need to run within the Power BI desktop to create the table into the SQL server. You can see that we are importing a library called RODBC.
You may not have R installed on your system, but you need this library as a must to have before running this script. We need our RODBC library on our system for this functionality to work.
Aside from this, we need to define the connection to our SQL server. This is the syntax we will use to feed in some of the values that are related to our SQL server, like server name, database (where you want to fetch the data) and the table name (which you want to create a table with). So these are the three entries we need to make.
Getting The Server Name, Database, And Table Name
We’ll go back to the SQL server so that we can get the server name. We’ll connect, then copy this name.
Then we’ll put it over here as a SQL server instance.
Next is the name of the database, which is MyDB. We’ll put it over here:
We don’t have any tables right now, so we’ll create a dummy table. This is the table name, which will be created as soon as I run this script.
We are calling the RODBC library. We have defined the connection with our server and the database name, and have given a table name that will be created with the defined connection. Click OK to run the script.
We got a response that says the table is empty.
If you get this response, this means the table in SQL server was created. You might get some warning sign related to permissions. In case you get it, you can select Public. Let’s go back to the SQL server to see whether the table was created or not.
We can see here that the dummy table has been created.
We’ll right-click and select Top 1000 rows.
We can see all the fields and the table that has been created over here.
***** Related Links *****
Upcoming Course: Basics Of R For Power BI Users (Part 1)
R In Power BI: Downloading R And RStudio
Power BI With R And RStudio: How To Get Started
Conclusion
In this tutorial, we created a table out of Power BI desktop, and this table was fed in SQL server. The question is can we automate this step or can we do it from the Power BI web service?
The answer is yes. In order to make it an automated process, you can use a Python script, or you can create a gateway and reschedule your refresh.
These are the multiple ways by which you can automate this process so that whatever data model you have in Power BI desktop, you get a continuous refresh and get that data loaded into SQL server.
Sam
