Slowly Changing Dimension Type 6 a Combination of SCD Type 1, 2 & 3.We always can not fulfill all the business requirements just by these basic SCD Types 1,2 & 3. So here lets see what is SCD Type 6 and what it offers beyond the basic SCD Types.
Where Scape RED natively supports type 1, 2 and 3 dimensions. But what on earth is a type 6 dimension? It is a combination of a type1, type 2 and type 3!
Basically there are two columns used to track a type 6 attribute –
one holds the newest value, and one holds a point in time value that references a start and end date time. This is a great way of being able to:
choose whether to report or filter by an employee’s current department or their department when an event / fact was recorded.
choose whether to report or filter by a customer’s current suburb or their suburb when an transaction / fact was recorded.
do a “where are they now?” type of report eg where are employees working now, that worked in this location 2 years ago.
Where Scape RED can build these type 6 dimensions without resorting to customizing the code. The standard Where Scape RED procedures will generate change detection code for time variant data (type 2 and 3 attributes) and RED will add change tracking columns to manage date ranges in those dimensions.
Just to summaries:
A type 1 slowly changing dimension (a Normal dimension in RED’s dimension wizard) will overwrite historical changes with the newest data – one record for each unique business key.
A type 2 slowly changing dimension (a Slowly Changing dimension in RED’s dimension wizard) will create new records each time an attribute that is marked as slowly changing does not match the “current” value for that business key. Unique start and end date ranges will manage which record is relevant at any point in time.
A type 3 dimension (a Previous Values dimension in RED’s dimension wizard) will hold the current and the previous attribute values for a tracked attribute in two attributes in the dimension.
The mysterious date ranged dimension in Where Scape RED is essentially a type 2 dimension that relies on the source system to maintain and pass changed data with date ranges.
So how would you build a type 6 dimension in RED using standard RED code? By copying a source attribute into two dimension attributes, one to hold the current value and one for a point in time value (with suffixes of _cur and _pit). This dimension should be built as a type 2 dimension in RED, with the _cur attributes marked as type 1, whilst the _pit attributes selected as type 2 attributes.
for more information follow this link.
http://www.disoln.org/2013/04/Slowly-Changing-Dimension-Type-6-a-Combination-of-SCD-Type-1-2-3.html?goback=%2Egde_1783009_member_232933293
Where Scape RED natively supports type 1, 2 and 3 dimensions. But what on earth is a type 6 dimension? It is a combination of a type1, type 2 and type 3!
Basically there are two columns used to track a type 6 attribute –
one holds the newest value, and one holds a point in time value that references a start and end date time. This is a great way of being able to:
choose whether to report or filter by an employee’s current department or their department when an event / fact was recorded.
choose whether to report or filter by a customer’s current suburb or their suburb when an transaction / fact was recorded.
do a “where are they now?” type of report eg where are employees working now, that worked in this location 2 years ago.
Where Scape RED can build these type 6 dimensions without resorting to customizing the code. The standard Where Scape RED procedures will generate change detection code for time variant data (type 2 and 3 attributes) and RED will add change tracking columns to manage date ranges in those dimensions.
Just to summaries:
A type 1 slowly changing dimension (a Normal dimension in RED’s dimension wizard) will overwrite historical changes with the newest data – one record for each unique business key.
A type 2 slowly changing dimension (a Slowly Changing dimension in RED’s dimension wizard) will create new records each time an attribute that is marked as slowly changing does not match the “current” value for that business key. Unique start and end date ranges will manage which record is relevant at any point in time.
A type 3 dimension (a Previous Values dimension in RED’s dimension wizard) will hold the current and the previous attribute values for a tracked attribute in two attributes in the dimension.
The mysterious date ranged dimension in Where Scape RED is essentially a type 2 dimension that relies on the source system to maintain and pass changed data with date ranges.
So how would you build a type 6 dimension in RED using standard RED code? By copying a source attribute into two dimension attributes, one to hold the current value and one for a point in time value (with suffixes of _cur and _pit). This dimension should be built as a type 2 dimension in RED, with the _cur attributes marked as type 1, whilst the _pit attributes selected as type 2 attributes.
for more information follow this link.
http://www.disoln.org/2013/04/Slowly-Changing-Dimension-Type-6-a-Combination-of-SCD-Type-1-2-3.html?goback=%2Egde_1783009_member_232933293
No comments:
Post a Comment