In a Datawarehouse application you have a table that holds customer data and som

Question

In a Datawarehouse application you have a table that holds customer data and some of the data are: Assume that the customer Named Yash Randall is moving from Madison, Wl to New York, NY. Your company assigns you to design the implementation of the slowly changing dimension Customer_Dim. Using the above instance as an example, answer the following questions: a) How would you implement the type 1 SCD? What is the benefit of such an approach and what are the drawbacks. b) How would you implement the type 2 SCD? What is the benefit of such an approach and what are the drawbacks. c) How would you implement the type 3 SCD? What is the benefit of such an approach and what are the drawbacks. Notice: In each case use the example to showcase the change in the records and also add any necessary attributes.

Explanation / Answer

5(a) Type1 SCD:

  

In this method new data/record overwrites the old records/data in dimension table

   Overwrites/changes the old record/data in dimensions table with new record

   Previous historical data is not available for changing records.

Benefits’: simple and easy to handle the SCD.

                 No need to maintain the old or previous information.(historical data in dimention table)

                 .

Drawbacks: all previous data/ history will be lost.

                    Not possible to maintain previous data or historical data

                   It is difficult to know that customer yesh Randal live in Newyork,NY before.

                   Previous information not availble

Customer_key

Customer_id

Customer_F_Name

Customer_L_Name

Customer_city

Customer_state

1

00123

korah

Blanca

Columbus

OH

2

15678

yash

Randal

NewYork

NY

3

79674

Johnathon

Millerton

NewYork

NY

4

90627

Mikayla

Damion

Washington

DC

5(b) Type2 SCD:

In this method new record/new information is added to the dimension table to represent the new data.

in this method both original data/record and new updated record will be available in table.

New record has its own primary key or adds sequence numbers (surrogate key)

Benefits’: to maintain the previous data or

           Accurately maintain the historical data.

           Easy to track the previous modifications

                

Drawbacks: Table sizes grow/increase fast.

            if the number of rows will increase the need more storage space, it leads performance is    

                 the problem.

           it is very difficulty of Extract Transform and load (ETL) process.

Customer_key

Customer_id

Customer_F_Name

Customer_L_Name

Customer_city

Customer_state

1

00123

korah

Blanca

Columbus

OH

2

15678

yash

Randal

Madison

WI (old record)

3

79674

Johnathon

Millerton

NewYork

NY

4

90627

Mikayla

Damion

Washington

DC

5

15679

yash

Randal

NewYork

NY(New record)

5(c) Type3 SCD:

          in this method add columns indicate attribute of interest.

          to add new columns to maintain changed information.and the date effective.

          it maintain or track the previous changes on historical data.

the columns are:

                   Customer_key,

                   Customer_id,

                   Customer_F_Name,

                   Customer_L_Name,

                   Original Customer_city,

                   Original Customer_state,

                   Present_Customer_city,

                  Present Customer_state,

                   Effective_date

3 new columns are added.

Benefits’:

            does not increase the size of table.

            new data easy to update.

            allows store some part of history

Drawbacks:

it does not maintain all previous history where an attribute value is changed more than once.

example:later yashRandal moves NewYork,NY to Washington,DC on 5th dec 2017 NewYork,NY information lost

After Yash Randal from Madison, WI to NewYork,NY   the original information gets updated, and we have the following table (assuming the effective date of change is April 05, 2017):

Customer_key

Customer_id

Customer_F_Name

Customer_L_Name

Original Customer_city

Original Customer_state

Present_Customer_city

Present Customer_state

Effective_date

2

15678

yash

Randal

Madison

WI (old record)

NewYork

NY(New record)

05-Apr-2017

Customer_key

Customer_id

Customer_F_Name

Customer_L_Name

Customer_city

Customer_state

1

00123

korah

Blanca

Columbus

OH

2

15678

yash

Randal

NewYork

NY

3

79674

Johnathon

Millerton

NewYork

NY

4

90627

Mikayla

Damion

Washington

DC

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.