CODEWITHSUNDEEP
ssasmdx
user3198708
user3198708

Reputation: 47

Last Available value MDX

I have a requirement where in i am to extract data from a cube, within the SSRS dataset using the query builder ,with the time dimension in the result set, across a range of dates. The conditions are

  1. The measures are to be displayed for each day of the date range.
  2. The sub total row should have the last available measures value for that time range.
  3. There is a time filter (currently a single date filter with a multi select option).

my MDX is as below.

  1. The measure has a 'Sum' as the aggregation type.

  2. I have a calculated measure with the scope defined as below.

     SCOPE([MEASURES].[Measure1]);   
    SCOPE([Date].[Date].MEMBERS);            
       THIS = TAIL(EXISTING ([Date].[Date].MEMBERS),1).ITEM(0)  ;            
    END SCOPE;  
 END SCOPE;

This above scope statement works perfectly. however, when i select in more that one date member this query slows WAYYYYYYY down. Performance numbers are

  1. Across 1 date - 4 seconds
  2. Across 2 dates - 22 minutes
  3. Across 3 dates - unknown (in Hours)

This drastic degradation in performance goes away if i remove the scope statement, which makes me thing that there should be a better way to do the same. the final report query is as below.

SELECT
  NON EMPTY
    {[Measures].[Measure1]} ON COLUMNS  
  ,NON EMPTY
    { [Dimension1].[Dimension1].[Dimension1].ALLMEMBERS*    
      [Dimension2].[Dimension2].[Dimension2].ALLMEMBERS*   
      [Dimension3].[Dimension3].[Dimension3].ALLMEMBERS*   
      [Date].[Date].[Date].ALLMEMBERS  
    } ON ROWS
FROM (
      SELECT {[Date].[Date].&[2014-06-13T00:00:00]
              ,[Date].[Date].&[2014-06-16T00:00:00] } ON COLUMNS
      FROM [Cube] 
     )

So the question again is, Is there a way to do the last available value part of the scope statement so as to have a better performance? Also, if there is another way to write the final mdx that would help the performance?.

Please let me know if there are anythings unclear regarding the question.

Thanks Srikanth

Upvotes: 0

Views: 2216

Answers (1)

FrankPl
FrankPl

Reputation: 13315

The first optimization step would be to change your query to

SELECT
  NON EMPTY
    {[Measures].[Measure1]} ON COLUMNS  
  ,NON EMPTY
    { [Dimension1].[Dimension1].[Dimension1].ALLMEMBERS*    
      [Dimension2].[Dimension2].[Dimension2].ALLMEMBERS*   
      [Dimension3].[Dimension3].[Dimension3].ALLMEMBERS*   
      {[Date].[Date].&[2014-06-13T00:00:00], [Date].[Date].&[2014-06-16T00:00:00] }
    } ON ROWS
FROM [Cube]

Furthermore, I am not sure why you added the SCOPE([Date].[Date].MEMEBER); (probably Date].[Date].MEMBERS, actually). Maybe it helps to omit it and the corresponding END SCOPE.

Upvotes: 1

Related Questions