OWB - How to implement a type 2 slowly changing dimension with a hash function ?

Card Puncher Data Processing

About

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

Steps

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)

Example :

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





Discover More
Card Puncher Data Processing
Oracle Warehouse Builder

Owb Documentation - Understanding Data Quality Management White...
Event Centric Thinking
Stream - How to capture change of state - Change Data Capture (CDC)

This page is how to capture the changes of state in order to create a stream. The stream processing will then perform an incremental computation Capturing data changes is far from a trivial task, and...



Share this page:
Follow us:
Task Runner