How to model work requests, their statuses and dimensional attributes

View previous topic View next topic Go down

How to model work requests, their statuses and dimensional attributes

Post  Etienne123 on Sun Jul 17, 2011 10:21 pm

Hi,

Our company generates a large volume of work requests (WRs) on a daily basis. These WRs have some quantifiable elements such as monetary values, dimensional attributes (such as owners) and also undergo status changes throughout the lifecycle of each WR.

I am however not sure how to model these components of WRs.

I am thinking of:

a fact table (one record written to the fact table on creation) with various important dates and values
a transactional fact table that will host the status changes for each WR
a dimension for the dimensional attributes with SCD applied

See the example:

http://i48.servimg.com/u/f48/16/68/36/76/wrs_bm11.jpg

My problems with this model are:
It is safe to assume the status transactional fact table will be exponetially larger than the normal fact table as there are multiple status changes for each work order, but the dimension will be as large (or larger due to SCD2) than the fact table. This does not sit quite comfortably with me. But I cannot see how an alternative of hosting the dimensional attributes in the fact table (degenerate dimension) will help as I will not be able to handle SCD2's (which is generally not applied to facts) if the dimensional attributes are degenerate within the fact table.
What about changes to dates in the fact tables? It is not proposed to apply SCD2 type changes to a fact table so how are changes to dates handled if they are part of the fact table?

I would like to hear some opinions on this please :-)

E

Etienne123

Posts : 1
Join date : 2011-07-17

View user profile

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