Type 3 Slowly Changing Dimension


In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

In our example, recall we originally have the following table:

Customer Key
Name
State
1001
Guru
Jharkhand

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

·        Customer Key

·        Name

·        Original State

·        Current State

·        Effective Date

After Guru moved from Jharkhand to Karnataka, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

Customer Key
Name
Original State
Current State
Effective Date
1001
Guru
Jharkhand
Karnataka
15-JAN-2003

Advantages:

- This does not increase the size of the table, since new information is updated.

- This allows us to keep some part of history.

Disadvantages:

- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Guru later moves to Texas on December 15, 2003, the California information will be lost.

Usage:

It’s rarely used

When to use Type 3:

Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.


Type 2 Slowly Changing Dimension

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.
In our example, recall we originally have the following table:
Customer Key Name State
1001 Guru Jharkhand

After Guru moved from Illinois to California, we add the new information as a new row into the table:
Customer Key Name StateActive
1001 Guru Jharkhand0
1005 Guru Karnataka1

Advantages:
- This allows us to accurately keep all historical information.
Disadvantages:
- The table size may increase due to historical data.
When to use Type 2:
Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Type 1 Slowly Changing Dimension

In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.
In our example, recall we originally have the following table:
Customer Key Name State
1001 Guru Jharkhand

After Guru moved from Jharkhand to Karnataka, the new information replaces the new record, and we have the following table:
Customer Key Name State
1001 Guru Karnataka

Advantages:
- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.
Disadvantages:
- All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before. 
When to use Type 1:
Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.

Slowly Changing Dimensions

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records.
There are three types of SCDs and you can use Warehouse Builder to define, deploy, and load all three types of SCDs.
The three types of SCDs are:
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. This is the default type of dimension you create. You do not need to specify any additional information to create a Type 1 SCD.
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.
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.



T-SQL LEAD LAG and SUM function based query

  Query on T-SQL window clause Below is the sales table Order_Date Name Product SubCategory ...