Defining Measures

Measures are fundamental elements in a multidimensional model that represent numerical data that users can aggregate. They are typically numeric values such as sales amounts, quantities, or counts that can be summed, averaged, or subjected to other aggregation functions.

Understanding Measure Groups

Measures are organized within measure groups. A measure group is a collection of measures that share a common source table in the relational data warehouse. Grouping related measures simplifies management and improves query performance.

Types of Measures

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

Basic Measures: These measures directly map to a column in a fact table. Their values are typically stored in the fact table and are aggregated using standard aggregation functions (SUM, COUNT, MIN, MAX, AVG).
Calculated Measures: These measures are defined by formulas that use existing measures, attributes, or constants. They are calculated on-the-fly during query execution.

Defining a Basic Measure

To define a basic measure, you typically select a numeric column from a fact table in your data source view. When creating the measure in SQL Server Data Tools (SSDT), you will specify:

Name: A descriptive name for the measure (e.g., "Sales Amount", "Quantity").
Source Column: The numeric column from the fact table.
Aggregation Function: The function used to aggregate the measure values across different dimension members (e.g., SUM, AVERAGE, COUNT DISTINCT).

Example using DAX/MDX Snippet (Conceptual)

While SSAS multidimensional model definition is often done through GUI tools, the underlying logic can be represented conceptually. For a measure like "Total Sales Amount" based on a `SalesAmount` column:


-- Conceptual MDX for a basic measure
DEFINE MEASURE [Measures].[Total Sales Amount] AS SUM([FactSales].[SalesAmount])

Defining a Calculated Measure

Calculated measures offer more flexibility. They are defined using MDX (Multidimensional Expressions) or DAX (Data Analysis Expressions) formulas. When defining a calculated measure, you specify:

Name: A descriptive name for the calculated measure.
Formula: An expression that calculates the measure's value. This can involve arithmetic operations, other measures, or built-in functions.

Example of a Calculated Measure: Profit Margin

Let's say you have measures for "Total Sales Amount" and "Total Cost". A "Profit Margin" measure could be calculated as:


-- MDX Formula for Profit Margin
DIVIDE(
    SUM([Measures].[Sales Amount]) - SUM([Measures].[Cost Amount]),
    SUM([Measures].[Sales Amount])
)

The DIVIDE function is used for safe division, preventing errors if the denominator is zero.

Note: When creating calculated measures, ensure your formulas are syntactically correct and logically sound to avoid runtime errors. Consider edge cases like division by zero.

Measure Properties

Measures have several properties that influence their behavior and presentation:

Format String: Defines how the measure value is displayed (e.g., currency, percentage, number of decimal places).
Kpis: Measures can be associated with Key Performance Indicators (KPIs) for performance monitoring.
IsAggregatable: Specifies whether the measure can be aggregated (most measures are).

Best Practice: Organize measures logically within measure groups based on their source tables. Use clear and consistent naming conventions for measures and measure groups. Define appropriate format strings for all measures to ensure clear data presentation.

Advanced Measure Concepts

Parent-Child Hierarchies and Measures

When working with parent-child hierarchies, measures might need special handling to aggregate correctly across different levels of the hierarchy.

Aggregation Storage Design

The way measures are aggregated and stored can significantly impact query performance. Understanding aggregation designs, including proactive caching and MOLAP/ROLAP/HOLAP storage modes, is crucial for optimizing your SSAS solution.