SQL Server Analysis Services

Analysis Services Reference

This section provides comprehensive reference documentation for Microsoft SQL Server Analysis Services (SSAS). Find detailed information on its architecture, objects, MDX and DAX query languages, XMLA, and other critical components.

Key Reference Areas:

• Object Model Reference:
  • Multidimensional Model Objects
  • Tabular Model Objects
• Query Languages:
  • Multidimensional Expressions (MDX)
  • Data Analysis Expressions (DAX)
• Interfaces and Protocols:
  • XML for Analysis (XMLA)
  • REST API Reference
• System Objects and Functions:
  • Built-in Functions
  • System Tables and Views
• Error Codes:
  • Analysis Services Error Codes

Multidimensional Model Objects

Detailed descriptions of objects within the multidimensional model, including Cubes, Dimensions, Measures, Hierarchies, and Attributes.

-- Example: Cube Object Structure
CREATE CUBE [AdventureWorks]
WITH
    DIMENSION [Date]
    DIMENSION [Product]
    DIMENSION [Geography]
AS
    MEASURE [Sales Amount]
    MEASURE [Total Sales]
GO
            

Tabular Model Objects

Documentation for tabular model concepts such as Tables, Columns, Relationships, Measures, and Calculated Columns.

-- Example: Table Definition in Tabular Model (conceptual)
TABLE 'DimProduct' (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50)
)
            

Multidimensional Expressions (MDX)

Reference for the MDX query language used to retrieve data from Analysis Services multidimensional models. Includes syntax, functions, and statements.

Key MDX Functions:

• [Measures].[Sales Amount].&[123] - Referencing a specific measure member.
• SUM(...) - Aggregation function.
• FILTER(...) - Conditional filtering.

SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    {([Date].[Calendar Year].&[2003]), ([Date].[Calendar Year].&[2004])} ON ROWS
FROM [AdventureWorks]
WHERE ([Product].[Category].&[3])
            

Data Analysis Expressions (DAX)

Reference for the DAX language used with Analysis Services tabular models and Power BI. Covers formulas, functions, and expressions.

Key DAX Functions:

• CALCULATE(...) - Modifies the filter context.
• SUM('FactSales'[SalesAmount]) - Summing a column.
• FILTER('DimProduct', 'DimProduct'[Category] = "Bikes") - Filtering a table.

Total Sales =
SUMX(
    'FactInternetSales',
    'FactInternetSales'[SalesAmount] * 'FactInternetSales'[TaxAmt]
)
            

XML for Analysis (XMLA)

Reference for the XMLA protocol, the standard interface for interacting with Analysis Services. Covers commands, data types, and structures.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Command>
    <Statement>SELECT FROM [AdventureWorks]</Statement>
  </Command>
  <Properties>
    <PropertyList>
      <DataSourceInfo>Provider=MSOLAP;Data Source=localhost;</DataSourceInfo>
      <Catalog>AdventureWorksDW</Catalog>
    </PropertyList>
  </Properties>
</Execute>
            

REST API Reference

Information on the RESTful APIs available for managing and querying Analysis Services, particularly for tabular models.

Example Endpoint: GET /api/v1.0/models('MyModel')/tables('MyTable')

Built-in Functions

A catalog of all built-in functions available in MDX and DAX, including their syntax, parameters, and return values.

System Tables and Views

Details on system tables and views that provide metadata and operational information about Analysis Services instances and databases.

Analysis Services Error Codes

A lookup for common error codes generated by Analysis Services, with explanations and potential resolutions.