Why does AS query my ROLAP dimension? Or, another good reason to use Unique Names

One of my particular hobby horses is always using full unique names when writing MDX, rather than just referring to the name of the member. Consider the following query in Adventure Works:

select {[Measures].[Internet Sales Amount]} on 0,
{[Source Currency].[Source Currency].&[United Kingdom Pound]}
on 1
from [adventure works]

OK it doesn’t make much sense in itself, but we know what it is doing. Now take a look at this query:

select {[Measures].[Internet Sales Amount]} on 0,
{[United Kingdom Pound]}
on 1
from [adventure works]

It returns the same results, but it’s bad for two reasons. First the reason I always knew about: if there’s another member with the same name (as there is in Adventure Works, there is more than one Currency dimension) then you can’t be sure what member the query will return. For example, you might have a Region on your Geography dimension called ‘West’ and a Customer with the surname ‘West’ – only unique name is going to be able to uniquely identify a member. The other reason I just found out about from Mosha, and it explains some weird behaviour I had noticed several times in the past but never understood. When you have a query (or worse, a calculation) that doesn’t use unique names then AS will search through the members on all dimensions to look for until it finds a member which has the right name. This is not too bad when all of your dimensions are MOLAP, but if you have a ROLAP dimension and AS decides to search through that then you can end up with a big performance problem. If you rerun the second query above and run a Profiler trace then you will see AS query the Internet Sales Order Details ROLAP dimension, and this makes the query substantially slower. Note that you might be lucky and that AS might find a member with a matching name before it comes to search the ROLAP dimension, but you probably don’t want to take that risk!

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: