Home > SSAS > SSAS 26# – “Ignore Error” when processing cube

SSAS 26# – “Ignore Error” when processing cube

I guess maintaining an Analysis Services database is not easy as 1-2-3. Maintaining SSAS for both development and production environment with multiple developers can be hard. It’s even harder when there are also many linked dimensions and measures. Now also imagine that there are also service level agreement that dictates a time window for cube processing. Also image that on top of all these, there is a proprietary data visualization tool that needs a lot of attention.

Fortunately there are more experienced co-workers who are supporting all of these. My job as a BI developer has a defined scope of responsibilities, that is, the projects I am assigned for development work. Since it’s development work, the development server is my playground. I recently noticed that my cubes were “browseable” one moment, and “cannot be browsed” minutes later. Processing the main cube with many linked dimensions and measures also generated errors.

It turned out that some shared dimensions were processed during the day. This broke my cubes and also the main cube that is linked to those shared dimensions. Fortunately it takes me only a few minutes to reprocess my cubes.

The more troublesome is the main cube where there are many linked dimensions and measures. It generated lots of errors during processing. If you read through the errors, there are really two types of errors. One type is the Dimension Key errors, where fact tables have key values that are missing from the dimension tables. Another type of errors are missing object errors, where tables or views that are sources of fact tables or dimension tables are missing.

Although missing dimension key values is a pretty serious problem, but in the development environment, we do have the freedom to ignore the errors as shown in the screen shot below. Missing object errors can also be fixed easily by copying them from the production server.

From the settings before processing the cube, we can choose to ignore four types of dimension key errors.

  • Key not found
  • Duplicate key
  • Null key converted to unknown
  • Null key not allowed

image

The information below is from a technet article Processing Options and Settings.

image

I guess the real lesson is that we, as developers, should try to avoid these types of mistakes in our design.

  • Key not found: this is because many of the dimension tables have no primary key constraints. This prevents foreign key constraints being created on the fact tables. When data is deleted or missing from the dimension tables, fact tables are left with many records with missing dimension key values.
  • Duplicate key: this is caused by the lack of primary key or unique index constrains on the dimension tables.
  • Null key converted to unknown: key values on the fact tables should not be left Null in general. If they truly have no values, populate them with “Unknown”, “N/A”, “Not Defined”, or something that is not Null. Null is a SQL term that represents Not Defined, so let’s translate it into something that human eyes can read.
  • Null key not allowed: again it’s better that we take care of the Null key value issue than letting Analysis Services guess what we meant in the first place.
    It takes me very short moment to choose “Ignore error”, and takes me just a moment longer for me to reflect on my disciplines as a BI developer.   
Categories: SSAS
  1. March 10, 2012 at 10:19 pm

    Good!

    Like

  1. No trackbacks yet.

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: