Donnerstag, 19. November 2009

When the Scoping gets tough ...

... the tough gets scoped.

There is a hairy observation to make that I would usually classify under the catgory "bug".

In arrogant ignorance of Microsofts issue tracking systems and even more, in absence of enough terminological background to even state a reasonable issue search query, we currently file it under "phenomenon with a workaround".

Imagine that you have developed an elaborate date hierarchy with two "branching" hierarchies, one for calendar weeks and one for months, quarters, years andsoforth. Branching means that from the point of view of attribute relationships, there are two outgoing relation branches, one in the attributes of the week hierarchy and one into the attributes of the month hierarchy.

From our experiments, calculations specified "purely" for the month scope will nevertheless also hit the week level of the parallel hierarchy just like if SSAS would rather parse the statement as "operate on (all) successor members of date".

SCOPE([DIM_DATE].[HIER_YQM].[LVL_MONTH].Members);
 // Do the usual nasty overrides and calculations
END SCOPE;

To get the behaviour right, we are now used to rather write such temporal scopes as

SCOPE([DIM_DATE].[HIER_YQM].[LVL_MONTH].Members,
      [DIM_DATE].[HIER_WEEK].[LVL_WEEK].[All],
      [DIM_DATE].[HIER_WEEK].[LVL_WEEK_YEAR].[All]);
 // Do the usual nasty overrides and calculations
END SCOPE;

where it is interesting that leaving the second all restriction away would reinforce the overrides at the calender year level nevertheless.

Still the behaviour (under the many strange behaviours of SSAS) leaves us puzzled. But I´m sure that there is a tough explanation for this in some other universe.

Keine Kommentare:

Kommentar veröffentlichen