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):
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):
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).
A user might not realize this and although I have fact values for all years, my cube now only shows values for 2008:
The correct values in my case would be as follows:
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.
Kommentare (0)