An Informal Compendium of SSAS Errors, Part 6: Common Errors

This is the last in a series of posts on some SQL Server Analysis Services (SSAS) errors for which there is apparently little, if any, documentation anywhere on the planet. This particular post is a coda that discusses some common errors, for the benefit of beginners. For an introduction to the compendium, see my first post. I’m going to skip a week around the Thanksgiving holiday and hope to post again on Saturday, Dec. 3, this time on the topic of changes in DMX and data mining in the upcoming version of SQL Server. That of course assumes that there are any changes; I haven’t seen anything of great significance in the first two Community Technology Previews for SQL Server 2012, which previously went by the code name Denali. The release candidate for SQL Server 2012 has just been released, however, and I hope for a chance to glance over the changes in this oft-neglected topic.

This post contains information in a table that sometimes renders in a different column format when viewed at SQL Server Central. If you have problems viewing the table, please check out the original post at https://multidimensionalmayhem.wordpress.com.

Error Cause/Solution
1. MDX Statement Ambiguity – When working with MDX, you receive this commonplace error:”The statement dialect could not be resolved due to ambiguity.” A row or column is probably not enclosed in { } curly braces the way it should be. This was my very first error in MDX, as it probably was for a lot of rookies before me.
2. Two Axis Limit – When working with MDX in SSMS or other Microsoft tools, you receive this heart-rending error: “Results cannot be displayed for cellsets with more than two axes” In order to view more than one axis, you need to use another tool besides SSMS, such as Panorama or Proclarity (I have yet to use either but have heard both recommended by professionals). That means no Pages, Sections, Chapters or anything besides a flat two-dimensional view. What is the point of going multidimensional if you’re limited to just two dimensions? It’s like going to the Taj Mahal, but only to use the restroom. Stand back while I get on my soapbox and rant: I like a lot of Microsoft’s products, especially SQL Server, but there are times when it can’t even handle its own software. There is a pattern across the company of adding exciting features, then overlooking crippling errors or missing functionality that renders them useless. Another case in the SQL Server world is Intellisense in T-SQL, which has been more of a nuisance than an aid for the last two versions because of a simple bug that interferes with typing object names.
3. More Column Names Than Defined – You receive a message including the phrase: “has more column names specified than columns defined” Some of the base table columns referenced in a view definition may no longer exist.
4. Two Operands from the Same Measure Group – You receive an error worded like this: The “right operand of the measure expression of the” object “cannot belong to the same measure group.” What this boils down to is that you can’t create a measure based on the value of two two tuples from the same measure group, i.e. MyMeasure.ValueSum / MyMeasure.RecordCount. For more information, see this thread. A Microsoft rep states at this webpage that, “This is a common request from customers and is planned to be implemented in our next release. For Yukon, you could use the following work around: if you want to associate the new calculated member with specific dimensions, you could set the non-empty behavior for the calculated member to a measure from a measure group that intersects with those dimensions” Unfortunately, this reply was posted five years ago but the feature apparently was not implemented, unless a separate problem is responsible.
5. Different Dimensionality – When working with MDX, you get this common error: “Two sets specified in the function have different dimensionality” This is a common error but it’s tricky to avoid; unlike the rest of these common MDX errors, you really need to think about your data at a higher level to understand it. The root of the problem is in comparing sets that really can’t be sensibly compared; it’s an apples and oranges problem writ large. It’s a bit like asking “how green is nine o’clock?” Just because the ultimate cause of such errors is often nonsensical logic doesn’t mean such errors are easy to spot. Being inexperienced, I still get it frequently.
Advertisements

About Stevan Bolton

I am a VB programmer and SQL Server DBA with an interest in MDX and multidimensional applications. I have an alphabet's soup of certifications: * 3 MCTS certifications in SQL Server 2008 R2, including a recent exam in MDX and Analysis Services * an MCDBA in SQL Server 2000 * an MCSD in VB6. I've kept up with each version of VB since then but haven't taken the newer exams * I also have a Master's in American history with a concentration in foreign affairs, as well as some work towards a doctorate in Latin American history * My B.S. is in print journalism I'll be posting whatever code I can to help out the SQL Server and VB developer communities. There is always someone out there more knowledgeable, so if you're a guru, feel free to correct any information I might post. I haven't yet been paid professionally to work with some of the technologies I've been trained in and enjoy, like MDX, so the word of those who have ought to carry more weight. There's a shortage of information on some of the topics I'll be posting on, such as the arcane error messages in Analysis Services (SSAS), so users might still find some value in my posts. If you learn of any job openings for MDX, SSAS, SQL Server and VB, feel free to E-mail me.

Posted on November 20, 2011, in An Informal Compendium of SSAS Errors and tagged , , , , , , . Bookmark the permalink. 2 Comments.

  1. Great post … very useful content!
    Did you know SSAS has a wiki on Technet? Your article is the kind of content that would benefit lots of customers, if you ever find the time for cross-posting (hint hint).
    http://social.technet.microsoft.com/wiki/contents/articles/1175.sql-server-analysis-services-ssas.aspx

    P.S. You’re on the mark about analytics (aka data mining) becoming more important than ever. Check out the Azure Numeric Library and let me kknow what you think.
    http://social.technet.microsoft.com/wiki/contents/articles/5993.microsoft-codename-cloud-numerics.aspx

  2. Thanks for the reply 🙂 (Sorry it took me so long to get back to you too). I glanced over the Technet site and might see if can cross-post it there – but it’s a Microsoft site, and I wonder if the the MS moderators would permit it, since I take a bit of a critical tone at times on some of these errors. I’ve used the Technet forums quite often but have never posted there.

    That Azure library is interesting…I was hoping we’d have Analysis Services cloud features in SQL Server 2012, but I don’t think there’s anything coming in this release (I will be happy if they just fix the errors I mentioned in these posts, LOL). Until that day comes though libraries like this will probably be of great use – especially with the coming data explosion, we’re probably going to need every data mining tool we can get just to keep the glut of data from drowning us. Analytics is going to be not just an afterthought but a necessity. As soon as I start working in the field professionally I will probably take a second look and see if I can make use of this. I’m sure there’s probably a way to port some of the code to VB, my favorite language, or at least call it from a VB application. If so, that would make it even more useful.

    Thanks again for the feedback – now I know someone’s getting some benefit out of my posts, LOL. So I guess I’ll have to stop procrastinating and finally post again – I planned on posting once a week at first, but so far it’s worked out to once in a blue moon. 🙂

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

%d bloggers like this: