Junk Dimension

[dbadwh]

In our data model, for customer dimension, we have customer related attributes like name,age, dob, etc. separately and the other transaction related dimension attributes in a separate junk dimension? Is it a good practice or should both the values should be in a single dimension?

[ngalemmo]

If the attributes are not related to the customer, then a junk dimension is one way to deal with them. If they are related to customer, sometimes placing them in a junk dimension is appropriate. Junk dimensions can serve as a 'poor man's' type 2, providing point in time attribute values without having to implement a type 2 for the primary dimension.

[BoxesAndLines]

If you already have an existing customer dimension then it makes little sense to create a junk dimension of customer attributes unless you are trying to solve another problem.

[umutiscan]

Customer is one of the biggest dimensions in a warehouse, and in my opinion using seperate dimensions and facts is always a beneficial approach. If you try to hold all the information about customer in a single dimension table, it becomes a rapidly changing dimension with millions of rows.

You say age is one of the attributes of your customer dimension. I think you don't have to store age column because you have "date of birth", and age can be calculated easily using a simple database function. My company has 30 millions of customers and storing age column in customer dimension means 30 millions of new rows each year. Using some age labels like "young customers" may be more efficient, because business is generally uses age column only for determination of these kind of labels.

This is a useful article if you have a problem with big dimension tables like customer etc. I benefited from this article while modeling the customer subject area.

http://www.kimballgroup.com/html/articles_search/articles1999/9908bIE.html?TrkID=IE199908_1

[hkandpal]

Hi,

could you please tell us more about the attributes, what type of attributes you are trying to store in a Junk dimension, how many distinct values are there.
Are you going to do any reporting on those attributes?


thanks

[Jeff Smith]

It depends on the number of customers.

I tend to keep customer dimensions fairly narrow - customer specific information that that don't change much. Customer Dimensions can be very, very long. I don't like to join to it unless customer level information is needed. I prefer to keep other attribute information in a seperate dimension if possible. I am more likely to need the customer's Zip Code or State information rather than the Customer, or even the market segmentation name. If I can put market segment on a Customer Attribute table (as long as the junk dimension doesn't become obscenely large), then a query that rolls up information to the Market Segment will run much faster than if I have to go through the Customer Dimension which may have tens of millions of rows.

[Sponsored content]