We have a general question on SAP HANA in the perspective of modelling. We created a CV with star-join that it contains multiple dimensions. The question is how can we push the filter (given by a business user at the report level) to the lowest level using input parameter/variable on CV. The filter given by business user is a attribute of the dimension, actually that is the point, it is not on the Fact table, the Id of that attribute is on the Fact table.
We have 2 ways for doing that, first one is to use variable at view level and give it as a prompt on report level. But in this case the filter does not go to the lowest level.
Second one is to use union pruning. But actually it is not useful for some cases, because we put the description of the Ids on the union node as constant values. If some of them change, it should be changed on union node also. In this case we use report level prompt as a filter and the filter goes to the lowest level.
Is there any other approach for doing this? Because we are working on huge datasets, we need to filter data as soon as possible and we need to push filter to the lowest level.
Thank you in advance,