Slowly Changing Dimension
Dimension source data may change over time, rather than changing on regular schedule, time base. In Datawarehouse there is need to track changes in dimension attributes in order to report historical data.
Slowly Changing Dimensions are often categorized into three types namely Type1, Type2 and Type3. The Following Example clearly explains the types of SCD’s.
Type 1: Updates existing record with modifications. (Does not Maintains History)
In the year of 2012, if the salary of the person to 19000, then the old values of the columns “Year” and “Salary” have to be updated with the new values. In this Type1, there is no way to find out the old salary of person Person Id – 1 in year 2012 since the table now contains only the new salary and year information.
Type 2: Creating an additional record (Does Maintains History)
In this Type 2, the old values will not be replaced but new values are stored in to another row of table. So at any point of time, the difference the old values and new values can be retrieved from database and early be compared. This is very useful for Real Time for reporting purposes.
Type 3: Creating New Fields. Keep old and new values in the existing row (Requires a design Change)
In this Type 3, the latest update to changed values can be seen. Example mentioned below how to add new columns and maintains old columns of that column changes as shown below.
The problem with the Type3 used in Real Time, if the salary of person1 changes, then added new columns to the same row that type u can change structure of cube every time.
Dimension source data may change over time, rather than changing on regular schedule, time base. In Datawarehouse there is need to track changes in dimension attributes in order to report historical data.
Slowly Changing Dimensions are often categorized into three types namely Type1, Type2 and Type3. The Following Example clearly explains the types of SCD’s.
Type 1: Updates existing record with modifications. (Does not Maintains History)
In the year of 2012, if the salary of the person to 19000, then the old values of the columns “Year” and “Salary” have to be updated with the new values. In this Type1, there is no way to find out the old salary of person Person Id – 1 in year 2012 since the table now contains only the new salary and year information.
Type 2: Creating an additional record (Does Maintains History)
In this Type 2, the old values will not be replaced but new values are stored in to another row of table. So at any point of time, the difference the old values and new values can be retrieved from database and early be compared. This is very useful for Real Time for reporting purposes.
Type 3: Creating New Fields. Keep old and new values in the existing row (Requires a design Change)
In this Type 3, the latest update to changed values can be seen. Example mentioned below how to add new columns and maintains old columns of that column changes as shown below.
The problem with the Type3 used in Real Time, if the salary of person1 changes, then added new columns to the same row that type u can change structure of cube every time.
nice sathish garu very understable exampe i need still more example with out usig scd i need example using with look up but how it is possible to main tain HISTORICAL data in look up
ReplyDeleteU DID NOT UNDERSTOOD MY QUES I WANT UP OPERATION USING LOOK UP
ReplyDelete