Microsoft SQL Server Analysis Services is an OLAP database that exposes data as cubes, which you query with MDX (multidimensional expressions). The provider models these cubes in relational views that you can query with SQL-92, or MDX.

Accessing Cubes as Views

Microsoft SQL Server Analysis Services is fundamentally different from a relational database; reflecting these differences, the CData ADO.NET Provider for Microsoft SQL Server Analysis Services does not support many SQL-92 features found in a driver for an RDBMS. To bypass any limitations with query translation, you can submit pass-through MDX queries from the provider.

See Retrieving Analysis Services Data for query limitations and more information on querying Microsoft SQL Server Analysis Services.

Modeling Views Dynamically

You can query all of the cubes in your Microsoft SQL Server Analysis Services instance; when you connect, the provider retrieves the metadata from Microsoft SQL Server Analysis Services and displays the available dimensions and measures as columns for the views (cubes). The provider dynamically updates the schemas over subsequent connections.

Instead of retrieving metadata every connection, you can set CacheLocation.

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 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 (INFO.TABLES, INFO.VIEW.COLUMNS, INFO.MEASURES) to discover the tabular model structure. This mode is designed for tabular data models and provides a simpler relational representation.

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.

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.

Accessing Schema Information

Query the System Tables to access schema information and other provider metadata.

Dynamic Management Views

Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and the server health of your Analysis Services instance. These objects can be queried through the provider by setting QueryPassthrough to 'true' and issuing the desired query:

SELECT * FROM $System.DBSchema_Tables   
WHERE TABLE_TYPE = 'SCHEMA'   
ORDER BY TABLE_NAME ASC  

Note that the DMV query syntax is based on a SQL SELECT statement, but does not support its full syntax. See the Analysis Services documentation on DMVs for more information.