The CData ADO.NET Provider for Microsoft Power BI XMLA models dimensions as tables, cubes as schemas, and a combination of the Workspace and DataSet Catalog as the Catalog. Live connectivity to these objects means any changes to your Microsoft Power BI XMLA account are immediately reflected when using the provider.

Notes:

• You can connect the provider to multiple Workspaces simultaneously by supplying a comma-separated list of Workspaces in the Workspace property. The result is that each Workspace shows up in the Catalogs as WorkspaceName_CatalogName.

• The provider models Microsoft Power BI XMLA entities like documents, folders, and groups as relational views, which allows you to write SQL to query Microsoft Power BI XMLA data.

MDX Data Model (Default)

In the default MDX mode (QueryMode = MDX), the provider models the data as follows:

Measure Attributes

By default, all measure attributes are listed in a 'Measures' view. However, you can set SplitMeasures to 'true' to split the measures view. The result is that each measure attribute is included in its respective view based on the Measure Group value. Further classification based on 'Measure Directories' is not included.

DAX Data Model

When QueryMode is set to DAX, the provider uses DAX metadata functions to discover the tabular model structure. This mode is designed for tabular data models, which is the standard architecture used by Power BI datasets.

Model Layout

The DAX data model exposes the following objects:

• Catalog - The database (same as MDX mode).
• Schema - Represents the schema of the tabular model.
• Table - Each table in the tabular model is exposed as a view with its native columns and data types.
• Measures - By default, all model-defined measures are grouped in a single global "Measures" view. Use SplitMeasures to distribute measures across tables (see below).
• MeasuresId - A virtual join column automatically added to every table, enabling BI tools to construct joins between tables and the Measures view. This is included for compatibility with visual query builders that require an explicit ON clause.

Measure Organization

By default, all measures are grouped in a single "Measures" view. You can use SplitMeasures and SplitMeasuresOn to control how measures are organized:

• SplitMeasures = false (default) - All measures are placed in a single "Measures" view.
• SplitMeasures = true, SplitMeasuresOn = MeasureGroup - Measures are grouped with the fact table they belong to (their home table). Tables that contain both columns and measures behave as unified entities.
• SplitMeasures = true, SplitMeasuresOn = DisplayFolder - Measures are grouped into tables that match the name of their DisplayFolder metadata. Measures without a DisplayFolder go into the shared "Measures" view.
• SplitMeasures = true, SplitMeasuresOn = DisplayFolder,MeasureGroup - Measures are grouped by DisplayFolder first; if a measure has no DisplayFolder, it falls back to its MeasureGroup (home table).

If a DisplayFolder name conflicts with an existing table name, the generated table is prefixed with "Measures_" to avoid ambiguity (e.g., "Measures_Sales").

Querying with DAX Mode

DAX mode translates SQL queries into DAX expressions. The following SQL patterns are supported:

Implicit Aggregation

Aggregate functions including SUM, AVG, MIN, MAX, COUNT, and DISTINCTCOUNT are supported and translate directly to their DAX equivalents. For example:

SELECT City, SUM(Amount) FROM Sales GROUP BY City

Complex arithmetic expressions within aggregates are also supported. For example,

SUM(Price * Quantity)

translates to a SUMX iterator that evaluates the expression row by row.

Joining Tables with Measures

To retrieve measures sliced by dimension values, join a table with the Measures view using CROSS JOIN or INNER JOIN:

SELECT s.City, m.[Total Amount]
FROM Stores s
CROSS JOIN Measures m

The provider translates this into a DAX SUMMARIZECOLUMNS expression that leverages the model's built-in relationships for efficient evaluation. NOTE: DAX query mode resolves joins through model relationships (using SUMMARIZECOLUMNS) and ignores explicit ON predicates.

INNER JOIN and LEFT JOIN between physical tables are also supported and use DAX NATURALINNERJOIN and ADDCOLUMNS patterns respectively.

Filtering, Sorting, and Pagination

WHERE clauses (including IN lists and IS NULL checks), ORDER BY, DISTINCT, LIMIT, and HAVING are all supported. LIMIT translates to DAX TOPN for server-side row limiting.

Data Types

DAX mode maps native tabular data types directly to SQL types, providing more accurate type information than MDX:

• String maps to string
• Int64 maps to long
• Double maps to double
• DateTime maps to datetime
• Decimal / Currency maps to decimal
• Boolean maps to bool

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including downloading documents and moving envelopes.