Archive for December, 2011

Analysis Services : How to add subfolders to your Measures Groups (Display folders tips and tricks)?

I’ve already blogged three times on measure groups matters and never thought I would have enough material for a fourth blog post. Fact is I stumble across an old post from Mosha Pasumansky on his sqlblog blog on the topic and rediscovered something I almost forgot with the years.

First you can find the three first posts here, although you’re not required to read them first to get the point of this post :

Analysis Services: What are the best practices for grouping measures ?

Analysis Services Feature Suggestion : Managing Measure Group visibility

Analysis Services : How to break distinct count measures into separate measure groups ?

To sum up Mosha’s blog post, you can add some extra flavour to your measures organization into folders with the following techniques :

– You already know that you can organize your measures into folders. But it is less known that you can have also sub folders. For doing this, as the UI in BIDS does not seem to allow you to do it, you jus have to enter the folders located on the path to the indicator separated by a “\” or “/” sign depending on your client tool. (ie : “folder 1\folder 2\…\folder x”)

– Even better, you can specify multiple folders to host the same measure by separating them with a “;” sign. (ie : “folder 1;folder 2;…;folder x”). In this case the measure will appear as much as the number of folders but it will act as one and single measure as selecting or de-selecting one of the clone will automatically impact the other instances.

Note : All those behaviors are client tool dependant as the information are only metadata attached to each measure and as nothing requires the client tool to make use of it. So depending on which tool you’re using, you may have to test its behavior before proposing those mechanism to you end users. I have tested them with success in Excel 2010.


Leave a Comment

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 Comment

Microsoft Days 2011 : Data Quality for datawarehouse projects

Recently, a colleague of mine and I were asked to record a webcast for the Microsoft Days 2011 Event.

It’s a french Microsoft event that took place between the end of september and november in 7 cities across the country.

In order to offer more food for thoughts to their visitors, Microsoft is asking to some experts in their domain to participate to webcasts.

Our webcast was focusing on Data Quality and its importance for datawarehousing projects. During the webcast we demonstrated why/how Data Quality Services (DQS) and Master Data Services (MDS) can be used together to achieve better data quality in our projects.

For those of you who are speaking french, you can take a look at it here.

Some of our colleagues have registered another webcast on Sharepoint 2010 which you can watch here.


Leave a Comment