SQL Server Analysis Services

Multidimensional Modeling Documentation

Designing Measures

Measures are the numerical data that users analyze in a cube. They represent the "what" of your business. This section covers the design considerations and implementation of measures within SQL Server Analysis Services (SSAS) multidimensional models.

What are Measures?

In a data warehouse, measures are typically found in fact tables. These are the quantitative values that you want to aggregate, sum, average, count, or perform other calculations on. Examples include sales amount, quantity sold, cost of goods, or number of units.

Types of Measures

Measures in SSAS can be categorized based on their aggregation behavior:

  • Standard Aggregations: These are the most common measures that can be aggregated using standard functions like SUM, COUNT, MIN, MAX, AVG.
  • Calculated Measures: These measures are derived from other measures or columns using MDX (Multidimensional Expressions) or DAX (Data Analysis Expressions) formulas. They allow for more complex business logic and insights.
  • Key Performance Indicators (KPIs): While not a distinct *type* of measure in the same way, KPIs are often built upon measures to track performance against predefined goals.

Creating Measures

Measures are typically defined within a Measure Group, which is associated with a specific fact table in your data source view. When you create a measure, you specify:

  • Name: A descriptive name for the measure (e.g., "Sales Amount").
  • Source Column: The column from the fact table that contains the raw measure data.
  • Aggregation Function: The method used to combine the values from multiple rows (e.g., SUM, COUNT, AVG).
  • Format String: How the measure values should be displayed (e.g., currency, percentage).

Example: Creating a Simple Sales Amount Measure

In SQL Server Data Tools (SSDT) or Visual Studio for SSAS, you would navigate to your cube, then to the "Measures" pane. Right-click and select "New Measure".


-- Example in the Dimension Designer or Cube Designer (Conceptual)
CREATE MEASURE [Adventure Works DW]. [Sales Amount]
AS SUM( [Measures].[Sales Amount] ),
FORMAT_STRING = '$#,##0.00';

Calculated Measures

Calculated measures provide powerful flexibility. They are defined using MDX formulas. This allows you to create measures that represent ratios, variances, or complex business calculations.

Common Use Cases for Calculated Measures:

  • Profit Margin: ([Measures].[Sales Amount] - [Measures].[Total Product Cost]) / [Measures].[Sales Amount]
  • Year-over-Year Growth: Using functions like LAG() or PARALLELPERIOD().
  • Custom Ratios: Any ratio relevant to your business analysis.

Example: Profit Margin Calculation


CREATE MEASURE [Adventure Works DW]. [Profit Margin]
AS ([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]) / [Measures].[Internet Sales Amount],
FORMAT_STRING = '0.00%';

Measure Formatting

Proper formatting is crucial for usability. SSAS supports various format strings:

  • 'C' or '$#,##0.00' for currency.
  • 'N' or '#,##0.00' for numbers.
  • 'P' or '0.00%' for percentages.
  • 'D' for dates.

You can also define custom format strings for more specific requirements.

Best Practices for Designing Measures

  • Use Consistent Naming Conventions: Ensure measure names are clear and easy to understand.
  • Choose Appropriate Aggregation Functions: Select the function that best represents the business meaning of the data.
  • Define Measures at the Lowest Granularity: This allows for maximum flexibility in aggregation.
  • Leverage Calculated Measures for Business Logic: Move complex calculations from client applications to the cube for consistency and performance.
  • Apply Meaningful Formatting: Make measures easy to read and interpret in reporting tools.
  • Document Measures: Use the "Description" property in SSAS to explain the purpose and calculation of each measure.
Important: Ensure that the data type of your source column is appropriate for the intended aggregation. For example, use numeric types for SUM or AVG.
Tip: Consider creating "degenerate dimensions" for descriptive attributes that are stored in your fact table but don't fit the traditional dimension model. These can sometimes be useful as measures for counting occurrences.

Related Topics