Error configuration in SSAS
In SSAS, there is an Error Configuration object that is central to the management of data integrity errors.
The following picture shows the ErrorConfiguration properties for a cube:
There are numerous objects where you can specify the error configuration via the properties dialog: dimension, cube, measure group and partition. Also, when you are on the “Process Database” dialog, you can change the error configuration via the “Change Settings…” button. In addition, the error configuration can also be overridden on the Batch and Process commands.
So why would you want to change one of these error configuration properties? The most common reason I have found is for the situation where a fact table has records with IDs that are not in the corresponding dimension table. For example, the sales fact table has records with product_id that do not exist in the product dimension table. If this happens the server will produce a KeyNotFound error during partition processing. By default, KeyNotFound errors are logged and counted towards the key error limit, which is zero by default. Hence the processing will fail upon the first error. But in most cases we don’t want the processing to bomb, instead we would rather just allocate the fact record to the unknown member (especially when you are in development and the data warehouse is only half-finished). To accomplish this, just modify the ErrorConfiguration on the measure group or partition to KeyNotFound=IgnoreError.
There are lots of other possible data integrity errors that have multiple ways of handling those errors. Instead of explaining them all in this blog, I’m going to refer you to Handling Data Integrity Issues in Analysis Services 2005 since it does a very thorough job of explaining all the various scenarios.
James,
Great article. I totally agree with using the error configuration in this matter. Let the cube process and most of the time your nulls, blanks or unknown values will surface on your reports and you can clean them up in the ETL as you go along in life. Thanks
Paul
Pingback:Microsoft – SQL Server – Analysis Services (SSAS) – “The Attribute Key cannot be found when processing Table…The attribute is” | Daniel Adeniji's - Learning in the Open