Good Examples of Dimensions?

[davej]

I am just getting started with this material and I'm looking for some good trustworthy examples.

For example if you have a mail-order company with one location and you have a product dimension and a customer dimension and a date dimension and the fact table contains sales amounts -- what are a few other dimensions that would be logical and correct?

If someone is perhaps interested in analyzing the sales in terms of the gender of the customer or the customer location would you do anything differently in the star schema other than simply having gender and city and state included in the customer dimension table? I am totally confused by examples I have seen where customer characteristics are broken into different dimensions. Why do that? Thanks.

[BoxesAndLines]

One reason is performance. If your company is large, say your local telco large, then you have millions of customers. Joining a multi-million row dimension table with a multi-million or billion row fact table can tax the best of databases. Another reason is drill across. If there are some customer demographics with hierarchical aggregations that I'd like to use to drill across multiple fact tables, I can do it easily by breaking those attributes out of the customer dimension.

[davej]

So you would duplicate some of the information in the customer table to create these other tables? A customer state/province table? A customer zipcode table? The customer gender becomes a degenerate dimension of the fact table?

[ngalemmo]

Generally use degenerate dimensions sparingly, specially attributes that someone may want a description for.  Its usually used for stuff like document numbers. Junk dimensions are a better alternative, for example, clustering demographic attributes (age, sex, etc…) into a single small dimension.

[Sponsored content]