Skip to Content
Author's profile photo poorna Malamandi Suresh

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.

/wp-content/uploads/2015/10/store_cnt_818294.png

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”


/wp-content/uploads/2015/10/query1_818295.png


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.

/wp-content/uploads/2015/10/cv_transparent_818303.png

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”


/wp-content/uploads/2015/10/query2_818305.png

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:


/wp-content/uploads/2015/10/plan_viz_818309.png



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:


/wp-content/uploads/2015/10/final_query_818311.png

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.


TF_FLAG.png


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”


/wp-content/uploads/2015/10/correct_res_818353.png



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





Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      well written poorna 🙂

      Author's profile photo Former Member
      Former Member

       

      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

      Author's profile photo Former Member
      Former Member

      nice document, was able to understand about transparent filter fully, thanks

      Author's profile photo Christoforos Verras
      Christoforos Verras

      Hey,

      When I define the measures, I do not have the option for SUM.

      Only Max, Min & Count are showing.

      Best,

      Christoforos

      Author's profile photo poorna Malamandi Suresh
      poorna Malamandi Suresh
      Blog Post Author

      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

      Author's profile photo Thuy Hoang
      Thuy Hoang

      Thanks very much, Poorna.

      This thread is really helpful to me 🙂

      Author's profile photo Carlos Orencio Horta
      Carlos Orencio Horta

      🙂 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.

      Author's profile photo Cyril Metge
      Cyril Metge

      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)

      • With No filter, the division is executed after aggregation, and results are correct, as expected
      • If I want to evaluate this calculated column for 3 month cumulated, simply adding the calmonth filter, without displaying the detail of month, the following happen :
        - 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

       

      Author's profile photo Priyanka Sadana
      Priyanka Sadana

      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