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

[VMolloy]

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)...

SampleID

SampleTurnaround

TestID

TestTurnaround

ResultID

ResultTurnaround

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...

SampleID	SampleTurnaround	TestID	TestTurnaround	ResultID	ResultTurnaround
1	24	1	12	null	null
1	24	2	10	null	null

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.

[BoxesAndLines]

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.

[VMolloy]

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 ?