SCD 2 Dimension Design Question
Posted: Thu Mar 22, 2007 12:31 am
Hi All,
Thanks for all the help you have provided so far.
Ok, this is related with SCD2 dimension and I need your experience for designing the hierarchy table associated with a SCD2 dimension.
The hierarchy is a recursive hierarchy.
My question is what usual practice is followed for designing recursive hierarchy associated with SCD 2, do we actually include the ID columns (the natural key of the dimension table) for the levels in the hierarchy, or we actually include the SID columns for the levels in the hierarchy.
Doing it with the ID columns would make sure that the fact data associated with the old SCD history rows is summed with the new data.
Doing it with the SID columns would associate the fact data with the correct history row and it won't be summed up with the new data.
I am just confused on what approach is usually required/followed. I hope I have described the situation correctly.
Thanks in advance.
Thanks for all the help you have provided so far.
Ok, this is related with SCD2 dimension and I need your experience for designing the hierarchy table associated with a SCD2 dimension.
The hierarchy is a recursive hierarchy.
My question is what usual practice is followed for designing recursive hierarchy associated with SCD 2, do we actually include the ID columns (the natural key of the dimension table) for the levels in the hierarchy, or we actually include the SID columns for the levels in the hierarchy.
Doing it with the ID columns would make sure that the fact data associated with the old SCD history rows is summed with the new data.
Doing it with the SID columns would associate the fact data with the correct history row and it won't be summed up with the new data.
I am just confused on what approach is usually required/followed. I hope I have described the situation correctly.
Thanks in advance.