EFFECT OF INTRODUCING INNER QUERY /SUB QUERY WHILE FETCHING DATA FROM CALCULATION VIEW IN SAP HANA

This is based on my experience in working with inner/sub query while accessing calculation view in SAP HANA .

NOTE: Using HANA studio Version: 1.00.85.0

To depict the scenario, I have taken a company’s business case where total cost incurred by the company is calculated  based on inventory maintenance and goods transportation cost.

Also there has to be restrictions applied based on User Id.

e.g. User 3  should view the data across ‘ECT’ street only of Country ‘IND’ and City ‘BAN’.

USER 4 should view data across ‘SAR’ street only of Country ‘IND’ and City ‘DEL’.

For modelling the above scenario, I have created 3 tables for Inventory data,transport and user restrictions.

Material Quantity :

Transport Quantity :

User restrictions:

Two Analytic views created for measures from Inventory and Transport.

In Inventory analytical view, one calculated column created with simple addition of 10 to material Quantity.

Calculation view CV_TEST created with union of both the inventory and Transport categories:

So the measures are:

MAT_QTY_CALC: MAT_QTY +10 and calculated in the Inventory Analytic View.

TOT_COST: MAT_QTY_CALC + TRANSPORT_QTY

TEST_QTY: TOT_COST /MAT_QTY_CALC

Now we can execute below query to view the results:

QUERY-1

SELECT “COUNTRY”,SUM(TOT_COST),sum(TEST_QTY),SUM(MAT_QTY_CALC) FROM “_SYS_BIC”.“—/CV_TEST” WHERE “STREET” IN (‘ECT’,‘SAR’) GROUP BY “COUNTRY”

COUNTRY;SUM(TOT_COST);SUM(TEST_QTY);SUM(MAT_QTY_CALC)

IND;280;1.333;210

Explanation:

As we have taken where condition on STREET, it comes into group by clause as well.

So, based on Country and street:

SUM(Mat_QTY) =  190

ECT-20

SAR- 170

SUM(MAT_QTY_CALC)=  210

ECT-30

SAR-180

SUM(Transport_QTY)=70

ECT-10

SAR-60

SUM(TOT_COST)= SUM(MAT_QTY_CALC)+ SUM(Transport_QTY)=280

SUM(TEST_QTY)= SUM(TOT_COST)/ SUM(MAT_QTY_CALC)=280/210=1.333

Visualize plan: Calculations happening at Calculation view level.

QUERY-2

Same query as Query-1 only difference is  Where condition on STREET has been applied using the inner query which in turn will fetch the street data from User table based on USER IDs provided.

SELECT “COUNTRY”,SUM(TOT_COST),sum(TEST_QTY),SUM(MAT_QTY_CALC) FROM “_SYS_BIC”.“—-/CV_TEST” WHERE “STREET” IN (SELECT “STREET” FROM “C1351935BK”.“CON” WHERE “USER” IN (3,4))

GROUP BY “COUNTRY”

COUNTRY;SUM(TOT_COST);SUM(TEST_QTY);SUM(MAT_QTY_CALC)

IND;280;2.666;210

Test_Qty column value not matching with Query-1 result set even though both queries are  fetching same data only.

Explanation:

SUM(Mat_QTY) =  190

ECT-20

SAR- 170

SUM(MAT_QTY_CALC)=  210

ECT-30

SAR-180

SUM(Transport_QTY)=70

ECT-10

SAR-60

IND,ECT combination:

SUM(TOT_COST)= 30+10=40

SUM(TEST_QTY)= 40/30=1.33

IND,SAR combination:

SUM(TOT_COST)= 180+60=240

SUM(TEST_QTY)= 240/180=1.33

Based on Country level aggregation:

SUM(TOT_COST)= 40+240=280

SUM(TEST_QTY)= 1.33+1.33=2.66

Visualization Plan for Query2 :

The query 2 visualize plan is completely different from the Query 1 as it is going to the ANALYTICAL views and doing the calculations:

CONCLUSION:

While accessing the calculation view data, introducing inner queries may lead to incorrect/uncertain results especially involving Average functions, as the calculation will happen at different granularity involving underlying Analytical views. So for introducing dynamic privilege based on User Ids, we can make use of the features provided by SAP HANA e.g. Dynamic analytic privilege.