Interested to Buy Any Domain ? << Click Here >> for more details...
what is difference between scd2 and scd3
- 3 Answers
- 9589 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / rashmirekha panigrahy
In SCD Type2 we keep the entire historical data in the dimension table whereas in SCD Type3 we keep partial historical data in the dimension table.
For Ex : Suppose we are having a Customer Table. Acustomer 1st staying at Los Angels. Then he is shifted to DEllas. again he is shifted to Texas.
In case of Type 2 if we are using date method it will show the location and how many days he stayed on that particular location.
Sk_C Cust_ID Cust_NM Location Start_Dt End_DT
101 200345 Williams Los Angels 01-Mar-2011 23-Jun-2012
678 200345 Williams Dellas 24-Jun-2012 12-Nov-2014
1562 200345 Williams Texas 13-Nov-2014 NULL
But In case of Type 3 we will keep only current location and previous location. Not more than that.
Sk_C Cust_ID Cust_NM Cur_Location Pre_Location
101 200345 Williams Los Angels NULL
He shifted to Dellas.
Sk_C Cust_ID Cust_NM Cur_Location Pre_Location
101 200345 Williams Dellas Los Angels
Again he shifted to Texas.
Sk_C Cust_ID Cust_NM Cur_Location Pre_Location
101 200345 Williams Texas Dellas
| Is This Answer Correct ? | 3 Yes | 0 No |
According to my findings, the key difference between SCD type 2 and type 3 is in their ability to maintain historical records. SCD type 2 is capable of storing multiple versions of a record, while SCD type 3 may only retain the current version and one previous version. However, there is some ambiguity regarding how many records SCD type 3 can hold.
To clarify, a good rule of thumb is to use SCD type 3 when the number of historic records that need to be retained is known in advance for e.g. If you need to store 5 records per user_id then the data structure is going to remain consistent over time i.e. 5 records hence implementing type 3 SCD will be a better choice. Conversely, if the number of records per user_id is uncertain, SCD type 2 may be a better choice.
| Is This Answer Correct ? | 0 Yes | 0 No |
What are Non-additive facts?
Hi Guys, I have been to L& T last week ..Please find the questions mentioned below 1)Slowly changing dimension types 2)DML statement 3)DDL statement 4)write a syntax to create database 5) differences between count(*) and count(column) 6) differences between union and union all 7) what is decode 8) what is NVL 9) Arrange the values in ascending order 10,5,20,15,null 10)Primary and surrogate key 11)what is group by clause does? 12)what is difference between truncate and delete 13)what is difference between OLAP and OLTP ? 14)Architecture of Datawarehouse 15)how do you test the data validation from source as (flat file and xml) to staging tables. 16)what is shedule and time bound the mentioned above question asked in two rounds.all the best
Are facts same as metrics and dimensions same as measures? If not , please tell the difference?
What is called dimensional modelling?
What is an er diagram?
Explain the main difference between inmon and kimball philosophies of data warehousing?
Explain if a flat file cotains 1000 records how can I get first and last records only?
What is the difference between Oracle Express and Oracle Discoverer?
What is incremintal loading?
What is data analysis?
Explain what are snapshots? What are materialized views & where do we use them? What is a materialized view log?
What is normalization, first normal form, second normal form, third normal form?
Teradata 
