Measures in Multidimensional Models

Measures represent the quantitative data that users typically want to analyze in a data warehouse. They are numeric values that are aggregated, such as sales amounts, quantities, or counts. In SQL Server Analysis Services (SSAS) multidimensional models, measures are stored in measure groups, which are typically derived from tables in the data source view.

Understanding Measures

Measures are the core of your analytical insights. They answer questions like "What are the total sales?" or "How many units were sold?". Measures are derived from columns in your fact tables. Common examples include:

• Sales Amount
• Quantity Sold
• Profit
• Transaction Count
• Average Order Value

Types of Measures

Measures in SSAS can be categorized based on how they are defined and processed:

• Aggregatable Measures: These measures can be directly aggregated using standard aggregation functions like Sum, Count, Min, Max, Average. Most measures fall into this category.
• Non-Aggregatable Measures: These measures represent values that should not be aggregated across dimensions. For example, a unit price measure is often best displayed as an average or at the lowest grain of detail, rather than summed up.
• Calculated Measures: These measures are computed dynamically using MDX (Multidimensional Expressions) or DAX (Data Analysis Expressions) at query time. They allow for complex calculations that are not directly stored in the database.

Creating Measures

Measures are typically created within a Cube designer in Visual Studio or SQL Server Management Studio. When you create a cube and select fact tables, Analysis Services often automatically suggests measures based on the numeric columns in those tables.

When defining a measure, you specify:

1. Name: A descriptive name for the measure (e.g., "Total Sales Amount").
2. Source: The underlying column in the fact table.
3. Aggregation Function: The function to use for aggregation (e.g., Sum, Count, Average).
4. Format: How the measure should be displayed (e.g., currency, percentage).
5. Description: Optional details about the measure.

Measure Groups

Measures are organized into measure groups. A measure group is a collection of related measures that are typically sourced from the same fact table. For example, a "Sales" measure group might contain measures like "Sales Amount", "Quantity Sold", and "Discount Amount", all derived from a sales fact table.

MDX for Calculated Measures

Calculated measures are defined using MDX. This allows for powerful on-the-fly calculations. Here's an example of an MDX expression for a calculated measure that computes profit margin:

<Measure Name="Profit Margin">
  // Calculate Profit Margin as (Sum of Sales - Sum of Cost) / Sum of Sales
  WITH MEMBER MEASURES.[Profit Margin] AS
    (Sum( Measures.[Sales Amount] ) - Sum( Measures.[Cost Amount] )) / Sum( Measures.[Sales Amount] )
  MEMBER_CAPTION = "Profit Margin"
  VISIBLE = 1
</Measure>
                

In this example:

• WITH MEMBER MEASURES.[Profit Margin] AS ... defines a new calculated measure.
• Sum(Measures.[Sales Amount]) and Sum(Measures.[Cost Amount]) refer to existing base measures.
• The expression calculates the difference between sales and cost, then divides by sales to get the margin.

Measure Formatting

Proper formatting of measures is essential for clear presentation. SSAS allows you to define currency formats, number formats, and percentage formats. This ensures that measures are displayed consistently and are easy for users to interpret.

For example, a "Sales Amount" measure might be formatted as currency ($1,234.56), while a "Discount Percentage" might be formatted as a percentage (10.5%).

By effectively defining and organizing measures, you empower users to perform powerful data analysis and gain actionable insights from your multidimensional cubes.