XMLA script to surface all DAX function definitions for SSAS

100%

XMLA script to surface all DAX function definitions for SSAS

✓ Published0🌍 Public

Last edited May 17, 2021

Created on May 17, 2021

This example shows how to surface all DAX function definitions for SQL Server Analysis Services by issuing an XMLA Execute command. The code queries the `$SYSTEM.MDSCHEMA_FUNCTIONS` schema rowset, filtering for rows where `Origin` is 3 or 4, which correspond to DAX functions. It retrieves fields such as function name, parameter info, description, interface name, and library name. The request targets a specific catalog ID and uses the standard XMLA namespace. This is a direct data-extraction approach, not a visualization library, but it produces the raw metadata needed to build any downstream chart or graph of DAX functions.

AI-generated description

Similar vizzes

Loading thumbnail…

XMLA to get schema details for Catalog 8c170566-a8b6-47b4-a6af-8734a8cfa239 Tabular Model

This example demonstrates how to retrieve comprehensive schema metadata for a specific Analysis Services Tabular Model database using XMLA (XML for Analysis) Discover requests. It sends multiple batched `<Discover>` calls to the server, each targeting a different TMSCHEMA request type—such as model, data sources, tables, columns, attribute hierarchies, partitions, relationships, measures, hierarchies, levels, annotations, KPIs, cultures, and object translations—all filtered by the database ID `8c170566-a8b6-47b4-a6af-8734a8cfa239`. The code uses the `Microsoft.AnalysisServices.Xmla` client protocol via the `System.Xml` and `System.Net` namespaces to execute the SOAP-based XMLA batch transaction. Each request returns structured dataset rows that collectively map the model’s entire object graph, enabling programmatic introspection of the tabular schema.

HHugoberry

66% match

Loading thumbnail…

Hidden Tabular SSAS specific DMVs

This visualization exposes the hidden structure of a tabular SSAS (SQL Server Analysis Services) model by issuing a series of `Discover` requests for TMSCHEMA metadata, including models, tables, columns, and measures. The code queries a specific database GUID via XMLA and presents the resulting schema objects as an interactive graph. Using D3.js, the example renders nodes and links that map out relationships between tables, hierarchies, and KPIs, revealing how the model is organized behind the scenes. The visualization relies on D3's force-directed layout to position elements, making the otherwise invisible metadata hierarchy tangible and explorable.

HHugoberry

60% match

Loading thumbnail…

Gist fcc3b4c6bb3d32bd50e83052b67b9e23

The example shows an XMLA (Analysis Services) script that creates a shared expression named “SharedExpression” in a tabular model database. The file, `CreateMExpression.xmla`, uses the `Create` command with a `Batch` transaction to define the expression’s metadata, including its `Name` and `Kind` fields. The expression’s `Expression` property is set to `#shared`, which references Power Query’s shared M function namespace. The code relies on the XMLA protocol for Analysis Services, specifically the `http://schemas.microsoft.com/analysisservices/2014/engine` schema, and includes a rowset definition for the expression’s properties. No data source or rendering is involved; it is a metadata definition for tabular modeling.

HHugoberry

59% match

Loading thumbnail…

PowerQuery an alternative to opening XML files

This example demonstrates how PowerQuery can serve as an alternative to manually opening and parsing XML files. It shows a direct query that uses the `OleDb.Query` function with the `MSDAOSP` provider and the `MSXML2.DSOControl` data source to access an XML catalog. The code specifies a file path, `c:\code\catalog.xml`, and executes the query to load its contents. This approach highlights a practical way to integrate XML data into data-analysis workflows without a separate parser. The visualization makes the data-access method visible, emphasizing the tool's role in bridging XML files and PowerQuery’s querying capabilities.

HHugoberry

58% match

Loading thumbnail…

Exposing #shared construct in Azure Analysis Services (change DatabaseID accordingly)

This example shows how to expose the contents of Power Query's `#shared` intrinsic constant inside an Azure Analysis Services tabular model, turning the built-in M function and value registry into a queryable table. The workflow uses three XMLA/TMSL scripts: first, an XMLA `Create` command injects a shared expression named `SharedExpression` that returns `#shared`; second, a TMSL `create` operation defines a table with `Name` and `Value` string columns, pulling data through an M partition expression that references the injected shared expression; finally, a TMSL `refresh` command processes the new table. The result is then viewed with a DAX `evaluate` statement against the `SharedExpressionTable`, demonstrating a round-trip from M metadata to a usable relational structure. The code relies on the XMLA protocol, TMSL JSON APIs, and the M expression engine, with no external visualization library involved.

HHugoberry

58% match

Loading thumbnail…

Gist 4905f6e091305c6a3ff3c1fe2c0a50b2

This example visualizes the structure of a DAX query plan by parsing a text-based log file into a hierarchical tree. It uses the Moo lexer to tokenize lines, identifiers, column references, and operator types, then builds a nested representation of the plan. The visualization likely renders this tree to show the flow of logical and physical operations, with each node displaying its operator type and associated properties such as filters, joins, or spooling details. The code defines custom token patterns for DAX-specific syntax, including column references like `'Table'[Column]` and measure references like `[Measure]`, and parses them into structured objects for display.

HHugoberry

58% match

Loading thumbnail…

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.

HHugoberry

57% match

Loading thumbnail…

Gist 47d998760fa7271ac3157ce7d9555612

This example demonstrates how to query and load data from external sources using Power Query's OleDb.Query function, specifically connecting to a local CSV file and an XML catalog. The code shows two approaches: one using Microsoft's ACE OLEDB provider to read a delimited text file, and another using the MSDAOSP provider with MSXML2.DSOControl to access XML data. The visualization highlights the raw query definitions rather than rendering graphical output, focusing on the data-acquisition layer. Both queries reference file paths on the local system, with the CSV query specifying the file directory and the XML query pointing to a catalog file.

HHugoberry

57% match