BI Project notes aggregation functions at design time for cube metrics

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.