Status Dimension Brainstorm,

View previous topic View next topic Go down

Status Dimension Brainstorm,

Post  ian.coetzer on Tue Sep 28, 2010 9:47 am

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
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Status Dimension Brainstorm,

Post  ngalemmo on Tue Sep 28, 2010 12:14 pm

The first one makes more sense.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Status Dimension Brainstorm,

Post  ian.coetzer on Tue Sep 28, 2010 2:05 pm

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







avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Status Dimension Brainstorm,

Post  Jeff Smith on Tue Sep 28, 2010 2:25 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Status Dimension Brainstorm,

Post  ian.coetzer on Tue Sep 28, 2010 2:52 pm

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.
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Status Dimension Brainstorm,

Post  ngalemmo on Tue Sep 28, 2010 5:10 pm

Which is, basically, option 2.

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

Either will work.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Status Dimension Brainstorm,

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum