SCD 2 implementation

[ozisamur]

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?

[ngalemmo]

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.

[ozisamur]

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 ?

[ngalemmo]

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.

[ozisamur]

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

[zoom]

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.

[ngalemmo]

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.

[Sponsored content]