SCD pure type 6
3 posters
Page 1 of 1
SCD pure type 6
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:
Fact table:
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.
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:
ClientKey | Forename | Surname | Agent | StartDate | EndDate |
123 | Emily | Williams | Jacob | 2013-01-01 | 2013-06-30 |
123 | Emily | Jackson | Jacob | 2013-07-01 | 2013-07-15 |
123 | Emily | Jackson | Olivia | 2013-07-16 | 9999-12-31 |
ClientKey | Total purchase | EffectiveDate |
123 | 200 | 2013-05-01 |
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
Re: SCD pure type 6
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: SCD pure type 6
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.
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
Re: SCD pure type 6
To B&L's point... what are you trying to accomplish? Are you trying to make it easier for you or for the users?
Re: SCD pure type 6
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.
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
Similar topics
» Kimbal Fact Table Type - Transactional Fact Type Issue
» rationale behind dimension with Type 0 and missing Type 5
» Type-2 Dates as Date Data Type ?
» Why do I need type 3 and 6 SCDs when I can implement type 7?
» Type 2 dimension or type 2 column?
» rationale behind dimension with Type 0 and missing Type 5
» Type-2 Dates as Date Data Type ?
» Why do I need type 3 and 6 SCDs when I can implement type 7?
» Type 2 dimension or type 2 column?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|