Identify a slowly changing dimension other than Type 1 in th

Identify a slowly changing dimension (other than Type 1) in the flight schema and discuss a strategy (specify the option) to accurately track this dimension, using an example. Draw a diagram to show how the changes are tracked.

Solution

In this type, for each change in a dimension attribute, an additional timestamped record is created and
inserted. The previous record is also maintained in the table, but with an expiry date. The expiry date
will show which is the latest record. Unlimited history is preserved for each insert.
Employee table: (before promotion event)

EMPLOYEE KEY   EMPLOYEE ID EMPLOYEE NAME     DATE OF BIRTH      DESIGNATION       EXPIRY DATE
   123                  42312      Anuradha P                      19900321      ASE T              (null)

If the employee gets a promotion
EMPLOYEE KEY EMPLOYEE ID   EMPLOYEE NAME DATE OF BIRTH   DESIGNATION   EXPIRY DATE
123 423123 Anuradha P           19900321           ASE T              20130701
124 423123                   Anuradha P           19900321           ASE (null)

The null Expiry Date in row two indicates that this record is the current version. In some cases, a
standardized high date (e.g. 99991231) may be used as an expiry date, so that nullvalue
substitution is not required when querying.