We have seen different Power BI implementation options in my article here. If you do not want to move your database infrastructure to cloud, but looking for for generating visualizations from Power BI service(cloud) connecting to on-premises data source, that is possible using Data Gateway. Power BI visualizations on cloud use gateway to get data from on premise data (database such as SQL Server, Oracle or even structured files such as spreadsheets or csv files.
Note : Complete on-premises solution is available with the release of Power BI Report Server which is part of Power BI Premium. This article assumes the Power BI service implementation as below and gateway component is highlighted.
On-premises data gateway allows Power BI service to connect to your database to refresh dataset. Gateway allows both scheduled refresh and live/direct query. Time for looking at the steps on how to install and configure the gateway.
Download the standalone data gateway and start the service in on-premises. To download, use the option below after logon to your account
After installation, you have to sign in using your Azure account and specify a name to your gateway so that Power BI service tie it to your Power BI account.
Make sure that On-Premises Data Gateway is running as a windows service in your PC. Ideally this will be one of your on-premises server with connectivity to your database.
Go to Power BI web login, select ‘Manage Gateway’ option as shown below
Note that your Data Gateway is already listed there. You need to create a data source and provide authentication details for database connectivity (Windows User details in case of a file data source such as excel spreadsheet)
Next, go to your workspace, select the data set and go to ‘settings’ as shown below
Under ‘Gateway connection’ section, select your data gateway and click ‘apply’
Under ‘Scheduled refresh’ section, turn on ‘Keep your data up to date’ and specify refresh frequency as below
That completes it. Your dataset is ready for scheduled refresh connecting to your on-premises data source!
For verifying, you can update data in SQL Server database (local server) and wait for the refresh to happen.
Once complete, launch your visualization which uses the dataset and see your updated data reflecting there!