Hilmar Buchta

SQL Server 2005 | SQL Server 2008

Some posts ago I wrote about the error message ‘A duplicate attribute key has been found when processing’. At the end of the post I suggested not to set the error configuration for the KeyDuplicate to IgnoreError (unless you are in a prototyping scenario).

Some people asked me about this (as it seems to be a nice and easy solution, similar to ‘on error resume next’ in VBA which I also wouldn’t recommend).

The main reason for me is that I definitely prefer getting a processing error instead having wrong values in the cube which could be the consequence.

For example, let’s take a look at a date dimension:

DateID Month Year
1 January 2008
2 February 2008
3 March 2008
4 April 2008
5 May 2008
6 June 2008
7 July 2008
8 August 2008
9 September 2008
10 October 2008
11 November 2008
12 December 2008
13 January 2009
14 February 2009
15 March 2009
16 April 2009
17 May 2009
18 June 2009
19 July 2009

For this simple dimension you can see that months appear more than once (once per year). This means, that the following attribute relationship is wrong (assuming that we used each of the columns above as key for the respective attribute):

image

Having set the KeyDuplicate to ‘ReportAndStop’ results in SSAS failing to process the dimension, so we are instantly aware that something is wrong. Here is the setting in BIDS (has to be set per dimension):

image 

And here is the error message if you do a ‘Process Full’ on the dimension:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‚DimDate_x0024_‘, Column: ‚Month‘, Value: ‚April‘. The attribute is ‚Month‘.

With this error message you may fix the problem and get things right. However, if you set the KeyDuplicate to ‘IgnoreError’ you will get no feed back from the system at all. However, if you take a look at the dimension you will find that each month is only associated with one of the years (in my case 2008).

image

A user might not realize this and although I have fact values for all years, my cube now only shows values for 2008:

image

The correct values in my case would be as follows:

image

So in this situation we would have totally wrong values in the cube without even getting an error message and this is the main reason for me to have KeyDuplicate set to ‘ReportAndStop’. In my trivial example, the error would be easily detected by the users but as cubes are getting more complex you might not notice some missing and some wrong values. Another source for an attribute relationship violation could be a modeling error for an SCD-2 dimension. If, for example, you relied on the attribute relationship between product and product group and forgot to model this properly although the data source keeps historic changes, than you might see the sales on the wrong product group. Again, with the ‘ReportAndStop’ option you would immediately know that there is an issue with your dimension.