MDX: This query optimization

0
votes

for displaying 278 records,taking 2 mins 47 seconds is there any way to optimization the query.

SELECT {[Measures].[Mins0] ,
        [Measures].[Mins10] ,
        [Measures].[Mins20] ,
        [Measures].[MinsAbove20] ,
        [Measures].[CCMPatientCnt] } ON COLUMNS ,

NONEMPTY(([DimManagedPopulation].[ManagedPopulationKey].[ManagedPopulationKey], 
          [DimManagedPopulation].[ManagedPopulationName].[ManagedPopulationName]), 
         [Measures].[CCMPatientCnt]) 
ON rows FROM [NavigateCube] 
WHERE ([DimAnchorDate].[Date Key].&[20160331]);
2
ssasmdx
Are any of those measures calculated? i would remove from the NONEMPTY either the name and add a calculated member with CURRENTMEMBER Properties('ManagedPopulationName') – mxix
@mxix:) thanks for the suggestion, i try CURRENT MEMBER , it gives better performance. thanks – Deepthi
I'm guessing this is a count? [Measures].[CCMPatientCnt] ...counts and unique counts are never so quick in olap. – whytheq

2 Answers

0
votes

It is difficult to suggest something but can you please try following code and report the outcome ?

SELECT 
{
    [Measures].[Mins0] ,
    [Measures].[Mins10] ,
    [Measures].[Mins20] ,
    [Measures].[MinsAbove20] ,
    [Measures].[CCMPatientCnt]
} 
ON COLUMNS ,
NONEMPTY(
    (
        [DimManagedPopulation].[ManagedPopulationKey].[ManagedPopulationKey], 
        [DimManagedPopulation].[ManagedPopulationName].[ManagedPopulationName]
    ), 
    ([Measures].[CCMPatientCnt], [DimAnchorDate].[Date Key].&[20160331])
) 
ON rows 
FROM [NavigateCube] 
WHERE ([DimAnchorDate].[Date Key].&[20160331]);
0
votes

Also it might help to move the set on rows into a WITH clause:

WITH 
  SET [PopSet] AS 
    NonEmpty
    (
      (
        [DimManagedPopulation].[ManagedPopulationKey].[ManagedPopulationKey]
       ,[DimManagedPopulation].[ManagedPopulationName].[ManagedPopulationName]
      )
     ,(
        [Measures].[CCMPatientCnt]
       ,[DimAnchorDate].[Date Key].&[20160331]
      )
    ) 
SELECT 
  {
    [Measures].[Mins0]
   ,[Measures].[Mins10]
   ,[Measures].[Mins20]
   ,[Measures].[MinsAbove20]
   ,[Measures].[CCMPatientCnt]
  } ON COLUMNS
 ,[PopSet] ON ROWS
FROM [NavigateCube]
WHERE 
  [DimAnchorDate].[Date Key].&[20160331];