Edit

Enabling DirectQuery for an ODBC-based connector

  • Article

Using M's built-in Odbc.DataSource function is the recommended way to create custom connectors for data sources that have an existing ODBC driver and/or support a SQL query syntax. Wrapping the Odbc.DataSource function allows your connector to inherit default query folding behavior based on the capabilities reported by your driver. This will enable the M engine to generate SQL statements based on filters and other transformations defined by the user within the Power Query experience, without having to provide this logic within the connector itself.

ODBC extensions can optionally enable DirectQuery mode, allowing Power BI to dynamically generate queries at runtime without pre-caching the user's data model.

Note

Enabling DirectQuery support raises the difficulty and complexity level of your connector. When DirectQuery is enabled, Power BI prevents the M engine from compensating for operations that can't be fully pushed to the underlying data source.

This article assumes familiarity with the creation of a basic custom connector.

Refer to the SqlODBC sample for most of the code examples in the following sections. Other samples can be found in the ODBC samples directory.

ODBC extensibility functions

The M engine provides two ODBC-related data source functions: Odbc.DataSource, and Odbc.Query.

The Odbc.DataSource function provides a default navigation table with all databases, tables, and views from your system. This function also supports query folding, and allows for a range of customization options. Most ODBC-based extensions use this function as their primary extensibility function. The function accepts two arguments—a connection string, and an options record to provide behavior overrides.

The Odbc.Query function allows you to execute SQL statements through an ODBC driver. It acts as a passthrough for query execution. Unlike the Odbc.DataSource function, it doesn't provide query folding functionality, and requires that SQL queries be provided by the connector (or end user). When building a custom connector, this function is typically used internally to run queries to retrieve metadata that might not be exposed through regular ODBC channels. The function accepts two arguments—a connection string, and an SQL query.

Data source function guidelines

Custom connectors can accept any number of function arguments. But to remain consistent with the built-in data source functions shipped with Power Query, the following guidelines are recommended:

  • Require the minimal set of parameters used to establish a connection to your server. The fewer parameters end users need to provide, the easier your connector will be to use.

  • Although you can define parameters with a fixed number of values (that is, a dropdown list in the UI), parameters are entered before the user is authenticated. Any values that can be discovered programmatically after the user is authenticated (such as catalog or database name) should be selectable through the Navigator. The default behavior for the Odbc.DataSource function is to return a hierarchical navigation table consisting of Catalog (Database), Schema, and Table names. However, this behavior can be overridden within your connector.

  • If you feel your users will typically know what values to enter for items they would select from the Navigator (such as the database name), make these parameters optional. Parameters that can be discovered programmatically shouldn't be made required.

  • The last parameter for your function should be an optional record called "options". This parameter typically allows advanced users to set common ODBC-related properties (such as CommandTimeout), set behavior overrides specific to your connector, and allows for future extensibility without impacting backwards compatibility for your function.

  • Security/credential related arguments MUST never be part of your data source function parameters, as values entered in the connect dialog will be persisted to the user's query. Credential-related parameters should be specified as part of the connector's supported Authentication methods.

By default, all required parameters for your data source function are factored into the Data Source Path value used to identify user credentials.

While the UI for the built-in Odbc.DataSource function provides a dropdown that allows the user to select a DSN, this functionality isn't available through extensibility. If your data source configuration is complex enough to require a fully customizable configuration dialog, we recommend that you require your end users to pre-configure a system DSN, and have your function take in the DSN name as a text field.

Next steps