Skip to Content
Author's profile photo poorna Malamandi Suresh

Union Node Pruning in Modeling with Calculation View

Data Modeling in SAP HANA using calculation view is  one of the supreme capabilities provided for end user to mold his raw data in to a well structured result set by leveraging multiple operational capabilities exposed by calculation view. On the way of achieving this we need to think on different aspects involved.

Let me take few lines here to quote some of the real world examples to provide better clarity on my talk.

We all know that there are two major parameters which we generally take in to consideration when we are qualifying or defining the standard of any automobile, which are nothing but ‘Horse Power(HP)’ and ‘Mileage’ of the automobile. There is always a trade off between the two, by which i mean that a higher HP automobile yields  reduced mileage and vice versa. Why does this happen? It is because we are making the underlying mechanical engines  to generate more HP and thus consuming most of the source energy(fuel) for this purpose.

Let us now get back from the mechanical world to our HANA world and start thinking the underlying execution of HANA database analogous to the above quoted mechanism.

When our calculation view starts computing complex calculations on big data its a matter of fact that the we will  have a  trade off between the performance and volume of data.

In this kind of Big Data scenarios where in we are expecting a better Horse Power from the underlying engine, let us even think how can we make the mileage/Performance also to be better in the current document.

One of the new features of HANA SPS11 called ‘Union Node Pruning in calculation view ‘supports us to achieve this by reducing the cost of execution of calculation view by Pruning union operation dynamically based on the query by end user.

Let us understand this by an example : Consider that we are creating sales report for a product across the years using a calculation view. The view consists of two data sources, which  are current sales data(for YEAR >= 2015) and archived sales data(YEAR <= 2014) and both of which are provided as input to union node of calculation view as shown below :

CV_PRUN.PNG

Now think of a scenario where in we are querying the calculation view to get the result of current_sales, wouldn’t it be great if the underlying execution engine queries only on the current_sales based data source and prune the operation on the archived data source.

Yes, this can now be achieved in the case of union operation in a calculation view by providing pruning definition in a predefined database table which is called as Pruning configuration table.

Definition of the pruning configuration table should be of the below format :

union_prun.PNG

and an example content  for the pruning configuration  table is as below :

pruning_content.PNG

CALC_SCENARIO comprises of the calculation view that involves union node pruning and INPUT column takes the data source names involved in the union node of calculation view.

Now in the advanced view properties of calculation view mention this pruning table as shown below:

view_properties.PNG

Now activate the above view which involves 2 data sources PRUN1 and PRUN2 with pruning configuration table.

And execute the query on that view involving a filter condition that is equal to the condition mentioned in the pruning configuration table :

SELECT

  “ID”,

  “YEAR”,

  sum(“SALES”) AS “SALES”

FROM “_SYS_BIC”.”hanae2e.poorna.sp11.ws42/CV_PRUN”

WHERE ((“YEAR” > (‘2005’)))

GROUP BY “ID”,

  “YEAR”

Visualize the plan for the above query and we see that the union node is pruned as the filter condition matches the one in pruning configuration table, which is  as shown below :

plan_viz.PNG

Now remove the pruning configuration table from view properties of the calculation view, activate it and execute the above query again and perform the plan viz of the same. We now see the union node coming in to picture, thus the query invoking both archived data and  current data in spite of requirement just being the current sales data.



union_non_prun.PNG

Thus Union node pruning in CV now helps to decide how the execution flow must be carried based on the query dynamically

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

Thank you

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Naresh Gadamsetti
      Naresh Gadamsetti

      Thank you for the blog

      is it possible to change the values in pruning table dynamically?I understand that you need to insert the data into this config table like 1 for 2014 and other for 2015 but I like to make this dynamic for any year not hard coding.

      Is this possible?

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

      Hi Naresh,

      With the current feature development, insertion of value to the config table is a prerequisite.

      So achieving it is done manually in the above example.

      We can definitely try to achieve the dynamicity of config table based on requirements using other database  artifacts like triggers or procedures.

      Thanks and Regards,

      Poorna

      Author's profile photo Rahul Sancheti
      Rahul Sancheti

      Hi Suresh,

      it turns out that the pruning only works if you include PROJECTIONS in the UNION Node. Is it designed so or is a bug ?

      Regards

      Rahul

      Author's profile photo Ravindra Channe
      Ravindra Channe

      Not necessarily, you need to ensure that the config table contains the right node / data source id and the conditions are maintained correctly in the config table.

      Regards,

      Ravi

      Author's profile photo Diego Diaz
      Diego Diaz

      Hi,

       

      I was wondering if there is a way to do Union Pruning based on an Input Parameter.

      So if Input Parameter Year is 2015 only CURRENT_SALES is processed and if Input Parameter Year is 2014 only OLD_SALES is processed.

       

      Thanks,
      Diego

      Author's profile photo Kalyan Dittakavi
      Kalyan Dittakavi

      Hi Poorna

       

      we are on SP 12 Rev 122 and I have very simple "Star Join" Calc view. I maintained my pruning Table properly but i don't see its reading the table. where as when I removed star join and used aggre, pruning works fine.

      does this mean Pruning is not supported for star joins?

      Thanks

      Kalyan

       

      Author's profile photo Guy Livne
      Guy Livne

      Hi  Kalyan,

      I have the same problem as you have with the union pruning and star join, Did you find any solution for this?

      I also notice that with unoin pruning I get long compilation time, about 7 sec., do you have the same problem?

       

      Regards,

      Guy

      Author's profile photo Zach Haupin
      Zach Haupin

      Where is the official product documentation for this feature? SAP shouldn't be expecting their users to piece together various bits of knowledge from forum posts and blogs.

      Author's profile photo Nitesh Ghotiya
      Nitesh Ghotiya

      Can we use pruning configuration table with date data type? I am trying to get a union view working with recent data in Hana and old data in virtual table from non Hana system. My queries don't appear to be using pruning configuration.

      Author's profile photo Sarthak Srivastava
      Sarthak Srivastava

      Hello poorna Malamandi Suresh ,

      Its a really nice blog, but I have a question on it, does it only work when sources in Union are table?

      As I am trying to use multiple CV's instead of Tables but it is not working with that.

       

      Thanks,

      Sarthak Srivastava