Example 1 using ‘Keep Flag’

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 SPS 09 to control which fields are kept during aggregation.

1.) Import csv data from file temperature.csv (the file derived from “Deutscher Wetterdienst” can be found here)

1.1) Select “Import” from File menu and choose “Data from Local File”

1.2) Set flag “header row exists”, use “;” as  separator.

1.3) Put the table in a schema used as a source for modeling. Name it e.g., “TEMPERATURE”

1.4) make sure that field “MESS_DATEN” is recognized as VARCHAR

2.) Create model “CVTemperature”

2.1) Create new Calculation View with name “CVTemperature”

2.2) Add 1 projection and 1 aggregation node (name them like in the screenshot below)

2.3) Use table “TEMPERATURE” as a data source in the projection node

2.4) Add fields “MESS_DATUM” and “LUFTTEMPERATUR” to the output

The model should now look like in the screenshot below.

2.5) define the following calculated columns in the projection node (language SQL):

      cc_year: to_varchar(year(“MESS_DATUM”)), VARCHAR 4

      cc_month: to_varchar(month(“MESS_DATUM”)) VARCHAR 2

      cc_day: to_varchar(dayofmonth(“MESS_DATUM”)) VARCHAR 2

      cc_hour: to_varchar(hour(“MESS_DATUM”)) VARCHAR 2

2.6) set “LUFTTEMPERATUR” to aggregation mode “max” in the added aggregation node

2.7) add all fields to the output except “MESS_DATUM”

2.8) In the default aggregation node: add all fields to the output and add the following calculated column with Data Type Integer and language SQL:

    cc_above_30: “LUFTTEMPERATUR”>30

3.) Test the model by running following SQL (replace “<view_name”> with the name of your created view:

SELECT "cc_year", sum("cc_above_30") FROM "_SYS_BIC". WHERE "cc_year"='1950' GROUP BY "cc_year" 

You see the result “1” for year “1950”

With the following SQL on the table you can check that there are more days with a temperature above 30 degree:

SELECT * FROM TEMPERATURE WHERE year(MESS_DATUM)='1950' AND LUFTTEMPERATUR>30 

You will see the following result:

There are in fact 2 days (the last two characters of “MESS_DATUM” represent hour) where the temperature was above 30 degrees

Why did the query to the Calculation View retrieve only “1” for “cc_above_30”?

The SQL statement only aggregated by column “cc_year”. Due to the logic of Calculation Views this meant that the other columns could be pruned away because they were not requested. In result at the node where cc_above_30 is calculated only one line is left for year 1950 (month, day, and hour are pruned away). Summing this single line up results in “1”.

How can modelling be improved

Set the keep flag for the attributes which should be kept in the aggregation when the calculation occurs. If you are interested in the number of days with maximum temperature above 30 degrees you need to keep the detail of year, month, and day. The keep flag has to be set for these three columns at each aggregation node:

Rerun the SQL query from above.

SELECT "cc_year", sum("cc_above_30") FROM "_SYS_BIC". WHERE "cc_year"='1950' GROUP BY "cc_year" 

You should see the result “2” for year “1950” because the fields for year, month, and day are kept in the aggregation where the calculation of “cc_above_30” occurs even though they are not requested by the query.

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