Semi-additive measures in AS2005 Standard Edition

• PetroWEB

  Hi Vania,

  Unfortunately, I'm not aware of such a MDX function; but if you can describe why the calculation needs to include the hidden member, maybe something else can be done

  
  
  

• walking fish

  There was a great deal of internal work done deep inside the runtime engine to support semi-additive measures. If all it involved was writing some fancy mdx it would have been much easier to solve. There is nothing that you can do which will perform anywhere near as fast as the native core aggregation functions.

  This is one area where Enterprise Edition will be the only way to get good performance at any real scale. I've been using LastNonEmptyChild on a 255GB AS database (see http://www.microsoft.com/sql/bi/ProjectREAL) for all of our inventory data (e.g. quantities on-hand). It is performing virtually the same as sum and count.

  _-_-_ Dave

  
  
  

• NimoTh

  Hi Mahesh,

  Often in AS 2000, Average is computed as Sum/Count:

  >>

  Newsgroups: microsoft.public.sqlserver.olap
  From: "Sean Boon [MS]"
  Date: Mon, 14 Jul 2003 10:55:54 -0700
  Subject: Re: avg

  If you want to create an average, what you need to do is create one
  measure
  called SUM, and another measure based on COUNT and then create a
  calculated
  member called AVG which would just be SUM/COUNT. I believe there are
  examples of this in the Foodmart 2000 cubes.
  

  Sean
  

  --
  Sean Boon
  SQL Server BI Product Unit
  >>

  
  
  

• AZ-xyz

  Hi Vania,

  Since I can't quite conceptualize your cube model, could you create a similar scenario in the Adventure Works sample cube That'll make it easier to suggest a solution.

  
  
  

• kasakaimumbai

  Hi,

  Thank you very much for your clarifications and suggestions.

  I just realized that the behaviour of Min and Max is the same also in the Enterprise Edition. I mean that they, in fact, aggregate with Min and Max on all the hierarchies of all the dimensions and not only on the Time dimension. I'm quite surprised, even because can't figure a situation where a company needs to aggregate this way on all the dimensions.

  However, now my problem is strongly increased, in fact I need to have a semi-addittive behaviour also for Min and Max on Time Dimension and, what's worse, also for the Enterprise Edition.

  I carefully read the documents you sent me a link at. They seem to be very useful, but I don't manage to apply these calculated members with the nonemptycrossjoin if my dimension has more than one hierarchy. In fact our customers usually use at least the ragged "First11month" hierarchy (with HiddenMembers when name is the same as ParentName), beside the one with Semester and Quarter.

  Have you some more hints on this issues

  Looking forward to have some more news, I thank you very much again.

  
  
  

• causal

  Hi Deepak

  Min and Max don't give you an error if you apply them to a measure in Standard Edtion, but they are used across all the dimensions instead that only on the Time Dimension!. I mean that all the hierarchies of all the dimensions use the Min/Max function to aggregate on that measure!

  As concerns First/Last, I managed to write some MDX scripts, but could not have the right results since I was not able to include in the aggregation functions (OpeningPeriod) also the HiddenMembers, which have to be not visible, but aggregated (as in Enterprise Edition if using Native SemiAddittive measures)...

  Any hints on this issues would be strongly appreciated!

  
  
  

• Fantacity

  Hi,

  I have a similar problem, I have AS2005 Enterprise but I need to use the semi-additive measures (LastNotEmpty) and the write-back functionality witch is not possible.

  AS2005 didn't allow to enable write-back in a semi-additive measure.

  Wicth is the best way for me to have both working Implement the LastNotEmpty with MDX Script and enable the write-back functionality or use the native LastNotEmpty aggregation and update the data in the fact table directly

  Update the data directly in the fact table give me the problem of how can I refresh the OLAP data on-line The user change the data and the data must be ready for query in the OLAP.

  Regards,
  Handerson

  
  
  

• LucasC

  Hi Vania,

  Aren't Min and Max standard built-in aggregations, as in AS 2000

  I guess some MDX script would be needed in Standard Edition for First/Last..

  
  
  

• TOWFAS

  Hi Dave and Vania,

  I think that there are a couple of points of confusion to clarify:

  - Vania states that:

  "Min and Max don't give you an error if you apply them to a measure in Standard Edtion, but they are used across all the dimensions instead that only on the Time Dimension!. I mean that all the hierarchies of all the dimensions use the Min/Max function to aggregate on that measure!"

  However, I don't believe that there are any "special semi-additive" Min/Max aggregations:

  http://msdn2.microsoft.com/en-us/library/ms175356.aspx

  >>

  Turn off semiadditive behavior

  Removes semiadditive behavior from a cube in which semiadditive behavior was previously defined. This selection resets a measure to SUM if it is set to any of the following aggregation function types:

  • By Account
    
  • Average of Children
    
  • First Child
    
  • Last Child
    
  • Last Nonempty Child
    
  • First Nonempty Child
    
  • None
    

  This option does not change measures with a regular aggregation function: Sum, Min, Max, Count, or DistinctCount.

  >>

  As Dave observes, there are large performance benefits to using built-in semi-additive aggregations like LastNonEmptyChild, versus using MDX. But Vania states that: "As concerns First/Last, I managed to write some MDX scripts, but could not have the right results". There are various MDX approaches, eg: elaborated in the articles below. So maybe Vania's wrong results can be corrected in SE (albeit with poorer performance than with EE), given more info on the problem definition:

  http://www.databasejournal.com/features/mssql/article.php/3445761

  >>

  Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
  By William Pearson

  >>

   

  http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

  >>

  Inventory Management Calculations in SQL Server Analysis Services 2005

  ...

  There are two commonly used methods for getting the last non empty member at a level. The more common, and usually more performant, method is using NonEmptyCrossJoin function as described above. But there is another: recursion.

  >>

  
  
  

• Thore

  And what about First/Last and Min/Max

  Thank you,

  Vania

  
  
  

• Michael Friedman

  Dear Deepak,

  sorry for my late and thank you for your courtesy.

  The fact is that, if I have a ragged hierarchy implemented as a "attributes hierarchy", I'd like to hide the members which have the same name of their parent, of course.

  But if I try to apply to this hierarchy some MDX calculations, based on the descendants at the leaf level, in order to find First and Last, I need also the hidden members to be included, otherwise the "shorter" arms of the tree would never be included, since they have only hidden members.

  Any hints about it
  Thanks again.

  Vania

  
  
  

• Ricky Wen - Microsoft

  Dear Deepak,

  sorry for not sending the cube model, but finally, with some complex MDX query calculation script I managed to achieve the right results, that are the min/Max, First/Last, FirstNonEmpty/LastNonEmpty, Avg,AvgNull=0 to aggregate only on a dimension, while the other in Sum and even in not Time dimensions. In fact I could not have as native by AS (in the Standard Edition as concerns First and Last).

  My only problem now is that, if I put HideMemberif "ParentName", the member is not included in my calculations (I want it not visible, but included in the calculations). So I was forced to set it as not hidden, but it's not what I would like. I would like to know if there is a MDX function which means "IncludeHiddenMembers", I mean something like .AddCalculatedMembers or .Allmembers, referring not to the calculated members, but to the hidden members. Are you aware of something like this I could not find it in the BOL....

  Thank you very much in advance!

  Vania

  
  
  
• Semi-additive measures in AS2005 Standard Edition