Messy SCD Type 0.....!

Hi all,

As of my understanding SCD Type 0 is a dimension into which we load data once and it never going to accept any inserts and updates. Then how it is named as SCD as SCD stands for Slowly Changing Dimension in which data changes over time.

Please correct me if any of my understanding in wrong.

Thanks,
Sri.

Don't get hung up on the name

You're semantically correct, but it doesn't matter. The concept is about the ability of a dimension to change, and one type of change is "None".

So in SCD type 0, data will be loaded once and it never allow any updates or inserts. Sounds interesting is there any real time business scenario for this?

Thanks,
Sri.

There are two parts to the answer.

The most common example of an SCD0 dimension is a calendar. We populate it, load it, then may never touch it again.

That said, the second part to the answer is to think of SCD attributes, not dimensions. It is common for dimensions to include attributes that never change, alongside others which may "slowly change" in the manner you have in mind. In other words, think of the expression as "dimensions having slowly changing attributes", rather than the shorthand "slowly changing dimension".

I don't remember which book, but I think one of the Kimball books uses the example of a transactions (facts) against loan accounts (dimension), where two of the attributes of the dimension are amount borrowed and interest rate. Depending on the business rules, amount borrowed may never change (SCD0), while interest rate may fluctuate throughout the life of the loan (SCD1 or SCD2).

Let's not get carried away. The numbering is to identify different dimension table structures and the patterns used to maintain them, not to describe the nature of an attribute. It is bad enough people are labeling accumulating snapshots as 'type 2 facts'. Technical terminology is supposed to be accurate and unambiguous. Let's try to keep it that way.

ngalemmo, I understand your principle, but I do not think it adequately caters for dimensions which may have attributes that conform to combinations of SCD0-3. I find this to be a relatively common scenario.

This topic is dealt with in Chapter 2 of The Data Warehouse Toolkit, under the heading "Dealing With Slowly Changing Dimension Attributes". The opening paragraph contains the sentence:

It is quite common to have attributes in the same dimension table that are handled with different change tracking techniques.

Adamson also deals with it in Chapter 8 of Star Schema, under the heading "Hybrid Slow Changes".

For example, within one dimension, we may have some attributes that never change (SCD0), some attributes that change in place (SCD1), some that trigger new rows (SCD2) and some that populate additional columns (SCD3).

Which SCD-number would you ascribe to this dimension? If we only describe it at the dimension level, we do not adequately support this case.

Ralph expanded the list to include 4, 5, 6 and maybe 7 and possibly 8 which represent various hybrids. But a type 2 table that has some type 1 attributes is still a type 2 table because of its key structure and method for updating.