DQ with Enterprise Gateway for PostgreSQL

DQ with Enterprise Gateway for PostgreSQL

July 27, 2019
directquery, enterprisegateway, gateway, customconnector, odbc, analytics

Table of Contents #

  1. TLDR
  3. Installing and Managing a Personal vs. Enterprise Gateway
  4. Direct Query / Custom Connector settings for Custom Connectors
  5. PowerShell Module
  6. Service Setup


This is to establish how to take the connector that you setup in my previous post: [DirectQuery Postgres How-To] ( /posts/directquery-postgres-howto/)

And utilize that service to run on an enterprise gateway. I will be repeating a few steps from that post because I want this to be it’s own “standalone” guide.

  1. Create ODBC Datasource
  2. Install Enterprise Gateway
  3. Set additional settings on Gateway
  4. Rewrite queries to directquery
  5. Resolve DSN credentials on PowerBI Service


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 ODBC MSI! 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 gateway setup.

Installing and Managing a Personal vs. Enterprise Gateway #

A couple important notes about Gateways within the PowerBI / PowerApps service:

  • Gateways all operate in a “cluster”.
  • Clusters are composed of an “anchor” or the first gateway installed for a new cluster and then every successive gateway joined to that cluster is basically a “failover” for the anchor.
  • At the time, it is not possible to change the anchor gateway after creation without removing all gateways from the cluster and then the anchor itself last (which destroys the cluster). This information is not available in the docs - only learned about it through opening this ticket on Github: https://github.com/MicrosoftDocs/powerbi-docs/issues/1005

With that out of the way, let’s go through the setup process for our new clusters' anchor.

Retrieve your On-Premise Enterprise Gateway installer here https://www.microsoft.com/en-us/download/details.aspx?id=53127

Double check that this download does not say “(personal mode)” anywhere during the installation! That version of the On-Premise Gateway will not function with the rest of the instructions!

Additionally, there is no 32-bit version of the gateway available and as a consequence, you will not need to look for an x64 version.

Then, when you launch the installer and after you go through the Oauth dialog signing in with your integration user credentials if you have one.

From here - we go into the following decision tree. Generally, if this is your first installation, you will want to create a new gateway and a new cluster.

Gateway Decsion Tree

In any case - keep your recovery key.

After installation and registration you will be presented with the following:

Gateway Setup and Registered


All of the above is available in many other resources minus some details - let’s get to the meat of this article:

Direct Query / Custom Connector settings for Custom Connectors #

Now, on the left are two tabs which are of primary interest to us if you want to implement DirectQuery:

Service Account - this governs what services your Gateways will have access to - in this case we want to use LocalSystem in order to access the System DSN we setup earlier

Gateway Service Account

If the running user is not LocalSystem, you will need to do to launch the services application as an administator:


from there - find the service called “On-Premise Gateway Service”

On-Prem Service

Change the Log On User to be the LocalSystem account:


Save all that and close out.

When you relaunch and sign into your Gateway - you should now see it running as the LocalSystem user.


Connectors - here we will pick a path which is accessible from the LocalSystem account.

Gateway Connectors

In this case it is just my user Documents/Power BI Desktop/Custom Connectors directory - but I will need to set the “Local Service” user to have full control permissions on the directory.

Folder Permissions

When that is complete, you should see any mez files appear within the Custom Connectors dialog of your gateway. Great! That’s step two down.

Powershell module #

A couple quick notes here about managing gateways with Powershell.

There is some management capability through the PowerShell module that is located in the On-Premise Gateway installation directory but there is no support for transferring a gateway between clusters in a single command or updating a gateway to the anchor status.

Sources: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-powershell-support


Service Setup #

Now, you have your gateway and your custom connector with DirectQuery everything is golden right? Wrong. You will have to rebuild all your Queries since you cannot convert from Import mode to DirectQuery.

So go do that on your own time. Done? Great.

Now, you will most likely face a number of tricky source issues when publishing to your PowerBI Service Environment.

First - when you publish, check on your dataset in the PowerBI service for the report that you just published to your workspace:

PowerBI Service Dataset

Then, jumping into the settings:

Dataset Settings

Most likely you will see an error in the gateway details area which will prompt you on the right to read an error message or add the data source to the gateway.

Dataset Error

If you don’t, great!

Still, dig into the the “Manage Gateways” button:

Manage Gateway

And make sure that your DSN has been accurately populated with the DirectODBC connector as the Data Source Type and that you are able to get a successful connection after entering your credentials!

Dataset DSN Management

If you have additional issues beyond that, please reach out. I look forward to making additional contributions from here but need more feedback from you, my users on where to go next.