Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?

Hi,

I'm designing a fact table to hold Hospital Stays. I think it should be a Transaction Fact Table - but that would indicate that it records an event at a single moment in time. So then I need a row for the "In" event and a row for the "Out" Event. Is that right? Or should it have a date & time for the In event and the Out event on the same row? That would allow easier calculation of duration, or even putting the duration into the fact table too.
However it would mean that the fact table row needed to be updated when the patient leaves the hospital.
Another consideration is that we have further granularity within the stay - ward stays and bed stays.
For a timeline or Gant chart it is really useful to display all these different events in the same data set, with duration in minutes.
But there are many attributes like discharge method or bed code that belong at only one level.
Sometimes hospital stays can overlap with each other, and so can ward stays and bed stays - impossible but apparently true!

I hope someone can help.

Al Wood

Hi ,

you can use a Accumulating Snapshot table, where one row for each event and it will capture the date when the patient is admitted to the hospital and when he leaves the the "left time" is updated.

thanks

I have worked with inpatient data before and I chose to create Fact tables for the different granularities of data, this felt like the best approach as it allowed the flexibility to query the different levels of data depending on the requirements of any reports/analysis.

If I remember correctly I ended up with Fact tables for:

Admission
Discharge
Spell (Admission and Discharge combined)
Consultant Episode
Ward Episode
Specialty Episode

In hindsight the Admission and Discharge Facts weren't really necessary as the Spell Fact covered everything but I felt that users would become confused with this table as it was an accumulating snapshot table and didn't just include ended spells.

Along with the above I created a more transactional table where each row representing a change in ward, specialty or consultant.
This was by far the most complex to produce due to the different episodes overlapping each other and figuring out which one actually caused something to change but it is possible.

Hope that helps.

Don't record a stay until it is completed. Then there is no need to update and you can store both dates on the same transaction fact row.

Thanks everyone.
I'm still struggling with this. I would like to enter the fact rows for stays only when the stay is finished, as "boxes and lines" suggests,
however there are two other considerations. One is that I need to handle updates or historic amendments to the data in the source system which needs a delta load into the fact table anyway, and the other is that one of the requirements is to report on which patients are in at the  current time.
To do this from the stays fact table would require a filter saying "[stay_end_date_time] is null" which I think may be inefficient sql. Or should I have a [current_stay_flag]?
To report on who is currently in a stay, at all three levels, (hospital, ward, bed) would it be better to use three Current Occupancy fact tables?

Episodes are a different problem altogether. We have realized that they are not events - they are groups of events that are grouped together according to arbitrary rules, often grouped afterwards, and only really used by the finance dept. The finance people, working from an official definition of Episode say things like "These things should be in one episode" and "This event should be in the next episode", but when the events in other clinical systems fall outside the date range or events get into the wrong episode they just want the data modified to fit. I don't want to alter dates of events just to include them in an arbitrary pot.
Perhaps volatile bridging tables (or dimensions) to join the "Episode" fact to other facts would work for this? Then we could create a screen for them to put events into the "right" episodes. What do you think?

Thanks,
Al Wood