DuckDB extension for querying Power BI Semantic Models with DAX.
Maintainer(s):
crazy-treyn
Installing and Loading
INSTALL pbi_scanner FROM community;
LOAD pbi_scanner;
Example
INSTALL pbi_scanner FROM community;
LOAD pbi_scanner;
SET pbi_scanner_auth_mode = 'azure_cli';
SELECT *
FROM dax_query(
'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;',
'EVALUATE TOPN(500, FactSales)'
);
About pbi_scanner
Query Power BI Semantic Models from DuckDB using DAX via XMLA endpoint.
- The
dax_queryfunction executes DAX and returns results as DuckDB tables. pbi_tables,pbi_columns,pbi_measures, andpbi_relationshipsexpose INFO.VIEW metadata results for model exploration.
The easiest way to get started is Azure CLI auth (az login) with SET pbi_scanner_auth_mode = 'azure_cli', but Azure CLI is not required.
You may also use the DuckDB azure extension to create an Azure secret. More details and examples are in the pbi_scanner repository.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| dax_query | table | Executes a DAX query against a Power BI Semantic Model and returns the result as a DuckDB table. | Supports named authentication parameters and DuckDB azure secret auth via secret_name or Secret= in the connection string. | [SELECT * FROM dax_query('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;','EVALUATE TOPN(500, FactSales)');] |
| pbi_tables | table | Returns semantic model table metadata via INFO.VIEW.TABLES(). | Uses the same named parameters as dax_query (auth_mode, access_token, secret_name, tenant_id, client_id, client_secret, effective_user_name, timeout_ms). | [SELECT * FROM pbi_tables('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');] |
| pbi_columns | table | Returns semantic model column metadata via INFO.VIEW.COLUMNS(). | Uses the same named parameters as dax_query (auth_mode, access_token, secret_name, tenant_id, client_id, client_secret, effective_user_name, timeout_ms). | [SELECT * FROM pbi_columns('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');] |
| pbi_measures | table | Returns semantic model measure metadata via INFO.VIEW.MEASURES(). | Uses the same named parameters as dax_query (auth_mode, access_token, secret_name, tenant_id, client_id, client_secret, effective_user_name, timeout_ms). | [SELECT * FROM pbi_measures('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');] |
| pbi_relationships | table | Returns semantic model relationship metadata via INFO.VIEW.RELATIONSHIPS(). | Uses the same named parameters as dax_query (auth_mode, access_token, secret_name, tenant_id, client_id, client_secret, effective_user_name, timeout_ms). | [SELECT * FROM pbi_relationships('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');] |
| __pbi_scanner_test_effective_execution_transport | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_parse_binxml_double | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_coerce_xml_text | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_dax_schema_probe | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_metadata_cache_roundtrip | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_service_principal_error_message | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_parse_chunked_double | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_parse_streaming_sx_double | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_coerce_xml_type | scalar | NULL | NULL | NULL |
| __pbi_scanner_test_parse_binxml_first_text | scalar | NULL | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| pbi_scanner_auth_mode | Default auth mode for pbi_scanner table functions (access_token, azure_cli, service_principal) | VARCHAR | GLOBAL | [] |