An Informal Compendium of SSAS Errors, Part 4: SSAS Object Design

This is the third 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 covers material for which the documentation is generally better than that discussed in previous posts; it is provided anyways though in the hope that it will help other developers track down errors a little faster. 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. 15 for the next installment, An Informal Compendium of SSAS Errors, Part 5: Incorrect Calculation Results, Miscellaneous and MDX Studio Errors.

This post contains information in a table format that may not render clearly if viewed at SQL Server Central because of the wider column style used at that site. If you have problems viewing the table, please check out the original post at https://multidimensionalmayhem.wordpress.com.

Error Cause/Solution
1. Server Operation Cancelled – When processing a dimension, you receive this arcane error message: “Server: The operation has been cancelled.” I was able to eliminate this error for several dimensions in various projects by setting the KeyDuplicate property (which can be found under the dimension’s ErrorConfiguration) to ReportAndContinue. For an alternative solution involving the ForceCommitTimeout server property, see http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx. It has been some time since I encountered these errors, so I do not recall any specifics about my design that might have made my solution work in my particular cases. It is possible that I was getting key errors frequently enough to make queries exceed the server timeout default of 30 seconds. In any event, it is worth a shot if other solutions fail.
2. Errors in the metadata manager – Deployment of an SSAS project fails with error messages similar to these: “Internal error: An unexpected error occurred (file ‘mdremote.cpp’, line 839, function ‘MDDimension:: FetchRemoteInfo’). Errors in the metadata manager. An error occurred when loading the TimeDimension dimension, from the file, ‘\\?\C:\Program Files\ Microsoft SQL Server \MSAS10.MSSQLSERVER \OLAP\Data \MyProject.0.db\ TimeDimension.1.dim.xml’. Errors in the metadata manager. An error occurred when loading the MyCube cube, from the file, ‘\\?\C:\ Program Files\ Microsoft SQL Server \MSAS10.MSSQLSERVER \ OLAP\Data\ MyProject.0.db \ MyCube.35.cub.xml’” One recommended solution is to change the deployment options by right-clicking the project in BIDS and selecting Properties, then the Deployment tab. Change the value from Deploy Changes Only to Deploy All. You can also drop the database in SQL Server Management Studio (SSMS). I have had better luck navigating to the offending files and deleting them manually, then rebuilding the project.
3. Duplicate Attribute Keys – You receive the common error message, “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing.” There are at least four potential reasons for this common error. Hilmar Buchta provides an excellent in-depth discussion of the first three at http://ms-olap.blogspot.com/2009/11/duplicate-attribute-key-has-been-found.html.
1. There is a null value in one of the key columns.
2. The values for the column’s Source and Name properties don’t match.
3. The column contains duplicates but could be made unique by adding more columns to the key column, especially when hierarchies are involved.
4. You’ve added a dimension identical to the fact table in the measure group. If you really need to use members of the fact table as dimension attributes, one solution is to create a view on the distinct values of the columns you want and turn them into a dimension. This is a problem I ran into frequently thanks to my inexperience. I may have found the solution online somewhere.
4. Duplicate Attribute Keys – You receive the common error message, “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing..”
Hierarchy Missing in the Browser – You recently added a hierarchy to a dimension, but it is not visible in the BIDS browser.
Try processing the dimension. It will normally be visible after that.
5. Join Cannot Be Reached – You receive the following error message: “A table that is required for a join cannot be reached based on the relationships…” This apparently occurs because you’re supposed to either use the bridge table (which allows you to select all of the related attributes) or to join it in the cube itself (with dimension References, etc.). I was able to fix this in one instance by reversing the relationship between two tables in the DSV.
6. Key Errors On a Double Fact Table – You receive key errors after adding another dimension based on the same data as the fact table. Although an SSAS guru may know of a workaround, it appears from my green vantage point that you apparently can’t add a dimension based on the same source as the fact table. I’ve consistently gotten key errors in any project I’ve tried this in, no matter how I linked the dimensions to the fact table in Dimension Usage.
7. Missing Parent-Child Values – You create a hierarchy, but some of the child values appear under their respective parents, while others do not. No error is raised. This is perhaps the most disturbing problem I have yet run into with SSAS, one that potentially far more serious implications than the other problems I criticize. This is not mere missing documentation or absent functionality for viewing multiple axes; this is inaccurate data. On one project I was working with, I created a parent-child hierarchy but immediately noticed that some of the children weren’t appearing under their respective parent records. I spotted the issue right away because I was very familiar with the data, after having used the same databases to study for my SQL Server exams for years. At first nothing worked to fix the problem, even changing an array of different properties, including the keys. What finally did the trick was to close the project, open a different one, then to reopen the old one,in that precise sequence. I imagine this is probably another SSAS metadata mistake on Microsoft’s part. It could be a costly one, since inaccurate results could easily go into a production environment.
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 October 8, 2011, in An Informal Compendium of SSAS Errors and tagged , , , , , , . Bookmark the permalink. 2 Comments.

  1. Hi Stevan: I just came across your articles on SSDM as I also have spent many hours in a concerted effort to make friends with this little known part of Sql Server. My copy of Jamie McClennan’s book is close to being worn out! I really appreciate the thoughtfulness that has gone into each of your artivles, I am very greateful for your insight into SSDM. By the way, you might be interested in the continued evolution of the SSDM technology has gone via with work of Predixion Software (www.predixionsoftware.com). There’s a Developer Version you can download and use to get to know the technology. With best wishes for every success, Michael Martin (michael@informationarts.ca – http://www.informationarts.ca)

  2. Thanks for the feedback Michael – since I’m self-taught and have yet to work in the field, trying to gauge my own skill level is difficult. I think once I finish this introductory series on SSDM I will move on to writing about how to use other mining tools with SQL Server, like Predixion Software, WEKA and RapidMiner and Autobox. That will give me a clearer picture of how the industry works and put SSDM’s capabilities in context. I haven’t been able to try these tools out yet, but I might be able to start blogging on them sometime this spring. Thanks again — Steve

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: