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