Microsoft Docs

Multidimensional Model in SQL Server Analysis Services

SQL Server Analysis Services (SSAS) Multidimensional model provides a robust platform for building sophisticated business intelligence solutions. It allows you to design and deploy data models that are optimized for analytical queries, providing users with fast and interactive access to large volumes of data.

Key Concepts

The multidimensional model is built around core OLAP (Online Analytical Processing) concepts:

  • Cubes: The central data structure, representing aggregated data from a business perspective.
  • Dimensions: Attributes that describe the data, such as Time, Geography, or Products.
  • Measures: Numerical values that can be aggregated, like Sales Amount, Quantity, or Profit.
  • Hierarchies: Structured relationships within dimensions, enabling drill-down and roll-up operations.

Designing a Multidimensional Model

Creating a multidimensional model typically involves the following steps:

1. Data Source and Data Source View

First, you define a data source connection to your underlying relational database (e.g., SQL Server). Then, you create a data source view (DSV), which is a logical representation of the data from your sources. The DSV allows you to reshape, rename, and join tables before they are used in the model.

2. Creating Dimensions

Dimensions are crucial for slicing and dicing your data. You can create dimensions based on tables in your DSV. Key aspects include:

  • Attributes: Columns from your source tables that represent characteristics of your data.
  • Hierarchies: Organizing attributes into parent-child or level-based hierarchies to facilitate analysis.
  • Member Properties: Additional information associated with dimension members that can be used for filtering or display.

3. Creating Measures and Measure Groups

Measures represent the quantitative data you want to analyze. They are typically derived from fact tables. Measures are organized into measure groups within a cube.

  • Aggregation Functions: Define how measures are aggregated (e.g., Sum, Count, Average, Min, Max).
  • Calculated Measures: Create new measures based on existing measures and expressions using MDX (Multidimensional Expressions).

4. Building the Cube

The cube is the central object that brings dimensions and measures together. When you define a cube, you select the dimensions and measure groups to include. SSAS then builds the multidimensional structure based on your design.

Performance Optimization

Consider using aggregation designs to pre-calculate and store aggregated data, significantly speeding up query performance for large cubes.

MDX and DAX

You interact with multidimensional models using query languages:

  • MDX (Multidimensional Expressions): The primary query language for SSAS multidimensional models. It's a powerful language for retrieving data, performing calculations, and manipulating multidimensional data structures.
  • DAX (Data Analysis Expressions): While primarily associated with the Tabular model, DAX can also be used to query multidimensional models through specific connectors and frameworks, offering a more familiar syntax for those coming from Excel or Power BI.

Example MDX Query:


SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    {[Date].[Calendar].[Calendar Year].Members} ON ROWS
FROM [Adventure Works DW]
WHERE ([Product].[Category].[Bikes])

Deploying and Using the Model

Once your multidimensional model is designed and processed, it can be deployed to an SSAS instance. Business intelligence clients like SQL Server Management Studio (SSMS), Excel, Power BI, and custom applications can then connect to and query the deployed cube.

Security Considerations

Implement robust security measures by configuring row-level security and dimension-level security to control user access to specific data subsets.

Further Reading