Debugging Attribute Relationships in the ROLAP Basis

I guess that this is quite obvious and could as well be published in our ROLAP blog, but just for the sake of completeness:

The following SQL-Statement lists all values of a source attribute

src

in your dimension source table/view

dim

which violate an attribute relationship (path) to a destination attribute

dest

.

select src from dim group by src having count(distinct dest)>1

Extending this a bit delivers all the bad rows:

select * from dim where src in (select src from dim group by src having count(distinct dest)>1)

Do not call a dimension attribute "DESCRIPTION"!

At least the Office Web Components (OWC) browser integrated into BI Development Studio and Management Studio will not show the values as a member property when the attribute (Name, not key nor translation) is called "DESCRIPTION".

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.

Nested Scopes and Attribute Relationships

Nested Scopes are a convenient syntax to structure subcube/crossjoin calculations in your MDX scripts. Most often, you would want to use more than one attribute from the same dimension in such a context and, in addition, navigate the associated hierarchies in the scoped calculations:

SCOPE({[DIMENSION].[HIERARCHY_1].[LEVEL_OR_ATTRIBUTE_1].&[KEY_1]},...);
   SCOPE({[DIMENSION].[HIERARCHY_2].[LEVEL_OR_ATTRIBUTE_2].&[KEY_2]},...);
        // Your calculation involving [DIMENSION].[HIERARCHY_1].CurrentMember
        // will result in navigating [DIMENSION].[ATTRIBUTE_1].[All]!
   END SCOPE;
END SCOPE;

However, with nested scopes it seems that similar overriding rules than with concatenating ordinary tuples apply: Suppose the attribute ATTRIBUTE_2 associated with the LEVEL_OR_ATTRIBUTE_2 level is related to the attribute ATTRIBUTE_1 (i.e., KEY_2 is unique for KEY_1, in contrast to the related from relationship where KEY_1 is unique for KEY_2), the example calculation context above would result to the All member of ATTRIBUTE_1. Duh.

Writing the nesting the other way round (which would seem semantically equivalent from only superficially reading the specs, as we usually do) the delivers the expected result

SCOPE({[DIMENSION].[HIERARCHY_2].[LEVEL_OR_ATTRIBUTE_2].&[KEY_2]},...);
    SCOPE({[DIMENSION].[HIERARCHY_1].[LEVEL_OR_ATTRIBUTE_1].&[KEY_1]},...);
        // Your calculation involving [DIMENSION].[HIERARCHY_1].CurrentMember
        // will finally navigate [DIMENSION].[ATTRIBUTE_1].&[KEY_1]!
    END SCOPE;
END SCOPE;