Installing Power BI Data Gateway For Refresh of PostgreSQL Database
Here is the case scenario, we have a database (PostgreSQL) running on Azure and we need to provide some insights on key tables for the marketing team. We already completed the following steps:
- Create the ODBC connection string to the PostgreSQL
- Create the report
- Publish the report
The report looks nicely on PowerBI online but can’t refresh the data since there is no relation between the report stored online and the database running on Azure.
In order to perform a schedule refresh, we will need to install the Power BI data gateway. Since I build and run the report from my computer and publishing out to other team, I chose
We won’t be able to do Live/Direct Query but we would be able to configure scheduled refresh/manual refresh od the data source.
From Power BI Online, go ahead and click on the [Downloads] button located on the top right corner >> Select the [Data Gateway]. You can also use the direct link at: https://powerbi.microsoft.com/en-us/gateway/
Run the installer and select the appropriate Gateway installation type. You will be provided 2 options:
- Enterprise that can be shared and reused by multiple users, support Power BI, PowerApss, Logic Apps and Microsoft Flow too
- Or Personal mode that can only be used by you and Power BI
Other factors such as the location of the installation of the gateway, the network that is in place and the policy regarding gateway within your organization might impact which type of installation you will be setting. Note though that if you install the gateway on a personal computer, the gateway will not run when your computer is turn off. Read more at: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem
Once you have completed the installation, go back to Power BI Online and go to the [Manage Gateways]. Select [Add data sources to use the gateway]
On the next screen, ensure you are selecting the [ODBC] data source type and set up the right Connection String to the Server, Database, Username and password
Once the connection is successfully established, go to the [Users] tab
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]
On the next screen ensure that you are selecting the right [Gateway connection]
Then turn on the [Scheduled refresh] option to ensure that you 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:
Once you click on [Apply] you should be able to see a success message
You can also now test the manual refresh of the dataset
***
NIFTIT is a development and design agency in New York, Vietnam & Hong Kong. We are dedicated to empowering businesses of all sizes and non-profit organizations through nifty solutions. Our team develops customized solutions according to the best business practices in the industry. In our weekly blog, we provides the latest news and useful technical blogs about SharePoint, Office 365 and Power BI. Don’t hesitate to subscribe to our newsletter!
[Read more: power bi review: how power bi save your time on reports]
If you find these sites useful, please bookmark this article!
