How to model work requests, their statuses and dimensional attributes
Page 1 of 1
How to model work requests, their statuses and dimensional attributes
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:
https://i.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
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:
https://i.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
Similar topics
» How to model jobs/work orders
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Meta-model of Kimball dimensional model
» Why we use Dimensional Model over De-normalized relational Model ?
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Meta-model of Kimball dimensional model
» Why we use Dimensional Model over De-normalized relational Model ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|