PBIXRay Python Library

pbixray is an open source Python library for parsing Power BI .pbix files directly from Python, without Power BI Desktop or a live Analysis Services connection.

It is designed for developers who need to inspect semantic models, extract metadata, read Power Query logic, and work with the internals of PBIX files programmatically. The same API also supports Excel .xlsx files that contain embedded PowerPivot models and Analysis Services backup (.abf) files.

pbixray is read-only: it returns pandas DataFrames and requires no network access and no Power BI or Excel install. File type (PBIX vs XLSX vs ABF) is auto-detected from the file contents — the same API works either way.

Install

pip install pbixray

Quick Start

from pbixray import PBIXRay

model = PBIXRay("path/to/your_report.pbix")

print(model.tables)
print(model.metadata)
print(model.power_query)
print(model.dax_measures)
print(model.relationships)

Supported Inputs

• Power BI .pbix files
• Excel .xlsx files with embedded PowerPivot models
• Analysis Services backup (.abf) files — same data model as a .pbix, just without the zip envelope, so PBIXRay("path/to/backup.abf") works unchanged

Multi-partition tables — both classic SSAS partitioning and incremental-refresh partitions — are decoded in full: get_table() concatenates every partition in storage order.

Large Models (on-disk loading)

By default the entire decompressed data model is held in memory. For models whose uncompressed size approaches or exceeds available RAM, pass on_disk=True: the decompressed data is streamed to a temporary file and memory-mapped, so only the pages a requested table actually touches are faulted in. Use temp_dir to control where the spill file is created (it defaults to the system temp directory).

# Spill to disk + mmap instead of holding everything in RAM.
with PBIXRay("path/to/large.pbix", on_disk=True, temp_dir="/fast/scratch") as model:
    df = model.get_table("Sales")
# leaving the `with` block releases the mapping and removes the temp file

PBIXRay is also a context manager. Calling model.close() (or exiting the with block) deterministically releases the memory map and the metadata connection. When on_disk=False (the default) behavior is unchanged. Metadata (DAX, tmschema_*, etc.) is loaded lazily on first access, so simply opening a file is cheap.

The DataModel member is read in place from the container file whenever it is STORED in the zip (the normal case — it carries its own compression). If the member is additionally uncompressed (a raw ABF backup inside the zip), on_disk=True serves it directly from the .pbix/.xlsx with no temp-file copy at all.

Core Properties

These properties expose the most useful parts of a model as Python values or pandas DataFrames.

Common Examples

List tables

print(model.tables)

Read Power Query / M code

power_query = model.power_query
print(power_query[["TableName", "Expression"]])

Inspect measures

measures = model.dax_measures
print(measures[["TableName", "Name", "Expression"]])

Inspect calculated columns

columns = model.dax_columns
print(columns[["TableName", "ColumnName", "Expression"]])

Inspect relationships

relationships = model.relationships
print(
    relationships[
        [
            "FromTableName",
            "FromColumnName",
            "ToTableName",
            "ToColumnName",
            "Cardinality",
            "IsActive",
        ]
    ]
)

Inspect row-level security

rls = model.rls
print(rls[["RoleName", "TableName", "FilterExpression"]])

Inspect object-level security

ols = model.ols
print(ols[["RoleName", "TableName", "ColumnName", "Scope", "Permission"]])

Each row is one secured object: Scope='Column' rows hide or expose a single column, Scope='Table' rows (where ColumnName is None) a whole table. Permission is None (hidden), Read (visible) or Default. Plain row-level-security rows are excluded — see model.rls.

Inspect perspectives

perspectives = model.perspectives
print(perspectives[["PerspectiveName", "ObjectType", "TableName", "ObjectName"]])

Each row is one object included in a perspective; ObjectType is Table, Column, Measure or Hierarchy, and IncludeAll is populated only for Table rows. This is a friendly roll-up over the raw tmschema_perspective_* endpoints.

Inspect aggregations

aggregations = model.aggregations
print(aggregations)

Each row maps one aggregation-table column (“Manage aggregations”) to a detail (base) table. Summarization is the human label (GroupBy, Sum, Count, Min, Max); DetailColumn is None for the “Count table rows” case.

All three return an empty DataFrame (with the documented columns) on models that don’t use the feature.

Read a table’s contents

sales = model.get_table("Sales")
print(sales.head())

To decode only a subset of columns from a wide table (decoding the others is skipped), pass columns:

sales = model.get_table("Sales", columns=["ProductKey", "Sales"])

With strings_as_categorical=True string columns come back as pd.Categorical, so each distinct value is stored once instead of once per row — a large memory saving on low-cardinality string columns:

sales = model.get_table("Sales", strings_as_categorical=True)

Dictionary decode runs on a native Huffman kernel (xmhuffman) and fans out across cores automatically for large dictionaries.

Stream large tables in chunks

For tables too large to materialize whole, iter_table yields DataFrame chunks instead of one DataFrame. Chunks follow VertiPaq segment boundaries, and chunk_size further splits each segment (chunks never span two segments, so tail chunks may be shorter). String columns default to pd.Categorical, sharing one categories array across all chunks; pass strings_as_categorical=False for plain object-dtype strings.

with PBIXRay("path/to/large.pbix", on_disk=True) as model:
    for chunk in model.iter_table("Sales", chunk_size=1_000_000):
        process(chunk)  # chunk.index is the global row range

