Skip to Content

So far we have known different modeling techniques for implementing business scenarios which include data from different sources, but need to be reported together in the output. The classic example for HANA modeling workshop is to display the “Actual” and “Planned” data, which can be combined together in a Calc view using UNION operator.

Planned_Actuals.JPG

Fig 1. Actual vs Planned implementation using Projection nodes in UNION

The similar business case can be modeled for reporting data from different regions where data from each region can be stored in a different table as the data can be sourced from multiple sources each for a given region. In this case also, the HANA model can be based on a Calc view which is created using the UNION of Analytic views with exact same structure for each region and additional information can be supplied for identifying the region to which the data belongs to.

Regions_model.JPG

Fig 2. Combining data for different regions using Analytic Views in UNION

The additional information for the Regions or Actual – Planned data can be added to the model by different methods:

  • Data attribute from the source data: Adding an attribute and populating it with the Region code
  • Calculated attribute in the Projection: With the “Constant Value” of ‘P’ (Planned) or ‘A’ (Actuals)
  • Using new SPS05 feature of “Constant Column

The scope of this document is to analyze the positive impacts of the new feature provided in SP05 called Constant Column. (Please refer to slide 13 in the document).

What is Constant Column

Constant column is used to denote constituent objects in the UNION node of the Calc view. Each of the included objects is identified with the column mapping as the constant values.

How does Constant Column help

Constant column helps the Query pruning in the execution when the Constant column value is used in the filter criteria of the Query.

The following Model information and Query execution plans will help understand the Query pruning using Constant Column.

  • Create 4 tables (TC_R_TAB1, TC_R_TAB2, TC_R_TAB3, TC_R_TAB4) to store data for different regions. Each table contains 3 columns as follows:

C_COL1 : contains the Region code – ‘US’, ‘EU’, ‘AS’, ‘AU’

C_COL2 : contains some text value which can also be used for filtering the data in query

C_COL3 : contains numeric value to be used as a Measure

  • Create Analytic Views one for each table.
  • Create two Calc views, one with Constant Column implementation and one with Source system mapping as follows;

cons_CV.JPG

Fig 3. Calc View with UNION mapping using Constant Column

In the Calc view with Constant Column implementation (ZGCV_TAB_UNI_WCC), the C_COL1 is mapped using the Constant values in the UNION node. Each of the constituent Analytic view is denoted with the Region code for the underlying data.

without_cons_CV.JPGFig 4. Calc View with UNION mapping using Source Column

In the Calc view without Constant column implementation (ZGCV_TAB_UNI_WOCC), the C_COL1 is mapped from the source data. The source data can come from the underlying tables or a Calculated Attribute in the Analytic views.

  • Executing the same query against each of the Calc view and compare the Visual execution plan.

First Query:


select C_COL2, sum(“C_COL3”)

from “_SYS_BIC”.”uniontest/ZGCV_TAB_UNI_WCC”

where C_COL1 = ‘US’

group by C_COL2;

Second Query:

select C_COL2, sum(“C_COL3”)

from “_SYS_BIC”.”uniontest/ZGCV_TAB_UNI_WOCC”

where C_COL1 = ‘US’

group by C_COL2;

The execution plan for the first query on Calc view with Constant column shows that ONLY Analytic view with “US” mapping is queried.

cons_1.JPGFig 5. Execution plan for query on Calc View with Constant column

The execution plan for the First query on Calc view with Constant column shows that ONLY Analytic view with “US” mapping is queried. Here since the mapping of US is done to the TC_R_TAB1 as shown in Fig 3. HANA performs the Query pruning and executes the search accessing ONLY one of the underlying Analytic view. This provides high level of query execution performance as other Analytic views are not queried at all. The other Analytic search shown above are related to internal calculations and summation of the Key Figure.


The execution plan for the Second query on Calc view without Constant column shows that ALL the Analytic views in the UNION are accessed. Although the search results on the Analytic views do not produce any result, no data is returned from the Analytic views.

cons_2.JPG

Fig 6. Execution plan for query on Calc View without Constant column


