Gist 6bae1006f0a86eb81bd0914e96710170

Power BI desktop tabular mining model

This example demonstrates a Power BI Desktop tabular mining model, exposing the structure of Data Mining Extensions (DMX) schema queries used to retrieve model metadata. The visualization shows how the model defines expressions, variations, sets, and extended properties, with the underlying code failing to access the referenced mining model due to permission errors. The implementation relies on Power BI’s tabular engine and DAX, while directly embedding DMX queries like `select* from $tmschema_expressions` to inspect the model’s internal schema. The rendering approach is native to Power BI’s table visual, displaying the raw query results and error states without external libraries such as D3 or Three.js, focusing instead on the interaction between DAX and the mining model’s metadata layer.

69% match

PBIX deconstruction of Power BI files

This example deconstructs a Power BI (.PBIX) file by parsing its internal JSON layout and data transformation metadata, then reconstructing the report page as an interactive scatter plot. The visualization parses the file’s sections, visual containers, and query definitions, extracting coordinates, dimensions, and the visual’s configuration (including projections and aggregation) to determine plot placement. The scatter plot maps the X and Y axes to count aggregations of fields from Query1, with categories and series driven by the Z field and its clustered groups; the accompanying structure mirrors the source’s raw layout JSON, highlighting how Power BI’s internal model maps to rendered elements. The result is a deconstructed view of a Power BI report, illustrating the relationship between the report's serialized metadata and the visual output.

68% match

Gist 44e95c5ae2e212c1b41485ea7e31e0c4

This example shows a Power Query function that connects to an embedded Power BI Desktop tabular model and retrieves session information from the `$SYSTEM.DISCOVER_SESSIONS` dynamic management view. The function first locates the most recently accessed user profile folder, reads the `msmdsrv.port.txt` file to obtain the local port number, then uses that port to list available databases via the `AnalysisServices.Databases` API. It selects the first database and executes the provided DAX query using `AnalysisServices.Database`. The code demonstrates dynamic file system traversal, port discovery, and parameterized Analysis Services queries within the Power Query M language.

66% match

Gist 1ebcc44e40d3ea92b4063ad981cac31f

This example shows how to extract and prepare query log data from a local Power BI Tabular model for further analysis. It demonstrates reading the most recent `msmdsrv.port.txt` file from the Power BI Desktop Analysis Services workspace folder to obtain the active port number. The code then uses the `AnalysisServices.Database` function to connect to that port, retrieves the database catalog name, and evaluates the `QueryLog` table. Finally, it renames two columns—`SESSION_LAST_COMMAND_START_TIME` and `SESSION_LAST_COMMAND`—to clean the schema for downstream use in Power Query.

66% match

sqlite query to surface the relationships from metadata.sqlite in PowerBI

This example visualizes the relationships between tables in a PowerBI data model by querying the metadata.sqlite database. It shows a table-level schema diagram where columns from different tables are connected based on foreign key relationships, with cardinality information included. The code uses SQLite queries through the PowerBI metadata store, joining the `relationship`, `table`, and `column` tables to extract source and target table/column pairs. The visualization renders the resulting graph structure, highlighting how tables interconnect through their columns. The implementation relies on standard SQL joins and explicit column selections, with the final query outputting the relationship endpoints and cardinalities for a clear overview of the model's data flow.

ABF structure of files located under a datamodel file in PBIX. #PowerBI #tabular

This visualization dissects the internal file structure of a Power BI PBIX datamodel, mapping the nested folders and files that store tabular data. It uses a custom tree layout to show how table, column, partition, and hierarchy objects are encoded on disk, with distinct node types for `.tbl`, `.prt`, `.idf`, and `.hidx` files. The rendering relies on D3’s hierarchical layout and `d3.forceSimulation` for node positioning, while color-coding and icons differentiate file categories. The source code parses the gist’s markdown specification to build the hierarchy, illustrating the relationship between human-readable names and system-generated hashed identifiers.

Gist f90e0dcc12099646652c5dcbc8400a24

This example visualizes the live query log of a Power BI tabular model by monitoring the Analysis Services workspace’s in-memory engine. It shows the most recent command and its start time, appending each new entry to a running history. The code uses the Power Query M language, calling `AnalysisServices.Database` to query the `$SYSTEM.DISCOVER_SESSIONS` DMV and `Folder.Files` to locate the `msmdsrv.port.txt` file that defines the dynamic port. The rendered output is a table combining previous and new log records via `Table.Combine`, with no external charting library involved.

Extracting Power Query queries

This example shows how to extract Power Query query definitions from an embedded Power BI tabular model by reading the model’s in-memory connection string. The PowerShell script first locates the dynamic port number for the local Analysis Services instance by scanning workspace files for `msmdsrv.port.txt`. It then uses the `Invoke-ASCmd` cmdlet to query the `$SYSTEM.TMSCHEMA_DATA_SOURCES` schema, revealing the connection string. The script decodes the base64-encoded `Mashup` portion of that string, writes it to a binary ZIP archive, and uses `System.IO.Compression.ZipFile` to extract `Section1.m`, which contains the readable Power Query formulas. This approach exposes the model’s underlying data-transformation logic without any direct UI interaction.