1. Analytical Application Infrastructure User Guide
  2. Data Management Framework
  3. Slowly Changing Dimensions (SCD)

Slowly Changing Dimensions (SCD)

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. There are three types of SCDs:

Type 1 SCDs - Overwriting

In a Type 1 SCD, the new data overwrites the existing data. Thus the existing data is lost as it is not stored anywhere else. No additional information is to be specified to create a Type 1 SCD.

Type 2 SCDs - Creating another dimension record

A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective time and expiration time to identify the time period between which the record was active.

Type 3 SCDs - Creating a current value field

A Type 3 SCD stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the selected attribute. When the value of any of the selected attributes changes, the current value is stored as the old value and the new value becomes the current value.

OFSAA supports Type1 and Type 2 types of SCD. You can define and manage SCD metadata using the Slowly Changing Dimension window. For information on constraints and assumptions of SCD execution on Hive Information Domain, see SCD execution on Hive Information Domain and Heterogeneous Support for SCD to RDBMS sections in OFS Analytical Applications Infrastructure Administration Guide.

The Roles mapped for Slowly Changing Dimensions module are as follows:

  • SCDACCESS
  • SCDREAD
  • SCDWRITE
  • SCDPHANTOM
  • SCDAUTH
  • SCDADV

Figure 7-36 Slowly Changing Dimension Summary window


This image displays the Slowly Changing Dimension Summary window.

The Slowly Changing Dimension Summary window displays the available SCDs with details such as Map Reference Number, Table Name, Stage Table Name, and Source Priority. You can add new SCDs, modify, view, and purge existing SCDs.

You can search for an SCD based on Stage Table Name, Dimension Table Name, and Map Reference Number.