Dimension - Fact Modeling

View previous topic View next topic Go down

Dimension - Fact Modeling

Post  coffee_maker on Tue Jun 11, 2013 8:31 am

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



coffee_maker

Posts : 2
Join date : 2013-06-11

View user profile

Back to top Go down

RE: Dimension - Fact Modeling

Post  rathjeevesh on Sun Jun 16, 2013 5:09 pm

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

rathjeevesh

Posts : 15
Join date : 2013-02-16

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