Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 87

Semi-additive aggregation is extremely useful when you have a fact table that
contains snapshot data. For example, if you had a fact table containing information
on the number of items in stock in a warehouse, then it would never make sense
to aggregate these measures over time: if you had ten widgets in stock on January
1, eleven in stock on January 2, eight on January 3, and so on, the value you would
want to display for the whole of January would never be the sum of the number of
items in stock on each day in January. The value you do display depends on your
organization's business rules.

Let's take a look at what each of the semi-additive measure values actually do:

• AverageOfChildren : This displays the average of all the values at the
lowest level of granularity on the Time dimension. So, for example, if date
was the lowest level of granularity, when looking at a year value, then
Analysis Services would display the average value for all days in the year.
• FirstChild : This displays the value of the first time period at the lowest
level of granularity, for example, the first day of the year.
• LastChild : This displays the value of the last time period at the lowest level
of granularity, for example, the last day of the year.

•

FirstNonEmpty : This displays the value of the first time period at the lowest
level of granularity that is not empty, for example, the first day of the year
that has a value.

•

LastNonEmpty : This displays the value of the last time period at the lowest
level of granularity that is not empty, for example, the last day of the year
that has a value. This is the most commonly used semi-additive type; a
good example of its use would be where the measure group contains data
about stock levels in a warehouse, so when you aggregated along the Time
dimension what you'd want to see is the amount of stock you had at the end
of the current time period.