Regular aggregates
Each measure has a regular aggregate. Aggregation rules can be used in addition to the regular aggregate. Aggregation rules define how a measure is aggregated in relation to one of more dimensions. A measure is aggregated by first applying the regular aggregate to all dimensions not specified by aggregation rules, then applying aggregation rules in the order they are listed.
A semi-aggregate measure is a measure that may aggregate differently relative to one or more dimensions within a cube. For example, relative to warehouses, inventory levels are additive. Relative to time, inventory levels are computed as of a point in time. This is typically the first or last occurrence within a time period (first or last day of the month). Therefore, an inventory level measure would have a Regular Aggregate of Sum, and an Aggregation Rule of First or Last relative to the Time dimension.
The Regular Aggregate property can have values of Average, Calculated, Count, Count Distinct, Count Non Zero, Custom, Maximum, Median, Minimum, Standard Deviation, Sum or Variance.
The Custom value indicates that the value of the measure is computed by an external business process. Custom measures are a specialized form of non-distributive measure that do not roll up. Values must exist in the measure or aggregate tables at the precise level of aggregation required for a query, otherwise the values are shown as Null. You can customize measure values using advanced business logic and make those values available in IBM® Cognos® Analytics with Watson.
The Calculated value controls the order of operations for calculations. When you use a Calculated Regular Aggregate, IBM Cognos Dynamic Cubes first aggregates each measure in the expression using its Regular Aggregate property. Then, it uses the values of the aggregated measures to calculate the expression.
Use Sum and Count aggregates rather than Average where possible. You can also use simple calculations by selecting a measure and assigning a rule, such as Average.
| Location | Time | Sales | Average Returns |
|---|---|---|---|
| USA | Q1 | 10 | 2 |
| USA | Q2 | 30 | 4 |
| USA | Q3 | 50 | 6 |
Sales is defined with a Regular Aggregate of Sum. Average Returns are defined with a Regular Aggregate of Average.
In this example, the calculated measure, Measure A, is defined by the expression (Sales - Average Returns).
If Measure A is assigned a Regular Aggregate value of Sum, its value is computed as follows if grouping by distinct values of Location.
10 - 2 = 8
30 - 4 = 26
50 - 6 = 44
-------------
Measure A 8+ 26 + 44 = 78If Measure A is assigned a Regular Aggregate value of Calculated, its value is computed as follows if grouping by distinct values of Location.
Sales 10 + 30 + 50 = 90
Average Returns (2 + 4 + 6) / 3) = 4
--------------------------------------
Measure A 90 - 4 = 86