Messy SCD Type 0.....!

View previous topic View next topic Go down

Messy SCD Type 0.....!

Post  sridharbabu on Tue Oct 13, 2015 8:06 am

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.

sridharbabu

Posts : 3
Join date : 2015-10-13

View user profile

Back to top Go down

Re: Messy SCD Type 0.....!

Post  ron.dunn on Tue Oct 13, 2015 4:44 pm

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".

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Messy SCD Type 0.....!

Post  sridharbabu on Thu Oct 15, 2015 3:57 am

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.

sridharbabu

Posts : 3
Join date : 2015-10-13

View user profile

Back to top Go down

Re: Messy SCD Type 0.....!

Post  ron.dunn on Thu Oct 15, 2015 6:15 am

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).

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Messy SCD Type 0.....!

Post  ngalemmo on Thu Oct 15, 2015 8:34 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Messy SCD Type 0.....!

Post  ron.dunn on Thu Oct 15, 2015 9:09 am

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.

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Messy SCD Type 0.....!

Post  ngalemmo on Thu Oct 15, 2015 9:56 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Messy SCD Type 0.....!

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum