pbi_scanner

June 16, 2026 · View on GitHub

DuckDB extension for querying Power BI Semantic Models with DAX.

Experimental: This extension is under active development. APIs, behavior, and performance characteristics may change between releases.

Features

Query semantic models with dax_query(connection_string, dax_text, ...)
Discover model metadata with pbi_tables, pbi_columns, pbi_measures, and pbi_relationships
Multiple auth paths: azure_cli, access_token, and service_principal
DuckDB secret integration via TYPE azure secrets (credential_chain / service_principal)
Power BI locator support (powerbi://...) plus direct XMLA fast path (https://.../xmla?...)
Local metadata cache for resolved targets/schemas (no token/secret persistence)
DuckDB-Wasm build support for wasm_mvp and wasm_eh (see docs/wasm.md)

Quick Start

Prerequisites

DuckDB extension toolchain requirements (CMake, C++ build toolchain, OpenSSL)
Git with submodule support
Azure CLI (az) installed

For browser DuckDB-Wasm usage, use auth_mode := 'access_token' and pass a short-lived token from the host application. Native auth paths such as azure_cli, browser-side service_principal, and Azure credential-chain secrets are intentionally unsupported in browser WASM. See docs/wasm.md for build, testing, CORS/proxy, and browser validation details.

Step 1: Clone and Build

git clone --recurse-submodules https://github.com/<your-org>/pbi_scanner.git
cd pbi_scanner
make
make test

If you already cloned without submodules:

git submodule update --init --recursive

Step 2: Start DuckDB

./build/release/duckdb

The bundled shell already has pbi_scanner linked in.

Step 3: Run a First Query

In the examples below, we use Azure CLI login to authenticate.

az login

Then use one auth option below.

Option A: Azure CLI auth mode

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 ROW("probe_ok", 1)'
);

Option B: Azure secret + `secret_name`

If you already use another DuckDB Azure secret provider, you can use that secret instead of the credential_chain example shown here.

INSTALL azure;
LOAD azure;

CREATE SECRET pbi_cli (
    TYPE azure,
    PROVIDER credential_chain,
    CHAIN 'cli'
);

INSTALL pbi_scanner FROM community;
LOAD pbi_scanner;

SELECT *
FROM dax_query(
    'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;',
    'EVALUATE ROW("probe_ok", 1)',
    secret_name := 'pbi_cli'
);

Replace the workspace and semantic model placeholders with values you can access.

Connection Configuration

Connection String Forms

Power BI Locator (Typical User Input)

Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;

Direct XMLA Endpoint (Advanced / Resolved Path)

Data Source=https://example.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=<resolved-database-id>;Initial Catalog=sobe_wowvirtualserver-<resolved-database-id>;

Use the direct XMLA form when you already have a resolved target and want to bypass public Power BI locator resolution.

Authentication Modes

dax_query(...) supports:

auth_mode := 'azure_cli'
auth_mode := 'access_token'
auth_mode := 'service_principal'

Set a session default when using direct auth modes:

SET pbi_scanner_auth_mode = 'azure_cli';

When both are provided, per-call named auth_mode := ... overrides the session SET value for that call.

DAX column names

By default, dax_query and the pbi_* metadata table functions return XMLA column names unchanged (including DAX table qualifiers and square brackets).

Opt in to normalization when you want DuckDB-friendly names: DAX square brackets are removed and table qualifiers are dropped (Fact[Amount] becomes Amount, [Total Sales] becomes Total Sales). When the same column name appears more than once, qualified names are disambiguated with a table prefix (TableA[Amount] and TableB[Amount] become TableA_Amount and TableB_Amount); any remaining collisions get numeric suffixes (Amount_2, TableA_Amount_2, etc.). DuckDB quotes identifiers with spaces when you reference them in SQL.

Enable normalization for the session:

SET normalize_dax_column_names = true;

Per-call named parameter overrides the session default for that query:

SELECT *
FROM dax_query(
    'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;',
    'EVALUATE ''Fact Allocation''',
    normalize_dax_column_names := true
);

Schema probe row limit

dax_query uses a limited schema probe (TOPN) during bind to discover column types without scanning the full result. Control the row limit with a named parameter or a process-wide environment variable:

