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.
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.
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.
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.
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.
6) Fully working 😊
Be the first to comment