Type 6 / Hybrid
The Type 6 method combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6). One possible explanation of the origin of the term was that it was coined by Ralph Kimball during a conversation with Stephen Pace from Kalido. Ralph Kimball calls this method "Unpredictable Changes with Single-Version Overlay" in The Data Warehouse Toolkit.
The Supplier table starts out with one record for our example supplier:
| Supplier_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA | CA | 01-Jan-2000 | 31-Dec-9999 | Y |
The Current_State and the Historical_State are the same. The Current_Flag attribute indicates that this is the current or most recent record for this supplier.
When Acme Supply Company moves to Illinois, we add a new record, as in Type 2 processing:
| Supplier_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | IL | CA | 01-Jan-2000 | 21-Dec-2004 | N |
| 124 | ABC | Acme Supply Co | IL | IL | 22-Dec-2004 | 31-Dec-9999 | Y |
We overwrite the Current_State information in the first record (Supplier_Key = 123) with the new information, as in Type 1 processing. We create a new record to track the changes, as in Type 2 processing. And we store the history in a second State column (Historical_State), which incorporates Type 3 processing.
For example if the supplier were to relocate again, we would add another record to the Supplier dimension, and we would overwrite the contents of the Current_State column:
| Supplier_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | NY | CA | 01-Jan-2000 | 21-Dec-2004 | N |
| 124 | ABC | Acme Supply Co | NY | IL | 22-Dec-2004 | 03-Feb-2008 | N |
| 125 | ABC | Acme Supply Co | NY | NY | 04-Feb-2008 | 31-Dec-9999 | Y |
Note that, for the current record (Current_Flag = 'Y'), the Current_State and the Historical_State are always the same.
Read more about this topic: Slowly Changing Dimension
Famous quotes containing the word type:
“We need a type of theatre which not only releases the feelings, insights and impulses possible within the particular historical field of human relations in which the action takes place, but employs and encourages those thoughts and feelings which help transform the field itself.”
—Bertolt Brecht (1898–1956)