Configuring of Data Gateway in Power BI
In order to perform a scheduled refresh, we will need to install the Power BI data gateway.
- From Power BI Online, go ahead and click on the [Downloads] button located on the top right corner >> Select the [Data Gateway].
2. Run the installer and select the appropriate Gateway installation type. We will be provided with 2 options:
- Enterprise that can be shared and reused by multiple users, support Power BI, PowerApps, Logic Apps and Microsoft Flow too
- Or Personal mode that can only be used by individual and Power BI
3. Other factors such as the location of the installation of the gateway, the network that is in place and the policy regarding gateway within our organization might impact which type of installation we will be setting. Note though that if we install the gateway on a personal computer, the gateway will not run when our computer is turn off.
4. Once we have completed the installation, go back to Power BI Online and go to the [Manage Gateways]. Select [Add data sources to use the gateway].
5. On the next screen, ensure we are selecting the [ODBC] data source type and set up the right Connection String to the Server, Database, Username and password.
Note: For connection string pass the value in the below format
database={database name};driver={PostgreSQL Unicode(x64)};port=5432;server={private IP of postgresql};sslmode=require
6. Once the connection is successfully established, go to the [Users] tab and tick the relevant users to add them to the Data Source.
7. We need to ensure that the dataset is running the proper gateway connection. In order to do that click on the dataset and select [Schedule Refresh].
8. On the next screen ensure that we are selecting the right [Gateway connection].
9. Then turn on the [Scheduled refresh] option to ensure that we can setup the refresh frequency. With the current license of Power BI we are using (Power BI for Office 365) we are only able to schedule for [Daily] refreshes and setup a maximum of 8 time for refresh:
10. Once we click on [Apply] we should be able to see a success message.
11. We can also now test the manual refresh of the dataset.
Conclusion
We can see Power BI Service connect managed database through On premise Data Gateway. Some configurations are tricky, but they are not difficult.
If you found this interesting or have any feedback, please let me know in the comments section.
This blog is part four of a four-part series:
Part 1 — Creating Virtual Network and PostgreSQL
Part 2 — Creating and Configuring Virtual Network Gateway (Point-to-Site)
Part 3 — Installing ODBC driver for remote connection to PostgreSQL DB
About the Author:
Raghavendra BN is a DevOps Engineer, currently working in Version 1’s Foundation’s team. Follow Version 1 and Raghavendra BN for more blogs around Microsoft Azure and Azure DevOps.
