Skip to Content

In some prototyping with Calculation Views in HANA, I came across something I thought was of interest so I decided to share. This involves the various behaviors and pushdowns that occur with regard to the use of Analytic Privileges within a Calculation View. The use of this concept can help minimize data transfer between processing layers and ultimately be a good tool for improving performance in your models.

This should complement some of the previous discussions/blogs that illustrate how variables or direct input parameters can be effectively pushed down.

So let me set up the scenario

1) Created a simple calculation view that consumes the same Analytical View twice via projections. Material is the common attribute and a different measure is selected from each. These two projections are unioned together and then aggregated for the final output, see a graphical representation of this below.

Calculation View.JPG

2) Create an Analytic privilege with the following – see attached for screenshot

– Analytic View privilege; Attribute View restriction on MATNR, no restriction assigned

– Calculation View privilege; dimension on MATNR

Analytic Privilege.JPG

3) Choose Visualize Plan Following SQL statement with a user that is assigned the Analytic Privilege we just created.

SELECT “MATNR”, “VVREV”, “VVRET”

FROM “_SYS_BIC”.”sandbox.Justin/CV_COPA_AP_TEST”

We can observe in the Visualization that there is no restriction/filter pushdown in the OLAP/Analytic Engine (no BWPop Search) since we have not added an Analytic Privilege with any restrictions.

Visualize 1 - Overview.JPG

Visualize 1 - Detail.JPG

4) Modify the Analytic Privilege to include a restriction at on the Calculation View field MATNR (not the Analytic View).

Analytic Privilege - CV restriction.JPG

5) Execute the SQL statement to check result (only one material is now returned) and also start up the Visualize Plan as before. We can notice that now even through the attribute restriction is at the Calculation view level, the optimizer recognizes the field mapping and can push the analytic privilege restriction down to the lowest possible level, which in this case is the Analytic View.

SQL REsult 1.JPG

Visualize 2 - Overview.JPG

Visualize 2 - Detail.JPG

6) I also changed the name of the MATNR field in the Semantic node to MATNR_2 to ensure that a simple name recognition was not taking place and the filtering occurring. I found that the translation of the field is also occurring and causing filter pushdown, so the mapping of the field is truly interpreted correctly.

SELECT “MATNR_2”, “VVREV”, “VVRET”

FROM “_SYS_BIC”.”sandbox.justin/CV_COPA_AP_TEST”

7) Additionally, placing an Analytic Privilege column  restriction on the Analytic View directly will also achieve the same result.

So in summary, my intent here was to illustrate a technique that will help analyze and speed up your models. Since one of the core concepts in HANA data modeling is to avoid passing too much data to each level in the model, the various processing engines and also physically to a client tool, using Analytic Privileges should be a strong consideration in your strategy to achieve this. Previously, my assumption was that placing an Analytic Privilege on a Calculation view would only cause a restriction AFTER the final result was calculated, but here we can clearly see that it is not the case.

Happy HANA!

Justin

To report this post you need to login first.

2 Comments

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

  1. Justin Molenaur Post author

    Just want to revisit this post as I am seeing a slightly new interface look with the Visualize plan in HANA Studio rev70.

    In the plan for analyzing a query on an Analytic view, there is a clear step in processing to perform the SELECT in the dynamic analytical privilege that executes ahead of the OLAP engine. The step seems to execute ALL dynamic analytic privilege stored procedures, not just the ones related to the specific view being queried, so that may be of interest too.

    Anyhow, on to the plan. This specific user has the two separate dynamic AP’s assigned, one for the model being queried, and one for another unrelated model. The query being issued is very simple, one column and one SUM aggregation.

    We can see from this visualization that there is an extra Calc engine step to the right that is reading the authorization tables. Correspondingly, there is a BWPopSearch node on the actual data. So the calc engine determines what is authorized then uses it in the main calc search process or “issues” the found criteria to the OLAP engine.

    Plan 1.jpg

    In the timeline view, we can also observe that the extra calc engine step executes as a prerequisite to starting the Analytic Search activities.

    Plan 2.jpg

    Happy HANA,

    Justin

    (0) 
    1. Rahul Pant

      Hi Justin,

      Thanks for this great blog!!

      We plan a scenario where an authorization table will maintain all restrictions for all users, and all restrictions are via Dynamic AP’s. So, since you mention that 

      “The step seems to execute ALL dynamic analytic privilege stored procedures, not just the ones related to the specific view being queried, so that may be of interest too

      Will this mean any significant performance impact (when compared to static AP’s) while using this approach( As all DAP’s will be executed for each user irrespective of whether they are needed for specific view)

      Regarsd,

      Rahul

      (0) 

Leave a Reply