Slow MDX performance
Posted: Sun Apr 28, 2019 6:11 pm
Running a query against a cube with six dimensions. The cube does not have any rules against it. The cube has had its dimensions reordered to optimize. The query's target dimension has one consolidation with approximately 450K children.
The query:
Every dimension except the one being queried has a leaf element specified. The measure, Allocation Flag, is numeric. This query takes 1:15 (minutes) to execute in PA. In 10.2, the full drill through, including this same query, renders a cube view in approximately 12 seconds, over a minute less and 5x faster.
If I use the Filter by View extract, just to test against, it is virtually instantaneous. The drill through is on a calc cube and uses the alloc.Mapping cube to build the subsets required to display the source allocations of a drilled target intersection, i.e. where did this number come from?
This seems like an extraordinary length of time for a relatively straightforward query. Use a cube view, change title elements and delays of only a few seconds occur as the desired data is rendered.
Wondering if there is something I'm missing. Otherwise, my next step is to try constructing a zero suppressed view, reading it and building the appropriate subsets, but I hate having to code around a problem like this.
The query:
Code: Select all
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [AcctOrg Source] ), 0),
([alloc.Mapping].([Version].[Active_01],
[Pool Master].[100],
[AcctOrg Target].[XXX-100-DIR : 99.XYZ.990057404.56],
[Pool Target].[200],
[Measure].[Allocation Flag]) > 0 ))If I use the Filter by View extract, just to test against, it is virtually instantaneous. The drill through is on a calc cube and uses the alloc.Mapping cube to build the subsets required to display the source allocations of a drilled target intersection, i.e. where did this number come from?
This seems like an extraordinary length of time for a relatively straightforward query. Use a cube view, change title elements and delays of only a few seconds occur as the desired data is rendered.
Wondering if there is something I'm missing. Otherwise, my next step is to try constructing a zero suppressed view, reading it and building the appropriate subsets, but I hate having to code around a problem like this.