Original: BI project notes aggregation functions at design time for cube metrics
Microsoft SQL Server Analysis Services provides several functions to aggregate measures against the dimensions that are included in the measure group. By default, measures are summed by each dimension. However, you can modify this behavior by using the AggregateFunction property. The additive of an aggregate function determines how the measure is aggregated in all the dimensions of the cube. Aggregation functions have three levels of additive:
Additive-additive measures primarily mean that the value of a member in a parent hierarchy is equal to the sum of all its child member values.
- Sum-the parent member value equals the sum of all its child member values, which is the default aggregate function of the SSAS Analysis service.
- Count-a function that calculates a non-null value for a particular column in a fact table, or calculates the number of rows in a fact table. A parent member can also be evaluated by adding all its child member values.
Semi-additive-semi-additive measures are aggregated only for certain children.
- The max-parent member value equals the minimum value in all of its children.
- Min-The parent member value equals the maximum value in all of its children.
- FirstChild-The value of the parent member is equal to the sum of the child member values, but if the value of the parent member is equal to the value of the first child member in the Time dimension.
- LastChild-The value of the parent member is equal to the sum of the child member values, but if the value of the parent member is equal to the value of the last child member in the Time dimension.
- Firstnonempty-The value of the parent member is equal to the sum of the child member values, but if the value of the parent member is equal to the value of the first non-empty member in the Time dimension.
- Lastnonempty-The value of the parent member is equal to the sum of the child member values, but if in the time dimension, the value of the parent member is equal to the value of the last non-empty empty member.
- Averageofchildren-Evaluates all the dimensions in the cube's time dimension at the lowest level of granularity, and then averages the values. (non-loophole member)
- ByAccount-When a cube contains a dimension of an account type, you need to use the aggregate by Account function. The per-account aggregation function for a measure is a property of the dimension accounts member.
Non-additive-the value of a parent member cannot be valued by its own.
- DistinctCount-a non-repeating calculation that counts columns that have no duplicates in the fact table, and the member values are determined by the no-duplication technique for that member.
- None-do not make any aggregations.
Reference documents:
To configure a measure Value property
http://technet.microsoft.com/zh-cn/library/ms175623 (v=sql.105). aspx
Define semi-additive behavior
http://technet.microsoft.com/zh-cn/library/ms175356 (v=sql.105). aspx
Using aggregate functions
http://technet.microsoft.com/zh-cn/library/ms365396 (v=sql.105). aspx
Measures and measure groups
http://technet.microsoft.com/zh-cn/library/ms174792 (v=sql.105). aspx
BI Project notes aggregation functions at design time for cube metrics
