Analysis Services : Beware of unused dimensions “out of sight, out of mind”

OLAP Cubes in general and Analysis Services in particular are very handy, easy to learn and powerful tools to find quick answers to huge and unexpected questions.

Once that’s said, one of the first thing I teach the end users I have a chance to meet during workshops is that they must never trust what they see without questioning the exposed data in the context of their functional specifications. Easy to learn, but yet hard to master …

Along the years I have gathered a list of common misconceptions about cubes and OLAP databases that I usually put in a good place in my workshops’ agenda. Just to be sure they won’t get disappointed several weeks/months later when they will get their hands on the solution we have built for them…

Here I want to discuss the mechanism by which dimensions that are not used in a query still have an impact on the result. In my list of misconceptions this one is called “Beware of unused dimensions : out of sight, out of mind”.

Indeed, that’s not because a dimension is not used in a query that it does not have an impact on the result and it is an extensive source of questioning/errors when you’re not accustomed to your cubes business rules.

In every cases the usage of those features must be thoroughly documented in the user documentation.

1) Left aside dimensions : impact of overridden default members

The most common source of problem  are the dimensions’ default members. By default, it’s the ‘All’ member, but it can as well be something else if you decide it so.

For example, when querying a stock indicator the default member of the time dimension will most probably be something like the last date for which we know the stock level as aggregating stocks over time at the ‘all’ level of the dimension has no meaning (There is other ways to achieve this including using SCOPE statements or LAST NON EMPTY aggregations, but that’s just for the purpose of my demonstration).

In case the dimensions left aside your query or pivot table do have their default members defined as their respective ‘All’ member, then no problem, everything goes as if they weren’t there at all. And as it is the most common scenario you may be mislead to think that this is always the case, but it’s not.

If I take back my stock example, you realize that if you leave your “snapshot time” dimension aside, your query gives you only the last known figures and not all known stocks from the beginning of times. That’s exactly because the left aside dimension has one of his member defined as default member. You can achieve the same result by explicitly filtering the query by the dimension custom default member meaning that it has indeed a filter action on the query context.

As a corollary to this, every dimension, used or not in a query, has a current member in the context of any query.

2) Left aside dimension’s hierarchies : how do behave unused hierarchies ?

A more subtile behavior occur when you are using hierarchies in your queries.

Take for example a standard “Time” dimensions with two hierarchies defined as H1 : “Year -> Week -> Day” and H2 : “Year -> Quarter -> Month -> Day”. The default member of the dimension is the ‘All’ member of the ‘All’ level.

When left aside, the current member of the dimension is ‘All’ and by extension, every hierarchy in the dimension inherit the same behavior.

Now here’s the questions :

Q1 : If I filter my query by using a member of the quarter attribute of the dimension, what is the current member of the left aside members ?

A1 : You’re right, it’s the ‘All’ member.

Q2 : And what is the current member of the left aside H1 hierarchy ?

A2 : Yep again, the ‘All’ member.

Q3 : And now, what is the current member of the left aside H2 hierarchy ?

A3 : If you answered the same member of the quarter level at the respective quarter level, then you guessed it right. That’s because hierarchies are really some kind of views over dimension attributes, so filtering attributes do have an impact on the hierarchies and vice-versa.

Sometime, when coding measures in MDX, you end up testing toward which hierarchy the formula should be evaluated as you have to explicitly quote the hierarchy in the formula when there are two of them or more. In this case testing if you are not on the default member doesn’t guaranty that the hierarchy is actually used as it can be left aside but filtered by the usage of another hierarchy or attribute as exposed earlier.

All in all, though it may seem straightforward to query cubes, particular attention should be accorded to the behavior of cubes when they grow in complexity.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: