Home > SSAS > Check your spelling in SSAS 2008 MDX queries

 

Check your spelling in SSAS 2008 MDX queries

January 6th, 2009

I just found out that because of wrong/different spelling of a dimension name/attribute in SSAS 2008 the server may return the following message:
The set must have a single hierarchy to be used with the complement operator.

To fix the issue, we must ensure that all the dimensions in our queries are spelled exactly the way they are named in the cube (surprise?)…

I encountered this message by first building a Reporting Services report with data coming from SSAS. Then, a colleague misspelt a dimension name and instead of Natural Code he typed Natrual Code. Next time I needed to run the query in Query Designer I got the message. The query indeed used a complement operator on that dimension, exculding a particular account by its Natural Code. The query was something like:

SELECT {
[Measures].[Amount]
} ON COLULMNS,
{
([Account].[Major Account].ALLMEMBERS *
[Employee].[Employee Name].ALLMEMBERS)
} ON ROWS
FROM (
SELECT (-{[Account].[Natural Code].&[990]}) ON COLUMNS
FROM [Finance]
)
If you enjoyed this post, make sure you subscribe to my RSS feed!

 

SSAS , ,

Comments are closed.