Power BI live connection to PostgreSQL

4
votes

Can someone give me some options about how I can connect a PostgreSQL database to Power BI?

Right now, I used the Power BI Desktop and drivers to connect to my local database. I then published the data to Power BI for users to access and set up a daily refresh schedule with a Personal Gateway installed. This worked fine.

My issue is that my users now want refreshes every 30 minutes instead of daily and Power BI only allows 8 refreshes per day. This seems like it would require a live connection. My only Windows machine is quite weak and I live across the world from my end-users, so my only option is to set up a remote server.

  • I have an Azure Linux VM which I would prefer to use, but Power BI does not work on Linux as far as I can tell
  • My ETL pipelines and database are all based on PostgreSQL and I do not want to switch over to MS SQL or the Azure database product, if I can avoid it

Should I create a Windows-based VM on Azure and install PostgreSQL there and then replicate the required tables for Power BI to visualize? What is the best set up? I did not see any option on the Power BI website to connect live to Postgres so I am a bit concerned.

3
postgresqlazurepowerbi

3 Answers

3
votes

This is an old question, so you've probably figured out a workaround, but just to confirm:

No, Power BI does not offer a live connection to PostgreSQL at the moment. You can see the current list of what Power BI does live connect to here: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#live-connections-and-directquery-to-on-premises-data-sources

If a live connection to PostgreSQL is important to you, I would recommend posting an idea at https://ideas.powerbi.com/ (or up-voting someone else's idea - though I don't see one right now). Microsoft does review these ideas. I'd also recommend sharing the link here, so others searching for how to do this can up-vote the same idea.

In the meantime, a couple of different workarounds:

  1. Even though you can't automate refreshes as often as you'd like, you can do additional manual refreshes. You can initiate the refresh yourself, or you can suggest end-users click the refresh button to get the latest data.

  2. If you don't want to manually refresh, you could look into a 3rd party tool such as Power Update (http://poweronbi.com/power-update-features/). I've never used it before, but it can refresh a Power BI Desktop file and publish it up to the service. This would have the same effect as a manual refresh, but automated.

Note: This question was also asked (and answered) here: https://community.powerbi.com/t5/Integrations-with-Files-and/DirectQuery-for-PostgreSQL-Gateways-on-Linux/td-p/103418.

3
votes

Since the august release 2019 of power BI there is now a directquery connection for postgres.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2019-feature-summary/

1
votes

For any future viewers of this question - I'm working on building and maintaining a custom connector for exactly this purpose. So far I've been able to access most features except those which require datetime adds or diffs. We do have this working in our production environment w/ Postgres 11 via an enterprise gateway.

Repo: https://github.com/sgoley/DirectQuery-for-ODBC-in-PowerBI

Please feel free to reach out to me if you'd like to help me resolve any outstanding bugs remaining or just learn more.

A how-to is available at medium here: https://medium.com/just-readr-the-instructions/directquery-with-postgres-from-powerbi-desktop-f3d8c4dc5e15

Edit: As of August 2019 release, PowerBI will be supporting Direct Query in the native PostgreSQL connector: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2019-feature-summary/#postgresql