Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Joining fact tables

3 posters

Go down

Joining fact tables Empty Joining fact tables

Post  jmather 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 : 65
Location : Hartford, CT

Back to top Go down

Joining fact tables Empty Re: Joining fact tables

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

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

http://aginity.com

Back to top Go down

Joining fact tables Empty Re: Joining fact tables

Post  Dave Jermy 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

Back to top Go down

Joining fact tables Empty Re: Joining fact tables

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum