ODBC Custom Connector Dialect

[BL123456]

Hello community!

I recently implemented a custom connector based on ODBC to enable direct query mode.

Is there a way to provide a dialect to the connector that would tell PowerBI how to aggregate/run specific logic?

This is currently available in Tableau, and such ability could greatly help us.

Alternatively, how can I control/adjust the way PowerBI builds the queries when using different visualizations/widgets? 

[BL123456]

Hi @Anonymous 

Huge thanks for your reply!

Is there any other way I can adjust the way PowerBI converts a DAX query to my ODBC database query? Could you also add a few words or direct me to the right documentation/article explaining how the translation works behind the scenes?

Thank you for your help in advance

[Anonymous · Accepted solution]

Hi @BL123456 ,

In Power BI, there isn’t a direct way to provide a dialect to a custom connector like you can in Tableau. However, Power BI provides several ways to aggregate or transform data.

Custom SQL Queries: You can write custom SQL queries in Power BI to control the data retrieval process. This can be done in the SQL Server Connector.

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

DAX Measures and Calculated Columns: Using Data Analysis Expressions (DAX), you can create custom measures and calculated columns to control how data is aggregated and displayed.

Measures in Power BI Desktop - Power BI | Microsoft Learn

Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn

Query Folding: Ensure that your custom connector supports query folding. Query folding is the process where Power BI translates its internal queries into the native queries of the data source. This can help optimize performance and ensure that the logic is pushed down to the data source.

Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn

Power Query M Language: You can use the M language in Power Query to transform and shape your data before it is loaded into Power BI. This allows you to apply specific logic and transformations at the data retrieval stage.

While these methods don’t provide a direct dialect feature like Tableau, they offer flexibility in controlling and customizing the data retrieval and visualization process in Power BI.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.