Purpose of this blog is to refresh on-premise data sources available in Power BI.
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.
- 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.
Here the salary of employee name Aurick is 2 K
There are many more on-premise data sources that can be used to refresh data using Data Management Gateway in Power BI
Supported on-premises data sources:
- Custom SQL/Native SQL
- Access Database
- File (CSV, XML, Text, Excel, Folder)
- IBM DB2 Database
- MySQL Database
- Oracle Database
- PostgreSQL Database
- SharePoint List
- SQL Server Database
- Sybase Database
- Teradata Database
Supported online data sources:
- AppFigures (Beta)
- Azure Blob store
- Azure HD Insight
- Azure Marketplace
- Azure SQL Database
- Azure Table store
- Blank query (query that is not accessing any data source)
- Dynamics CRM Online
- GitHub (Beta)
- Google Analytics
- Hadoop File (HDFS)
- OData Feed
- Sweet IQ (Beta)
- Twilio (Beta)
- QuickBooks Online (Beta)
- Zendesk (Beta)