I'm in the process of building a new data warehouse and I am starting out by building a few dimensions. I have created a fairly typical "Date" dimension and that seems ok. Now I am trying to create a "Person" dimension which contains "Date of Birth Key" and "Date of Death Key" attributes, both of which are related to the key attribute of the Date dimension.
When I process the Person dimension I am receiving an error about duplicate keys. Looking into it, I found that the code that is generated for processing the Date of Birth attribute is incorrectly joining to the Date dimension using the Date of Death column:
SELECT DISTINCT [Dim_Person].[Date of birth key] ,[Dim_Date].[FullDate] FROM [Dim].[Person] AS [Dim_Person], [Dim].[Date] AS [Dim_Date] WHERE [Dim_Person].[Date of death key] = [Dim_Date].[DateKey] <--- This line is WRONG!
I have attached a screenshot showing the Date of Birth attribute. The Date of Death attribute is identical other than looking at the Date of Death Key field.
Am I doing something wrong, or is this a bug in SSAS ?