Joining fact tables

View previous topic View next topic Go down

Joining fact tables

Post  jmather on Tue Apr 26, 2011 2:47 pm

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

jmather

Posts : 6
Join date : 2011-04-26
Age : 59
Location : Hartford, CT

View user profile

Back to top Go down

Re: Joining fact tables

Post  ngalemmo on Tue Apr 26, 2011 5:21 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining fact tables

Post  Dave Jermy on Wed Apr 27, 2011 8:29 am

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.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Joining fact tables

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