Dimension keys and key migration

View previous topic View next topic Go down

Dimension keys and key migration

Post  THRJG74 on Tue Jan 18, 2011 12:52 pm

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


Last edited by THRJG74 on Tue Jan 18, 2011 12:54 pm; edited 1 time in total (Reason for editing : re-phrased intension)

THRJG74

Posts : 4
Join date : 2011-01-18

View user profile

Back to top Go down

Re: Dimension keys and key migration

Post  BoxesAndLines on Tue Jan 18, 2011 2:16 pm

Dimensions should only have a single column surrogate key.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Dimension keys and key migration

Post  THRJG74 on Tue Jan 18, 2011 2:37 pm

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.

THRJG74

Posts : 4
Join date : 2011-01-18

View user profile

Back to top Go down

Re: Dimension keys and key migration

Post  ngalemmo on Tue Jan 18, 2011 2:46 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension keys and key migration

Post  THRJG74 on Tue Jan 18, 2011 2:55 pm

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

Posts : 4
Join date : 2011-01-18

View user profile

Back to top Go down

Re: Dimension keys and key migration

Post  THRJG74 on Tue Jan 18, 2011 4:26 pm

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

THRJG74

Posts : 4
Join date : 2011-01-18

View user profile

Back to top Go down

Re: Dimension keys and key migration

Post  ngalemmo on Thu Jan 20, 2011 2:14 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension keys and key migration

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum