Understanding Measures

What Are Measures?

In SQL Server Analysis Services (SSAS) multidimensional modeling, measures are the numerical values that users can analyze and aggregate. They represent business metrics such as sales amount, quantity sold, profit, or employee count. Measures are typically stored in fact tables in your data warehouse or data mart.

Measures are the core of OLAP cube analysis. Without measures, a cube would simply be a collection of descriptive attributes (dimensions) with no quantifiable data to explore.

Types of Measures

Measures can be categorized based on how their values are aggregated and calculated:

Regular Measures

These are the most common type of measures. Their values are directly aggregated from the underlying data source using a specified aggregation function (e.g., SUM, COUNT, AVG, MIN, MAX).

Calculated Measures

Calculated measures are not directly stored in the fact table. Instead, their values are computed on-the-fly using DAX (Data Analysis Expressions) or MDX (Multidimensional Expressions) formulas. These formulas can reference other measures, dimensions, or attributes.

Examples of calculated measures include:

• Profit Margin: Calculated as (SUM(Sales Amount) - SUM(Cost)) / SUM(Sales Amount)
• Sales per Employee: Calculated as SUM(Sales Amount) / COUNT(Employee Key)

/* MDX Example for Profit Margin */
CREATE MEMBER CURRENTCUBE.[Measures].ProfitMargin AS
  IIF(SUM([Measures].[Sales Amount]) = 0, NULL,
      (SUM([Measures].[Sales Amount]) - SUM([Measures].[Cost])) / SUM([Measures].[Sales Amount])
     ), FORMAT_STRING = 'Percent';

Key Performance Indicators (KPIs)

KPIs are special types of measures that allow you to track progress against defined goals. They typically consist of:

• Value: The actual measure value.
• Goal: A target value for comparison.
• Status: Indicates whether the value is meeting, exceeding, or falling short of the goal (often represented by icons).
• Trend: Shows the historical performance of the value.

Measure Groups

In SSAS multidimensional models, measures are organized into Measure Groups. A measure group is typically associated with a single fact table in your data source. All measures within a measure group share the same dimensionality.

Organizing measures into appropriate groups improves the usability and performance of the cube.

Common Measure Properties

• Name: The display name of the measure.
• Caption: An alternative name for display.
• Description: A brief explanation of the measure.
• Format String: Defines how the measure value is displayed (e.g., currency, percentage, number of decimal places).
• Aggregation Function: The default aggregation method for regular measures.
• DataType: The underlying data type of the measure.

Best Practices

• Use Meaningful Names: Measures should have clear, descriptive names.
• Define Appropriate Aggregations: Choose the correct aggregation function for each measure.
• Utilize Calculated Measures Wisely: Use them for complex calculations that are not directly available in the fact table.
• Configure Format Strings: Ensure measures are displayed in a user-friendly format.
• Organize into Measure Groups: Group related measures logically.
• Document Measures: Provide descriptions for clarity.