Design Tip No 25 - Parent > Child > Child (Maybe)

View previous topic View next topic Go down

Design Tip No 25 - Parent > Child > Child (Maybe)

Post  VMolloy on Wed Apr 01, 2009 4:26 am

I am trying to model a parent child relationship as a single fact table. I have a relationship that goes Sample > Test > Result where a sample can have zero or more tests and a test can have zero or more results. Each entity has a measure called turnaround time. According to the design tip #25 I have modelled the parent-child relationship to the most atomic child level (Result). So my fact table structure goes something like this (excluding a bunch of other dimension keys)...

SampleIDSampleTurnaroundTestIDTestTurnaroundResultIDResultTurnaround

This is fine for the most part. The problem arises where the Sample has no Tests or the Test has no Result. I still might want to see a turnaround time for a sample even if it never had any tests or results associated with it.

Any tips on best practice way to model this. My current thinking is that, if for example, I have a sample with a test but no result, I could do the following...

SampleIDSampleTurnaroundTestIDTestTurnaroundResultIDResultTurnaround
124112nullnull
124210nullnull

It is possible though that the test could later have results and updating the fact table to reflect this (possibly have to delete the original fact and insert one or more new facts in its place) would really complicate the ETL process.

Any thoughts much appreciated.

VMolloy

Posts : 2
Join date : 2009-03-31

View user profile

Back to top Go down

Re: Design Tip No 25 - Parent > Child > Child (Maybe)

Post  BoxesAndLines on Wed Apr 01, 2009 9:18 am

Seems to me your mixing grains. The only metric that is additive here is ResultTurnaround. Do a search on Accumulating Snapshot Fact tables to get some ideas on modeling a process over time.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Design Tip No 25 - Parent > Child > Child (Maybe)

Post  VMolloy on Wed Apr 01, 2009 9:43 am

The rationale I used when bringing all SAMPLE and TEST and RESULT information down to the RESULT level was as follows.

METHOD is a Dimension related to TEST. PRODUCT is a dimension related to SAMPLE. If I have separate SAMPLE and TEST facts, I cannot answer the query - "Show me the number of times each method was performed on samples of each product".

I have tried various models including a shared SAMPLE dimension (with SAMPLE and TEST as separate fact tables). But this wont work.

As regards the turnaround time measures, they are independent across sample, test and result i.e. the adding up the turnarounds for all tests in a sample doesnt give you the turnaround for the sample - tests could be done in parallel.

Any ideas ?

VMolloy

Posts : 2
Join date : 2009-03-31

View user profile

Back to top Go down

Re: Design Tip No 25 - Parent > Child > Child (Maybe)

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