Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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.

Comments

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.