Today I was working with SQL Server Analysis Services 2008 and it connects to a SQL Server 2008 database engine to pull data for a dimension. In my dimension I have a bunch of columns that are bits describing whether an object has this or that property. I built a nice dimension that included all my bit columns and attempted to process the cube but received this error:
A duplicate attribute key has been found when processing: Table: ‘%TableName%‘, Column: ‘%ColumnName%‘, Value: ‘%Value%‘. The attribute is ‘%AttributeName%‘
This error was a little misleading in my case, the attribute was tied to a a key value in the dimension and I could guarantee the key had no duplicates. The possible values in my bit columns were 1, 0 and NULL. I don’t really have any proof of this but I think SSAS was having trouble with my NULL values in a bit datatype and throwing the error above.
I found that if I changed the attribute’s NULL processing the issue was solved. Select the attribute in the dimension editor and in the properties window expand KeyColumns, change the NullProcessing property from Automatic to UnknownMember. This forced SSAS to do what I intended with the NULL values rather than letting the service attempt to figure it out on its own.
If anyone knows SSAS enough to know why this problem happened to me only on my bit datatype columns and not on others I’d love to hear from you.