- SAP Managed Tags:
The following text provides background information about the "Keep Flag" that can be set in Calculation Views since HANA 1.0 SPS 09
Ensure that calculations occur at a granularity that includes attributes for which the Keep Flag is checked
Whenever the level of granularity has an impact on the outcome of a measure. This means whenever the sequence matters in which calculations and aggregations are executed
The following is a small example where the sequence matters and different results occur depending on whether aggregation (+) or calculation (*) happens first:
Example data:
Day value1 value2
1 2 3
1 3 4
2 1 3
a) First aggregate on day level afterwards multiply.
Aggregation results in:
Day value1 value2
1 5 7
2 1 3
Multiplication leads to:
Day value1 value2 multiplied
1 5 7 35
2 1 3 3
b) First multiply than aggregate on day level.
Multiplication results in:
Day value1 value2 multiplied
1 2 3 6
1 3 4 12
2 1 3 3
Aggregation creates:
1 5 7 18
2 1 3 3
You want to determine on how many days per year the maximum air temperature was above 30 degrees. The granularity of your data is on year, month, day, and hour level (source: Deutscher Wetterdienst)
Use a calculated column that is set to “1” if the maximum temperature is above 30 degrees and “0” otherwise. Sum this column up to determine the total number of days where the temperature was above 30 degrees
With this approach the results vary depending on what granularity (e.g., year alone, or year, month and day) the calculation is requested because the maximum is calculated at the level of aggregation.
Use keep flag to ensure that the outcome of the “above 30” evaluation is always calculated when year, month, and day are included in the aggregation level
See here for a detailed instruction of the example
a) The keep flag will force a fine granular aggregation even in situations in which it might not be required. This overhead will lead to increased temporary memory consumption and longer runtimes
b) As the example below illustrates it is not always clear what the correct aggregation granularity should be and therefore the aggregation granularity should not be fixed in these situations
You have purchased wine, beer, and mineral water at different conversion rates. You want to know how much you paid in total. In addition you want to compare whether you are better of with a daily or hourly average conversion rate. To do this calculation you want the aggregation to happen at different aggregation granularities (i.e., day / hour) which might prevent usage of a fixed keep flag.
See here for a detailed instruction of the example
Avoid this kind of calculations that are sensitive to the order of execution and move them to the uppermost node of the uppermost Calculation View in a stacked scenario (see SAP Note 2088371). Use the keep flag to ensure that these measures in the upper-most node will be calculated at the intended granularity if it is clear what the intended granularity is.
Purpose of keep flag:
Ensure that calculations occur at a granularity that includes attributes for which the Keep Flag is checked
When is it important to consider:
Whenever the level of granularity has an impact on the outcome of a measure. This means whenever the sequence matters in which calculations and aggregations are executed
Formally:
Example:
The following is a small example where the sequence matters and different results occur depending on whether aggregation (+) or calculation (*) happens first:
Example data:
Day value1 value2
1 2 3
1 3 4
2 1 3
a) First aggregate on day level afterwards multiply.
Aggregation results in:
Day value1 value2
1 5 7
2 1 3
Multiplication leads to:
Day value1 value2 multiplied
1 5 7 35
2 1 3 3
b) First multiply than aggregate on day level.
Multiplication results in:
Day value1 value2 multiplied
1 2 3 6
1 3 4 12
2 1 3 3
Aggregation creates:
1 5 7 18
2 1 3 3
Modelling Example 1:
You want to determine on how many days per year the maximum air temperature was above 30 degrees. The granularity of your data is on year, month, day, and hour level (source: Deutscher Wetterdienst)
Approach:
Use a calculated column that is set to “1” if the maximum temperature is above 30 degrees and “0” otherwise. Sum this column up to determine the total number of days where the temperature was above 30 degrees
Problem:
With this approach the results vary depending on what granularity (e.g., year alone, or year, month and day) the calculation is requested because the maximum is calculated at the level of aggregation.
How can modelling be improved:
Use keep flag to ensure that the outcome of the “above 30” evaluation is always calculated when year, month, and day are included in the aggregation level
See here for a detailed instruction of the example
Why not set the keep flag per default?
a) The keep flag will force a fine granular aggregation even in situations in which it might not be required. This overhead will lead to increased temporary memory consumption and longer runtimes
b) As the example below illustrates it is not always clear what the correct aggregation granularity should be and therefore the aggregation granularity should not be fixed in these situations
Modelling Example 2:
You have purchased wine, beer, and mineral water at different conversion rates. You want to know how much you paid in total. In addition you want to compare whether you are better of with a daily or hourly average conversion rate. To do this calculation you want the aggregation to happen at different aggregation granularities (i.e., day / hour) which might prevent usage of a fixed keep flag.
See here for a detailed instruction of the example
General recommendation:
Avoid this kind of calculations that are sensitive to the order of execution and move them to the uppermost node of the uppermost Calculation View in a stacked scenario (see SAP Note 2088371). Use the keep flag to ensure that these measures in the upper-most node will be calculated at the intended granularity if it is clear what the intended granularity is.
1 Comment
