Azure 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. The following mapping is for the layout of the model:

• Catalog - Displayed in the provider as a Catalog.
• Cube - Displayed in the provider as a Schema.
• Measure - Available in the provider under the special Measures view.
• Dimension - Each dimension is exposed as a view.
• Level - Each individual level of a hierarchy is exposed as a column on the appropriate dimension view.

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.

Joining Measures and Dimensions

In order to retrieve measures per specific level value, issue a join between the Measure view and any Dimension or set of dimensions. For example, issuing the following retrieves the number of customers in each city:

SELECT m.[Customer Count], c.[City] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

Note that there is no ON condition necessary. That is because tables are already related appropriately in Azure Analysis Services. If you are using a tool that requires ON conditions, set IncludeJoinColumns to true. This appends a number of foreign key columns to each view, relating them to one another. These columns do not return data on their own, but may be picked up on automatically with tools to construct the ON conditions for joins where needed.

Aggregating Data

Data stored in Azure Analysis Services is already aggregated. In many cases, attempting to retrieve an aggregate may be syntactically equivalent to not specifying anything. For example, the following query returns the exact same data as the previous:

SELECT SUM(m.[Customer Count]), c.[City] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m
GROUP BY c.[City]

The exception to this rule is when an aggregation of filtered results is requested. In such cases, a calculation is requested from Azure Analysis Services. For example, to calculate the sum and average of customers in France and Germany:

SELECT SUM(m.[Customer Count]), AVG(m.[Customer Count]), c.[Country] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m
WHERE c.[Country] IN ('France', 'Germany')
GROUP BY c.[Country]

Selecting Multiple Hierarchies

In Azure Analysis Services, individual dimensions are made up of hierarchies which may have one or more levels. For instance, the AdventureWorks Customers table has City, Country, and Gender. City and Country are part of the same hierarchy while Gender is its own hierarchy.

When selecting multiple hierarchies, the method to support this is to cross join the values in MDX. While not obvious from a relational table model of the data as the provider presents, this can cause very expensive queries to be executed. For example, executing the following:

SELECT c.[Country], m.[Customer Count]
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

The query results in 6 rows. However, selecting Gender as well:

SELECT c.[Country], c.[Gender], m.[Customer Count]
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

The query now results in 12 rows. This is because Gender and Country are on different hierarchies, thus a crossjoin is required in order to return both together. Each additional hierarchy added to the SELECT multiplies the total results by the number of available values in that hierarchy. Thus to get a count of how many rows to expect, one can execute the following:

SELECT (COUNT(c.[Country])*COUNT(c.[Gender])) AS totalrows
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 

Response Row Limit

Due to how selecting multiple hierarchies multiplies the total number of result rows, it is possible to balloon the number of response rows very quickly, which results in timeouts. In order to try to give some visibility into which queries are very expensive, the ResponseRowLimit connection property has been added as a mechanism to try and guide users into better practices. When set, it calculates how many rows to expect before any query is executed. If the number of predicted rows exceeds the limit, an error is thrown indicating how many rows to expect back with the query.

It is recommended to select only the columns required or to apply a WHERE criteria. Both can significantly reduce the number of response columns, which has a huge impact on performance. If you are already familiar with the provider and what queries may be expensive, ResponseRowLimit may be disabled by setting it to 0.

Dynamic Management Views

Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and the server health of your Azure 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.