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:
“It used to be said that you had to know what was happening in America because it gave us a glimpse of our future. Today, the rest of America, and after that Europe, had better heed what happens in California, for it already reveals the type of civilisation that is in store for all of us.”
—Alistair Cooke (b. 1908)