Multidimensional Models

Overview

Multidimensional models, also known as OLAP cubes, enable fast, interactive analysis of large volumes of data. They support complex queries, aggregations, and calculations, providing business users with intuitive, slice‑and‑dice capabilities.

Key components of a multidimensional model include:

  • Cubes – containers for measures and dimensions.
  • Dimensions – structures that describe data attributes like time, geography, or product.
  • Measures – numeric data that can be aggregated (e.g., sales, quantity).
  • Hierarchies – logical groupings that enable drill‑down and roll‑up analysis.

Designing a Multidimensional Model

1. Identify Business Requirements

Gather requirements from stakeholders to determine which metrics and dimensions are essential for analysis.

2. Define Source Data

Select relational tables, views, or data warehouse objects that will feed the cube.

3. Create Dimensions

Use the Dimension Wizard or Script to define attributes, hierarchies, and relationships.

CREATE DIMENSION [Date] AS
(
  KEY COLUMN = DateKey,
  ATTRIBUTE [Year] = Year,
  ATTRIBUTE [Quarter] = Quarter,
  HIERARCHY [Calendar] = (Year, Quarter, Month, Day)
);

4. Build the Cube

Define measure groups, assign measures, and map them to appropriate dimensions.

CUBE [Sales]
(
  MEASUREGROUP [FactSales] (
    MEASURE [Sales Amount] = SUM(Amount),
    MEASURE [Units Sold]   = SUM(Units)
  ),
  DIMENSION [Date],
  DIMENSION [Product],
  DIMENSION [Customer]
);

Processing the Cube

Processing loads data from the source and builds aggregations. Choose the appropriate processing option:

  • Full Process – rebuilds the entire cube.
  • Incremental Process – loads only new or changed data.
  • Refresh Aggregations – recalculates aggregates without reloading data.

Example XMLA processing command:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>AdventureWorksDW2019</DatabaseID>
    <CubeID>Sales</CubeID>
  </Object>
  <Type>ProcessFull</Type>
</Process>

Security

Implement role‑based security to control access to cubes, dimensions, and cells.

  • Define Roles with permissions (Read, Read Write, etc.).
  • Use Dimension Security to restrict attribute visibility.
  • Apply Cell Security for fine‑grained control over measure values.
CREATE ROLE [SalesManager]
AS
  PERMISSIONS = {
    READ CUBE [Sales],
    READ DIMENSION [Date],
    READ DIMENSION [Product],
    READ DIMENSION [Customer]
  };
GRANT ROLE [SalesManager] TO USER [DOMAIN\jdoe];

Best Practices

  1. Keep dimension tables skinny – avoid unnecessary attributes.
  2. Design hierarchies that match business navigation paths.
  3. Use aggregations to improve query performance; start with default aggregation designs and adjust based on usage.
  4. Schedule regular incremental processing to keep data fresh.
  5. Validate security roles in a test environment before production deployment.

Additional Resources