days of hospitalization as a "dynamic Measure"
4 posters
Page 1 of 1
days of hospitalization as a "dynamic Measure"
Hi gurus!
I'm working on some clinical data.
I'm dealing with hospitalizations, and one of the measures that i'd like to use is: Days of hospitalization, with an obligation:
Suppose that an hospitalization starts on 15th december 2008 and ends on 15th January 2009: a total of 30 days of hospitalizations,
but I'd like to group the first 15 days (15th december 2008 - 31st december 2008) in the 2008, and the second 15 days (1st January 2009 - 15th January 2009)
in the 2009.
I've already implemented two shared dimensions that handle dates: one for date start and one for discharge date.
Is there a way to meet this business requirement?
Tnx for reading!
I'm working on some clinical data.
I'm dealing with hospitalizations, and one of the measures that i'd like to use is: Days of hospitalization, with an obligation:
Suppose that an hospitalization starts on 15th december 2008 and ends on 15th January 2009: a total of 30 days of hospitalizations,
but I'd like to group the first 15 days (15th december 2008 - 31st december 2008) in the 2008, and the second 15 days (1st January 2009 - 15th January 2009)
in the 2009.
I've already implemented two shared dimensions that handle dates: one for date start and one for discharge date.
Is there a way to meet this business requirement?
Tnx for reading!
nickall- Posts : 5
Join date : 2009-12-10
Re: days of hospitalization as a "dynamic Measure"
You should be able to calculate it on the fly, but it would be a bit tricky... First, it would help if your date dimension contained a day of the year value and number of days in the year. To handle stays split over years, you need two passes through the facts (UNION). The first to calculate the number of days from admission to either the end of the year or discharge which ever comes first. Then a second pass for those fact where the discharge date is in the next year to calculate the number of days from the beginning of the year to the discharge.
Re: days of hospitalization as a "dynamic Measure"
I guess this might depend on volumes and also on the tools being used, but would it make the queries easier if the grain of the fact were one row per patient, per hospitalization, per day?
D_Pons- Posts : 16
Join date : 2009-02-10
Location : UK
Re: days of hospitalization as a "dynamic Measure"
Yes! That would certainly make counting days of hospitalization easy. Have a 'HospitalizationDays' measure in the fact table that is always set to 1 for each fact record. No matter how you aggregate the fact data you will get a correct total.
(There are still some queries that are tricky and could require multiple passes, for example "how many patients did we have in December that had a stay of 30 days or longer?")
(There are still some queries that are tricky and could require multiple passes, for example "how many patients did we have in December that had a stay of 30 days or longer?")
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Similar topics
» some kind of "dynamic" dimension
» Best way to implement date ranges in schemas (fact 1 valid "from" date1 "to" date 2)
» Employee Vacation days
» Dynamic dimensions
» The great SQL query "template" from edition 1 of Data Warehouse Toolkit
» Best way to implement date ranges in schemas (fact 1 valid "from" date1 "to" date 2)
» Employee Vacation days
» Dynamic dimensions
» The great SQL query "template" from edition 1 of Data Warehouse Toolkit
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum