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:
PRODUCT | STORE | CUSTOMER | QUANTITY | REVENUE | UNIT |
---|---|---|---|---|---|
Budweiser | EDEKA | IBM | 360 | 720 | BOTTL |
Coke | EDEKA | ALDI | 260 | 390 | BOTTL |
Coke | EBAY | IBM | 200 | 300 | BOTTL |
Coke | EDEKA | IBM | 250 | 375 | BOTTL |
Headset | METRO | ALDI | 2 | 120 | PACKG |
Headset | EBAY | IBM | 10 | 600 | PACKG |
ipad | EBAY | ALDI | 10 | 6000 | PACKG |
ipad | METRO | ALDI | 10 | 6000 | PACKG |
ipad | METRO | IBM | 10 | 6000 | PACKG |
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
well written poorna 🙂
Hi Poorna,
Thank you SO MUCH! I was actually holding my breath as I followed these directions. It worked beautifully!
It's great to utilize the power of internet with the available free blogs.
Appreciate your effort for making such useful blogs and helping the community.
Obrigado,
Sunitha
nice document, was able to understand about transparent filter fully, thanks
Hey,
When I define the measures, I do not have the option for SUM.
Only Max, Min & Count are showing.
Best,
Christoforos
Hi Christoforos,
If the measure are of Numeric Data Type then itself we see the aggregation of type SUM.
For a non-numeric data type only Max, Min & Count type aggregations are allowed.
Thanks,
Poorna
Thanks very much, Poorna.
This thread is really helpful to me 🙂
🙂 Great example to understand the "Transparent feature" feature . An additional way to address the issue could be set the aggregation = count for the StoreCount in the TopView.
Hi Poorna,
Thanks a lot for this post that was very helpful.
I'm experiencing exactly the same behavior without a counter, but a simple calculated column where we are calculating a simple division (Actual/Forecast)
- 0calmonth is added in the group by
- division evaluated at month level
- result of each month (wrongly) cumulated at the querried level
-> In this situation, like in the one you described, using the transparent filter solves the issue, and result are now correct when testing via data preview or SQL.
The issue is that I tried to integrate this view in a BW HANA Composite provider, and then, filtering is adding again the attribute in the group by, leading to incorrect result.
I don't exactly know if the HCPR is not compatible with transparent filter, or if the composite itself is adding filter attribute to the drill down.
Do anybody know if a transparent filter option can also be used with composite provider ?
Such a feature would allow the used of intermediate aggregation / Keep flag feature directly inside the view instead of using BEX exception aggregation that in many cases are not pushed down to HANA. Any input / help on this topic would be greatly appreciated.
Cheers,
Cyril
Hi Poornapragna,
very well explained!
I have a doubt:
Why cannot we use the field in a join condition if it has transparent filter set as true?
Thanks,
Priyanka Sadana