Best practise in creating fact tables for health care

Hi all,

I'm facing a situation where I'm unsure what the best approach for designing my fact tables is. I'm working on a star-schema for a hospital and currently I have identified over 330 surgery related attributes. The problem lies in defining/designing the proper fact tables.

To put it simple we want to measure the outcome of performed surgery by some dimensions (patient, specialist, time etc).
The problem lies in defining the right fact table. Since multiple types of surgery are to be supported, and all types of surgery have 1 or more overlapping attributes I''m unsure what to do.

For example I have three types of surgery. SurgA, SurgB, SurgC. And I have attributes A to H. Each surgery also contains information about presurgery conditions (like diabetes, previous heart attack, previous stroke etc).

SurgA	SurgB	SurgC
A	A	B
B	C	E
C	E	G
D	F	H

What would be the best practise in designing the fact tables?

1. 1 large fact table containg the presurgery attributes and all attributes for all types of surgery
   
2. each surgery type gets its own fact table, creating a schema where facts look-a-like for a given number of columns and apply presurgergy columns to the fact tables as well. Downside of this is that the fact-table will contain many null-values for nonrelated surgery columns for a given surgery (seems an unwanted situation for me regarding query performance & calculations)
   
3. split the pre surgery attributes into a presurgery-fact table and each surgery gets it's own fact table
   
4. other ?
   

Of course there are also post-surgery conditions, but for the example I left them out.

Hoping to find some insights on the matter here. Thanks in advance for your time to reply.

Regards from Holland,
Ben

(of course I can supply more info on the matter if this is desired for a better conversation)

BenFransen wrote:Hi all,

1. 1 large fact table containg the presurgery attributes and all attributes for all types of surgery
   
2. each surgery type gets its own fact table, creating a schema where facts look-a-like for a given number of columns and apply presurgergy columns to the fact tables as well. Downside of this is that the fact-table will contain many null-values for nonrelated surgery columns for a given surgery (seems an unwanted situation for me regarding query performance & calculations)
   
3. split the pre surgery attributes into a presurgery-fact table and each surgery gets it's own fact table
   
4. other ?
   

Regards from Holland,
Ben

I don't think you'll get all of this in one fact table. For example, your pre-surgery attributes are dependent on the patient. I would characterize them as patient conditions which could be another fact table. For the surgery dimension, I would look at Kimball's solution for heterogeneous product dimension. Given the variability of product and surgery attributes, it might be a good fit.

Thanks for your reply, I will take a look at them. I was also reading about two other posibilities which made me think they would make sense:

- Accumulating snapshot fact table
- Supertype (for matching attributes) and subtypes

Are these approaches suitable as well, and what would be pro's and con's?

I'm not sure how an accumulating snapshot would be appropriate here. The super/sub types is analogous to Kimball's heterogeneous product dimension.

Well, as for the accumulating fact table I thought it would make sense since:

- a patient signs in (for example a day before surgergy)
- a patient undergoes the surgery
- a patient goes to a clinic to rest/recover (takes for example 3 days)
- a patient gets fired (the 3rd / 4th day since sign in)

These seem like milestones to me, or at least I interpreted them like milestones regarding the docs (The Data Warehouse Toolkit 3rd ed.).

Or would you argue different and it's better to split them into seperate facts, and if so; why?

Just trying to get more insight on the different angles since knowledge about the topic isn't inhouse besides of me.

I haven't typically seen it modeled that way. An outcome is the result of claim activity. Claims are usually modeled when they are finalized (i.e. paid). That means the process is over. This is very beneficial from a processing standpoint as once you get a claim, it never changes. It might be adjusted in the future but not changed. This makes the process very transactional. Surgery is an activity on the claim, identified through a ICD code. Typically, the ICD code is sufficient information in claims for the business to understand what the activities or attributes were involved with the procedures.

If (one of the things) you want to model is the patient's movement through the surgery process then an accumulating snapshot is probably the way to go to model that specific requirement.

Accumulating snapshots work well as long as you have a defined set of activities that happen in sequence. You can get away with it if an activity in the sequence can be skipped but they don't really work if you can have loops in a process.

I would usually create facts for each individual step in the process (which would have quite a few dimensions to allow detailed analysis of each step) and then the accumulating snapshot would have (relatively) few dimensions: you would normally only include Dims that applied to all steps in the process. Trying to cram step-specific Dims into an accumulating snapshot fact is probably a mistake - apart from any that are obviously required such as a Date FK for each step in the process