Design Tip No 25 - Parent > Child > Child (Maybe)
2 posters
Page 1 of 1
Design Tip No 25 - Parent > Child > Child (Maybe)
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)...
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...
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.
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.
VMolloy- Posts : 2
Join date : 2009-03-31
Re: Design Tip No 25 - Parent > Child > Child (Maybe)
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Design Tip No 25 - Parent > Child > Child (Maybe)
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 ?
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
Similar topics
» Unusual(?) parent/child Fact table design issue
» Need help with Bridge Table Design for: 1) Ragged-Hierarchy 2) Parent Nodes can be used more than once.
» How do we design parent-child relationship of the lowest grain in the fact.
» Design Tip #25 - Parent > Child > Child (Again)
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Need help with Bridge Table Design for: 1) Ragged-Hierarchy 2) Parent Nodes can be used more than once.
» How do we design parent-child relationship of the lowest grain in the fact.
» Design Tip #25 - Parent > Child > Child (Again)
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|