Measures and Measure Groups

A measure represents a column that contains quantifiable data, usually numeric, that can be aggregated. A measure is generally mapped to a column in a fact table.

You can also use a measure expression to define the value of a measure, based on a column in a fact table as modified by a Multidimensional Expression. A measure expression enables weighting of measure values; for example, currency conversion can be used to weight a sales measure by an exchange rate.

Attribute columns from dimension tables can be used to define measures, but such measures are typically semiadditive or nonadditive in terms of their aggregation behavior. For more information about semiadditive behavior, see Defining Semiadditive Behavior.

You can also define a measure as a calculated member by using a Multidimensional Expressions (MDX) to provide a calculated value for a measure based on other measures in the cube. Calculated members add flexibility and analysis capability to a cube in Analysis Services. For more information about calculations, see Calculations, and Defining and Configuring a Calculation.

Measure Groups

In a cube, measures are grouped by their underlying fact tables into measure groups. Measure groups are used to associate dimensions with measures. Measure groups are also used for measures that have distinct count as their aggregation behavior. Placing each distinct count measure into its own measure group optimizes aggregation processing.

The following diagram represents the FactSalesQuota fact table and the two dimension tables associated with it, DimTime and DimEmployee.

The FactSalesQuota fact table defines the Sales Quotas measure group of the Adventure Works cube, and the DimTime and DimEmployee dimension tables define the Time and Employee dimensions in the Adventure Works DW sample Analysis Services project.

The fact table contains two basic types of columns: attribute columns and measure columns. Attribute columns are used to create foreign key relationships to dimension tables, so that the quantifiable data in the measure columns can be organized by the data contained in the dimension tables. Attribute columns are also used to define the granularity of a fact table and its measure group. Measure columns define the measures contained by a measure group. In the FactSalesQuota fact table, the SalesAmountQuota column is used to define the Sales Amount Quota measure. This measure is contained by the Sales Quotas measure group, and is organized by the Time and Employee dimensions.

Granularity

Granularity refers to the level of detail supported by a fact table. For example, the FactSalesQuota fact table has a foreign key relationship with the DimEmployee table, on the EmployeeKey primary key column. In other words, each record in the FactSalesQuota table is related to a single employee; therefore, the granularity of the measure group as viewed from the Employee dimension is at the individual employee level.

The granularity of a measure group can never be set finer than the lowest level of the dimension from which the measure group is viewed, but the granularity can be made coarser by using additional attributes. For example, the FactSalesQuota fact table uses three columns, TimeKey, CalendarYear, and CalendarQuarter, to establish the granularity of the relationship with the DimTime table. As a result, the granularity of the measure group as viewed from the Time dimension is by calendar quarter, and not by day, which is the lowest level of the Time dimension.

You can specify the granularity of a measure group with relation to a specific dimension by using the Dimension Usage tab of the Cube Designer. For more information about dimension relationships, see Dimension Relationships.

Aggregate Functions

When a dimension is used to organize measures in a measure group, the measure is summarized along the hierarchies contained in that dimension. The summation behavior depends on the aggregate function specified for the measure. For example, the Employee dimension has a hierarchy named Employee Department, which is structured in levels like those in the following diagram:

Each record in the FactSalesQuota fact table is directly related to a single record in the DimEmployee dimension table by a foreign key relationship with the EmployeeKey column. Therefore, each value in the Sales Amount Quota measure relates to a single leaf member in the Full Name level of the Employee Department hierarchy, and can be directly loaded from the fact table. However, the value of the Sales Amount Quota measure for members in levels above Full Name cannot be directly loaded, because each member represents more than one record. In other words, a single member from the Title level may have several employees associated with it, and therefore several members in the Full Name level. The measure values for these nonleaf members are not directly loaded from the data source, but are instead aggregated from the members below it in the hierarchy.

Not all measures are derived directly from a value stored in a column of the fact table. For example, the Sales Person Count measure defined in the Sales Quota measure group of the Adventure Works cube in the Adventure Works DW sample Analysis Services project is actually based on the count of unique values (or distinct count) in the EmployeeKey column of the FactSalesQuota fact table.

The aggregation behavior of each measure is determined by the aggregation function associated with the measure. For more information about aggregate functions, see Defining Semiadditive Behavior.

See Also

Concepts

Defining and Configuring a Measure
Defining and Configuring a Measure Group
Defining Semiadditive Behavior

Help and Information

Getting SQL Server 2005 Assistance