MSDN

Measures in SQL Server Analysis Services

Measures are numerical values that are aggregated from data in a cube. They represent the key business metrics that users will analyze, such as sales amount, quantity sold, or profit margin. In SQL Server Analysis Services (SSAS), measures are fundamental to building a multidimensional model that provides insights into business performance.

Understanding Measures

A measure is typically associated with a fact table column that contains numerical data. SSAS provides a rich set of aggregation functions to define how these numerical values are summarized across different dimensions. Common aggregation functions include:

• SUM: Adds up all the values in a column.
• COUNT: Counts the number of rows or distinct values.
• AVG: Calculates the average of values.
• MIN: Finds the minimum value.
• MAX: Finds the maximum value.

Beyond basic aggregation, SSAS allows for the creation of Calculated Measures. These measures are not directly derived from a fact table column but are computed using formulas that can involve other measures, attributes, or mathematical operations.

Creating Measures

Measures are typically created within the Cube Designer in SQL Server Data Tools (SSDT) or Visual Studio. You can define measures based on columns from your data source view.

Types of Measures

• Simple Measures (Regular Measures): These are directly mapped to a fact table column and use a standard aggregation function.
• Calculated Measures: These are defined by a Multidimensional Expressions (MDX) formula.
• Key Performance Indicators (KPIs): While not strictly a measure type, KPIs are built upon measures to track progress against specific goals.

MDX for Calculated Measures

MDX is the query language used in SSAS for retrieving data from cubes. For calculated measures, you'll write MDX expressions.

Example: Calculating Profit Margin

Consider a cube with [Measures].[Sales Amount] and [Measures].[Cost Amount]. A calculated measure for profit margin could be defined as:

IIF(
    [Measures].[Sales Amount] <> 0,
    ( [Measures].[Sales Amount] - [Measures].[Cost Amount] ) / [Measures].[Sales Amount],
    NULL
)
        

Example: Using a Time Dimension

To calculate Year-over-Year (YoY) sales growth, you might use functions like LAG or PARALLELPERIOD.

// Assuming a [Date] dimension with a [Calendar Year] level
[Measures].[Sales Amount YoY Growth] =
    ( [Measures].[Sales Amount], PARALLELPERIOD([Date].[Calendar Year].CurrentMember, 1) )
    / [Measures].[Sales Amount] - 1
        

Measure Groups and Aggregations

Measures are organized into Measure Groups within a cube. Each measure group is typically associated with a specific fact table. SSAS can pre-calculate aggregations for measures to improve query performance. These aggregations can be configured to store summarized data for frequently accessed combinations of dimension members.

Performance Considerations

• Choose appropriate aggregation functions for your measures.
• Design your cube and aggregations strategically to balance storage space and query speed.
• Understand the difference between measures that are physically stored (regular measures) and those that are calculated on-the-fly (calculated measures).

Further Reading

• Multidimensional Model Design (Analysis Services)
• MDX Syntax (Analysis Services)