Beware Hidden Calculated Members

Here is another one of those “by design” issues (see the Not All Calculated Members Are Born Equal blog for another recent gotcha) that are not documented but can sure waste you precious time figuring out. The issue was first submitted on connect.microsoft.com by Greg Galloway almost a year ago where it was left in an investigation phase (what can we do to get the SSAS team actually review the connect cases and post findings?). Then, I submitted a similar issue and official support case since I was 100% sure it was a bug and I needed a closure. Alas, Microsoft came back to declare it “by design”:

“…per feedback from several AS 2005 Beta customers, for compatibility with pre-existing AS 2000 behavior, AS 2005 RTM (and later releases) was changed to not include hidden calculated members in cell calculations and scopes (except when a hidden calculated member is explicitly referenced in the calculation scope).”

The issue affects hidden calculated members and scope assignments where a scope assignment simply ignores hidden calculated members if they are not explicitly referenced by the script. You can easily repro the issue by making the following changes to the Adventure Works script:

/*– Allocate equally to quarters in H2 FY 2005 ————————*/

CREATE
MEMBER
CURRENTCUBE.[Measures].[Visible Quota] AS
null,

FORMAT_STRING = “Currency”,

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Sales Targets’ ;

CREATE
MEMBER
CURRENTCUBE.[Measures].[Invisible Quota] AS
null,

FORMAT_STRING = “Currency”,

VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = ‘Sales Targets’ ;

Scope

(

[Date].[Fiscal Year].&[2005],

[Date].[Fiscal].[Fiscal Quarter].Members

);

this = (ParallelPeriod

(

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember

), [Measures].[Sales Amount Quota]) * 1.35;

Here, I created two identical calculated members with the only difference that the first one is visible and second one hidden. Then, execute the following query:

SELECT {[Measures].[Sales Amount Quota], [Measures].[Visible Quota], [Measures].[Invisible Quota]} on 0

FROM [Adventure Works]

WHERE [Date].[Fiscal].[Fiscal Quarter].&[2005]&[1]

And here are the results (surprise!):

The Visible Quota member is populated as it should be but Invisible Quota refuses to budge as the script simply ignores it. If the script scopes on the Invisible Quota member explicitly or you make it visible, then we get the expected results. Since the issue is by design and a fix won’t be available anytime soon, you should be very careful when you use hidden calculated members and expect them to be affected by scope assignments. I use often helper calculated members which I hide since they shouldn’t be visible to the end users. This issue hit me really hard after QA reported wrong results and your humble correspondent had to debug a cube with some 1000+ lines of code.