Analytic Privilege behavior within Calculation View/Analytic View - VizPlan details

[justin_molenaur2]

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.

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

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.

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

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.

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