As you can see in Fig 6. HANA queries each Analytic view and returns the data from the Analytic view which satisfies the Query data criteria. Here Query pruning does not happen and multiple processes / resources are used in the query execution. Although the searches are executed in Parallel, it has been observed that the overall execution time is higher compared to the Query on Calc view with Constant column implementation.

If the queries are executed multiple times in parallel, which is a practical scenario for any reporting application, then the difference in the average execution time is quite high in both the modeling scenarios.

On performing the tests on real life scenario with a UNION of 6 Analytic views, each containing about 200 – 400 million records, it has been observed that the query on the Calc view with Constant column is executed in 1.37 seconds on filtering the data from one table, whereas the same query was executed in 8.9 seconds on the Calc view without Constant column implementation.

The difference for multiple parallel query execution ( 5 queries in parallel) was observed to be average run time of 2.1 seconds for Calc with with Constant column as compared to average run time of 32 seconds on Calc view without Constant column.

I would like to request fellow HANA practitioners to perform similar tests using different models using Constant Column and without Constant Column and share their findings.


To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

    1. Ravindra Channe Post author

      Hi Lars,

      Thanks for the kind words. 🙂 I can never match the professionalism in your blogs / documents. I really look forward to your documents. They are always brimming with knowledge.

      Have a good time Down Under. I am sure the weather there is better than what we have here. 🙂

      Regards,

      Ravi

      (0) 
  1. Justin Molenaur

    Excellent concept and discussion.

    Just to add – a correlation that may be useful to anyone with a BW background is that this is similar to logical partitioning in an Multiprovider. Each underlying cube would be marked with ‘InfoObject specific properties’ (EG ‘US’, ‘EU’), which enables immediate pruning of entire sets of data when querying on the MultiProvider.

    Thanks,

    Justin

    (0) 
  2. Rajarshi Muhuri

    Hi Ravi

    Saw this writeup , as you had posted its link on my question . Very nice insight to Union performance .

    I need to understand the execution plan better  to optimize further : ) any pointers on that ?

    (0) 
        1. Ravindra Channe Post author

          I am a great fan of you, almost to the point of worshiping… 🙂 .

          In Indian culture, the Guru (teacher) is as respectable as God. I had so many opportunities to learn from you, so the references to your posts, praises to your comments will go on for ever.

          I will always be delighted to share the valuable artifacts you post on the forum. 🙂

          Regards,

          Ravi

          (0) 
          1. Lars Breddemann

            Too kind Ravi, far too kind.

            Seeing how much you contributed to the SCN community I can only lift my hat and say “thanks for all your hard work!”.

            I’m sure I’m not the only one appreciating your constant effort of sharing knowledge and exchanging experiences.

            That’s what the whole community idea is about.

            Well done and please: keep on 🙂

            Alright – ‘nough back tapping! Back to work everybody!

            hahahaha

            (0) 
  3. Ramana Krothapalli

    How about query pruning on measures? It does not seem to work like in BW multiprovider.  For example if you union two calculation views called orders and shipments. The measure order_quantity is available in orders calculation view and measure  shipment_quantity is coming from the shipments calculation view. When you execute SQL asking product dimension and just order_quantity or just shipment_quantity, both the calculation views seem to execute. Can you check this in your system?

    Ramana

    (0) 
  4. Jorge Flores Duran

    I tried to do this by introducing calculated constant columns (i.e. a calculated column on each side with static values’) and came with a interesting finding.

    If I made the constant mapping of course the query pruning happens, but when I did standard “by name” mapping, the pruning happens as well!!

    So, to do query pruning, either way works, but if you do “by name” mapping from source fields without calculated ones, there is no pruning!

    Just wanted to share!

    (0) 
  5. Praveen Kumar

    HI,

    Thanks for the very well explanatory document.

    I have a question .suppose i have a projection node on the top of the UNION node and i have created a constant column on the union.

    i am trying to pass Input Parameter for the filter so if i apply a filter on the source column in projection (projection is on the top of Union) will execute only the node selected in Filter or it will execute all the nodes inside union.

    Basically i wanted to confirm, using Input parameters can we perform the pruning as explained in your document.

     

    Thanks

    (0) 

Leave a Reply