AS2005? PeriodsToDate - How to release hand break?

AS2005? PeriodsToDate - How to release hand break?

• B Lambert

  >Hopefully it'll be fixed in SP2...

  In the preview of SP2 what I have it is not the case.

  
  

• gmcbay

  Hi Deepak,

  see my reply to Chris.

  
  

• Francisco Tavares

  Hi Vladimir,

  I did run your examples, but what I wanted to point out was that the computation of the PeriodsToDate() set alone doesn't take much time. Chris explained well what I was speculating - that somehow the calculated member and query can be optimized when the aggregated set is statically constructed (ie. a constant), versus when it is dynamically computed. Now, as to the nature of the optimization - maybe Mosha could shed some light on the inner workings ...

  
  
  

• Zingam

  Hi Deepak,

  Your query caclulates the PeriodsToDate on the axis and doesn't cover the problem what I have.

  Have you run my examples

  
  

• Igor Afanasev

  Well, it doesn't seem to be PeriodsToDate by itself - this query takes just 1 sec. So, when combined with Aggregate()

  >>
  

  select {[Measures].[Internet Sales Amount]} on 0,

  PeriodsToDate([Date].[Calendar].[Calendar Year],

  [Date].[Calendar].[Month].&[2004]&) on 1

  from [Adventure Works]

  -------------------------------------------------------------------------

  Internet Sales Amount
  January 2004 $1,340,244.95
  February 2004 $1,462,479.83
  March 2004 $1,480,905.18
  April 2004 $1,608,750.53
  May 2004 $1,878,317.51
  June 2004 $1,949,361.11
  July 2004 $50,840.63
  August 2004 (null)

  >>

  
  
  

• teplitsa

  By 'static' I meant explicitly stating the uniquenames of each member in the set, but that's just me being pedantic. I don't understand why the query optimiser doesn't recognise that the set returned by PeriodsToDate can be treated in the same way as a static set either; I guess it's just one of those things to watch out for. Hopefully it'll be fixed in SP2...
  
  

• Bigforky

  Vladimir,

  I can repro your results and have come across similar scenarios in the past: it seems like the query optimiser is able to put together a better query plan when it sees an explicitly stated set compared to when you use MDX functions to return a set. See also the section on 'Do Not Use Calculated Members that are Constants' here, which I think refers to the same issue:
  http://blogs.msdn.com/sqlcat/archive/2006/10/12/best-sql-server-2005-mdx-tips-and-tricks-part-1.aspx

  I suppose the real question to ask is whether AS2K was as bad in all scenarios as AS2005 is with PeriodsToDate, and whether by explicitly stating the set we're getting better performance in AS2005 than we ever could with AS2K....

  Chris

  
  
  

• shayc

  Hi Chris,

  If I rewrite the query in oder to use the "static" set as

  --Sample 2

  WITH

  SET [PeriodSet0] AS {PeriodsToDate([Date].[Calendar].[Calendar Year],
  [Date].[Calendar].[Month].&[2004]&)}
  
  member [Date].[Calendar].[Column 1] as
  AGGREGATE([PeriodSet0])

  SET [RowSet0] AS {[Customer].[Customer].[All Customers].children}
  SET [RowSet1] AS
  topcount([RowSet0],100,([Date].[Calendar].[Column 1],[Measures].[Internet Sales Amount]))

  SELECT
   {[Date].[Calendar].[Column 1]} ON COLUMNS,
   CROSSJOIN({[Customer].[Customer].[All Customers],[RowSet1]},{[Measures].[Internet Sales Amount]}) ON ROWS
  FROM [Adventure Works]

  --End Sample 2

  It takes 19 seconds. It is still very different from 2,5 second.

  You also mensioned that It is bad to use CurrentMenber in the Aggregate. But I can't. AS2K5 still have a bug in Aggreagte without Measures.CurrentMember. (It is another topic). 

  >suppose the real question to ask is whether AS2K was as bad in all scenarios as AS2005 is with >PeriodsToDate, and whether by explicitly stating the set we're getting better performance in AS2005 than >we ever could with AS2K....

  with

  --member [Time].[Colum 1] as 'aggregate(PeriodsToDate([Time].[Year], [Time].[1997].[Q3].[9]), [Measures].currentMember)'

  member [Time].[Colum 1] as 'aggregate({
  [Time].[1997].[Q1].[1],
  [Time].[1997].[Q1].[2],
  [Time].[1997].[Q1].[3],
  [Time].[1997].[Q2].[4],
  [Time].[1997].[Q2].[5],
  [Time].[1997].[Q2].,
  [Time].[1997].[Q3].[7],
  [Time].[1997].[Q3].,
  [Time].[1997].[Q3].[9]
  },  [Measures].currentMember)'

  set [Set0] as '[Customers].[Name].Members'
  set [Set1] as 'TopCount([Set0], 100, ([Time].[Colum 1], [Measures].[Store Sales]))'

  select {[Time].[Colum 1]} on 0,
  crossjoin({[Customers].[All Customers], [Set1]}, {[Measures].[Store Sales]}) on 1
  from Sales

  This runs in AS2000 wonderfull, either with PeriodsToDate or not.
  The FoodMart ist a bad example for performance tests. It too small. But I have tested the similar query against real database with millions of rows in facts and more the 100K leaf elements in dimensions.

   

   

  
  
• AS2005? PeriodsToDate - How to release hand break?