Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've set up a Data Source Connection to the Adventure Works DW database using the services credentials.

I'm creating a dimension using the DimDate table. To simplify things I'm using only the Calendar Quarter, Calendar Year, Day Number of Month, English Month Name, and the Date Key columns.

Each of these attributes have been set to AttributeHierarchyVisible = False.

I dragged the Calendar Year into Hierarchies followed by calendar Quarter, English Month Name, Day Number of month.

I set the attribute Relationships to Date Key -> Day Number Of Month -> English Month Name-> Calendar Quarter -> Calendar Year

All relationships are set to rigid.

When I try to deploy this set up I get the following errors:

Error 2 Internal error: The operation terminated unsuccessfully. 0 0

Error 3 Server: The current operation was cancelled because another operation in the transaction failed. 0 0

I also receive this warning

Warning 1 Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimDate', Column: 'CalendarQuarter', Value: '2'. The attribute is 'Calendar Quarter'. 0 0

I can't figure out what is going on or why this is erroring. The book I'm using gave me the steps listed above and Goggle has failed me thus far (a very scary thing).

Any help you guys can give to help me figure out what I'm doing wrong would be greatly appreciated.

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

I've often found errors reported from SSAS indicating a duplicate key value in the dimension. When I've reviewed the source data, the duplicates aren't found. While I can't shed light on why this error is popping up in the dimension, I can offer a solution that bypasses the error and allows the cube to finish deploying and processing.

If you open the dimension object and look at the properties, you'll see a section for ErrorConfiguration. If you change this to "custom", you can tell SSAS what to do when it encounters errors in processing the object. If you want to log the error and allow the processing to continue, use "ReportAndContinue" as the value for properties like KeyDuplicate, KeyNotFound, NullKeyConvertedToUnknown, NullKeyNotAllowed - whatever is appropriate for your design. I set the KeyErrorLimit to 1 and set the KeyErrorLimitAction to StopLogging so your log files don't get overloaded with the same or similar errors.

As I said, this doesn't SOLVE the issue, but it does allow the cube to continue processing and sometimes, I've found, that's enough.

share|improve this answer
    
You are awesome. Thank you so much this has been driving me nuts for the better part of a week. Your solution worked perfectly and got my project back on its feet. –  Lumpy Mar 3 '11 at 15:10
    
@Lumpy - glad to hear it, just remember to watch those logs to make sure you aren't allowing meaningful errors to fall through the cracks. –  ScottCher Mar 3 '11 at 18:13
    
FYI to the proposed edit: it should be a comment like this... –  gbn May 1 '11 at 13:21
    
@gbn there's a proposed edit? I don't see one either on my answer, the original question or the comments. Just curious to what you are referring. –  ScottCher May 2 '11 at 20:11
2  
The problem is that his hierarchy levels cannot be identified uniquely by the key he has specified. This is very common when a hierarchy is created without manually configuring the composite key for each level. In most hierarchies this is a required step to avoid the error he is having. The link I provided below explains very clearly why this happens and why it is meaningful enough to be corrected. –  Kenneth Aug 1 '11 at 16:57
show 3 more comments

i feel you're treatnig the symptoms and not the cause by suppressing errors

I set the attribute Relationships to Date Key -> Day Number Of Month -> English Month Name-> Calendar Quarter -> Calendar Year

if that data looks like "16062000"--> 6 --> "June" ---> "Q2" --> 2000

then Analysis Services will indeed get the hump, as for the "Q2" it will havea "Q2" feeding 2000 and 2001 etc

you have to make sure the hierarchy consists of unique keys

e.g.

16062000--->062000-->Q12000--->2000

you can use different fields for the Name property of each attribute, but use the keys for the keys

oh and if you sort by key on the month, this will prevent the classic sorting of months alphabetically

ol

share|improve this answer
add comment

This is going to cause problems with your dimension. More importantly, your cube will give incorrect answers. Ignoring processing errors is never a good idea. This is a very simple problem to correct. You just need to set your key attributes properly. By default, SSAS will use the same column for name & key.

Usually, the only attribute that should be configured this way is the true key (there are exceptions). The column that is unique across all records. From your post I would guess this is DateKey.

The rest of your attributes should be using composite keys.

To see the problems with your cube, set AttributeHierarchyVisible = True and play with the data. SSAS is confused about what your trying to do.

ol penney provided you with the correct answer. This may also help: http://ms-olap.blogspot.com/2009/11/duplicate-attribute-key-has-been-found.html

share|improve this answer
    
Great explanation - thanks! –  ScottCher Aug 12 '11 at 14:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.