I'm a little confused about the documentation (BOL) of semi-additive
measures. From what I see from tests and information on the internet I
come to the following definitions, a additive measure is a measure where
the cell values of a parent can be calculated from the childs and the
aggregation is done in the same way for all dimensions, a semi-additive
measure is a measure that does not aggregate in the same way over the
different dimensions, but the cell value of a parent can be calculated
from the cell values from the childs and a non-additive measure that the
cell values could not be calculated from the childs.
More specifically for SQL2005 I found that the behaviour of the
following aggregation functions is the following:

Sum,Count: Additive over all dimensions (the value of a parent is the
sum of its childs) and aggregates in the same ways over all dimensions.
Min,Max: Additive over all dimensions Min, Max (the Min value of a
parent is the Minimum of the Min values of its childs). Aggregates in
the same way over all the dimensions.
DistinctCount: Is not additive.
AverageOfChildren,FirstChild,LastChild,FirstNonEmp ty and LastNonEmpty:
Aggregates as a Sum over all the Dimensions with exception of a
Dimension of type Time where it aggregates (and rollups) as the
Average,First,Last,First Non Empty and Last Non Empty.
By Account the Aggregate function depends on the AccountType of a
member of the (only) Dimension of type Account.
None: Which does no aggregation at all (I expect that you should use
this with certain AccountTypes where you don't want to aggregate).

Based on the above I would say that Sum,Count,Min and Max are additive,
and that DistinctCount and Non are non-additive and that
AverageOfChildren,FirstChild,LastChild,FirstNonEmp ty and LastNonEmpty
are semi-additive and treats the Time dimension different from the other
dimensions.
However when I look in the BOL it says that Sum is additive and that
Count,Min,Max,ByAccount,AverageOfChildren,FirstChi ld,LastChild,FirstNonEmpty,LastNonEmpty
are additive and that None and DistinctCount are non-additive.

Who is correct the BOL or is there something wrong with my definitions,
also as the BOL does not really specifies for which dimensions the
aggregation is different, am I correct in thinking that the only special
dimension is the Time dimension.
IS there also some other resource that describes semi-additivity in
SQL2005 more correctly.


Thanks for all the help in advance

Marc Mertens

Hi Marc,

When you say: "Who is correct the BOL or..", BOL itself is apparently
inconsistent on semi-additive aggregations. This BOL section confirms
your interpretation:

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

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.

The only issue I have with your aggregation assignments is that I would
regard Min/Max as non-additive.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak Puri wrote:
Thanks Deepak,

I agree with you that Min and Max is not technically a sum and can
not be called full additive, although it behaves as additive measure.
But Sum,Min, Max and Count are certainly not semi-additive. I think
there indeed some errors in the BOL

Marc Mertens

My understanding of additive is that a parent value can be calculated by
adding together the values of any children. Only Sum and Count meet this
criteria. By this definition Min and Max are not additive.

I actually sent MS feedback on the aggregate functions page in BOL on
the issue that Count should not be marked as semi-additive and received
a notification that the corrections will be incorporated in the next
update of BOL.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#f9gyChLGHA.1424 (AT) TK2MSFTNGP12 (DOT) phx.gbl>,
mertens.techdata (AT) gmail (DOT) com says...