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 :

/wp-content/uploads/2016/09/data_1027471.png

Transport Quantity :

/wp-content/uploads/2016/09/data2_1027500.png

User restrictions:

/wp-content/uploads/2016/09/data3_1027501.png

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:

/wp-content/uploads/2016/09/data4_1027512.png

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.

/wp-content/uploads/2016/09/data5_1027552.png

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:

/wp-content/uploads/2016/09/data8_1027621.png

/wp-content/uploads/2016/09/data19_1027655.png

/wp-content/uploads/2016/09/data9_1027656.png

 

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.

To report this post you need to login first.

1 Comment

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

Leave a Reply