Here is an example picture of my layout.
As you can see I have my SCD types present (status/startdate/enddate/businesskey).
My key is a surrogate key that identifies each record.
My problem is that my hierarchy seems to error out and it may be due to an incorrect layout. Here is my current key column structure.
LineOfBusinessId (key column: LineOfBusinessId) (name column: LineOfBusinessName)
WorkerDivisionId (key column: LineOfBusinessId, WorkerDivisionId) (name column: WorkerDivisionName)
WorkerId (key column: LineOfBusinessId, WorkerDivisionId, WorkerId) (name column: WorkerName)
This dimension errors out because a workername changes and when the select distinct occurs during full processing it finds duplicates. I am just wondering what the best way of setting up the key columns is for this specific situation. Do the names need to be part of the key columns or is my hierarchy completely incorrect. I was sure I had this set up correctly but I am obviously questioning myself now.
I have received some advice that the best thing to do in this situation is to use the surrogate key as part of my key columns, but then found out that this will cause issues as I only want one record to show up when querying my SCD. Later, I received some more advice saying that I shouldn't use the surrogate in the id key columns but in the name key columns, specifically at the Worker level.
I am rather skiddish of updating all of my cubes with this setup without hearing from someone who has made a hierarchy of this type. When I look at this setup, I have to think that others have made hierarchies of this type as it is rather simple. Any advice/direction on this is greatly appreciated.