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 :
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:
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”
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
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.
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”
Test_Qty column value not matching with Query-1 result set even though both queries are fetching same data only.
SUM(Mat_QTY) = 190
Based on Country level aggregation:
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:
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.