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

However, if we rewrite the query using a calculated member, as in:

WITH
MEMBER Measures.SalesPreviousPeriod AS
([Measures].[Sales Amount],
[Date Order].[Calendar Date Order Calculations].[Previous
Period])
SELECT
{
Measures.[Sales Amount],
SalesPreviousPeriod
} ON 0,
NON EMPTY
[Date Order].[Calendar].[Date].&[20010712]
ON 1
FROM
Sales

The result seems to be incorrect:

Current Date Order SalesPreviousPeriod

July 12, 2001 14,134.80 NULL

As we can see, the value of SalesPreviousPeriod is Null . This isn't a bug,
and although it's hard to understand, there is a logical explanation for why
this happens; it's certainly not what you or your users would expect to happen,
though, and is likely to cause some complaints. This behavior is a result of the
way attribute relationships within a dimension interact with each other; this is
referred to as 'attribute overwrite', and you can read a full discussion of it here:
http://tinyurl.com/AttributeOverwrite . There's also a discussion of how
attribute overwrite causes this particular problem in the following blog entry:
http://tinyurl.com/chrisattoverwrite .

The easiest way to avoid this problem is to never implement a
calculation dimension as a new hierarchy on an existing dimension.
Calculation dimensions should be implemented as new, standalone
dimensions, and should not have any relationship with any other
dimension.