This example is referenced in my other post about the usage of the "Keep Flag" in SAP HANA Calculation Views. Please have a look at the other post to get a better understanding of the context for this example
The following example illustrates the impact of the "Keep Flag" that can be set in HANA Calculation Views since HANA 1.0, SPS 09 to control which fields are kept during aggregation. It will also be discussed when setting the keep flag is not warranted.
1.) Create table "DrinksReceipt" and insert example data
CREATE COLUMN TABLE "DrinksReceipt" (day Date, hour VARCHAR(2), PRODUCT NVARCHAR(40),AMOUNT Decimal(10,2), CONVERSIONRATE Decimal(4,2));
INSERT INTO "DrinksReceipt" VALUES ('2017/02/02','10','Wine',30,1);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/02','18','Wine',20,2);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/02','10','Beer',40,1);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/02','10','Beer',50,3);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/03','10','Beer',30,2);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/03','18','Beer',40,1);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/03','11','Wine',50,2);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/03','19','Wine',100,3);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/03','20','MinearlWater',20,4);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/04','10','MineralWater',30,5);
INSERT INTO "DrinksReceipt" VALUES ('2017/02/04','10','Wine',40,6);
2.) Create a model that consumes these data. The result of the respective steps are shown in the screenshot below
2.1) Include table in "DrinksReceipt" as source in a Calculation View of type "Cube"
2.2) Add all columns to the output and create a calculated column "cc_payment" of type Decimal(11,2) using the following expression:
"AMOUNT"
*"CONVERSIONRATE"
2.3) use aggregation type "Avg" for column “conversionRate”
3.) Compare the results when aggregating only by day and when aggregating by day and hour using the following SQL (you will have to replace <viewName> with the name of your Calculation View):
SELECT
'OnlyDay' "AggregationType",
sum("Payment")
FROM
(
SELECT
"DAY",
sum("cc_payment") AS "Payment"
FROM
"_SYS_BIC".<View_Name>
GROUP BY
"DAY"
)
UNION ALL
SELECT
'DayAndHour',sum("Payment")
FROM
(
SELECT
"DAY",
"HOUR",
sum("cc_payment") AS "Payment"
FROM
"_SYS_BIC".<View_Name>
GROUP BY
"HOUR",
"DAY"
)
This SQL combines the result when aggregating (averaging) the conversion rate only by "Day" and when aggregating by "Day" and "Hour" into one output. You should thus see the following results:
You can see that the payment sum differs depending on what attributes are used in the group-by. The reason is that the multiplication operator used in the calculated column is sensitive to the order of aggregation/calculation.
If you want to ensure that the multiplication is always done on the aggregation level of “DAY” and “HOUR” you can set the keep flag for these two attributes. Re-check with the above statement and see that both aggregation statements lead to the same result.
This is a good workaround if you are sure that you always want to aggregate at the day and hour level. However, imagine you are offered the option to use an average conversion rate per day instead of per hour. If you are considering which option minimizes your payment you do not want that “HOUR” is always included in the aggregation even if you only group by “DAY” in the SQL. In this case using the "keep flag" would not be indicated. Also keep in mind that using the keep flag can lead to a higher than neccessary granularity during processing. This higher granularity will be reflected in an increased temporary memory consumption and a higher CPU load compared to when processing is done at a lower granularity.
This example is referenced in my other post about the usage of the "Keep Flag" in SAP HANA Calculation Views. Please have a look at the other post to get a better understanding of the context for this example