SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: When processing Microsoft SQL Server Analysis Services (SSAS) 2005 cube I am getting multiple errors. Example:

1. Internal error: The operation terminated unsuccessfully.
2. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
3. Errors in the OLAP storage engine: An error occurred while processing the 'MyPartition' partition of the 'MyMeasureGroup' measure group for the 'MyCube' cube from the MyDatabase database.
4. Errors in the OLAP storage engine: The attribute key cannot be found: Table: MyFactTable, Column: MyDimKey, Value: 1234.
5. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: MyDimAttrKey of Dimension: MyDim from Database: MyDatabase, Cube: Policy, Measure Group: MyMeasureGroup, Partition: MyPartition, Record: 112233.
6. OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

How do I investigate these errors?

A: In my case all 6 above errors were reported when I tried to load fact table but one dimension key from fact table did not exist in dimension structure, that is I have not incrementally updated dimension before loading fact table. That does not mean that you have exactly the same problem, here I'll just explain were do you find what cause these errors.
Error 1: This just tells you that cube/measure group processing failed.
Error 2: Again, just information that there was error, but there is no cause for this.
Error 3: Most likely this error points to partition that failed during processing. This still does not mean that this partition has problem. If you are processing multiple partitions in parallel, it is possible that failure happened on another measure group or partition and because of that this partition processing was cancelled. If this is such case, you will see related error 6 for this partition.
Error 4 and 5: points to actual error that caused processing fail. That is what you have to fix.
Error 6: If you are processing multiple partitions in parallel, if one processing fails, other parallel processing tasks will fail and report this error.

I would like to point here, that when you do deployment from BIDS studio, you might see errors 4 and 5 not in Error window list, but in warning window list(!).

Another way to investigate these errors is to expand reported information in deployment progress window. There you will see red icon by each object that failed, and when you drill in, you will see query that was executed in source database and error message. Example of expanded structure:

Processing Database”..”
Processing cube..
Processing measure group
Processing Partition
SQL Queries
Error Message 1

Quite often you can get a lot of information on error if you will copy SQL query and execute it in the source database.

You can also get similar error when you are processing dimension that is based on the view and table structure that this view is based on changed. To fix this just drop and recreate view.