SSAS – Measure Group Error Configuration (Fact and Dimension Key Errors)

SSAS allows a large amount of flexibility in its ability to handle inconsistencies between keys in the fact table and the available keys in the dimension table, that is, the way that it treats fact data when there is no dimension key found to match it to. This post looks at error configurations in SSAS and the outcomes of various processing options.

Default Behaviour

When the measure group is created it inherits a default error configuration. This can be seen in the properties of the measure group (as below). In this situation, the error for a KeyNotFound error (which occurs when the fact key does not find an appropriate dimension key) will raise an error. Because the default behaviour is to allow zero errors, (KeyErrorLimit = 0) and the error action is to stop processing (KeyErrorLimitAction) processing will fail when the fact is processed without a corresponding dimension key.

Default Error Configuration

 

If we process without referential integrity, you will see an error and warning in the process dialog indicating processing stoped because of a limit was reached and warning showing the attribute keys that could not be found.

When the measure group is processed through an xmla command, the same error message is returned as the results for processing.

Over-riding the default behaviour at processing

The default processing behaviour can be altered at processing by setting the error configuration for the process operation. Generally, there are two approaches for managing these issues. Firstly, you can choose to recognise the error, manage it but not stop processing (that is, you stop logging the error) or secondly, you can choose to ignore the error completely (so that it does not register as an error).

Recognising the Error

To stop logging, you simply change the On error action setting dim the ‘Dimension key errors’ tab to stop logging (as below). While the measure group will process successfully, you will be warned that key errors have occurred.

The generated XMLA includes an ErrorConfiguration tag for the limit action;

<ErrorConfiguration
…..

<KeyErrorLimitAction>StopLogging</KeyErrorLimitAction>

</ErrorConfiguration>

 

When the measure group is processed, processing is successful however, the errors are logged.

While this may seem a suitable method for processing the measure group, the method has implications for SQL Agent jobs use a step to process and execute the command as XMLA (an SQL Server Analysis Command step type). In this situation, even though the cube processes as expected, the agent step raises an error. Note that the step only fails when a facts key cannot be found. When there are no fact/dimension errors, the step runs as successful.

Ignoring the Error

An alternate method of control is to ignore the error completely. In this instance we simply set the ‘Key Not Found’ error to ignore the error (as below);

In this situation, the an agent job will process regardless of any fact key errors and there will be no warnings to indicate which keys were not found.

Changing the Default Measure Group Behaviour

Needless to say that managing this error configuration can occur at the measure group level (measure group properties à custom error configuration as above). The benefit of setting the error configuration for the measure group is that individual partition processing does not require specific error configurations in-order to process.

Including the Fact

The final consideration for the treatment of fact / dimension key errors should be how to treat a fact record that has no related dimension key. The two options for this are to convert the record to an unknown member in the violating dimension or to disregard the record completely. Both options can be set in the KeyErrorAction property for the measure group.