Junk Dimensions

[shabut]

I have a situation where we have to track number of loan appliations submitted and matched with lenders by almost every single attribute (around 45) on the application form. Some of the attributes are like Loan_Amount Frist_Mortgage, Second_Mortgage, Frist_Interest_Rate, Second_Intesrest_Rate, Property_Type, Loan_Purpose etc. To make the grupings smaller and easier we will be tracking most of these nubmers in ranges, for example Loan_Amount could be $50,000 to $100,000, $100,001 to $150,000 and so on.

We already have a factless fact table for each application submitted by a customer however the new requirement is to be able to analyze these applications by one or all of the 45 or so attributes. It does not look very feasible to add 45 different dimensions for each attribute.

What would be the best way to handle this situation. One solution could be to create couple of junk dimensions with closely related attributes with every possible combination of values and since we will be using ranges for most of the attributes so the dimension will not be very big (couple hundred records the most).

Any other suggestions are welcome?

Thanks.

 

[johnherman]

I recommend you go back to step one. You have a collection of attributes. Do Relational Analysis. In a nutshell, this entails grouping like attributes under entities, identifying relationships between entities and attributes, and identifying the domain of values for each attribute.

Then do Dimensional Analysis, identifying dimensions from your entities.

Then, if you have several dimensions with only a single attribute, consider a combined (or junk) dimension.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[shabut]

Thanks for the quick reply. I've already done the dimensional analysis and have my dimensions figured out however there's a large list of attributes which don't really relate to each other or have any kind of hierarchy and creating a separate dimension for each single attrbute is bit of an overkill so I think a junk dimension or may be more than one junk dimensions (depending upon number of rows of the cross product) should be the way to go.

 

[johnherman]

It appears that a junk dimension or two (or three) is the right course. Have you read Kimball's philosophy on junk dimensions? In a nutshell:

A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, we remove the flags from the fact table while placing them into a useful dimensional framework.

Hope that helps.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright