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 http://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. |
