Rules for interpreting calculated aggregations

The Calculated setting in the Regular Aggregate property controls the order of operations for calculations

In Framework Manager, the Calculated aggregation type setting only applies to:
  • stand-alone calculations
  • calculations that are based on measures from a measure dimension and embedded within that same measure dimension.

It is not supported for calculations that are embedded within query subjects.

For more information, see Order of operations for model calculations.

Framework Manager uses the following rules to interpret the calculated aggregation type in the Regular Aggregate property.

  • Standard aggregation functions (average, count, maximum, minimum, standard deviation, sum, variance) and references to model query subjects are aggregated first. The remaining operations are then applied to the aggregation result.

    For example, to divide debt by credit for each row, the SQL looks like this:

    customer, debt, credit, debt/credit as
    percent_debt from x

    To aggregate for all customers, the SQL looks like this:

    Select sum(debt), sum(credit), sum(debt)/sum(credit)
    as percent_debt from (Select customer, debt, credit from x)
  • If the query item in the calculation is a fact and the aggregation type for the query item is average, count, maximum, minimum, or sum, the aggregation type of the query item is used.
  • If the query item in the calculation has no aggregation type set, the aggregation type minimum is applied in the query. It is not possible in SQL to have an aggregation setting of none.
  • Aggregate functions are interpreted as if they are applied to a value in a single row when these functions are used in the detail context. For example, a Reporting report has the Auto Group and Summarize property that is set to false.
  • Aggregation of a query item is based on the aggregated expression derived from the item definition.

    For example, you want to total this stand-alone calculation:

    / [namespace].[Company].[credit]

    The calculation is aggregated with this expression:

    Total([namespace].[Company].[debt]) / Total([namespace].[Company].[credit])
  • Scalar aggregates, also known as running, ranking, and moving aggregates, are calculated for report granularity unless the For clause is explicitly specified.
  • Granularity of aggregate functions is set by grouping for determinants or by keys of corresponding levels in the cube.

    For example, Rank([namespace].[Company].[debt] is interpreted as Rank([namespace].[Company].[debt] for Report).