5

I've set up a Data Source Connection to the Adventure Works DW database using the service's 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 setup, 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 Google 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.

Improve this question

5 Answers 5

Reset to default
0

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.

Improve this answer
5
  • 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, 2011 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, 2011 at 18:13
  • 3
    This is terrible advice.
    – Kenneth
    Jul 19, 2011 at 15:07
  • @Kenneth I agree, "As I said, this doesn't SOLVE the issue...", I'd love to hear your thoughts on identifing the cause and what the solution might be.
    – ScottCher
    Aug 1, 2011 at 15:05
  • 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, 2011 at 16:57
8

I feel you're treating 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 have a 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.

Improve this answer
1
  • Your solution works. But if you used the format YYYYMMDD you get the right sort order. Combine the key formats YYYYMMDD for day, YYYYMM for month, YYYYQ without a letter for quarter and YYYY for year and you get unique integer keys for the whole hierarchy.
    – Stefan Wilms
    Jun 7, 2017 at 6:14
7

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 you're trying to do.

ol penney provided you with the correct answer. This may also help: A duplicate attribute key has been found when processing… by Hilmar Buchta.

Improve this answer
0
0

In case of duplicate keys and to do what Ol Penny proposed:

16062000--->062000-->Q12000--->2000
  1. Edit your dimension
  2. Click on the right of KeyColumns in the Properties window
  3. Specify the NameColumn explicitely
Improve this answer
0

In attributes tab, by selecting the calendar quarter column and pressing f4 we get its property window. There go to the keyColumns property and add calendar year along with calendar quarter. When we do that we get an error at calendar quarter attribute as we provided more than one column in keyColumns. Now go the NameColumn property and change it from none to calendar quarter. So the error will be resolved.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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