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.

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, SSAS. Bookmark the permalink.

One Response to Error configuration in SSAS

  1. paul says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>