Status Dimension Brainstorm,

Hi,

Which design makes more sense, and why?

1)

FactData
DataKey
Order_StatusKey
Trace_StatusKey
Admin_StatusKey
InstallmentAmount
etc.

DimStatus
StatusKey
StatusType
StatusName
StatusDescription

2)

FactData
DataKey
StatusKey
InstallmentAmount
etc.

DimStatus
StatusKey
OrderStatusName
TraceStatusName
AdminStatusName

The first one makes more sense.

Hi,

Thank you for the reply.
Yes the first one also makes more sense.

I'm currently working on an idea for this and I have to explain why the first option makes more sense.
It does but I cannot give reasons other than:

1) Simplifies the dimensional model.
2) Sticks to conventions - one dimension can be references multiple times in one fact table - like DimDate etc.

However the situation is:

We have 4 status categories:
The business is built around these 4 status categories / streams.

Example:

Let's Say these are the 2 status categories / streams:

Order
Track

And they have the following statusses:

Order
* Start
* Running
* Complete

Track
* On Hold
* Tracking
* Found
* Closed

So they want to have a DimStatus with the following example records:

StatusKey OrderStatus TrackStatus
1 Start On Hold
2 Running On Hold
3 Running Tracking

So a 'fact' can be related to say StatusKey = 2
Which means that the OrderStatus 'type' = "Running"
and the TrackStatus 'type' = "On Hold"

I do not like this idea - think it overcomplicates the data warehouse.

What is your ideas?

I cannot get my head around this scenario

The dimensions seem a little thin. If the 3 dimensions have only 4 columns (Key, Type, Name, Description) and no hierarchy then consider combining the 3 dimensions into 1 junk dimension. But if there are higher roll up levels or if the 3 dimensions will be used in other fact tables, then by all means keep them seperate.

Hi,

Thanks, I notice that 'Junk Dimensions' have been mentioned in a reply to one of my previous posts as well.
I finally took the time to read the Article about Junk Dimensions:
http://www.rkimball.com/html/designtipsPDF/DesignTips2003/KimballDT48DeClutter.pdf

It makes sense to me, I just previously ignore this article since I did not like the name "Junk" would have preferred something like "Consolidated Dimension" or "Combination Dimension" but I know one can give it any physical name eventually.

Thanks for the tip, indeed this is a case for a 'Junk' dimension.

Which is, basically, option 2.

Option 1 is clearer, while option 2 gives you one less FK in the fact table.

Either will work.