Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 157

The first part of the calculation makes use of two functions, NonEmpty and
LastPeriods , to check if the required six months are available to have a
meaningful average.

•

NonEmpty takes two sets and removes the tuples from the first set that are
empty when evaluated across the tuples in the second set. Because our
second parameter is a measure, we get only the tuples in the first set where
the measure evaluates to a non NULL value.

• LastPeriods returns, as we've seen, the previous n members from its

second parameter.

So, the calculation takes the set of the last six months then removes the ones where
there are no sales and checks if the number of items left in the set is still six. If not,
the set contains months without sales and the average will be meaningless, so it
returns a NULL value. The last part of the formula computes the average over the
same set computed before using the AVG function for Sales Amount.

This version of the calculation works fine for months, but at other levels of the
Calendar hierarchy it doesn't - it's a six time period moving average, not a six month
moving average. We might decide we want to use completely different logic at the
Date or Quarter level; we might even decide that we don't want to display a value at
anything other than the Month level. How can we achieve this?

We have two options: use scoped assignments, or add some more conditional logic
in the calculation itself. In Analysis Services 2005, scoped assignments were the best
choice for performance reasons, but in later releases of Analysis Services, the two
approaches are more or less equivalent in this respect; and since adding conditional
logic in the calculation itself is much easier to implement, we'll take that approach
instead. What we need is another test that will tell us if the CurrentMember on
Calendar is at the Month level or not. We can use the Level function to return the
level of the CurrentMember; we then need to use the IS operator to compare what
the Level function returns with the Month level as follows:

CREATE
MEMBER CURRENTCUBE.Measures.AvgSixMonths AS
IIF (
NOT
[Date Order].[Calendar].CurrentMember.Level
IS
[Date Order].[Calendar].[Month]
OR
Count (
NonEmpty (
LastPeriods (
6,