SCD 2 implementation

View previous topic View next topic Go down

SCD 2 implementation

Post  ozisamur on Tue Nov 11, 2014 10:06 am

I have an ORDER fact table and CUSTOMER dimension.
I filled the CUSTOMER dimension with the source tables like : tcustomers, tlanguages.

TLANGUAGES:
LANGUAGE_ID | LANGUAGE_NAME
1 | English
2 | Italian
3 | Arabic

TCUSTOMERS
CUSTOMER_ID | CUSTOMER_NAME | LANGUAGE_ID
1 | John | 1
2 | Jamie | 3
3 | Susan | 1

When I create the dimension I use the following sql:
SELECT
    t1.customer_name,
    t2.language_name
FROM
    tlanguages t1, tcustomer t2 WHERE t1.language_id = t2.language_id

So far, It works. Okey.
When the language has changed I want to insert a new record. But I do not want to insert a new record when the language_name column has been modified.

For example if the customer language id changed from 1 to 2 I should add new record. But if customer language change from 'English' to 'Eng' I do not want to insert new record.

Assume that in the tlanguages table 'English' has changed to 'ENG'. If I use the above join statement and compare the data set I see that language_name has been changed and I will add new record. But I should not do that. Becasue English and ENG are the same.

What is the best practice of this problem ? Should I control the language_id ? What will you do if you were me?

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: SCD 2 implementation

Post  ngalemmo on Tue Nov 11, 2014 12:55 pm

It is not unusual to have a type 2 dimension with some type 1 (non-historical) attributes. Such attributes are updated in place across all applicable rows.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD 2 implementation

Post  ozisamur on Tue Nov 11, 2014 2:14 pm

Actually My question is something related about the column which is both scd 1 and scd 2.

If the language is changed from English to Italian I should add new row but from English to ENG I should not add new row. Just change it. But I filled the language text column via a join and English is different for ENG and Italian. How should I implement the SCD 2 ?

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: SCD 2 implementation

Post  ngalemmo on Tue Nov 11, 2014 10:47 pm

Store the ID in the customer dimension as well as the description. Treat the language description as a type 1 attribute in a type 2 table. The user doesn't need to see the ID, but it is needed to help maintain the dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD 2 implementation

Post  ozisamur on Wed Nov 12, 2014 12:18 am

Thanks, I think that is the easisest solution I have ever heard !

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: SCD 2 implementation

Post  zoom on Fri Nov 14, 2014 2:50 pm

You mentioned that you have a ORDER Fact table. That tells me your customer buying things. If your customer are buying books (English or Italian), then language is not an attribute of a customer dim. You need a separate Language dim. Use your Fact table to bring Customer and Language dim keys together which is based on the Order transaction.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: SCD 2 implementation

Post  ngalemmo on Fri Nov 14, 2014 5:08 pm

While it is certainly conceivable language could be an attribute of a product, a language dimension is a bit overkill. In the original poster's case, it is an attribute of customer, which is very common in retail models to support customer engagement (marketing, customer service, etc…). Relegating it as a dimension off a fact simply does not work in such an environment.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD 2 implementation

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