none
Performance issue on MAX function MDX

    Question

  • Hi I was writing one MDX code for my report.

    It was giving right result but it is running about 8 min which is not acceptable

    Can anyone have a look and give me some  tips to improve the performance

    Please correct my code if you have any idea 

    WITH
    	SET [SORTaskTrade] AS 
    	{
      	[Dim_TaskCatalogue].[TradeCode].[All].Children
    	}
    
    	SET [SORSubCategory] AS 
    	{
    	 [Dim_TaskCatalogue].[SubcategoryDescription].[All].Children
        }
    
    	SET [SORTaskCode] AS 
    	{
    	[Dim_TaskCatalogue].[SCJIDCode].[All].Children
    	}
    
    	SET [TaskNumber] AS 
    	{
    		FILTER([Dim_Task].[TaskNumber].[All].CHILDREN, [Measures].[TaskCompleted] > 0)
    	}
    
    
    	MEMBER [MEASURES].[NoTasks] AS 
    	(
    			[Measures].[TaskCompleted]
    	),SOLVE_ORDER = 1
    	
    	MEMBER [MEASURES].[SMVDuration] AS 
    	
    		Max(
    		     [TaskNumber]
    				,[Measures].[TaskExpectedOnsiteDurationSeconds]
    			)
    			
     	MEMBER [MEASURES].[QuickestOnSite] AS
    	
    
    		Min(
    		      [TaskNumber]
    				,[Measures].[TaskActualOnsiteDurationSeconds]
    			)
    
    SELECT
    NON EMPTY{
    	[MEASURES].[NoTasks] 
    	,
    	[MEASURES].[SMVDuration] 
    	,
    	[MEASURES].[QuickestOnSite]
    }
    
    ON 0,
    non empty
    {
    	[SORTaskTrade] 
    	*
    	[SORSubCategory] 
    	*
    	[SORTaskCode]
    	*
    	[Dim_TaskCatalogue].[TaskType].[All].CHILDREN 
    
    }
    
    DIMENSION PROPERTIES
    MEMBER_CAPTION
    
    ON 1
    
    FROM [Cube]
    
    WHERE(
    	{StrToMember(@StartDate,CONSTRAINED):StrToMember(@Enddate,CONSTRAINED)}
    
    )
    
    CELL PROPERTIES
     VALUE
    ,FORMATTED_VALUE
    ,CELL_ORDINAL;

    Thanks in advance


    • Edited by BandSr Thursday, June 27, 2013 10:18 AM
    Thursday, June 27, 2013 10:16 AM

Answers

  • I would suggest a similar approach.

    Create a new separate measure with a min aggregation and populate it with the value you want the min for, but in the case of a zero use a high number (max number).

    so the sql could look something like this.

    CASE when Yourmeasure = 0 THEN 9999999999 ELSE YourMeasure END as newMinAgg

    But then you must only use this measure for your min aggregation.

    It's not very pretty, but it works very well for performance.

    Cheers,

    Duane

    Thursday, June 27, 2013 2:03 PM

All replies

  • You can start by testing each measure one at a time in the same query and timing each one (comment out the others).

    Clear the cache before testing each measure by running this piece of code in an xmla session.

    <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Object>
        <DatabaseID> Adventure Works DW Multidimensional</DatabaseID>
      </Object>
    </ClearCache>

    Just change the database name.

    That way you can test which measure is the most expensive and concerntrate on fixing that one.

    TaskNumber set can be improved by creating a new measure on the measure group with a max aggregation type. Set the measure value to 1 where Task Completed > 0 in the sql table or view and then instead of filtering on this set multiply the value being filtered in these calculated measures by this new max measure - that will eliminate the filter which can be quite expensive.

    So on this approach your query would be something like:

    MAX(Dim_Task].[TaskNumber].[All].CHILDREN, [Measures].[Taskexpectedonsitedurationseconds] * [measures].[Your new max aggregated measure])



    • Edited by Duane Dicks Thursday, June 27, 2013 10:54 AM another addition
    • Proposed as answer by Duane Dicks Thursday, June 27, 2013 1:56 PM
    Thursday, June 27, 2013 10:46 AM
  • Hi,

    I have created measure using MAX in the cube, which is working perfect 

    and running in fraction of seconds

    I had another issue. I want to add another MIN measure in the cube but I want to exclude 0 from that measure group.

    for example I have a values like 35,98,21,2,8,0 so here I want to see 2 is Min value I don't wanna see 0.

    Can u guide me please

    Thursday, June 27, 2013 1:48 PM
  • I would suggest a similar approach.

    Create a new separate measure with a min aggregation and populate it with the value you want the min for, but in the case of a zero use a high number (max number).

    so the sql could look something like this.

    CASE when Yourmeasure = 0 THEN 9999999999 ELSE YourMeasure END as newMinAgg

    But then you must only use this measure for your min aggregation.

    It's not very pretty, but it works very well for performance.

    Cheers,

    Duane

    Thursday, June 27, 2013 2:03 PM