We have Calculation view in HANA which is union of two analytical views. The calculation view performs under 30 secs for 1 year dates and in 8 secs for one month date range.
However when user tries to filter the data retrieved in step 1 the filter set takes more than 1 min and 15 secs even for the full results that was retrieved in step 1. This is consistent in all applications (Webi, explorer and data preview in HANA studio) for a specific dimension.
We have checked the following
1) The indivdual ANALYTICS views that are part of the calculation view has good performance and is less than the calculation, so it should not be join issues as the analytics views perform well
2) The SQL statement generated by the calculation view becomes extremely complex with nested select when the filter for the dimension is performed.
3) We have not created any index on the dimension. This is our last resort we want to avoid creatng index on the dimension.
Any performance tips will be greatly appreciated.