Dimension in SQL Analysis Server not unique in hierarchy

Question

I have created a Dimension in Analysis-Server like with the following hierarchy:

CountryRegion
StateProvince
City
PersonName

with the following relationship

DimCustomerID -> PersonName -> City -> StateProvince -> CountryRegion

Now, my Data for this dimenstion contains the following rows:

DimCustomerID   CustomerID  AddressID   CountryRegion   StateProvince   City        PersonName
4134            13788       19569       United Kingdom  England         Birmingham  Fernandez Kari
4717            11533       18752       United States   Alabama         Birmingham  Gill Ebony

When I try to process the Dimension, I get the error

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'BI_DimCustomer', Column: 'City', Value: 'Birmingham'. The attribute is 'City'.

I see, that the City Birmingham is duplicated with differen StateProvince. But they also have two different CountryRegions.

What can I do to get this running?

Thank you.

Answer 1

You need to specify the KeyColumns for the City Attribute. Eg (CountryRegion,StateProvince,City).

https://learn.microsoft.com/en-us/sql/analysis-services/multidimensional-models/dimension-attribute-properties-reference

See eg http://www.bidn.com/blogs/DevinKnight/ssis/1887/ssas-understanding-keycolumns-dimension-property

David