Joining fact tables
3 posters
Page 1 of 1
Joining fact tables
Hi,
I'm designing a clinical data warehouse with a fact table for an admission, a separate fact table for labs:
idLabs_F int(11) PK
idAdmission_F int(11)
idLabs_D int(11)
Result varchar(45)
Results_dtm datetime
Order_dtm datetime
Collected_dtm datetime
Received_dtm datetime, and a labs dimension(testCode, TestDesc, units, ref range).
I'm joining the fact tables with the identity key in Admissions_Fact, idAdmission_F. Does anyone see a reason not to do this as it seems to me it should work OK. I'm questioning it as I've not seen this in any of the examples I've seen of joining fact tables. thanks for any help here.
Jeff
I'm designing a clinical data warehouse with a fact table for an admission, a separate fact table for labs:
idLabs_F int(11) PK
idAdmission_F int(11)
idLabs_D int(11)
Result varchar(45)
Results_dtm datetime
Order_dtm datetime
Collected_dtm datetime
Received_dtm datetime, and a labs dimension(testCode, TestDesc, units, ref range).
I'm joining the fact tables with the identity key in Admissions_Fact, idAdmission_F. Does anyone see a reason not to do this as it seems to me it should work OK. I'm questioning it as I've not seen this in any of the examples I've seen of joining fact tables. thanks for any help here.
Jeff
jmather- Posts : 6
Join date : 2011-04-26
Age : 65
Location : Hartford, CT
Re: Joining fact tables
You are not thinking dimensionally. Why would you want to join on a made up fact PK if the query does not require that level of detail?
The lab fact should contain appropriate dimensionality so it can stand on its own. If there are dimensions relating to an admission that are useful when looking at lab, then the lab fact should contain those dimensions.
If you need to combine measures from both tables you combine facts they way any facts are combined... summarized on common dimensions then join (or union) the aggregate sets.
The lab fact should contain appropriate dimensionality so it can stand on its own. If there are dimensions relating to an admission that are useful when looking at lab, then the lab fact should contain those dimensions.
If you need to combine measures from both tables you combine facts they way any facts are combined... summarized on common dimensions then join (or union) the aggregate sets.
Re: Joining fact tables
Nick is right; if the labs fact needs some of the dimensions on the admissions fact then they should be included on the labs fact as well (including the admission reference if it's a degenerate dimension).
If your admissions fact is an accumulating snapshot, with one row per admission, then you could always store summarised labs facts on the admissions fact.
If your admissions fact is an accumulating snapshot, with one row per admission, then you could always store summarised labs facts on the admissions fact.
Dave Jermy- Posts : 33
Join date : 2011-03-24
Location : London, UK

» Joining Fact tables
» Joining two Fact tables???
» Joining fact tables
» DegenerateDimension joining Fact tables
» Joining/Relating Fact Tables
» Joining two Fact tables???
» Joining fact tables
» DegenerateDimension joining Fact tables
» Joining/Relating Fact Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum