sql server data warehouse: General Question on Slowly Changing Dimensions -- sql server data warehouse

General Question on Slowly Changing Dimensions

Nestor
9/3/2007 8:28:33 AM

sql server data warehouse: Hi all,

Some questions on identification of Slowly Changing Dimensions. When
exactly do we determine if a dimension is classified as SCD?

For example, if I have a Customer Dimension table
Customer Key Name Country
1001 John USA

If John changed his state to Australia, we can justify this as a SCD Type
II (for historical recording) and insert the table so that we have

Customer Dimension
Customer Key Name Country
1001 John USA
1002 John Australia

and in the fact table we can have something like

Fact Table
factid Customer Key Field1
1 1001 $10
2 1002 $10

and there we have our SCD.

However, if we normalize original dimension table into

Customer Dimension
Customer Key Name
1001 John

Country Dimension
Country Key Country
2001 USA
2002 Australia

Then the fact table will be
FactID CustomerKey CountryKey Field1
1 1001 2001 $10
2 1001 2002 $10

and they both solve the same problem. The question is, when do you split
the attributes out as a separate dimensional table and when do you make it
a SCD? Are they any best practise for this?

Cheers

Re: General Question on Slowly Changing Dimensions

Mike C#
9/13/2007 10:29:43 PM

In the future will you want a report of "Customers by Country" or any other
reports for which Country is a dimension? If so, then set up a separate
Country dimension. If not, and "Country" is just an attribute of "Customer"
(like "Age" is an attribute of "Customer"), then you're fine leaving it in
the Customer dimension. Personally I'd advise creating a separate Country
dimension, however, since most businesses like their data sliced and diced
by Country, by State/Territory, and by just about anything else you can
think up.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server data warehouse group.