Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Ignore processing errors in SSAS 2005 and 2008

Posted by thomasivarssonmalmo on July 21, 2009

When you start with a SSAS prototype or a proof of concept cube you seldom have the time to clean data in a comprehensive way. You want to show the customer a cube with their data and if it is not perfect that is not important in a prototype. In this simple example I will show how you temporarely can ignore dimension key errors like when you have a fact table record with a dimension key that is missing in the dimension table. This example will work both in SSAS 2005 and 2008. Remember that this is not a praxis recommended for production systems. Also check my blog entry about inferred members that shows how SSAS 2005 takes care of this problem in a better way in the long run.
 
 I will start with building one time dimension table and a fact table with the following script in SQL Server 2005 management studio.
 
Create Table TimeDim
( FullDate datetime)
 
Create Table FactOrphan
(FullDate datetime,
 Qty int)
 
Insert Into TimeDim Values(‘2009-07-19’)
Insert Into TimeDim Values(‘2009-07-20’)
Insert Into TimeDim Values(‘2009-07-21’)
 
Insert Into FactOrphan (FullDate, Qty) Values (‘2009-07-19’,1)
Insert Into FactOrphan (FullDate, Qty) Values (‘2009-07-20’,1)
Insert Into FactOrphan (FullDate, Qty) Values (‘2009-07-21’,1)
Insert Into FactOrphan (FullDate, Qty) Values (‘2009-07-22’,1)
 
When this is working correctly you will have the last record in the fact table, with the date ‘2009-07-22’ as an orphan which means that it is not related to a record in the time dimension table. If you have primary- and foreign key constraints between the two tables this situation will never occur because you will get an error when you enter the last fact record.
 
Open BIDS and create a new SSAS project, connect to the database where the two tables reside and create a data source view with these two tables. Create a logical primary key in the dimension table and relate the fact table to the dimension table in the data source view. The finished result  will look like this:
 
 
After this you can right click the cubes folder in the solution view and build the cube with the wizard. It will build both the dimension and the cube. Hierarchies in the dimension table is not important for this example.
 
Now it is time to process this cube and see what happens. Right click on the root folder at the top in the solutions window and select process. We will process this cube with the default settings.
 
 
Click run and see what happens. You will get the following long error message in the process log window.
 
"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_FactOrphan, Column: FullDate, Value: 2009-07-22. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Full Date of Dimension: Time Dim from Database: FactOrphans, Cube: FactOrphans, Measure Group: Fact Orphan, Partition: Fact Orphan, Record: 4. 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. Errors in the OLAP storage engine: An error occurred while processing the ‘Fact Orphan’ partition of the ‘Fact Orphan’ measure group for the ‘FactOrphans’ cube from the FactOrphans database"
 
To force SSAS 2005 and later to ignore this error we will have to click on the change settings button after you have retried to process the cube again in the solutions window. This is the default setting of the dimension key error tab in the change settings dialogue. The first tab is not that interesting in this scenario. Activate the "Use custom error configuration setting" and all the choices below will be available. Change the setting to the ones in the next picture. 
 
 
If you want to log the discarded records you must change the "key not found" setting to "report and continue". This will help you to identify the records that was discarded in the cube processsing. The log file will look like the previous error message when did a default full processing.
 
 
Finally when you process with these setting the cube processing will work and you will see the following processing window.
 
 
 Summary: This is a way of building a prototype quickly when the ETL for the dimensions and fact table loads is not finished or have not started yet. It is not a long term solutions since fact records will be removed from the cube without any further notice unless you do someting with the log file.
Advertisements

2 Responses to “Ignore processing errors in SSAS 2005 and 2008”

  1. andreas said

    Ah, I didn’t know that. Thanks Thomas!

  2. Hui said

    Hi thomas, when I specify the Error Log path, SSAS always gives me the error saying system can not find the path. If I specify the log file on the C drive , it will say access denied. Do you have any ideas why it’s behaving this way? thanks Hui

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: