locked
How to optimize this query RRS feed

  • Question

  • SELECT 
      NON EMPTY 
        [Measures].[Money] ON COLUMNS
     ,NON EMPTY 
        [Branch].[Branch Code].MEMBERS*[Client].[Client Tit].MEMBERS  ON ROWS
    FROM 
    (
      SELECT 
        [Company].[Company Tit].[Company Tit].&[RFD] ON 0
      FROM 
      (
        SELECT 
          [Branch].[Branch Tit].[Branch Tit].&[VST] ON 0
        FROM [Cube2]
      )
    );

    This mdx works fine but looks very huge 
    I bileve that it must be optimized
    The logic is - get Money for all clients from company RFD and branch VST
    Any suggestion how to optimize this query
    Monday, May 11, 2009 6:42 AM

All replies

  • looks fine to me
    how long does the query take?
    how long would you expect it to take?

    greets,
    gerhard
    - www.pmOne.com -
    Monday, May 11, 2009 6:47 AM
    Answerer
  • One possible suggestion is that sub-selects do not change the context in which the CrossJoin expression on the Rows axis is evalutated. Additionally, the CrossJoin expressions as they are full cartesian products of all members of all involved attribute hierarchies are extremely expensive operations. The above query can be optimized by using the NonEmpty function and changing the context that the NonEmpty expression is evaluated along the other three involved attribute hierarchies. The query below should be much better performing especially if the [Branch].[Branch Code] and the [Client].[Client Tit] attribute hierarchies are very large.

     

    SELECT	{ 
    		[Measures].[Money] 
    	}ON COLUMNS,
    	NonEmpty(
    		[Branch].[Branch Code].MEMBERS
    		* [Client].[Client Tit].MEMBERS,
    		{ [Measures].[Money] }
    	) ON ROWS
    FROM 	[Cube2]
    WHERE 	(
    		[Company].[Comany Tit].[Company Tit].&[RFD],
    		[Branch].[Branch Tit].[Branch Tit].&[VST]
    	)

     

     

     

    Monday, May 11, 2009 10:05 AM