forum.venkateswarlu.net
      Register      Login
SSIS(INTERVIEW QUESTION)

2 replies to this topic

Harikrishna Sikhakolli #1
Member
108 Points
 Posted on 29 Jun 2012 03:50 PM IST what is the difference between scd type2 and scd type3
and how many history records maintain in scd type2 and scd type3 
SSIS     747 views     Reply to this topic
Durga Devi #2
Member
12 Points
 Replied on 04 Jul 2012 04:59 AM IST A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. In data warehousing, there are three commonly recognized types of SCDs. describes the types of SCDs

Type1: Stores only one version of the dimension record. When a change is made, the record is overwritten and no historic data is stored.
Type2: Stores multiple versions of the same dimension record. When the dimension record is modified, new versions are created while the old ones are retained.
Type3:Stores one version of the dimension record. This record stores the previous value and current value of selected attributes.

Overview of Defining Type 2 Slowly Changing Dimensions

A Type 2 SCD retains the full history of values. When the value of a triggering 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 date and expiration date to identify the time period for which the record was active. Oracle Warehouse Builder also enables you to set a specific non-null date value as the expiration date. The current record is the one with a null or the previously specified value in the expiration date.

All the levels in a dimension need not store historical data. Typically, only the lowest levels is versioned.

To define a Type 2 Slowly Changing Dimension (SCD), you must identify the following:

•For the level that stores historical data, specify the attributes used as the effective date and the expiration date.

•Choose the level attribute(s) that triggers a version of history to be created.

You cannot choose the surrogate identifier, effective date attribute, or expiration date attribute as the triggering attribute.

Each version of a record is assigned a different surrogate identifier. The business identifier connects the different versions in a logical sense. Typically, if there is a business need, Type 2 SCDs are used.


 
Reply to this topic
Harikrishna Sikhakolli #3
Member
108 Points
 Replied on 05 Jul 2012 05:59 AM IST Thank you. 
Reply to this topic