SSAS Dimensions Hierarchy causes deployment errors

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.

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.

StackExchange Database Administrators Q#1516, answer score: 8