Azure Analysis Services Documentation

Query Model

Introduction to the Azure Analysis Services Query Model

Azure Analysis Services (AAS) provides a powerful tabular data modeling engine that enables business intelligence professionals to build semantic models that can be queried by client applications such as Power BI, Excel, and custom applications. The query model defines the structure and relationships of the data, making it understandable and accessible for analysis.

This document delves into the core components of the AAS query model, how to interact with it, and best practices for designing effective models.

Core Concepts

Tables

Tables are the fundamental building blocks of an AAS model. They represent collections of related data, typically derived from relational databases, data warehouses, or other data sources. Each table has a defined set of columns.

Columns

Columns represent individual data attributes within a table. Each column has a name, a data type (e.g., String, Integer, Date, Decimal), and can be configured with various properties like formatting and aggregation behavior.

Relationships

Relationships define how tables are connected to each other. These connections are crucial for navigating data across different tables and enabling complex queries. Relationships are typically defined between one or more columns in one table (the "from" table) and one or more columns in another table (the "to" table). AAS supports one-to-many, many-to-one, and one-to-one relationships.

Example Relationship:


    -- Conceptual representation of a relationship
    Table: Sales
        Columns: ProductKey (int), Quantity (int)
    Table: Products
        Columns: ProductKey (int), ProductName (string)

    Relationship: Sales[ProductKey] -- 1..* -- Products[ProductKey]

Measures

Measures are calculations that aggregate data. They are dynamic and respond to the context of a query. Common aggregation functions include SUM, AVERAGE, COUNT, MIN, and MAX. Measures are typically written using DAX (Data Analysis Expressions).

Example DAX Measure:


    Total Sales = SUM(Sales[SalesAmount])

Hierarchies

Hierarchies represent user-defined navigation paths within a table, allowing users to drill down or roll up data. Common examples include Time hierarchies (Year > Quarter > Month > Day) or Geographical hierarchies (Country > State > City).

Querying the Model

Azure Analysis Services models can be queried using several methods:

MDX (Multidimensional Expressions)

MDX is a powerful query language traditionally used with SQL Server Analysis Services' multidimensional mode. While AAS is primarily tabular, it exposes an MDX endpoint for compatibility with existing tools and applications that rely on MDX.

Example MDX Query:


    SELECT
        {[Measures].[Total Sales]} ON COLUMNS,
        {[Date].[Calendar Year].Members} ON ROWS
    FROM [YourModelName]

DAX (Data Analysis Expressions)

DAX is the primary query and calculation language for tabular models in Azure Analysis Services. It's known for its expressive power and flexibility in defining complex business logic. DAX queries are often used within tools like Power BI or when retrieving data directly via the XMLA endpoint.

Example DAX Query:


    EVALUATE
    SUMMARIZECOLUMNS (
        'Date'[CalendarYear],
        "Total Sales", SUM ( Sales[SalesAmount] )
    )

REST API

Azure Analysis Services provides a REST API that allows programmatic interaction with the service. This API can be used for managing models, executing queries (both MDX and DAX), and retrieving metadata.

Best Practices for Designing Query Models