Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Lose those single quotes!

with 2 comments

Although they’re no longer necessary in AS2005 MDX and I no longer bother with them, I’ve seen a lot of examples of people still using single quotes in MDX in calculated member and set definitions. Up until recently I thought this was just a matter of taste and that it did no harm to leave them in, but last week I realised that if you do leave them in it makes debugging MDX queries much harder. To illustrate, run the following two MDX queries:

WITH MEMBER MEASURES.TEST AS BLAH
SELECT
{[Measures].TEST}
ON COLUMNS
FROM [Adventure Works]

and

WITH MEMBER MEASURES.TEST AS ‘BLAH’
SELECT
{[Measures].TEST}
ON COLUMNS
FROM [Adventure Works]

Both return errors. The first gives this error message the single cell returned:
VALUE #Error Query (1, 30) The dimension ‘[BLAH]‘ was not found in the cube when the string, [BLAH], was parsed.

but the second gives this error message in the same place:
VALUE #Error The dimension ‘[BLAH]‘ was not found in the cube when the string, [BLAH], was parsed.

So you can see if you don’t use single quotes and there’s an error somewhere in your calculated member definitions you get the row and column where the error was found (highlighted in bold); if you do use single quotes you don’t get this useful information. For some of the three or four page queries that I sometimes have to debug this can save a lot of time…

Written by Chris Webb

February 12, 2007 at 10:59 pm

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  1. Agree. I have discussed this very point in one of my old blogs "To quote or not to quote" here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/04/02/10052.aspx

    Mosha

    February 13, 2007 at 12:29 am

  2. It\’s obviously been a bad week for me remembering things, hasn\’t it?

    Chris

    February 13, 2007 at 4:47 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: