HANA Performance - Union Pruning

[former_member285763]

Hello Team,

I read that with union pruning configurations , we can control the data sources with in the union node which needs to be executed/skipped as per the reporting SELECT statements where clauses.

But, I have different kind of pruning/performance requirement , Just wanted to know whether it can be possible or not with UNION in calculation view.

Use case:

1. We have different customers in our business (They are grouped under 3 types of customers).

2. Each customer type has their own business logic & accordingly 3 HANA views are developed

So, I want to create a Final View by performing a Union with all 3 HANA views which are created for each customer type.

Based on the customer who triggers this Final HANA view, It has to Skip the other two HANA views inside the Union. Is that possible with Union Pruning Configuration??

[dvankempen]

You asked a question. Don't forget to mark the answer that helped you most as correct. Thanks!

For the readers, before you leave, don't forget to up/down vote the answers. You can vote on the question too.

Thanks.

[lbreddemann · Accepted solution]

yes, that’s possible.

It doesn’t even require a union pruning configuration. Constant union mapping can do that.

[former_member285763 · Accepted solution]

Thank you lars.breddemann for your inputs.

In my case I have below questions , It would be great if u can clarify them.

I will union all of the 3 views & create some union constant column(CUSTOMER_TYPE) with values: Type1, Type2, Type3.

1.This Union Pruning will only be called only when this CUSTOMER_TYPE column is Part of WHERE clause of the SQL which is being triggered right ???

2. If the answer is YES for the point 1, Then we might have to go with VARIABLE on CUSTOMER_TYPE right ? instead of Input Parameter, As WHERE clause will only be generated with Variables ?

3. But, In Variables screen, we dont have Parameter Type:Derived From Procedure/Scalar Function (or) Derived From table options right ? As this CUSTOMER_TYPE filtering should happen on Security based on the customer who is accessing the View(Assume we have maintained some security table for CUSTOMER ID Vs CUSTOMER_TYPE relationships)

Regards,

Kiran

[lbreddemann · Accepted solution]

to 1) Nope, it does not need to be part of the WHERE condition. It also works if, e.g. there is a filter condition that evaluates to constant true/false.

to 2), 3) n/a

For things like "data only visible for specific users" you may want to consider analytic privileges.

[former_member285763 · Accepted solution]

Thanks lars.breddemann for your confirmation that IP also work for Union Pruning with expression filters.

If we go with Dynamic Analytical privileges the row level security will be applied at the top after executing the entire view right ? is it not increasing the execution time?

Thanks

Kiran