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

SCD, is this Table/Column dependent

 One of the most common doubts that occur in the mind of a DW designer is that are SCD’s done on a table or a column? Rather than thinking in terms of tables/columns, we would think in terms of dimensions & attributes. Now consider a dimension that contains department & location of the department. The business requires tracking of new departments. But they do not want to track the history of older departments. Also the business requires tracking the change in the location of a department. The business requires maintaining historical location too. In this case 2 SCD implementations are required in the same dimension. One for tracking departmental change. The other for tracking the change in location of the department. The departmental change requires SCD-1 as explained earlier. The location tracking requires SCD-2. Here the SCD implementation would be column based or rather attribute based.
       But for the ease of implementation, this department dimension can be split into department & location dimensions. Now the department dimension would be SCD-1 implementation. Location dimension would be SCD-2. Hence, whenever attributes in the same dimension require different tracking ways then a general practice of splitting the dimension would cater to the needs. Hence, in DW SCD would become synonymous with column as well as table. I would say, do not think in terms of tables. Think in terms of dimensions that qualify the facts. If in the same dimension, 2 different attributes require different tracking patterns, then they must not be there in the same dimension. This is the general convention. This makes sense, since it would make the implementation easier & the dimension would not grow unexpectedly.
            But again consider the same case wherein the facts (employee expense) do not depend on the department location. In such a case we might go in for snow flaking. But again this would be a very remote condition & hence would not discussed further. But the important point to note is that if a dimension has 2 differently changing attributes, then the dimension has to be remodeled as 2 different dimensions.

Enter supporting content here

Attitude,Not Aptitude, Determines Altitude