CODEWITHSUNDEEP
reporting-servicesssasmdxcube
Sonic Soul
Sonic Soul

Reputation: 24909

MDX query running slow, but only in certain arrangement

I am programmaticly generating a MDX query in the following format:

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

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.

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)

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.

the dimension is joined to the measure by ID field (int)

the dimension has 238 values

if i do a simple query:

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

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.

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.

Upvotes: 0

Views: 1526

Answers (2)

Sonic Soul
Sonic Soul

Reputation: 24909

After a lot of trial and error, I got my query to process in 6 minutes (from 3.5 hours).

2 things allowed me to optimize it this way:

1. Use Month/Year members in Date Range instead of Dates
Before:

SUM ({[Time].[Calendar].[Date].&[2011-11-01T00:00:00]:[Time].[Calendar].[Date].&[2011-11-30T00:00:00]

After

SUM ({[Time].[Month].&[2011-11-01T00:00:00]

This was huge. Since OLAP treats each date member as a separate entity, it cross joins all the dates in the range, with all other filters. In my case, i wanted a full month, or full year, so there was no need for a date range.

2. Put all common filters (in all the members) into common WHERE clause

In my case, i had quite a few members sharing the same filters. Simply putting them into the bottom of the query, sped things up another 40%.

In the end, my query went from 4 hours, to 6.5 minutes. I can further speed it up by adding appropriate aggregations in my cube definition. but didn't get to that part yet.

Upvotes: 1

ic3
ic3

Reputation: 7680

Can you try with this version ? :

 WITH 
   MEMBER [Measures].[MTD] AS
    SUM ({[Time].[Calendar].[Date].&[2011-11-01T00:00:00]:[Time].[Calendar].[Date].&[2011-11-30T00:00:00]
    * {{[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],[Dimension1].[Dimension1].[Val1],[Dimension2].[Dimension2].[Val2],[Dimension3].[Dimension3].[Val3])  )

The - sign before [Dimension5] it's a mistake ?

It's making the crossjoin a bit smaller and putting single values in the tuple. Good questions if this improves performance.

Upvotes: 0

Related Questions