MDX: Query Optimization

Question

For displaying the result set, it takes 2 minutes. Is there any way to optimise the query?

WITH MEMBER [Measures].[TestMeasure] AS
  ( 
    [Measures].[EnrollPatientCnt] 
  + [Measures].[AssessmentPatientCnt] 
  + [Measures].[ProgramAssessmentPatientCnt] 
  )
MEMBER [Measures].[TotalCount] AS
  Count( 
    NonEmpty(
     ( 
       { [DimAssessment].[AssessmentText].[AssessmentText] },
       { [DimAssessment].[QuestionText].[QuestionText] },
       { [DimAssessment].[AnswerText].[AnswerText] } 
     )
    ,{ 
       [Measures].[AssessmentPatientCnt]
     , [Measures].[TestMeasure] 
     }
  ) )
SELECT 
  NON EMPTY [Measures].[TotalCount] ON COLUMNS
FROM [NavigateCube]
WHERE 
  (
    {
      ( 
       { 
         [DimManagedPopulation].[ManagedPopulationName].&[1034]&[TC Tammy Brown Care Team] 
       } 
      )
    } 
  );

Answer 1

Basically the same approach as Greg/Mosha:

WITH 
MEMBER [Measures].[TestMeasure] AS
    [Measures].[EnrollPatientCnt] 
  + [Measures].[AssessmentPatientCnt] 
  + [Measures].[ProgramAssessmentPatientCnt] 
MEMBER [Measures].[MeasureIsEmpty] AS
  IIF(
      NOT ISEMPTY([Measures].[TestMeasure])
   OR NOT ISEMPTY([Measures].[AssessmentPatientCnt])
   ,1
   ,NULL
  )
MEMBER [Measures].[TotalCount] AS
  SUM(
     [DimAssessment].[AssessmentText].[AssessmentText]
    *[DimAssessment].[QuestionText].[QuestionText]
    *[DimAssessment].[AnswerText].[AnswerText]
    ,[Measures].[MeasureIsEmpty]
  )
SELECT 
  NON EMPTY [Measures].[TotalCount] ON 0
FROM [NavigateCube]
WHERE [DimManagedPopulation].[ManagedPopulationName].&[1034]&[TC Tammy Brown Care Team];

Answer 2

How does this query perform?

WITH MEMBER [Measures].[TestMeasure] AS
  IIf(
   Not(IsEmpty([Measures].[EnrollPatientCnt]))
   or Not(IsEmpty([Measures].[AssessmentPatientCnt]))
   or Not(IsEmpty([Measures].[ProgramAssessmentPatientCnt]))
   ,1
   ,Null
  )
MEMBER [Measures].[TotalCount] AS
  Sum (
    ( { [DimAssessment].[AssessmentText].[AssessmentText] },
    { [DimAssessment].[QuestionText].[QuestionText] },
    { [DimAssessment].[AnswerText].[AnswerText] } ),
    [Measures].[TestMeasure]
  )
SELECT NON EMPTY [Measures].[TotalCount] ON COLUMNS
FROM [NavigateCube]
WHERE (
{
  ( { [DimManagedPopulation].[ManagedPopulationName].&[1034]&[TC Tammy Brown Care Team] } )
} )

It's not identical, but it's a similar concept to what Mosha blogged about here.