An Informal Compendium of SSAS Errors, Part 2: Data Mining

                This is the first 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. For an introduction to the compendium, see https://multidimensionalmayhem.wordpress.com/2011/09/21/an-informal-compendium-of-ssas-errors-part-1-introduction-and-data-mining-errors/. Check back Saturday, Oct. 1 for the next installment, An Informal Compendium of SSAS Errors, Part 3: Unresolved Errors.

Error	Cause/Solution
1. New Measures Aren’t Visible in the Mining Structure Add A Column Dialog Box – You can’t add calculated measures to an existing mining structure in BIDS because they’re not visible in the Add Column dialog box.	All I had to do was process the cube, close the project then reopen it and the new measures appeared when using the Add Column button.
2. An Attribute Value Not Supported by the Mining Algorithm Prevents Processing– You receive an error similar to the following, although the specific range of values may be different from case to case: “Error (Data mining): An attribute value is outside the range of values [-1.79769313486231E+308,1.79769313486231E+308]. This is not supported by the data mining algorithm.”	Setting NullProcessing to Preserve may resolve some of these errors. I received this message several times in connection with calculated measures that were dependent on other calculated measures. After checking the underlying data, I found that some of the calculations had values of 1.#INF, which is normally equivalent to dividing by zero. It turns out that some of the dimension columns in the root measure had their NullProcessing set to Automatic, which allowed missing values to mess up the calculations. Cases like these were the only times I have encountered the error, which I was able to resolve by setting NullProcessing to Preserve for those columns. Then I processed the relevant cubes, followed by the mining structures.
3. Predict Function OLAP Error – When using the MDX Predict function, you receive the following unfathomable error: “The specified mining model is not part of an OLAP mining structure”	This is one of those arcane messages which return no results on search engines. It seems to occur when a DSV is set to a SQL Server source rather than a cube, so the only solution may be the messy one of changing the DSV itself.
4. DMX Function Context Errors – When using data mining functions, you receive errors like this, although the name of the function may vary: “The PREDICTHISTOGRAM function cannot be used in the context”	This signifies that a particular function can’t be used with the algorithm the mining structure is built on. For example, Lag cannot be used with Time Series mining models. I have seen this error in connection with PredictHiatogram and Lag, although it is probably not limited to those two DMX functions.
5. Processing of a Mining Dimension Fails – “Source mining model cannot be processed. The algorithm for the source mining model does not support data mining dimensions.”	Data mining dimensions apparently cannot be built on models that use certain algorithms without causing this error. The only one of the nine algorithms Microsoft included in SSAS 2008 R2 that I have seen trigger this error is Time Series, although I cannot recall if I have tested the others yet or not.
6. Unprocessed Dimension Errors – You receive one of the following errors, possibly in combination with the others, when attempting to process a cube: 1. “Object does not contain any cases. The drillthrough store is empty either because the ProcessType enumeration was set to ProcessClearStructureOnly, or a DMX DELETE statement cleared the store.” 2. “Mining model failed because either the training data is empty or there were no significant attributes found” 3. You receive VALUE! errors on calculated measures 4. Error messages warning you that SSAS could not find an attribute	I received this cluster of errors on a couple of occasions after adding new data to the dimensions, because I did not process them before processing the cube. SSAS couldn’t retrieve the data so it could not process the associated mining models and in turn, could not process the mining model dimensions associated with them. Processing the dimensions followed by the cube fixed all of these errors.
7. Missing Source OLAP Object with Data Mining – When working with data mining, you receive an error indicating that a “source OLAP object” for a column “cannot be found.”	This error went away after I changed the column type of a calculated measure from Cyclical back to Continuous, which is what the Wizard had originally assigned.
8. Multiple Key Sequence Columns – Deployment on a mining structure that uses the Sequence Clustering algorithm fails with the following error text: “Error (Data mining): Multiple sequence key columns were detected in the sequence table in the MyClusteringModel model. The Microsoft Sequence Clustering algorithm only accepts tables with one sequence key column.”	I may be making some kind of inadvertent error due to inexperience, but I have received this error repeatedly in various projects even when only one column has been designated as a Key Sequence. For example, in one particular project I have a DateID set to Key and a DateID in a nested table set to Key Sequence. It always generates this particular error unless I set the nested DateID to Key as well, which may unfortunately throw off the results of the algorithm. This is the only solution I know of to this strange error, which is at a minimum badly worded, if not an outright bug. Either SSAS is falsely detecting a second Key Sequence column, or it is detecting a different problem and returning an inaccurate message.
9. Undefined Key Column in Sequence Clustering – You receive an error containing the message “a key column is not defined” when processing a Sequence Clustering structure.	One potential answer to this predicament is to make sure that any nested table has a key defined in its DSV. See the answer by Rok1 at http://social.msdn.microsoft.com/Forums/en-US/sqldatamining/thread/cf05cb1e-744e-4a16-b109-f4b55c67c80c for more details. In the case that I encountered, this solution did not work. For some reason, the column in question had been incorrectly autogenerated as PredictOnly in the mining model. After I set it to Key the error was resolved.

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.¹ 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 (https://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, https://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}