Deliver a SCD Snowflake dimension

[SMitchell]

Hello,

I need to use a dimension build for the SCD functionality for a snowflake dimension. Here is a simplistic table structure / design for what I'm trying to do:

D_Dim1
--------------
Surrogate_Key (pk)
Status

D_Dim2
--------------
Surrokgate_key (pk)
country

D_Dim3
---------------
Surrogate_key (pk)
D_Dim1_Surrogate_key (fk)
D_Dim2_Surrogate_key (fk)
Name
Scd_Eff_Start
Scd_Eff_End
Scd_Curr


I need to deliver the D_Dim3 table using a dimension build because we want to track SCD but I need to pull the surrogate keys from the other dimension tables (already delivered) for make the foreign key references.

Any Ideas on how to do this? Or is it possible to use a Fact build to do some type of SCD tracking? I can I can always use triggers and default to track SCDs at the database level.

[SMitchell]

I thought I would give an update on how I was able to get this working for a smaller dimensional build that is pretty close to this.

I had a Country <-> Region snowflake dimension to create from and deliver to.

Basically,

D_Country
-------------
Country_ID (PK)
Country_Code
Country

D_Region
--------------
Region_ID (PK)
Country_ID (FK)
Region_COde
Region

So, I created a dimension hierarchy that used different templates, one for each level. The Region template contained the Country_ID and Country_Code attributes. The Country_Code attribute is used as
the parent ID for the hierarchy and the Country_ID attribute is used in the Dimension Build.

I mapped the Country_ID attribute in the Region template to the $PARENT_SURROGATE column token.

I'm hoping to take the same approach with the more complex snowflake dimension. I just need to logically come up with a hierarchy structure.

[MFGF]

Thanks for the update! :)