Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

New to MDX

0 views
Skip to first unread message

Lucas Natoli

unread,
May 27, 2003, 5:30:41 PM5/27/03
to
Hi!

I'm developing a cube for health care industry. One of the measures I
need to calculate is the average use of hospital rooms. I have a fact
table that can tell when a room was reserved, if the schedule was
canceled, if the person didn't show up and the amount of days a room has
been ocuppied (I don't know if that's correct the term).
The very question is.. how can I calculate the average of days used for
a room, knowing that many records would reflect that a person only
reserved a room?

My first intent was to calculate:
sum(daysRoomOccupied) / distinct patients

This is not a valid measure, since many patients may have not ocuppied a
room, but only reserved it.

Can anyone help me or show my a north ???


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

dpuri

unread,
May 28, 2003, 2:49:42 AM5/28/03
to
One solution is to create a SQL view of the fact table, which filters
out records where the room was not occupied. Building a cube from this
view will allow the average to be defined by:
sum(daysRoomOccupied)/count(daysRoomOccupied).

Since you may have other measures that include all the fact table
records, a separate cube(s) can be built for these, and then all cubes
combined in a single virtual cube.

- Deepak

Paul

unread,
May 28, 2003, 5:56:19 AM5/28/03
to
Lucas:

If you have the amount of days the room has been occupied, why not sum
the amount and divide by the number (count) of rooms? Maybe I missed
your objective.

Paul

Lucas Natoli <anon...@devdex.com> wrote in message news:<e6GU6cJJ...@tk2msftngp13.phx.gbl>...

0 new messages