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.

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.

  • 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
  • 3
    This is terrible advice. – Kenneth Jul 19 '11 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 '11 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 '11 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.

  • 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 '17 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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