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