DQ with Postgres from PowerBI Desktop

DQ with Postgres from PowerBI Desktop

June 15, 2019
directquery, postgres, customconnector, odbc, analytics

Table of Contents #

  1. TLDR
  2. Postgres DSN
  3. Custom Connector for ODBC DirectQuery
  4. Enterprise Gateway

** Safety Briefing, this being a PowerBI custom connector, this is entirely focused on a Windows centric walkthrough as I’m not currently working with either a Linux or Mac OS environment. Not discriminating, just not available to me at this time. **


I was recently put in the situation of finding a way to reduce refresh time on a PowerBi embedded instance. My first approach was to try and use a PowerApps dataset refresh which you can read more about here: [PowerApps Refresh Attempt] ( /posts/powerbi-dataset-refresh-from-report/)

After that failure I was surprised to come across a semi-functional perhaps early attempt at a connector on github! PowerBI Dataset Refresh from Report

While it would not work with my particular database (missing SSL mode handling) or on our enterprise gateway (no test method), a little reading made me confident enough to give fixing those issues an attempt. How little I was prepared for the next week of trial and error which has produced the following MVP.

Postgres DSN #

First steps: This being an ODBC data source, you will need to build a compatible data source in your ODBC Data Source Administrator.

To do that, please ensure that you have the latest Postgres provided driver installed. Available here: Postgresql.org! I would generally recommend using the 64 bit version as well.

Then, create a new System DSN using your Postgres Unicode Driver:

Driver Select

Next, enrich with all the relevant details:

Source Select

(noting that you will not be able to change the DSN Name after it’s original setup)

Finishing with a test to ensure connectivity. Once that has been saved and is complete, we’re ready to jump into the connector setup.

Custom Connector for ODBC DirectQuery #

This walkthrough focusing on the Postgres distro, you will still utilize the most recent version of the connector distribution - currently available as part of the repo here: DirectQuery for ODBC in PowerBI and download, then unzip the repo.

Of primary interest to you is the .mez connector file located at within the repo at “ODBCPostgresDirect\bin\Debug”. This is the current build of the custom connector file.

In case you are needing to use one of the other branches (SQL Server or MySQL) just follow those directory trees instead. Example: “ODBCMySQLDirect\bin\Debug”.

This will need to be placed into your PowerBI Custom Connector directory for local development. If you used the default powerbi installation values, this would be:

cd "C:\Users\<yourusername>\Documents\Power BI Desktop\Custom Connectors"

From here, you can launch or relaunch PowerBI if you already had it open and accept the permissions screen that will notify you that third party connectors are not endorsed by Microsoft and you use at your own risk.

You’re now ready to go!

Unfortunately, there is no way to convert Import based tables to DirectQuery so any tables in that format will have to be recreated. Also, there are a number of transformations that will be unavailable in the query editor if you

Using with an Enterprise Gateway #

If you are interested in using this with the PowerBI Gateway, I’ll be covering this in an additional post so please, leave a comment and let me know you’re interested!