Prologika Forums

Hey Teo ... hope you are well!

I have an MDX I'm attempting to code for a SSRS 2005 dataset ... and it is slow!  Heck, it takes forever just for VS to verify the syntax.  Any help would be appreciated ... here it is:

SELECT
    NON EMPTY {
        --[Measures].[Average],
        --[Measures].[Top Bottom Ratio],
        [Measures].[Answer Count]
    } ON COLUMNS,
    NON EMPTY {
        filter (
            [Survey].[Survey Year].[Survey Year].ALLMEMBERS *
            [Question Answer].[Question Number].[Question Number].ALLMEMBERS *
            [Question Answer].[Question Report Abbr].[Question Report Abbr].ALLMEMBERS *
            [Question Category].[Question Category].[Question Category].ALLMEMBERS *
            [Group].[Group Hierarchy].ALLMEMBERS
            , [Group].[Group Hierarchy].Properties("Group Full Name") = strtomember(@GroupGroupFullName).MemberValue
        )
    } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
    SELECT ( STRTOSET(@SurveyTypeSurveyTypeHierarchy, CONSTRAINED) ) ON COLUMNS
    FROM (
        SELECT ( STRTOSET(@ResponseStatusResponseStatus, CONSTRAINED) ) ON COLUMNS
        FROM (
            SELECT ( STRTOSET(@QuestionAnswerAnsLabel, CONSTRAINED) ) ON COLUMNS
            FROM (
                SELECT ( STRTOSET(@SurveySurveysByYear, CONSTRAINED) ) ON COLUMNS
                    FROM [SysSurveyDW]))))

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

When I attempt to run ... I get the "query preparation in progress" dialog forever!  Once it finally gets to executing, it takes forever and a day.  If there is a better way ... I'm open to hearing about it.  I'm definitely NOT an  MDX pro :)

Thanks - Wayde 

Yeah, auto-generated queries are known to be problematic. My gut feeling is telling me that the filter statement is causing the issue. Did you produce this by hand? If you have used the MDX query designer in Design mode, shouldn't the filter clause be added as a SUBSELECT. Anyway, some basic troubleshooting steps:

1. Instead, of cross-joing all dimensions and ending up with a humongous set that you subsequently filter, apply the filter on the Group Foll Name hierarchy and join the resulting set to the other dimensions.

2. Start eliminating the dimensions in the filter clause to see if the performance improves.

2. If cross-joining dimensions in the filter clause is causing the performance degradation, try using Exists to reduce the size of the cross-joined sets, especially with large dimensions.

3. Look at the SQL Profiler to see what's going on. I assume by now your know the SSAS 2005 Performance Guide by heart. See how much time is spent in retrieving data vs. the calulation engine. Mosha has written a nice MDX Studio that can give you some insights about how queries execute.

Thanks!  Modifying the filter() expression as recommended did the trick.

Yes, the auto-generated queries suck!  I'm now doing everything by hand :)