MSDN Community

Working with SSAS Measures

Measures are fundamental to Business Intelligence (BI) solutions built with SQL Server Analysis Services (SSAS). They represent the quantitative values that users want to analyze, such as sales, profit, quantity, or cost. Understanding how to define, manage, and use measures effectively is crucial for creating powerful and insightful analytical models.

What are SSAS Measures?

In SSAS, measures are dynamic calculations that aggregate data from your data source. Unlike calculated members, which are often static and defined within the cube structure, measures are typically based on existing fact table columns and are aggregated using standard aggregation functions (SUM, COUNT, AVERAGE, MIN, MAX).

Creating Basic Measures

The most common way to create a measure is by selecting a numerical column from your fact table in the SSAS project's Dimension Usage or Measure Group views and setting its aggregation type.

For example, to create a 'Total Sales' measure:

1. Open your SSAS project in SQL Server Data Tools (SSDT).
2. Navigate to your Measure Group or the Dimension Usage tab.
3. Right-click on the fact table and select "New Measure".
4. In the Measure Editor:
   • Name: Enter Total Sales.
   • Source Column: Select the fact table column that stores sales amounts (e.g., SalesAmount).
   • Aggregation Function: Choose Sum.
5. Click OK.

Working with MDX for Measures

While basic measures are straightforward, more complex calculations require Multidimensional Expressions (MDX). MDX allows you to define calculated measures that perform intricate logic, conditional aggregation, and time-based calculations.

Example: Calculating Profit Margin

Let's define a 'Profit Margin' measure that calculates the ratio of profit to sales.

CREATE MEMBER CURRENTCUBE.[Measures].[Profit Margin] AS
    IIF(
        [Measures].[Total Sales] = 0,
        NULL,
        DIVIDE(
            [Measures].[Total Profit],
            [Measures].[Total Sales]
        )
    ),
FORMAT_STRING = '0.00%';
            

In this MDX statement:

• CREATE MEMBER CURRENTCUBE.[Measures].[Profit Margin] AS ... defines a new measure named 'Profit Margin' within the current cube.
• IIF([Measures].[Total Sales] = 0, NULL, DIVIDE(...)) handles the division by zero scenario, returning NULL if Total Sales is zero.
• DIVIDE([Measures].[Total Profit], [Measures].[Total Sales]) performs the core calculation.
• FORMAT_STRING = '0.00%' formats the output as a percentage with two decimal places.

Measure Properties

Measures have several properties that influence their behavior and presentation:

• Name: The display name of the measure.
• Source Column: The underlying column in the fact table for basic measures.
• Aggregation Function: The function used to aggregate the source column.
• Format String: Controls how the measure's value is displayed (e.g., currency, percentage, number).
• Description: Provides details about the measure.
• Kpis: Can be associated with Key Performance Indicators (KPIs).

Best Practices for Measures

• Use Descriptive Names: Names should clearly indicate what the measure represents.
• Define Clearly: Ensure each measure has a single, well-defined purpose.
• Leverage MDX: Don't shy away from MDX for complex calculations; it's a powerful tool.
• Apply Formatting: Use format strings to ensure consistent and user-friendly presentation of data.
• Avoid Redundancy: Create measures that are truly necessary and avoid duplicating calculations.
• Document Measures: Use the Description property to explain the logic and purpose of each measure.