Rules for interpreting calculated aggregations
The Calculated setting in the Regular Aggregate property controls the order of operations for calculations
- 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:
Select 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].[debt] / [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
Forclause is explicitly specified.
- Granularity of aggregate functions is set by grouping for determinants
or by keys of corresponding levels in the cube.
Rank([namespace].[Company].[debt]is interpreted as
Rank([namespace].[Company].[debt] for Report).