SELECT *
FROM dax_query(
    'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;',
    'EVALUATE ''Fact Allocation''',
    schema_probe_rows := 50
);

export PBI_SCANNER_SCHEMA_PROBE_ROWS=50

When unset, the default is 100 rows. Set schema_probe_rows := 0 per query to probe with the full DAX statement when a limited probe is not applicable.

Live check (requires workspace auth in .env or PBI_BENCH_*):

uv run --group bench verify_dax_column_names.py

Quick Start already shows the Azure CLI mode and the Azure secret mode end to end. Use this section as reference for secret variants and other auth modes.

DuckDB Secret-Based Auth

Create a reusable DuckDB Azure secret:

INSTALL azure;
LOAD azure;

CREATE SECRET pbi_cli (
    TYPE azure,
    PROVIDER credential_chain,
    CHAIN 'cli'
);

Use either pattern with dax_query:

SELECT *
FROM dax_query(
    'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;Secret=pbi_cli;',
    'EVALUATE ROW("probe_ok", 1)'
);

Or by named argument:

SELECT *
FROM dax_query(
    'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;',
    'EVALUATE ROW("probe_ok", 1)',
    secret_name := 'pbi_cli'
);

Service principal secret (DuckDB `azure` extension)

For automation or when you use a service principal registered in Microsoft Entra ID, create a TYPE azure secret with PROVIDER service_principal. The extension reads tenant_id, client_id, and client_secret from that DuckDB secret (the same fields you set in SQL as TENANT_ID, CLIENT_ID, and CLIENT_SECRET) and acquires a Power BI API token the same way as auth_mode := 'service_principal' with named parameters.

When you pass secret_name (or Secret= in the connection string), the token comes from the DuckDB secret; session SET pbi_scanner_auth_mode does not override that for that call.

INSTALL azure;
LOAD azure;

CREATE SECRET pbi_sp (
    TYPE azure,
    PROVIDER service_principal,
    TENANT_ID '<tenant-guid>',
    CLIENT_ID '<app-client-id>',
    CLIENT_SECRET '<client-secret>'
);

INSTALL pbi_scanner FROM community;
LOAD pbi_scanner;

SELECT *
FROM dax_query(
    'Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;',
    'EVALUATE ROW("probe_ok", 1)',
    secret_name := 'pbi_sp'
);

For a local CLI smoke test that builds this CREATE SECRET from environment variables, see query_semantic_model_sql_minimal.py (PBI_SQL_USE_AZURE_SECRET=1, PBI_SQL_AZURE_PROVIDER=service_principal, and SP_TENANT_ID / SP_CLIENT_ID / SP_CLIENT_SECRET or the AZURE_* / PBI_XMLA_* fallbacks).

Catalog and Metadata Discovery

Use these helper functions to inspect semantic model structure.

SELECT * FROM pbi_tables('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');
SELECT * FROM pbi_columns('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');
SELECT * FROM pbi_measures('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');
SELECT * FROM pbi_relationships('Data Source=powerbi://api.powerbi.com/v1.0/myorg/Example%20Workspace;Initial Catalog=example_semantic_model;');

Local Extension Artifact Load (Optional)

If using another DuckDB shell, load the built extension explicitly:

duckdb -unsigned -c "LOAD './build/release/extension/pbi_scanner/pbi_scanner.duckdb_extension';"

Windows:

duckdb.exe -unsigned -c "LOAD './build/release/extension/pbi_scanner/pbi_scanner.duckdb_extension';"

Development

Build and Test

make          # release build
make test     # run tests

Other build variants:

make debug
make reldebug
make relassert

Focused offline tests (sqllogictest + Catch unit tests):

make test-pbi-offline
# or individually:
./build/release/test/unittest "test/sql/pbi_scanner.test"
./build/release/pbi_scanner_unit_tests

DuckDB-Wasm validation (requires Emscripten 3.1.64; see docs/wasm.md):

make wasm_eh
uv run test/wasm/run_pbi_wasm_smoke.py --build
uv run test/wasm/run_pbi_wasm_sqllogictest.py --build
# or, after building wasm_eh:
make test-pbi-wasm

Browser smoke proves load, mock XMLA HTTP, and auth-header propagation. Browser sqllogictest runs wasm_sql/pbi_scanner_wasm.test for offline WASM-specific SQL assertions (kept outside test/ so native unittest does not pick it up). Architecture notes: docs/wasm_testing_plan.md.

