Dimension keys and key migration

Hi ,

My concerns is related to dimensions and how the relationship between dimension and fact is actually enforced in couple of specific situation.

I have dimensions with history so the dimension currently have a composite key based on the two numeric keys, A surrogate key and date numeric in the format YYYYMM.

In the Fact table also same key structure is followed but the numeric key is now part of the Calendar dimension ( this is true for dimensions as well for great extent).

If I am to create a FK relationship to add a new dimension to the Fact table , Should I be migrating my date numeric and the surrogate from the dimension ? or I should only take the surrogate ?

Is there a standard way to handle this scenario ? / Is there a reference to this scenario on the standard reference by Mr.Ralph. or any other material ?

Please share your thoughts on this.

Thanks
RG

Dimensions should only have a single column surrogate key.

Thanks for the quick reply , appreciate your thoughts , So the concept of composite key on dimensions is wrong and is violating the Dimensional modeling concepts ?

I am seeing , multiple dimension been referred by a single fact table numeric date key attribute. This is also I my mind a not good idea.

So the concept of composite key on dimensions is wrong and is violating the Dimensional modeling concepts ?

Yes. Dimensions should only have a single surrogate primary key. The relationship between fact and dimension is always many to one.

A single fact foreign key may reference more than one dimension table, but only in the case of sub-type dimensions. For example, in retail banking it is common to have an Account dimension. However, there are many different types of accounts with significantly different attributes. So, one would model sub-type dimensions to hold attributes unique to a type of account, such as mortgage, revolving lines of credit, etc. These sub-type tables would share the same PK as the main Account dimension.

Thanks , that cleared my doubt , Is there any text that I can quote / read on this ... because , If am to create a constructive conversation with in the team , I need some solid facts to prove that the current approach is not the way to go forward.

So even in case of SCD type 2 concepts , are we to stick with single key surrogate ? dont we need to version ? and in some cases , like Partitioning normally we need secondary criteria to do that right ?

THRJG74 wrote:Thanks , that cleared my doubt , Is there any text that I can quote / read on this ... because , If am to create a constructive conversation with in the team , I need some solid facts to prove that the current approach is not the way to go forward

I found it on " Data warehouse toolkit - Retails Sales , Page 61" ,

The Authors have quoted as -

Before we leave the topic of keys, we want to discourage the use of concatenated or compound keys for dimension tables

Thanks

So even in case of SCD type 2 concepts , are we to stick with single key surrogate ? dont we need to version ? and in some cases , like Partitioning normally we need secondary criteria to do that right ?.

For type 2, each row is a new version, with its own unique key. You sort things out through the natural key and effective date ranges.

As far as partitioning goes, it has nothing to do with keys, or dimensional modelling for that matter.