YTD calculations performance

YTD calculations performance

• Robin Speed

  The techniques outlined here:
  http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry
  and
  http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry
  
  ...should be useful in solving this problem too.

  
  
  

• fleshi

  Hi Steve,
  Appreciate your help!

  The NonEmpty function seems to evaluate the empty-state of the base measure ([Sales]) rather than the aggregated YTD calculated measure, which gives the same result as when the Non-Empty Behavior property is set.

  I guess what I'm after is having the empty-state evaluated on the aggregated result rather than on the measure state in the current time scope. sorry for the clumpsy wording and lack of terms, but do you understand why I'm trying to accomplish

  Thanks,

  -Christian

  
  

• Edmund Leung

  Since you are using AS 2005 there are a couple of new functions you can use to eliminate empties.  Here are the descriptions from Mosha's blog site:

  http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

  NonEmpty(<set> [,<filterset>])

  NonEmpty function gives you the power of NON EMPTY clause, but inside MDX expressions,
  not just at the top level of SELECT statement. It completely supercedes deprecated function
  NonEmptyCrossJoin, and fixes all of its shortcomings - i.e. takes into account calculations,
  preserve duplicates etc. The syntax is also more streamlined and similar to Exists function,
  i.e. NonEmpty(set, filterset).

  Exists(<set> [, <filterset>] [, <msrgroupname>])

  This function perform manually what autoexist performs automatically - i.e. it does Exists against dimension table(s). The obvious difference from the CrossJoin function (which like the rest of MDX employs autoexist) is the ability to specify filter set for Exists. There is one more variant for this function, which accepts 3 parameters instead of 2. The third parameter is the name of the measure group. In this case, Exists will be executed against fact table of that measure group instead of against dimension table(s). This variant of the function most closely resembles deprecated NonEmptyCrossJoin function.

  You could try and re-write the query to use NonEmpty:

  SELECT NONEMPTY ({
   [Measures].[Sales],
   [Measures].[Sales YTD]
  }) ON COLUMNS,
  NONEMPTY ({
   [Customers].[Enterprise Description].[Enterprise Description].ALLMEMBERS *
   [Customers].[Customer Description].&[21168 SONNICO INSTALLASJON AS] *
   [Suppliers].[Supplier Name].[Supplier Name].ALLMEMBERS *
   [Product].[Product Group Description].[Product Group Description].ALLMEMBERS *
   [Product].[Product Description].[Product Description].ALLMEMBERS
  }) ON ROWS
  FROM [Elektroskandia DW]
  WHERE (
    [Time].[Year - Month].[Month].&[2005]&[10]
  )

  HTH,

  - Steve

  
  
  

• kojot

  Thanks for the enlightening help from both of you, Steve and Chris!

  The problem seems to mainly have been a major BF on my part, because when explicitly utilizing the hierarchy in the Product dimension rather than cross joining ProductGroup and Product the query returned with the correct result set, and within what I guess will be an acceptable timeframe (about 17 sec on a cold cache). The trick was to use DrillDownMember on the ProductGroup level.

  When it comes to Chris's trick with the time dimension, it seems to quicken the query about 20% on a cold cache, but isn't noticeably faster on subsequent runs.

  For the record, here's the query I ended up with:

  WITH
   SET _dateRange AS 'YTD([Time].[Year - Quarter - Month].[Month].&[2005]&[10])'
   MEMBER [Measures].[InDateRange] AS 'Except(Descendants([Time].[Year - Quarter - Month].CurrentMember,, LEAVES), _dateRange).Count = 0'
   SET _newDateRange AS 'Filter([Time].[Year - Quarter - Month].Members, [Measures].[InDateRange] AND Not( [Measures].[InDateRange], [Time].[Year - Quarter - Month].Parent))'
   MEMBER [Measures].[Sales YTD] AS 'Aggregate(_newDateRange, [Measures].[Sales])'
   
  SELECT {
   [Measures].[Sales],
   [Measures].[Sales YTD]
  } ON COLUMNS,
  NON EMPTY {
   [Customers].[Enterprise Description].[Enterprise Description].Members *
   [Customers].[Customer Description].&[21168] *
   [Suppliers].[Supplier Name].[Supplier Name] * {
   DrillDownMember([Product].[Product Group - Product].[Product Group].Members,
    [Product].[Product Group - Product].CurrentMember) }
  }  ON ROWS
  FROM [Elektroskandia DW]
  WHERE (
   [Time].[Year - Quarter - Month].[Month].&[2005]&[10]
  )

  Thanks again for your help!

  -Christian

  
  
• YTD calculations performance