SSAS dimensions hierarchies: A duplicate attribute key has been found when processing

Question

I am completely new to SSAS an I am trying to deploy a simple cube with only one dimention comprised of multiples attributes. What I did already was to create a DSV from my data source and then I created a dimension from my fact table. It seams that no matter what happens, I get the following error message:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Fact_Statistics', Column: 'Team', value: 'ANA'. The attribute is 'Team'.

This is my hierarchy: Id (SK) -> Player id -> Team -> Player Name -> Salary

I don't understand, obviously the problem is not that the value is null, like I've seen in other threads, telling me to set NullProcessing under KeyColumns to something else than automatic, but this is not the problem in this context.

Any help would be greatly appreciated.

Answer 1

Probably you have Team ANA listed under multiple Player Names and/or Salary values.

This is a really tricky area of SSAS. The quickest way forward is probably to install BIDS Helper and use the "Dimension Health Check" function:

http://bidshelper.codeplex.com/wikipage?title=Dimension%20Health%20Check&referringTitle=Documentation

It will show you all the issues in your data (not just the first one which you have discovered so far) and give you some info on how to proceed.

Personally I've gone off building attribute relationships due to the difficulty of debugging and fixing these issues. I tend to build dimensions now where every attribute relates directly to the key attribute. You never see these errors and performance seems very similar. You can still present the users with hierarchies.

If that is not an option for you, then you could try adding the columns for the higher-level attributes to the Key property of all the lower levels. Technically this will work but it is awkward to set up and maintain.

Answer 2

This approach solved my problem:

Instead of having the attributes following chained relationships, I simply leave the relationships as they were by default.

Player id (SK) -> Conference
Player id (SK) -> Division  
Player id (SK) -> Team 
Player id (SK) -> Player Name 
Player id (SK) -> Salary