SCD pure type 6

View previous topic View next topic Go down

SCD pure type 6

Post  Guest on Thu Sep 12, 2013 2:40 am

We need to generate two client reports, one with history data and another with latest data as per our requirement.
1. Client details as on the event date (when the fact record was created).
   >> This can be achieved by SCD Type 2 (join fact and dimension using dimension keys)
2. Client details as on today.
  >> We need to self join dimension table to get latest client details if we use SCD Type 2. This is more complicated when the natural key includes more than one columns.

I think of using SCD 'Pure Type 6' to avoid self joining of dimension table to get latest data when joining with fact table (as mentioned in http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Below the sample Pure Type 6 table:
Dimension table:
ClientKeyForenameSurnameAgentStartDateEndDate
123EmilyWilliamsJacob2013-01-012013-06-30
123EmilyJacksonJacob2013-07-012013-07-15
123EmilyJacksonOlivia2013-07-169999-12-31
Fact table:
ClientKeyTotal purchaseEffectiveDate
1232002013-05-01
Report with history client details can be generated by:
select * from FactTable F
join DimTable D on F.ClientKey = D.ClientKey
where F.EffectiveDate between D.StartDate and D.EndDate

Report with latest client details can be generated by:
select * from FactTable F
join DimTable D on F.ClientKey = D.ClientKey
where getdate() between D.StartDate and D.EndDate

Do you think this desing is fine? Is there any limitation in this scd type?

Many thanks.

Guest
Guest


Back to top Go down

Re: SCD pure type 6

Post  BoxesAndLines on Thu Sep 12, 2013 9:06 am

How do I know a type 6 foreign key from a type 1,2, or 3 foreign key? You have now made the end user understand the content as well as the structure to query your dimensional model. There's nothing in the data model that tells the user that he must include date in all his joins to the type 6 dimension. A more straightforward solution would be to simply create a type 1 and 2 dimension.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: SCD pure type 6

Post  LAndrews on Thu Sep 12, 2013 12:43 pm

Even though I'm a traditional purist (I only consider type 1,2,3 as SCD types, the others are just hybrids/modifications) - your example doesn't reflect "type-6".

Type 6 dimension contains "current" versions of the attributes. For example, on a given row, the dimension would have "Surname" (type-2 attribute) and "Current_Surname" (type-1 attribute).

Your fact record is created with the surrogate key in effect for the fact record date.

Your joins are then simplified, with no date logic required by the report creator - their only decision is to use the "Surname" or "Current_Surname" attribute.


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: SCD pure type 6

Post  ngalemmo on Thu Sep 12, 2013 1:57 pm

To B&L's point... what are you trying to accomplish? Are you trying to make it easier for you or for the users?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD pure type 6

Post  Guest on Thu Sep 12, 2013 2:42 pm

Thanks for your valuable time.

This is just to make the query easier (for user) by avoiding additional self join which make the query complex if the natural key includes more columns, also to consider the performance if multiple dimensions to be joined with fact table and each dimension table needs self join (only if latest data is required). Please note that this is not 'Type 6' but 'Pure type 6' (as mentioned in http://en.wikipedia.org/wiki/Slowly_changing_dimension). I also don't want to include additional columns to capture original value for each column. I think of giving up this method if not a standard type.

Thanks again.

Guest
Guest


Back to top Go down

Re: SCD pure type 6

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