Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

ray_ux

Setting up scheduled refresh - PostgresSQL

As you may know, there are a lot of issues with the Postgres database and Power BI online service, even if it is working properly on the Power BI Desktop. I feel there is a need to write a blog for this as many users encountered similar issues in the community and there isn’t a clear way to fix it. Hopefully, this blog should solve all the problems you have with the Postgres database.

Common problems

  • Error when processing the data in the dataset
  • Remote certificate invalid
  • Scheduled refresh is disabled because at least one data source is missing credentials.
  • No password has been provided in the backend.
  • Missing npqsql driver.

ray_wu_1-1659094578313.png

All these errors linked to the same problem because of encrypt connection and disabling this is in Power BI online service would not work, we need to install on premise connection gateway. Also, personal mode gate does not work with Postgres validation.

 

Set up

Firstly, we need to install the followings:

1) Premise gateway for Power BI.

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

 

You will need to sign into your email address/organisation to login.

ray_wu_2-1659094647779.png

 

2) Npgsql v4.0.10

 

https://github.com/npgsql/npgsql/releases/tag/v4.0.10

 

Make sure to install GAC installation otherwise it would not work.

ray_wu_3-1659094728799.png

 

If you are using this in an organisation, then it is most likely to be blocked. You will need to open the power shell and run as admin. Cd to the file location and run the command to execute the installer.

 

 

 

 

 

 .\Npgsql-4.0.10.msi

 

 

 

 

 


This will allow you to run the setup without it being blocked.

 

Now to configure for Power BI Online service.

 

3) Power BI Online Service - Manage gateways

 

If you have set up the on-premise gateway, you should be able to see it in the gateway management.  Now you need to add information to the server, credentials and unencrypt connection. You may see the other postgres gateways on the list but those ones do not work, which is why we needed the on-premise gateway in order to get the credentials working properly. 

ray_wu_4-1659095077157.png4) Navigate to the dataset source you want to refresh and right click setting

ray_wu_5-1659095110591.png

 

Make sure to select the gateways name you have chosen in the dropdown and confirm.


Note: You do not need to configure the data source credentials on this part because we have already done it through gateway management. Most people tried to configure the data source in this page setting without going through the gateway management which led to problems.

 

5) Scheduled refresh

 

Now we should have this option enabled.

ray_wu_6-1659095417081.png

 

6) Fully working 😊

 

ray_wu_7-1659095446410.png