Configuring Measure Properties
Measures have properties that enable you to define how the measures function and to control how the measures appear to users.
Measure Properties
Measures inherit certain properties from the measure group of which they are a member, unless those properties are overridden at the measure level. Measure properties determine how a measure is aggregated, its data type, the name that is displayed to the user, the display folder in which the measure will appear, its format string, any measure expression, the underlying source column, and its visibility to users.
| Property | Definition |
|---|---|
AggregateFunction |
Determines how measures are aggregated. For more information, see Aggregation Functions. |
DataType |
Specifies the data type of the column in the underlying fact table to which the measure is bound. |
Description |
Provides a description of the measure, which may be exposed in client applications. |
DisplayFolder |
Specifies the folder in which the measure will appear when users connect to the cube. When a cube has many measures, you can use display folders to categorize the measures and improve the user browsing experience. |
FormatString |
Determines the display format. For more information, see Display Formats. |
ID |
Displays the unique identifier (ID) of the measure. This property is read-only. |
MeasureExpression |
Specifies a Multidimensional Expressions (MDX) expression that defines the measure. |
Name |
Specifies the name of the measure. |
Source |
Specifies the column in the data source view to which the measure is bound. |
Visible |
Determines the visibility of the measure. |
Aggregation Functions
Microsoft SQL Server 2005 Analysis Services (SSAS) provides functions to aggregate measures along the dimensions that are contained in measure groups. By default, measures are summed along each dimension. However, the AggregateFunction property lets you modify this behavior. The additivity of an aggregation function determines how the measure is aggregated across all the dimensions in the cube. Aggregation functions fall into three levels of additivity:
- Additive
An additive measure, also called a fully additive measure, can be aggregated along all the dimensions that are included in the measure group that contains the measure, without restriction.
- Semiadditive
A semiadditive measure can be aggregated along some, but not all, dimensions that are included in the measure group that contains the measure. For example, a measure that represents the quantity available for inventory can be aggregated along a geography dimension to produce a total quantity available for all warehouses, but the measure cannot be aggregated along a time dimension because the measure represents a periodic snapshot of quantities available. Aggregating such a measure along a time dimension would produce incorrect results.
- Nonadditive
A nonadditive measure cannot be aggregated along any dimension in the measure group that contains the measure. Instead, the measure must be individually calculated for each cell in the cube that represents the measure. For example, a calculated measure that returns a percentage, such as profit margin, cannot be aggregated from the percentage values of child members in any dimension.
The following table lists the aggregation functions in Analysis Services, and describes both the additivity and expected output of the function.
| Aggregation function | Additivity | Returned value |
|---|---|---|
Sum |
Additive |
Calculates the sum of values for all child members. This is the default aggregation function. |
Count |
Additive |
Retrieves the count of all child members. |
Min |
Semiadditive |
Retrieves the lowest value for all child members. |
Max |
Semiadditive |
Retrieves the highest value for all child members. |
DistinctCount |
Nonadditive |
Retrieves the count of all unique child members. |
None |
Nonadditive |
No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If no value can be read from the fact table for a member, the value for that member is set to null. |
ByAccount |
Semiadditive |
Calculates the aggregation according to the aggregation function assigned to the account type for a member in an account dimension. If no account type dimension exists in the measure group, treated as the None aggregation function. For more information about account dimensions, see Account (SSAS). |
AverageOfChildren |
Semiadditive |
Calculates the average of values for all non-empty child members. |
FirstChild |
Semiadditive |
Retrieves the value of the first child member. |
LastChild |
Semiadditive |
Retrieves the value of the last child member. |
FirstNonEmpty |
Semiadditive |
Retrieves the value of the first non-empty child member. |
LastNonEmpty |
Semiadditive |
Retrieves the value of the last non-empty child member. |
Display Formats
You can select the format that is used to display measure values to users by using the FormatString property of the measure.
Although a list of display formats is provided, you can specify many additional formats that are not in the list. You can specify any named or user-defined format that is valid in Microsoft Visual Basic. The following table contains some examples of available named and user-defined formats. The table assumes that the regional setting in Control Panel on the client computer is English (United States).
| Source data type | Format | Display format value | Example output |
|---|---|---|---|
Numeric |
Named |
General Number |
123456789 |
|
|
|
0 |
|
|
Fixed |
123456789.00 |
|
|
|
0.00 |
|
User-defined |
$#,#.00 |
$123,456,789.00 |
|
|
|
$0.00 |
|
|
#,#0.0000 |
123,456,789.0000 |
|
|
|
0.0000 |
Date/time |
Named |
Medium Date |
31-Dec-99 |
|
|
Long Date |
Friday, December 31, 1999 |
|
User-defined |
mm/dd/yyyy |
12/31/1999 |
|
|
mmm-dd-yyyy |
Dec-31-1999 |
Boolean |
Named |
Yes/No |
Yes |
|
|
True/False |
True |
For more information about valid predefined and user-defined formats, search on "format function" in the Development Tools and Languages section of the MSDN Library.
See Also
Concepts
Configuring Measure Group Properties