Quality Checks

make format-check
make tidy-check

Apply formatting fixes:

make format-fix

Local Script Tooling

Run helper scripts with uv:

uv run bench_native_http.py --smoke
uv run --group bench query_semantic_model_minimal.py

Cursor Worktree Setup

If you use Cursor worktrees (/worktree or --worktree), this repo includes .cursor/worktrees.json to bootstrap each new worktree automatically:

Copy .env from the root worktree when present (only if the new worktree does not already have one)
Run git submodule sync --recursive and git submodule update --init --recursive
Fail fast if extension-ci-tools did not populate (so submodule problems are not silent)

Bootstrapping submodules does not run make; build artifacts such as ./build/release/test/unittest appear only after you build in that worktree (for example make / make test).

If setup did not run (or you need to repair a worktree manually), from the worktree root you can run:

bash .cursor/setup-worktree-unix.sh

Platform Notes

macOS

xcode-select --install
brew install cmake openssl
OPENSSL_ROOT_DIR="$(brew --prefix openssl@3)" make
make test

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install -y git build-essential cmake pkg-config libssl-dev
make
make test

Fedora/RHEL equivalent:

sudo dnf install -y git gcc-c++ make cmake pkg-config openssl-devel

Windows

Recommended: WSL2 + Linux steps.

For native Windows builds, bootstrap once, then use the repo wrapper:

.\scripts\dev-win.ps1 bootstrap
.\scripts\dev-win.ps1 build
.\scripts\dev-win.ps1 test -R test/sql/pbi_scanner.test

Command wrapper behavior:

bootstrap installs missing prerequisites with winget (VS Build Tools + C++ workload, Git, CMake), then clones/bootstraps vcpkg into local/vcpkg (or %VCPKG_ROOT% when set).
Uses VsDevCmd.bat from VS 2022 Build Tools first, then VS 2019 Build Tools.
Uses cmake.exe on PATH, else Visual Studio CMake fallback paths.
Configures with repo-safe defaults and vcpkg toolchain integration:
- -DCMAKE_IGNORE_PATH=C:/msys64
- -DCMAKE_TOOLCHAIN_FILE=%VCPKG_ROOT%/scripts/buildsystems/vcpkg.cmake
- -DVCPKG_TARGET_TRIPLET=x64-windows-static-release by default (override with %VCPKG_TARGET_TRIPLET%)
Builds with serialized MSBuild (-- /m:1) to reduce Windows file-lock failures.

Optional launcher:

scripts\dev-win.cmd build
scripts\dev-win.cmd test -R test/sql/pbi_scanner.test

For live local benchmarking on native Windows, prefer the repo-local Python environment:

uv run --group bench python -u query_semantic_model_minimal.py

On Windows, this path uses Azure CLI access-token auth directly to avoid a known crash observed with DuckDB Azure secret credential-chain resolution inside the Python process. The bundled CLI fallback remains available with:

$env:PBI_BENCH_USE_BUNDLED_CLI='1'
uv run --group bench python -u query_semantic_model_minimal.py

If .\scripts\dev-win.ps1 configure reports a missing vcpkg toolchain file, run .\scripts\dev-win.ps1 bootstrap or set %VCPKG_ROOT% to an existing vcpkg checkout.

Troubleshooting

OpenSSL Not Found

macOS: OPENSSL_ROOT_DIR="$(brew --prefix openssl@3)"
Linux: install libssl-dev or openssl-devel
Windows: run .\scripts\dev-win.ps1 bootstrap to install/bootstrap vcpkg

Submodule Errors

git submodule update --init --recursive

Azure CLI Auth Fails

az logout
az login --scope "https://analysis.windows.net/powerbi/api/.default"

Unsigned Extension Load Error

Use the bundled shell (./build/release/duckdb) or load local extension with DuckDB -unsigned.

Limitations

Live authenticated workloads are intentionally excluded from CI tests
Power BI/Azure availability and network quality directly impact query latency
Extension binaries should be rebuilt for each DuckDB version you target
The project is experimental and APIs may evolve

License

This project is licensed under the MIT License. See LICENSE.