On-Premise Data Refresh in Power BI

Posted On August 13, 2015 by Posted in

Purpose of this blog is to refresh on-premise data sources available in Power BI.

Prerequisite:

Microsoft SQL Server Management Studio, Microsoft Excel 2013, Power BI Desktop Tool, Microsoft Data Management Gateway

Steps for On-Premise Data in Power BI:

Setting up On-Premise Data Refresh in Power BI will help refresh data directly from the data source to dataset. Here we will be looking on how to refresh on-premise data in Power BI using SQL data and Excel data.

First we will be working on Excel data and then SQL data.

I] Working on Excel Data:

To begin with Excel Data we must have the following things on your PC

Microsoft Excel 2013
Power BI Desktop Tool
Microsoft Data Management Gateway
Login to Power BI Service

Create an Excel worksheet in Microsoft Excel 2013. And save it with some appropriate name.

Now open Power BI Desktop

Click on Get Data and select the type of file you need like in our case it is Excel and then click on

Select the Excel file from the appropriate location and click Open.
You will get a Navigator window, select the sheet(s) you require and click on load

The data gets loaded.

Once loaded, you can view the sheet(s) and the columns in Field section on the Right hand side of the Power BI Desktop window.
Now, you can create any chart or report in this and main is to save the file. This is saved with an extension of .pbix
Now open Power BI Home page and create a login (If you have created your login already then use the same). Meanwhile it will ask you to install Microsoft Data Management Gateway. Install Microsoft Data Management Gateway on your machine.

Click on Get Data tab (at the left hand side bottom). Choose the saved .pbix file you saved using Power BI Desktop.
Create the required chart(s) and report(s).

Here, chart based on Employee ID and salary is taken. Now the employee with name Bhavna (ID 1004) is 50 K. So, change it in the excel file, say, we change it to 20 K.
Now save the changes done in Excel file and go to Power BI. Click on the dataset where you created the report using this Excel data and schedule the refresh.

For scheduling the refresh click on the Dataset and click on SCHEDULE REFRESH you will get the below window check the gateway connections and the schedule time and click the Apply button at the bottom of that widow.

Now click on REFRESH NOW. The data gets automatically refreshed and you can see the changes in the report.

As shown in above bar chart the salary that has been changed is reflected here.

This is how refresh of on-premise in Power BI works using Excel data.

II] Working on SQL Data:

To begin with SQL Data we must have the following things on our PC

Microsoft SQL Server Management Studio
Power BI Desktop
Microsoft Data Management Gateway
You have to create a login to Power BI

Create a Table in Microsoft SQL Server Management Studio. And save it with some appropriate name.
For demo purpose I have created a table named Emp_Details with following rows and data.
Now similarly Get Data from the SQL table in Power BI and create Dataset.

Here the salary of employee name Aurick is 2 K

Now we will update the SQL data using Update query and change it to 20 K

Run the select query to check the changes. And save the change done.
Now, go to Power BI and similarly SCHEDULE REFRESH (as done for Excel data) and then refresh the dataset which you have used to create the report.

As you can see the Dataset is refreshed and the Salary of Employee named Aurick is changed to 20 K.