SCD 2 Dimension Design Question

[loveojha2]

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.

[ray.wurlod]

If it's truly recursive, you need a self-join (also known as a "fish hook join"), and therefore you really only have the option to define that join on the primary key (therefore the Surrogate Key).

Or are you planning to snowflake the recursion. In that case you don't really have a Type 2 design, as it won't handle an arbitrary number of recursions, and therefore has the potential to reduce the amount of history that can be preserved.

May I ask what kind of dimension is recursive? And what you understand recursive to mean?