MSDN Documentation / SQL / Analysis Services / Multidimensional Models / Cubes / Measures

Understanding Measures in SQL Server Analysis Services Multidimensional Models

Measures are at the heart of any business intelligence solution built with SQL Server Analysis Services (SSAS) multidimensional models. They represent quantifiable business metrics, such as sales amount, profit, units sold, or inventory count, that users want to analyze and aggregate. Measures are typically numeric values derived from fact tables in your data warehouse.

What is a Measure?

A measure is a dynamic value that can be aggregated. Unlike attributes (dimensions), which describe the context of data (e.g., product, time, location), measures provide the numerical data to be analyzed within that context. For example, in an e-commerce cube, 'Sales Amount' would be a measure, and 'Product', 'Date', and 'Customer' would be dimensions.

Types of Measures

SSAS supports several types of measures, each serving a specific purpose:

• Standard Measures: These are the most common type, directly representing a column from a fact table (e.g., SUM(FactInternetSales[SalesAmount])).
• Calculated Measures: These measures are defined by MDX (Multidimensional Expressions) formulas. They allow you to derive new metrics from existing measures or other data (e.g., Profit Margin = ([Measures].[Sales Amount] - [Measures].[Total Cost]) / [Measures].[Sales Amount]).
• Semi-Additive Measures: These measures can be aggregated across some dimensions but not others. For instance, 'Account Balance' can be summed across accounts but might not be meaningful to sum across time.
• Referential Measures: Used to define relationships between cubes or perspectives.

Creating Measures

Measures are typically created in SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS) within a cube project. The process involves:

1. Identifying the source columns in your fact table(s) that contain the numerical data you want to analyze.
2. Defining the aggregation function (e.g., SUM, COUNT, MIN, MAX, AVG) to be applied to these columns.
3. Optionally, writing MDX formulas for calculated measures.

Example: Creating a Standard Measure

To create a 'Total Sales' measure from a 'SalesAmount' column:

CREATE MEASURE [Measures].[Total Sales] AS
    SUM( [YourCube].[YourFactTable].[SalesAmount] )

Example: Creating a Calculated Measure (Profit Margin)

CREATE MEASURE [Measures].[Profit Margin] AS
    DIVIDE(
        [Measures].[Total Sales] - [Measures].[Total Cost],
        [Measures].[Total Sales]
    )

Measure Groups

Measures are organized into Measure Groups. A measure group typically corresponds to a fact table in your data warehouse. This organization helps in managing measures, defining their relationships with dimensions, and optimizing query performance.

Aggregation Functions

The choice of aggregation function is crucial for the accuracy of your analysis. Common functions include:

• SUM: Adds up all values.
• COUNT: Counts the number of rows or non-null values.
• MIN: Finds the smallest value.
• MAX: Finds the largest value.
• AVERAGE: Calculates the arithmetic mean.
• DISTINCTCOUNT: Counts the number of unique values (e.g., distinct customers).

Measures vs. KPIs

While measures provide the raw numerical data, Key Performance Indicators (KPIs) build upon measures. A KPI typically involves a target value, a status indicator (e.g., good, bad, warning), and a trend. KPIs use measures as their underlying data source.

Measures are fundamental to deriving insights from your data. By carefully defining and managing your measures, you empower users to perform meaningful analysis and make informed business decisions.