Measures in Multidimensional Models

Measures are the numerical values that users analyze in a cube. They represent business metrics such as sales amount, quantity sold, or cost. Measures are typically aggregated from fact tables in a data warehouse. In SQL Server Analysis Services (SSAS) multidimensional models, measures are defined within a measure group, which is associated with a data source view.

Types of Measures

SSAS supports several types of measures:

Count: Counts the number of rows or records.
Sum: Calculates the sum of values in a column.
Average: Calculates the average of values in a column.
Min: Finds the minimum value in a column.
Max: Finds the maximum value in a column.
Distinct Count: Counts the number of unique values in a column.
Custom: Allows for user-defined aggregation logic, often implemented through MDX expressions.

Measure Groups

Measures are organized into measure groups. A measure group typically corresponds to a fact table in the underlying relational database. Each measure group contains one or more measures. When you design a cube, you can select which measure groups to include.

Creating Measures

Measures are created within a measure group in SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). The process generally involves:

Selecting the source column from the data source view.
Choosing the aggregation type (e.g., Sum, Average).
Defining the measure name, format string, and other properties.

Tip: When creating measures, always consider the business context and how the data will be aggregated. Ensure that the chosen aggregation type aligns with the semantic meaning of the data.

Measure Properties

Measures have various properties that control their behavior and presentation:

Name: The display name of the measure.
Source: The column or expression from which the measure's data is derived.
AggregateFunction: The aggregation type (e.g., Sum, Count).
FormatString: Specifies how the measure's value is displayed (e.g., currency, percentage).
Description: A textual description of the measure.
Kpi: Indicates if the measure is part of a Key Performance Indicator (KPI).

Calculated Measures

In addition to basic measures derived directly from source columns, you can create calculated measures. These are measures whose values are computed using Multidimensional Expressions (MDX) formulas. This allows for more complex business logic and analysis, such as year-over-year growth or profit margin calculations.

A common scenario for calculated measures is to derive metrics that are not directly stored in the fact table but can be calculated from existing measures or columns. For example:

SUM( [Measures].[Sales Amount] ) / COUNTROWS( [Fact Internet Sales] )

This MDX expression calculates the average sales amount per transaction.

Note: For complex calculations, it's often beneficial to create them as calculated members within a dimension's hierarchy or as named sets rather than as calculated measures, depending on the scope and usage.

Best Practices

Meaningful Names: Use clear and descriptive names for your measures.
Consistent Aggregation: Ensure aggregation functions are applied logically to the data.
Appropriate Format Strings: Apply correct format strings for readability (e.g., currency, percentages).
Performance Considerations: For very large datasets, consider the performance implications of certain aggregation types, especially Distinct Count.
Use Calculations Wisely: Leverage calculated measures for derived metrics, but consider their impact on query performance.

Example: Sales Amount Measure

Consider a sales fact table with a SalesAmount column. A common measure would be the sum of this column:

Measure Name	Source Column	Aggregation Function	Format String
Total Sales	`[dbo].[FactInternetSales].[SalesAmount]`	Sum	`$#,##0.00`

This measure, when displayed in a report or analysis tool, would show the total sales amount, aggregated appropriately based on the dimensions selected in the query.

By effectively defining and organizing measures, you provide users with the essential building blocks for insightful data analysis within their multidimensional solutions.