How to Use DirectQuery with Power BI ODBC Connector (Workaround Guide)

Announcements ODBC PowerPack
, ,
1

Power BI’s native ODBC connector does not support DirectQuery — it forces you into Import mode only. This guide shows you how to work around that limitation using the ZappySys DirectQuery ODBC Connector, so you can query data in real time without importing it first.

The Problem: Power BI ODBC Doesn’t Support DirectQuery

When you connect to a data source via ODBC in Power BI, you’re locked into Import mode. That means:

  • Data is copied into Power BI’s memory on every refresh
  • Large datasets cause slowdowns and memory pressure
  • You can’t get live, real-time data without scheduling a refresh
  • APIs and frequently changing data sources quickly go stale

If your use case demands real-time reporting — or your dataset is simply too large to import — this is a serious roadblock.

The Fix: ZappySys DirectQuery ODBC Connector

The ZappySys DirectQuery ODBC Connector is a free custom .mez connector that adds DirectQuery support over ODBC. Once installed, you can:

  • Query APIs, databases, and cloud apps in real time via ODBC
  • Avoid storing large datasets inside Power BI
  • Eliminate data duplication between systems
  • Use any ODBC-compatible source (REST APIs, files, databases, etc.)

Step 1: Download and Install the Connector

  1. Download the connector ZIP from:
    https://zappysys.com/downloads/ZappySys-DirectQuery-ODBC-Connector.zip

  2. Extract the .mez file from the ZIP.

  3. Copy the .mez file to your Power BI custom connectors folder:

    C:\Users\<YourUsername>\Documents\Power BI Desktop\Custom Connectors

    Create the Custom Connectors folder if it doesn’t exist.

  4. Open Power BI Desktop and go to:
    File → Options and settings → Options → Security

  5. Under Data Extensions, select Allow any extension to load without validation.

  6. Restart Power BI Desktop.

Step 2: Create an ODBC DSN

Before connecting in Power BI, you need a 64-bit System DSN that points to your data source.

  1. Open ODBC Data Sources (64-bit) from the Windows Start menu.
  2. Go to the System DSN tab and click Add.
  3. Select your ODBC driver (e.g., ZappySys ODBC Driver) and click Finish.
  4. Fill in the connection details and give the DSN a name, for example: MyZappyDSN.
  5. Click OK to save.

Important: You must use the 64-bit ODBC administrator. Using the 32-bit version will cause connection failures in Power BI.

Step 3: Connect Power BI to Your ODBC Source via DirectQuery

  1. In Power BI Desktop, click Get Data.
  2. Search for and select ZappySys DirectQuery ODBC Connector.
  3. When prompted, enter either:
    • Your DSN name: MyZappyDSN
    • Or a full connection string:
      Driver={ZappySys ODBC Driver};Host=...;User=...;Password=...;
  4. Select DirectQuery as the connectivity mode.
  5. Click Connect and choose the tables or queries you want.

Step 4: Configure the On-Premises Data Gateway (For Power BI Service)

If you publish your report to the Power BI Service and need scheduled or on-demand refresh, you must configure the On-Premises Data Gateway as well.

  1. Copy the same .mez file to the gateway machine:

    C:\Users\<GatewayUsername>\Documents\Power BI Desktop\Custom Connectors

  2. Edit the gateway config file at:

    C:\Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

  3. Add the following inside the <appSettings> section:

    <setting name="EnableCustomConnectors" serializeAs="String">
  <value>true</value>
</setting>

  4. Restart the On-Premises Data Gateway service.

  5. Create the same DSN name (MyZappyDSN) on the gateway machine using the 64-bit ODBC administrator.

  6. In Power BI Service, go to your dataset settings and map it to the gateway connection.

When Should You Use This Approach?

This workaround is the right fit when:

  • You need live, real-time data in your reports
  • Your source data is too large to import into Power BI
  • You’re connecting to REST APIs or cloud apps via ODBC
  • You want to avoid duplicating data across systems

If your data changes infrequently and fits comfortably in memory, standard Import mode with scheduled refresh may be simpler.

Known Limitations

This connector is currently in beta, so keep the following in mind:

Limitation Detail
Visual compatibility Some Power BI visuals and transformations may not fully work in DirectQuery mode
DAX restrictions Complex DAX measures may behave differently or be unsupported
Performance Every report interaction sends a live query — slow sources will produce slow reports
Beta status Edge cases and bugs may exist; improvements are actively in progress

Upcoming improvements include better visual support, performance optimizations, and improved stability.

Troubleshooting Common Issues

Symptom Likely Cause Fix
ZappySys DirectQuery ODBC Connector not visible in “Get Data” Custom connectors not enabled, or Power BI not restarted Re-check security settings and restart Power BI
Gateway fails to connect DSN name mismatch between Desktop and Gateway machine Ensure DSN name is identical on both machines
“Driver not found” error ODBC driver not installed Install the ZappySys ODBC driver on the gateway machine
Connection works in Desktop but fails on Service Using 32-bit DSN Recreate the DSN using the 64-bit ODBC administrator