The dictionaries of every selected column are decoded up front and kept for the whole iteration, so on dictionary-heavy models (e.g. wide free-text columns) pass columns to project only what you need. Combine with on_disk=True to also keep the decompressed model itself out of RAM.

Inspect data connections

print(model.connections)

Reports expose their Connections manifest — the list of data connections declared by the report — as a list of dictionaries. Self-contained (import) models usually return an empty list.

Power Query from DataMashup

power_query and m_parameters read the M from the Analysis Services metadata, which works for import models. Some models — notably DirectQuery / native SQL — keep their queries and parameters only in the report’s DataMashup part ([MS-QDEFF]). model.data_mashup and model.mashup_queries parse that part directly:

df = model.mashup_queries        # Name, Kind, IsParameter, Expression, Type, DefaultValue, AllowedValues
params = df[df["IsParameter"]]   # the Power Query parameters and their metadata

mashup = model.data_mashup       # None when the file has no DataMashup part
if mashup is not None:
    print(mashup.version)
    for q in mashup.parameters:  # MQuery objects
        print(q.name, q.param_type, q.default_value, q.allowed_values)

data_mashup is None for files without a mashup, and raises DataMashupError if the part is malformed. These accessors are additive — power_query and m_parameters keep their existing AS-metadata behavior.

Live-Connection (Thin) Reports

Some .pbix files are thin reports with no embedded model: they live-connect to an external Analysis Services server (analysisServicesDatabaseLive) or a Power BI Service dataset (pbiServiceLive). Because the model lives on a remote server, there is nothing to extract on disk, and constructing PBIXRay raises LiveConnectionError. The exception carries the parsed connection details so you can still identify what the report points at:

from pbixray import PBIXRay, LiveConnectionError, NoEmbeddedModelError

try:
    model = PBIXRay("thin-report.pbix")
except LiveConnectionError as e:
    print(e.connection_type)   # e.g. 'pbiServiceLive'
    print(e.database_name)     # remote dataset id, when available
    print(e.connections)       # full manifest (list of dicts)

The exception hierarchy is LiveConnectionError → NoEmbeddedModelError → PBIXRayError. NoEmbeddedModelError is raised when a file has no model and no connection manifest. Both also subclass RuntimeError for backward compatibility.

Data Model Details

pbixray can be used both as a quick inspection tool and as a lower-level metadata extraction library.

Use it to:

• enumerate tables and columns in a semantic model
• inspect DAX calculated tables, measures, and calculated columns
• extract Power Query and M parameter definitions
• audit relationships, referential settings, and cross-filtering behavior
• review row-level and object-level security rules
• resolve aggregation mappings and perspective membership
• measure model size and storage statistics

Tabular Model Schema Endpoints

pbixray also exposes direct equivalents of all 40 Analysis Services $System.TMSCHEMA_* DMVs by reading the embedded SQLite metadata database inside the PBIX. These endpoints are PBIX-only — on XLSX files they return empty DataFrames.

TMSCHEMA examples

# List all columns with table names and hidden flags
print(model.tmschema_columns[["TableName", "Name", "DataType", "IsHidden"]])

# Inspect incremental refresh policies
print(model.tmschema_refresh_policies)

# Inspect security role memberships
print(model.tmschema_role_memberships)

PBIX vs XLSX Capability Matrix

Both PBIX and XLSX (PowerPivot) files use the same API, but coverage differs. “Empty” below means a zero-row DataFrame — never None and never an exception.

Notes and Gotchas

• RowNumber is dropped from get_table() output — it is a VertiPaq internal storage position, not user data.
• Row order is storage order, not insertion order. VertiPaq sorts rows by lowest-cardinality columns first for RLE compression. Two calls are stable, but order will differ from a CSV exported from Excel. For row equivalence, compare as multisets: df.sort_values(list(df.columns)).reset_index(drop=True).
• Unknown table name → empty DataFrame, not an exception. Validate against model.tables if you need to detect bad names.
• model.tables returns a numpy array, not a Python list. It iterates fine, but model.tables == [...] won’t work as a plain equality check.
• XLSX calculated columns can have a display name different from the internal storage name (e.g. Category ↔ CalculatedColumn1). pbixray resolves these so schema.ColumnName and get_table() use the display name.
• get_table() materializes the whole table into a DataFrame, so large fact tables can be memory-heavy. Use iter_table() to stream in chunks, plus on_disk=True, the columns argument, and strings_as_categorical=True to keep memory in check.

Out of Scope

pbixray is a read-only data-model extractor. It does not:

• write, modify, or repack PBIX/XLSX files
• evaluate DAX — measure and calculated-column expressions are returned as source text only
• run a query engine (no DAX/MDX/M evaluation against the model)
• connect to Power BI Service, Analysis Services, datasets, gateways, or workspaces
• refresh the model
• parse the report layer (visuals, pages, bookmarks, themes) — data model only
• support .pbit, .pbids, or .pbip formats — only .pbix, .xlsx with an embedded PowerPivot model, and .abf backups

Requirements

• Python 3.8+ (tested through 3.13)
• Runtime dependencies: xpress8, xpress9, xmhuffman, kaitaistruct, numpy, pandas, apsw
• No Power BI Desktop required
• Works on macOS, Linux, and Windows

Links

• GitHub — hugoberry/pbixray
• PyPI — pbixray
• Interactive Demo