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

Patient Re-admission Measure (count)

3 posters

Go down

Patient Re-admission Measure (count) Empty Patient Re-admission Measure (count)

Post  mark.tan Fri May 15, 2009 3:11 am

Dear DM Experts,

I am ponding this interesting scenario. What is the best way to resolve this requirement?

The simple is requirement is to track the patient re-admission rate in a hospital. For e.g., if patient A was admitted on 1st March 2009, 20th March 2009 and 12th May 2009. I will have 3 records in the fact table. To constitute a "re-admission" criteria, users can define a date range. Let's assume the users decide 30 days. Hence, between 1st March and 20th March, it is considered as 1 count of re-admission. But if the users decide to 60 days is more appropriate, it will be 2 count.

The key challenge here is that the users want to keep the data range "open" and not fixed. What would be the best way to tackle such unique requirement.

There are some BI tools that can dynamically do that, but it will mean crunching the numbers in the front end application and performance can be low. Is there a design that can elegantly store these numbers and yet fulfilled the requirement?

Suggestions and comments are welcomed... Cheers!
mark.tan
mark.tan

Posts : 14
Join date : 2009-02-04

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

Post  ngalemmo Mon May 18, 2009 12:32 pm

Are you not simply counting the number of admissions between a date range and subtracting 1? Unless I'm missing something, there shouldn't be any need to change your current data structures.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

Post  mark.tan Tue May 19, 2009 4:37 am

ngalemmo wrote:Are you not simply counting the number of admissions between a date range and subtracting 1? Unless I'm missing something, there shouldn't be any need to change your current data structures.

I think I am not being specific. Maybe the following example will help. Assume these admission records and this could be the fact table.

Record Patient Admission Date Interval (between previous visit)
===== ===== ============ =====
1 A 1-Mar-2009 0
2 A 20-Mar-2009 20
3 A 12-May-2009 53

What the users want to define as parameter is to input an interval of 20 days for a patient to be consider an re-admission. If that is the case, the above is consider as 1 count of re-admission (because record 1 and 2 are within 20 days, while 2 and 3 are more than 20 day apart). However, if the users input 60 days, then the above scenario will result in 2 count of re-admission.

I know that this is crazy, and there probably is no other ways to design this elegantly, but maybe I am wrong. Thing can get even nasty if users want re-admission rate. And that means 50% if interval is set at 20 days, but 100% if the interval is set at 60 days.
mark.tan
mark.tan

Posts : 14
Join date : 2009-02-04

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

Post  BoxesAndLines Tue May 19, 2009 8:57 am

Seems like your design answers that question. What's the issue?
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

Post  ngalemmo Tue May 19, 2009 1:45 pm

The interval in the fact table is not paticularly useful since it is only zero the first time someone is admitted, ever. If a year goes by and there are then 3 admissions in 2 months, you would not be able to tell looking at the raw value.

It is a two pass query. You need to identify the earliest admission within the dates of interest and then, for those admitted, locate other admissions in the time period.

select a.patient, count(*)
from admissions a,
(select patient, min(admitdate) earlyDate from admissions
where admitdate between #lowdate and #highdate) b
where a.patient = b.patient
and a.admitdate > b.earlydate
and a.admitdate between #lowdate and #highdate
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

Post  mark.tan Thu May 21, 2009 6:02 am

ngalemmo wrote:The interval in the fact table is not paticularly useful since it is only zero the first time someone is admitted, ever. If a year goes by and there are then 3 admissions in 2 months, you would not be able to tell looking at the raw value.

Actually, I beg to differ about this interval field. It can be useful, as I can create "bands" (in some BI reporting tools). For example:
Interval 1 : 20 > interval >= 0
Interval 2 : 40 > interval >= 20
etc.

It can eliminate some processing time to compute the interval on the fly... anyway, just my thoughts...
mark.tan
mark.tan

Posts : 14
Join date : 2009-02-04

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

Post  ngalemmo Thu May 21, 2009 9:04 pm

It is useful if the interval is based on some date. If you were to load interval in the fact table and base it on days since the last admission, the interval is based on a moving target. So, the first admission has an interval value of 0. If the next admission is a year later, the interval would be 365. If the person is readmitted 20 days later, the interval is 385. How do you identify the readmission of interest if the intervals are 0, 365 and 385?

The way to do it is to track interval using a date dimension attribute, day count. You set it by numbering the dates from the earliest date in the dimension and incrementing it chronologically. But even with this, it is still a two pass query. You find the first admission of interest (f) and compare date counts with subsequent admissions (s). Your banding is based on s.daycount - f.daycount.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Patient Re-admission Measure (count) Empty Re: Patient Re-admission Measure (count)

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