SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Posted by Anonymous on 13 October 2011

Pingback from  Dew Drop – October 13, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.