StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMDX query running slow, but only in certain arrangement
text
Body
copied!<p>I am programmaticly generating a MDX query in the following format:</p> <pre><code>WITH MEMBER [Measures].[MTD] AS SUM ({[Time].[Calendar].[Date].&[2011-11-01T00:00:00]:[Time].[Calendar].[Date].&[2011-11-30T00:00:00] * {[Dimension1].[Dimension1].[Val1]} * {[Dimension2].[Dimension2].[Val2]} * {[Dimension3].[Dimension3].[Val3]} * {{[Dimension4].[Dimension4].[XXXX],[Dimension4].[Dimension4].[DBS],[Dimension4].[Dimension4].[FD],[Dimension4].[Dimension4].[GSCFDASIA],[Dimension4].[Dimension4].[FDEM],[Dimension4].[Dimension4].[DX],[Dimension4].[Dimension4].[OSWP],[Dimension4].[Dimension4].[OOO],[Dimension4].[Dimension4].[CFD]}} * {-{[Dimension5].[Dimension5].[SELI],[Dimension5].[Dimension5].[SELE],[Dimension5].[Dimension5].[MRDN],[Dimension5].[Dimension5].[WCSG],[Dimension5].[Dimension5].[NEWB],[Dimension5].[Dimension5].[VASS]}} }, [Measures].[Trade Count]) </code></pre> <p>As you can see, it's a simple way to create an intersection of dimensional values, and it has been working great for some time. </p> <p>Recently i rewrote the cube from using a single dimension table with many attributes, to using a separate table for each dimension (for processing performance reasons)</p> <p>The problem, is that when i filter by Dimension4 and Dimension5 together, it is all of a sudden really slow! if i comment out dimension4 and 5, the query takes a second.. if i un-comment Dimension4 query takes 40 seconds.. if i then un-comment Dimension5 line, the query takes 8 minutes. </p> <p>the dimension is joined to the measure by ID field (int) </p> <p>the dimension has 238 values</p> <p>if i do a simple query:</p> <p>select count on Columns, {-{[Dimension5].[Dimension5].[SELI],[Dimension5].[Dimension5].[SELE],[Dimension5].[Dimension5].[MRDN],[Dimension5].[Dimension5].[WCSG],[Dimension5].[Dimension5].[NEWB],[Dimension5].[Dimension5].[VASS]}} on rows from MyCube</p> <p>the query takes sub second. It is something about having a combination of dimensions (up top) that is making it so painfully slow. No idea how to troubleshoot it. </p> <p>something that may be worth noting, is that my cube has 13 partitions, and i have not set up "partition aggregation" step while setting up partitions. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload