Getting the Counters right with stacked Calculation Views

Data Modeling in SAP HANA provides great amount of flexibility to the end user. One of the key capabilities in Modeling sector of SAP HANA is computation of user required calculations. One of the variations in calculation which end user can perform in Calculation Views/Analytic Views is ‘Counter’.

Let us now understand the operations under the hood when stack of Calculation Views are created in a project.

Consider a simple ‘Product Details’ table which comprise of the product information along with its sales information as shown below:

Above table provides product details along with the store in which the product is available.

Let us now go ahead and create Graphical Calculation View that helps in getting the distinct number of stores for each product.

Step 1 : As first step towards achieving the above requirement let us create a Graphical Calculation View of CUBE type and add a projection node to it.Now add the above created table as data source to the projection node and connect the result of it to the default aggregation node in the View

Step 2: Now in the Aggregation Node create a new ‘Counter‘ on STORE column to get the distinct number of stores.

Step 3: Proceed ahead by setting QUANTITY and REVENUE columns as measure with aggregation type SUM, along with the above created counter being measure having ‘Count Distinct’ as its aggregation type and the left out columns are set to attributes. After which save and activate the View.

Once the activation goes successful execute the below SQL Query on top of the calculation view created to get the distinct number of stores for each product.

SELECT “PRODUCT”, sum(“QUANTITY”) AS “QUANTITY”, sum(“REVENUE”) AS “REVENUE”, sum(“StoreCount”) AS “StoreCount”

FROM “_SYS_BIC”.“hanae2e.poorna/CV_DISTINCT_COUNT”  WHERE “CUSTOMER” IN (‘IBM’,‘ALDI’)  GROUP BY “PRODUCT”

Thus the above result set helps us in getting the distinct number of stores for each product.

Till here we do not encounter any surprises while getting the result from ‘Counter’.

Let us now proceed and use the above created view as data source in another calculation view.

Step 4: Create another Graphical Calculation View of CUBE type, add the above created Calculation View as the data source to its aggregation node and extract the semantics as such from the underlying calculation view. After which save and activate the view.

Step 5: Now preview on the latter view in a similar way as we queried on the base view earlier.

SELECT “PRODUCT”,sum(“QUANTITY”) AS “QUANTITY”, sum(“REVENUE”) AS “REVENUE”, sum(“StoreCount”) AS “StoreCount”

FROM “_SYS_BIC”.“hanae2e.poorna/CV_TRANSP_FILTER_UNSET” WHERE “CUSTOMER” IN (‘IBM’,‘ALDI’)GROUP BY “PRODUCT”

Here is the surprise in result set, where the StoreCount which is a ‘Counter’ created in base Calculation View returns different (wrong) result (highlighted ones) when queried from the Top Calculation View.

To understand the reason behind wrong result we have to look at the execution plan of the above executed query:

CvTransparentFilter in the above representation is the Top Calculation View which is having CvCountDiscount as its data source.

Now in the Request Query on the Top Calculation View, we are querying PRODUCT and StoreCount columns along with the Filter applied on the CUSTOMER column.

There by, above query on the Top View sends a request to the underlying View to involve CUSTOMER column also as part of the requested attribute list. Which ends up in grouping the StoreCount value by (PRODUCT, CUSTOMER) which ideally should be grouped only by PRODUCT.

Consequence of which gives us wrong result when queried from Top View.

Step 6: To over come the above surprise in result set, we have a property called ‘Transparent Filter‘, which when flagged as true for CUSTOMER column in the Aggregation Node of Top View(CvTransparentFilter) and also in the Aggregation Node of underlying View(CvCountDistinct) solves the problem by pushing filter operation down to the lower projection node and remove CUSTOMER as View Attribute from the Aggregation Nodes. Which in turn makes the Top View to work on the result set grouped only by PRODUCT column irrespective of the filter column present in the requested query. Better picture of the same is shown below by the execution plan:

Step 7: Below is the ‘Transparent Filter‘ property that needs to be flagged to get the correct value of counter in the final result set when we are working with stacked Calculation Views.

Step 8: After setting ‘Transparent Filter‘ to true in Aggregation Node of both the Calculation Views, query on Top View fetches correct result for Counter column.

SELECT “PRODUCT”, sum(“QUANTITY”) AS “QUANTITY”, sum(“REVENUE”) AS “REVENUE”, sum(“StoreCount”) AS “StoreCount” FROM “_SYS_BIC”.“hanae2e.poorna/CV_TRANSPARENT_FILTER” WHERE “CUSTOMER” IN (‘IBM’,‘ALDI’) GROUP BY “PRODUCT”

Reference for ‘Transparent Filter‘ Flag is available in SAP HANA Modeling Guide under the section ‘Create Counters’.

Hope the provided information is useful. Any suggestion and feedback for improvement will be much appreciated.

Thank you