You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to design a dimensional modeling strategy for a new data warehouse. The data warehouse has a dimension table named Employees. The Employees dimension table contains information about the employees and their departments.
Employees are moved to different departments frequently. You need to preserve the historical information of the Employees table.
Which dimensional model should you use?
A.
Role-Playing Dimension
B.
Degenerated Dimension
C.
Type I Slowly Changing Dimension
D.
Type II Slowly Changing Dimension
Explanation:
Tip: "preserve the historical information" = "Type II"Slowly Changing Dimensions
For our cubes to provide meaningful information from year to year, we need to have dimensions whose members are fairly constant. If the dimensions are changing drastically month to month, our analysis across the time dimension becomes worthless. Therefore, we need mainly static dimensions. Some dimensions, however, change over time. Salespeople move from one sales territory to another. The corporate organizational chart changes as employees are promoted or resign. These are known as Slowly Changing Dimensions (SCD). SCDs come in three varieties: Type 1, Type 2, and Type 3, as defined by the Business Intelligence community. Not exciting names, but they didnt ask for my input, so its what we are stuck with!
Type 1 Slowly Changing Dimensions
When a dimension is implemented as a Type 1 SCD, we dont keep track of its history as it changes. The members of the dimension represent the way things are right now. With a Type 1 SCD, it is impossible to go back and determine the state of the dimension members at any time in the past.
Type 2 Slowly Changing Dimensions
When a dimension is implemented as a Type 2 SCD, four supplementary attributes are added to the dimension to track the history of that dimension. These four attributes are:
– SCD Original ID An alternative primary key for the dimension
– SCD Start Date The date this dimension member became active
– SCD End Date The date this dimension member ceased being active
– SCD Status The current state of this dimension member, either active or inactive
Type 3 Slowly Changing Dimensions
A Type 3 SCD is similar to a Type 2 SCD with one exception. A Type 3 SCD does not track the entire history of the dimension members. Instead, a Type 3 SCD tracks only the current state and the original state of a dimension member.A Type 3 SCD is implemented using two additional attributes:
– SCD Start Date The date the current state of this dimension member became active
– SCD Initial Value The original state of this attribute
(McGraw-Hill – Delivering Business Intelligence with Microsoft SQL Server 2008 (2009))