SQLServerPedia

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...

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...

Popular Entries