Performance problems when filtering and excluding hierarchy node

[Former Member]

Dear all,

I setup a query with a hierarchy linked to it, where I use a "hard-filtered" exclusion on this hierarchy for a specific node AND a variable which the users can use for flexible filtering purposes.

When I execute the query, the runtime of this query is unimaginable high and I really doubt, whether the underlying SQL-Statement is executed (or generated) correctly. Before I start tracing everything I want to know, whether you encountered some similar problems with such kind of selection and de-selection in a query?!

Thanks,

Andreas

[Former Member · Accepted solution]

Hi Andreas,

I had read in one of the performance Docs that,

"Inclusion gives better performance than exclusions.."

Never tried it but you can check if there is any performance improvement by including the nodes you need rather than excluding the one you don`t need..

Let me know..

Ashish

[Former Member]

Hi,

You need to make more analysis on ST03 worlkload analysis in Expert mode for BW system. It will show where the query takes lot of time Database, OLAP Cache , Master data or frontent.

Based on that you need to look for solutions.

Thank you

Arun

[Former Member]

I see your situation Andreas.

Normally this occurs when you have a hierarchy coupled with a variable. This happens also when the look-up data set is huge. As variables liked to hierarchial structures have a huge impact if the data grows by large.

Probable solution that i could think of is partitioning the data on which the query in run if partitioning is possible. But other folks may have better ideas.