An Informal Compendium of SSAS Errors, Part 1: Introduction
When practicing for my MCTS in SQL Server Analysis Services (SSAS) 2008 R2 last spring, I was shocked to receive a number of errors that Microsoft apparently provided no documentation for. Some of them are unfathomable thanks to wording that is practically cabalistic.1 Worse still, there is little to no mention of some of these errors on major search engines like Google or Yahoo. The discussions referenced in the handful of web pages that do mention these arcane messages tend to be brief and peter out without a satisfactory solution, at best. For all intents and purposes, what this means is that the developer is on their own: no one else on the planet may know the cause or the solution for some of these problems. Somebody wrote the error text for Microsoft, but they’re not talking.
In some cases I was able to decipher the messages, determine the causes and even sometimes to find solutions, either on my own or by tracking down the one web page on the planet where a solution was available. Since this cost me valuable study time and might cost developers lost time and money for production databases, I figured the most valuable contribution I could make to the SQL Server community would be to begin my casual blog with a list of these errors. This may be the only source of information on the Web for some of these problems, or among just a handful.
At this point, any answer, even an incorrect or incomplete one, would at least provide a starting point for other users to build on. Although I passed my MCTS and continue to gain experience with SSAS, I’m not yet getting paid to use MDX or DMX – so if a professional contradicts what I say here, take their advice before mine. I welcome constructive feedback on anything I post here, since I still consider myself a rookie. I have included attribution whenever possible for any sources I may have used to develop my answers, although I may have missed a credit or two inadvertently, since I found most of these solutions in a hurry more than six months ago. Initially, I didn’t keep accurate records until I hit on the idea of eventually including these errors in a blog someday. I’m providing this sloppy compendium as a courtesy, because I hate seeing labor go to waste reinventing the wheel all over again. This isn’t intended to provide an authoritative answer to these problems, because at this point any answer will do for some of them.
For whatever reason, the lack of documentation seems to be more pronounced in tandem with how much I like the particular areas of SSAS. Most of the arcane errors seem to occur in connection with data mining, an aspect of SSAS that is apparently little used despite being the apex of business intelligence (BI). It’s also my favorite part of SSAS, which is in turn my favorite part of SQL Server and stands out as one of the aspects of programming I enjoy – I’ve always been attracted to it, since building my first neural net well over a decade ago. There are fewer mysterious errors associated with MDX and ordinary SSAS objects like cubes and dimensions and the answers available in Microsoft’s documentation and on the Web tend to be more substantial, yet there are still some real time-consuming problems to be found. I have yet to see any serious errors on the relational side of SQL Server lacking documentation by either Microsoft or the rest of the world.
Although my plan may change, I will post a new part of the compendium each week, in reverse order of the frequency of the errors, by topic. For example, because there are so many odd problems associated with Data Mining (http://multidimensionalmayhem.wordpress.com/2011/09/21/an-informal-compendium-of-ssas-errors-part-2-data-mining/), I will provide the errors associated with it first. The next entry, http://multidimensionalmayhem.wordpress.com/2011/10/02/an-informal-compendium-of-ssas-errors-part-3-unresolved-errors/, covers poorly documented errors that I couldn’t resolve. This will be followed by posts on Incorrect Calculation Results and Miscellaneous and MDX Studio Errors, SSAS Object Design and finally Common MDX Syntax Errors. Check this post later for updated links to each topic.
1 This is my favorite word of the month. I’ve been using the vocabulary builder tapes available at this site: http://en.wikipedia.org/wiki/Bart’s_Friend_Falls_in_Love