Slow MDX performance

[PavoGa]

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:

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 ))

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.

[Steve Rowe]

Hi PavoGa,

If you can I would raise with IBM, seems like a defect? May not help you in the short term unless its already been fixed....

[PavoGa]

Hi PavoGa,

If you can I would raise with IBM, seems like a defect? May not help you in the short term unless its already been fixed....

Wondering if that is the case that it has been fixed. There are several reported MDX performance issues with the version we are on (TOPCOUNT, etc) that are fixed in later versions. Dealing with some risk-aversion to updates with the client.

I'll see about getting it raised as a defect and hopefully find out it is fixed.

Thanks, Steve.