Dimension - Fact Modeling
2 posters
Page 1 of 1
Dimension - Fact Modeling
Hi Folks,
I am new to modeling to please pardon me if this sounds a silly question. I have a bunch of tables in my logical model and I am trying to create a Star Schema using it. I am sharing one of the issues (and really there are plenty like this). This is life science model.
There is a table called Study that captures the different attributes of a Study (study name, status etc).
Now, for a Study, number of attributes in separate logical model associated to Study (and possibly some facts) are getting tracked. For ex:, for a Study there may be multiple Inquiries -
Inquiry:
STUDY_ID
INQUIRY_ID (INTEGER VALUE)
INQUIRY_TYPE
INQUIRY_START_DATE
INQUIRY_END_DATE
Also, for a Study, there could be multiple Quality reviews conducted -
Quality Review:
STUDY_ID
QUALITY_REVIEW_CATEGORY
QUALITY_REVIEW_ID(INTEGER VALUE)
QUALITY_REVIEW_START_DATE
QUALITY_REVIEW_END_DATE
One Study could have multiple inquiries and quality reviews.
How do I model such a process via Star Schema? Do I need to create two separate fact tables (as these are two separate processes?)
My business questions:
1) I would want to get number of inquiries or quality reviews done for Studies in a time frame, (Say between 10 Aug 2012 and 20 Sep 2013). I may need to use one of the dates in my Inquiry and Quality Review to connect with time dimension.
2) I should be able to to get number of quality reviews in a time frame where review ended after 10 days of its start (so, QUALITY_REVIEW_END_DATE - QUALITY_REVIEW_START_DATE) < 11).
Do I need to create fact-less fact tables?
Any suggestion would help me!
Thanks
I am new to modeling to please pardon me if this sounds a silly question. I have a bunch of tables in my logical model and I am trying to create a Star Schema using it. I am sharing one of the issues (and really there are plenty like this). This is life science model.
There is a table called Study that captures the different attributes of a Study (study name, status etc).
Now, for a Study, number of attributes in separate logical model associated to Study (and possibly some facts) are getting tracked. For ex:, for a Study there may be multiple Inquiries -
Inquiry:
STUDY_ID
INQUIRY_ID (INTEGER VALUE)
INQUIRY_TYPE
INQUIRY_START_DATE
INQUIRY_END_DATE
Also, for a Study, there could be multiple Quality reviews conducted -
Quality Review:
STUDY_ID
QUALITY_REVIEW_CATEGORY
QUALITY_REVIEW_ID(INTEGER VALUE)
QUALITY_REVIEW_START_DATE
QUALITY_REVIEW_END_DATE
One Study could have multiple inquiries and quality reviews.
How do I model such a process via Star Schema? Do I need to create two separate fact tables (as these are two separate processes?)
My business questions:
1) I would want to get number of inquiries or quality reviews done for Studies in a time frame, (Say between 10 Aug 2012 and 20 Sep 2013). I may need to use one of the dates in my Inquiry and Quality Review to connect with time dimension.
2) I should be able to to get number of quality reviews in a time frame where review ended after 10 days of its start (so, QUALITY_REVIEW_END_DATE - QUALITY_REVIEW_START_DATE) < 11).
Do I need to create fact-less fact tables?
Any suggestion would help me!
Thanks
coffee_maker- Posts : 2
Join date : 2013-06-11
RE: Dimension - Fact Modeling
If there is no relationship/dependency between inquiry and quality for a study then you need to create two different facts. 1 for Inquiry and 1 for Quality and store respective dim keys and measures in them. When you need to see quality & inquiry together you need to aggregate the information to study level and merge the information together. Study, time dimension will be you conformed dimensions.
-JR
-JR
rathjeevesh- Posts : 15
Join date : 2013-02-16
Similar topics
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Modeling - Dimension/Fact - Need advice please
» Modeling as Factless Fact or Dimension
» Dimensional Modeling - What Goes Into Fact/Dimension?
» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer
» Modeling - Dimension/Fact - Need advice please
» Modeling as Factless Fact or Dimension
» Dimensional Modeling - What Goes Into Fact/Dimension?
» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|