Slowly changing dimension is the ability to track change on a record from a data set.
How can we implement the type 2 to track the change and implement an history view.
The type 2 :
- add a new record for update data
- and flag the old record as the old data.
To achieve this goal, an hash function (as the function Oracle Database - ORA_HASH Function) is generally used. It permit to create a virtual primary key based on the value of each columns. If the content of a column change the hash value change and you are then able to target this changed record by using a SQL - Outer Join
Add a hash column to the data set
With the function Oracle Database - ORA_HASH Function, add a formula column in the actual data set (the target) and the new data set (the source) which use all the trigger columns. (In this example, all)
ORA_HASH(INGRP1.DESCRIPTION || INGRP1.TYPE_ID || INGRP1.GROUP_ID || INGRP1.HIERARCHY_ID || INGRP1.EAN_CODE || INGRP1.DIVISION_ID || INGRP1.BASE_UOM)
How to find the new/updated record ?
SOURCE.PK_HASH_VALUE = TARGET.PK_HASH (+) AND SOURCE.PK_HASH_VALUE <> TARGET.PK_HASH_VALUE
How to find the deleted record ?
TARGET.PK_HASH = SOURCE.PK_HASH_VALUE (+) AND SOURCE.PK_HASH_VALUE <> TARGET.PK_HASH_VALUE