Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SCD pure type 6

3 posters

Go down

SCD pure type 6 Empty SCD pure type 6

Post  Guest 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

SCD pure type 6 Empty Re: SCD pure type 6

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

SCD pure type 6 Empty Re: SCD pure type 6

Post  LAndrews 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

Back to top Go down

SCD pure type 6 Empty Re: SCD pure type 6

Post  ngalemmo 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?
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

SCD pure type 6 Empty Re: SCD pure type 6

Post  Guest 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

SCD pure type 6 Empty Re: SCD pure type 6

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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