How to configure an ODBC Datasource to PowerBI with CData ODBC Driver?

How to configure an ODBC Datasource to PowerBI with CData ODBC Driver?

FIRST CREATED ON 20 Mar 2025  I  AUTHOR Emma Camacho


CONNECTION STRING
For this Connection String, you can start with this example, but you can add more settings.
Driver={CData ODBC Driver for Microsoft Power BI XMLA};AuthScheme=AzureServicePrincipal;AzureTenant=<tenant id>;OAuthGrantType=CLIENT;OAuthClientId=<client id>;OAuthClientSecret=<secret>;Workspace=<workspace>;

FIELD QUERY SQL
For this field, try the following query and add the where clause you need:
SELECT distinct ColumnName as COLUMN_NAME, TableName as TABLE_NAME FROM sys_tablecolumns

TABLE QUERY SQL
For this field, try the following query and add the where clause you need:
SELECT distinct TableName as TABLE_NAME FROM sys_tables

VIEW QUERY SQL


Steps to get the following items <tenant id> <client id> <secret>
1) Add a new registration in portal.azure.com: App registrations - Microsoft Azure

2) In the App Overview, note the Application (client) ID as <client id> and Directory (tenant) ID as <tenant id>. In this example, the name of the App is PowerBI ODBC.

3) Create a secret

4) Note the Value as it will be available just once. This is the item <secret>.


Steps to find the item <workspace> and to grant access to the previously created App.
1) Log in to https://app.powerbi.com. 

2) From the list of Workspaces, set the item <workspace> to the one M&D should connect to. Create a M&D Datasource for each Workspace, if M&D should connect to more Workspaces.

3) In the Workspace(s), press Manage Access


4) Press +Add people or groups

5) Find the name of the App previously created, in this example, the name of the App is PowerBI ODBC and it add as a Member.


For more in information, see 

Info
Looking for Additional Guidance?
If you require more detailed information or further assistance, please visit our comprehensive Online Documentation. Our resources are designed to help you navigate all features and functionalities effectively.