Enable Power BI DirectQuery for ODBC: Solving the Live Data Problem

Introduction

Power BI native ODBC connectors do not support DirectQuery. This significant platform limitation forces users into Import mode, preventing real-time data access and causing refresh delays for critical business dashboards. If you have been searching for a “Power BI ODBC DirectQuery workaround” or a way to “enable DirectQuery for custom ODBC drivers,” this guide provides the exact steps to bridge that gap.

The Industry Challenge: Standard Power BI ODBC settings lack the DirectQuery option.

The Problem: Why “Import Mode” Fails Modern Data Teams

While the native Microsoft connector is useful for simple tasks, its lack of DirectQuery support creates several enterprise-level issues:

• Stale Data: There is no true real-time reporting, as data is only as fresh as your last refresh.
• Memory Constraints: Large datasets quickly bloat the Power BI model and increase memory usage.
• Refresh Overhead: Frequent changes in underlying data require complex and constant refresh schedules.
• API Lag: Data from cloud apps like Jira, SharePoint, or OneDrive becomes outdated between scheduled updates.

The Solution: ZappySys DirectQuery ODBC Connector

To fix this widespread industry problem, you can use the ZappySys DirectQuery ODBC Connector (a specialized custom Power BI connector). This tool allows you to treat any ODBC source as a live connection, unlocking real-time analytics for databases, APIs, and cloud apps.

Advanced Capabilities:

• Enable DirectQuery mode for virtually any 64-bit ODBC source.
• Query live data directly without importing massive datasets into Power BI.
• Seamlessly integrate with the ZappySys ODBC PowerPack for API and JSON connectivity.

Step 1: Install the ZappySys Connector

1. First, download and install the ZappySys ODBC PowerPack.
   This is essential for those using ZappySys drivers (e.g. REST API, JSON, JDBC Bridge (e.g. Trino, Infor, Athena) and more).
2. Download the Power BI Connector file: ZappySys-DirectQuery-ODBC-Connector.zip
3. Copy the extracted .mez file to <your-profile>\Documents\Power BI Desktop\Custom Connectors: (See next section to find exact path)
   
   Examples: C:\Users\<YourUsername>\Documents\Power BI Desktop\Custom Connectors C:\Users\<YourUsername>\OneDrive - <your-company>\Documents\Power BI Desktop\Custom Connectors

   1 2 3 4

   Examples:   C:\Users\<YourUsername>\Documents\Power BI Desktop\Custom Connectors C:\Users\<YourUsername>\OneDrive - <your-company>\Documents\Power BI Desktop\Custom Connectors

   
   Note: If the Custom Connectors folder does not exist, you must create it manually.For finding exact connector folder path see Microsoft Connector Extensibility documentation.

How to find the Custom Connector folder Path for Power BI

1. Type PowerShell on command line.
2. Run the lines below to print the base path and the full path. You can run one after another or paste both lines.
   
   [Environment]::GetFolderPath('MyDocuments') Join-Path ([Environment]::GetFolderPath('MyDocuments')) 'Microsoft Power BI Desktop\Custom Connectors'

   1 2	[Environment]::GetFolderPath('MyDocuments') Join-Path ([Environment]::GetFolderPath('MyDocuments')) 'Microsoft Power BI Desktop\Custom Connectors'

Step 2: Update Power BI Security for Custom Connectors

1. Open Power BI Desktop.
2. Navigate to File > Options and settings > Options > Security.
3. Under Data Extensions, enable the option “Allow any extension to load without validation”.
4. Save your changes and restart Power BI Desktop to apply the new security policy.

Power BI options security settings to enable custom connectors

Step 3: Set Up a 64-bit ODBC DSN

1. Open the ODBC Data Sources (64-bit) administrator.
2. Click the System DSN tab and select Add.
3. Choose your driver (for example, the ZappySys ODBC Driver for API/Jira/SharePoint).
4. Save the DSN with a clear name, such as MyZappyDSN.

Important Architecture Note: Power BI Desktop and the Gateway require a 64-bit DSN; 32-bit DSNs are not supported for this workflow.

Windows 64-bit ODBC DSN Configuration.

Step 4: Connecting for Live Data / DirectQuery Support

1. In Power BI Desktop, go to Get Data > More...
2. Search for “zappysys” in the list.
3. Select ZappySys ODBC Connector (For DirectQuery Support).
4. Provide your DSN name (MyZappyDSN) or a full connection string.
5. Select DirectQuery mode and click connect.
   

   ZappySys ODBC Connector – To enable DirectQuery option for ODBC Datasource (Custom Install required)

Note for PowerPack Users: You can use a full connection string (e.g., Driver={ZappySys ODBC Driver};Host=...;) by using the “Copy Settings” feature within the ZappySys Driver UI. Read more how to copy full connection string

Power BI Get Data showing ZappySys DirectQuery ODBC Connector selected

Choosing Your Selection Mode

When the navigator opens, you must choose how to fetch your data:

• Table Mode: Best for simple datasets where you want to select a table from a list.
• Query Mode (SQL): Recommended for performance. Writing a custom SQL query allows you to filter and join data at the source, which is much faster for DirectQuery.

Step 5: Verify Your DirectQuery Connection (Final Result)

Once connected, it is crucial to verify that the report is truly “Live.” A successful connection will change the behavior of Power BI Desktop.

Power BI Test Dashboard showing Storage Mode: DirectQuery

Step 6: Power BI Service & Gateway Configuration

Once your report is built, you must configure the On-premises Data Gateway to maintain the live connection in the cloud:

1. Place the .mez file in the custom connectors folder on the gateway machine.
2. Edit the gateway configuration file located at:
   
   C:\Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

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

3. Add the following setting inside the <appSettings> block:
   
   <setting name="EnableCustomConnectors" serializeAs="String"> <value>true</value> </setting>

   1 2 3

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

4. Restart the gateway service.
5. Create an identical 64-bit DSN on the gateway server and map the dataset in Power BI Service.

FAQ: Frequently Asked Questions

Does the native Microsoft ODBC connector support DirectQuery?

No. The native Microsoft ODBC connector is limited to Import mode only.

How can I enable DirectQuery for ODBC sources in Power BI?

By using the ZappySys DirectQuery ODBC Connector and following the custom extension setup outlined in this guide.

Where is the ZappySys DirectQuery connector download?

You can find the latest version here: ZappySys DirectQuery Connector ZIP.

Troubleshooting Checklist

• Connector Missing: Ensure “Allow any extension” is checked in Power BI security settings.
• Gateway Errors: Verify the DSN name is identical on both the local PC and the gateway server.
• 32-bit vs 64-bit: Ensure you are using a 64-bit System DSN; 32-bit will cause connection failures.
• Driver Installation: Ensure the ZappySys ODBC driver is installed on all machines where the report or gateway runs.