I was bit of experimenting with the hierarchies in analysis services and i came into an error, that i want to show you. I've installed the adventureworksDW database and built a dimension on the dimdate table of that database. I've created a small hierarchy:
A simple hierarchy as you can see. When i process this, the following error occurs:
This occurs because the monthname appears multiple times in multiple years and they has to be unique. Okay well there's a keycolumn property of the month attribute. Simple, just make the key unique and problem solved! So i added the datekey to key column and filled in the name column:
Process Succeeded!!! Okay, let's continue with something more interesting. This is too easy stuff. Hey well wait...This is my hierarchy:
This isn't what i want. This hierarchy has a lot of multiple members with the same value. Choosing a dimension key for making unique values is not a proper thing to do! Let's change the datekey to calendar year.
Process Succeeded! Let's see the result:
A simple post about a simple problem but it took my a while before i got it solved (at first i had a more complex hierarchy). Just making attributes unique by just adding a unique field could give you undesired results.