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.
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.