Related Facts?

View previous topic View next topic Go down

Related Facts?

Post  SteTatt on Fri Jul 06, 2012 6:38 am

Hi

I am trying to model some healthcare data and I am really struggling to understand how best to structure some of the tables.

Each patient within our system will have one or more case records, containing start and end date, current status etc for the different teams within the hospital then within each case the patient can have multiple contact records or even inpatient episode records.

I will have a patient dimension table and a fact table for each contact and a fact table for each inpatient episode with a FK reference in each table to the patient but I am unsure how I can store the case records as they relate to the patient and the contacts/episodes. Should they be a separate fact table and if so how can I show the relationship to the contacts

Any guidance would be really appreciated

Thanks

SteTatt

Posts : 3
Join date : 2012-07-06

View user profile

Back to top Go down

Re: Related Facts?

Post  BoxesAndLines on Fri Jul 06, 2012 8:43 am

What exactly is a case record? Is it a metric at a higher grain than Contact Record and Inpatient Episode or possibly a dimension?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Related Facts?

Post  SteTatt on Fri Jul 06, 2012 8:57 am

A case is an episode of treatment with a particular team. The episode would have a start and end date, a link to the patient, a link to the team, an outcome and a current status. The individual assessments, appointments or inpatient stays would then be linked to the case.

Thanks

SteTatt

Posts : 3
Join date : 2012-07-06

View user profile

Back to top Go down

Re: Related Facts?

Post  BoxesAndLines on Fri Jul 06, 2012 9:06 am

OK. I would look at combining the Contact Record and Inpatient Episode into a single fact as they appear to be at the same grain. Case Record looks like it is at a higher grain which requires another fact table. You can aggregate up the Contact Record and Inpatient Episode counts to the higher level fact (which would probably satisfy the majority of your queries).
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Related Facts?

Post  SteTatt on Fri Jul 06, 2012 10:36 am

Thanks for your reply.

Would I then add a relationship from the contacts fact to the case fact?



SteTatt

Posts : 3
Join date : 2012-07-06

View user profile

Back to top Go down

Re: Related Facts?

Post  BoxesAndLines on Fri Jul 06, 2012 11:54 am

No. Query optimizers that are aggregate aware should be able to choose the correct fact table based on predicates. Aggregate facts do not have relationships to their detail facts.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Related Facts?

Post  biendalton on Thu Jul 12, 2012 2:11 am

I thought both are the same.


______________________________
The Ideal Answer For type of life insurance policy & affordable term life insurance rate That You Simply Could Find Out About At this time

biendalton

Posts : 1
Join date : 2012-07-12

View user profile

Back to top Go down

Re: Related Facts?

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