NEB Revenge in Katmai

An advanced SSAS performance-related feature is Non Empty Behavior (NEB) which has been discussed in many places including this Mosha’s blog. The issue with NEB in SSSAS 2005 is that you can very easily get it wrong. The server will trust you (this could be an optimistic assumption) and accept a NEB even if it’s not correct. Example:

CREATE MEMBER CURRENTCUBE.[Measures].[Profit Gap To Prior Period]

AS [Measures].[Profit]-([Date].[Calendar].PrevMember, [Measures].[Profit]),

FORMAT_STRING = “#,#;(#,#)”,

NON_EMPTY_BEHAVIOR = { [Measures].[Profit]},

VISIBLE = 1;

Here, the intention is to default the expression for the Profit Gap To PriorPeriod calculated member to NULL if Profit is NULL. But the expression may be not be NULL even if Profit is NULL. This can yield incorrect results in 2005. In 2008, optimizations were made that don’t use the NEB expression. The net effect is that some calculated members that use wrong NEBs can produce different, albeit correct, results in 2008. If I am not wrong, in this webcast, Donald Farmer mentioned in this webcast that NEBs will be de-emphasized in SQL Server 2008. Meanwhile, if you are to use NEBs be sure that they are right to save you many hours of testing and head-scratching.