Make your own free website on Tripod.com
Venky's World
SCD
Home
About Me
Favorite Links
Contact Me
Why I Started This?
Welcome to the DW World
OLTP & OLAP
How to Start Isolating data
Grain is the Grail
Schemas
Normalization
Facts & Dimensions
Oh Yeah, Its ETL Time
SCD
SCD, is this Table/Column dependent
Handling Error Loads

SCD

 Fact tables are allowed to grow. Any new fact would be loaded into the fact table. But this must not be the case for the dimension tables. Consider, employee expenses with respect to the dimension department. Employee expenses occur every day. Each & every day the dimension table must not be populated with the same department just because a new employee expense of the same department has occurred. Hence a concept called SCD has to be used to track the change in dimensions. 
 
           SCD is an acronym for Slowly changing dimensions. How do we track change in dimensions? Do we allow the dimensions to grow? These questions are to be answered by the business depending on its importance to the business. In general there are 3 types of SCD’s that track the growth of dimensions. These 3 would almost cover most of the business requirements. 
 
No SCD: This means that the dimension would not change. It would be one time load. For example, Time dimension. 
 
SCD 1: This means that whenever some entity of an already loaded row of a dimension changes, the new one updates the older data. When new entities come they are inserted into the dimension table. In a nutshell, what this means is that we are losing history for this dimension. For example, consider customer dimension. This dimension would be having the address of the customer as one of the attributes of the customer. When the customer changes his address then the new address updates the older one. So whatever hotel booking the customer from the older address booked would now get reflected as the customer from the new address. When a new customer comes in this would get inserted into the dimension.
 
SCD 2: In the previous example we found that we are losing historical information. But sometimes it might be required to track historical data too. IN such cases SCD 2 is used .For example consider an employee who is working in department A. He is now transferred to Department B. Now if we use SCD 1 then all the older expense data that the employee had would now get reflected in as data for the department B. This is not a desirable scenario. In such cases SCD 2 is used. Here, whenever such a thing happens the older record is also kept. Also a new record recording the transfer would also get added.
            There might be requirements wherein none of the above dimension change tracking mechanism will suit. For example, consider a situation wherein it is required to compare side by side, the employee expenses of the same employee in his present department & his previous department. This requirement is not satisfied by either of the above-mentioned SCD’s. Here a new SCD called SCD-3 will be used. There might be requirements where in both SCD-2 and SCD-3 have to be combined. This is done by SCD-6. But these are all very rare & the most commonly used are SCD-1, SCD-2 & No SCD.
            The next question that would arise how would we identify whether a customer is new or not. How would I identify the departments? These are identified by what are called as business keys.

Enter supporting content here

Attitude,Not Aptitude, Determines Altitude