locked
Performance issue for the below Query MDX RRS feed

  • Question

  • Hi Friends,

    I have performance issue  for my Query as it was running about 15 seconds.

    is there anyway to increase the performance of this Query ?

    Please let me know if you have idea. Thanks

    SELECT
    NON EMPTY
    {
      [Measures].[TaskStatusSecondsDuration],
      [Measures].[MaximumSKTaskStatustime]
    } 
    ON 0, 
    NON EMPTY 
    {
    GENERATE(
    
             [Dim_Task].[TaskNumber].[TaskNumber].MEMBERS,
            
           TOPCOUNT(
                Nonempty(
                   CROSSJOIN (
    	
    		                    [Dim_Task].[TaskNumber].CURRENTMEMBER
    			                ,[Dim_TaskStatus].[StatusReasonReportingLabel].[StatusReasonReportingLabel].MEMBERS
    			                ),[Measures].[MaximumSKTaskStatustime]
    			                )			
    				,1
    				,[Measures].[MaximumSKTaskStatustime]
    	          )
    	          )
    
    	 }  
    	
    	DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       
       ON 1
       
    FROM  [Cube]

    Friday, November 15, 2013 1:05 PM

Answers

  • Hi

    1. You can create Nonempty at Generate function to limit the for each members

    It is similar to your but include the measure at comments section

    refer to this Richard post : http://richardlees.blogspot.com.au/2011/08/improving-mdx-join-performance.html

    SELECT
    NON EMPTY
    {
      [Measures].[TaskStatusSecondsDuration],
      [Measures].[MaximumSKTaskStatustime]
    } 
    ON 0, 
    NON EMPTY 
    {
    GENERATE(
             // Can also use NonEmpty with [Measures].[MaximumSKTaskStatustime] for further performance improvement
             [Dim_Task].[TaskNumber].[TaskNumber].MEMBERS,
            
           TOPCOUNT(
                Nonempty(              
    		                [Dim_Task].[TaskNumber].CURRENTMEMBER
    			          * [Dim_TaskStatus].[StatusReasonReportingLabel].[StatusReasonReportingLabel].MEMBERS
    			           ,[Measures].[MaximumSKTaskStatustime]
    			         )			
    				,1
    				,[Measures].[MaximumSKTaskStatustime]
    	          )
    		)
    	          
    
    	 }  
    	
    	DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       
       ON 1
       
    FROM  [Cube]


    Prav

    • Marked as answer by BandSr Monday, November 18, 2013 10:51 AM
    Friday, November 15, 2013 11:27 PM