Defining SCD Type at the record level

itcconsulting · Post by **itcconsulting** » Thu Jun 02, 2005 7:42 am

I'm in the process of finalizing a dimension loading process following Kimball's methodology. I have a question that may change my process. What's the affect of treating the whole record as Type 1 or Type 2 instead of individual columns on a record?

diamondabhi · Post by **diamondabhi** » Thu Jun 02, 2005 7:47 am

Welcome itcconsulting,
I think thats how it should be, can you be more specific with ur problem.

Thanks,
Abhi

itcconsulting · Post by **itcconsulting** » Thu Jun 02, 2005 8:00 am

If my dimension table contains 4 non key attributes and one (or two) of those attributes require Type 2 SCD response, could I just treat any changed row as Type 2?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 02, 2005 8:26 am

Yes. W.r.t. that one col.

itcconsulting · Post by **itcconsulting** » Thu Jun 02, 2005 10:22 am

What's the downside of treating any change as a Type 2 regardless of the column?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 02, 2005 10:26 am

You need to include complex conditions and more development time.

You landup with performance head-ache and development back-ache.

Again, everything depends upon your business requirement, analysis and design.

itcconsulting · Post by **itcconsulting** » Thu Jun 02, 2005 3:18 pm

In speaking with the business community, I found that there is a business requirement to define the SCD Type at the field level. Could someone provided some advice on the DataStage logic needed make the field level SCD decision within my server job?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 02, 2005 3:21 pm

Search this forum for more details.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 02, 2005 4:22 pm

The DataStage logic is exactly the same as the "logical logic". If a field is a candidate for recording change, then a new record must be created whenever there is a change in that field. Therefore, you must determine whether a change has occurred. This involves being able to see (pre-load) what's already current in the target table, and to test for change in every candidate field.