Multi-valued dimension with distinct attributes

View previous topic View next topic Go down

Multi-valued dimension with distinct attributes

Post  ddasari on Tue Feb 01, 2011 6:27 pm

In the health care data analysis DW, the grain of one of my fact tables is the episode of care. How can I include procedure codes performed on an episode ? if I create a multi-valued dimension for it and used bridge tables to model this, it will end up with as many records in the bridge table as there are in the fact table since it will include physician name and the procedure date which would be unique per episode. The other option I have is the parent-child model where I can create a fact table at the episode Physician level and not have any dimensions that I can retrieve from the episode fact and use the episode of fact to join to episode physician fact and get the information for the procedure groups. However, based on the tips by Margy (tip # 95) and Ralph (tip # 25) I know this is not a good practice. I dont have any good reason though. can you please advise which method is better and why ?

Thanks much!

ddasari

Posts : 2
Join date : 2011-02-01

View user profile

Back to top Go down

Re: Multi-valued dimension with distinct attributes

Post  ngalemmo on Tue Feb 01, 2011 6:44 pm

You could make the grain to include a normal physician and procedure dimension. Include an episode id as a degenerate dimension to allow grouping. This is not unlike maintaining order lines in a sales fact table. Another way is to make physician and procedure separate multivalued dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multi-valued dimension with distinct attributes

Post  ddasari on Tue Feb 01, 2011 6:50 pm

Thank you for the response.
The fact table should also include the various diagnoses associated with the episode and the length of stay and admitting date and discharge date which are at the episode level. Diagnosis in this case, I assume is a dimension but with several values to it. But again every diagnosis should also include the physician id. How can I include several such multi-valued dimensions with unique attributes in the fact table ?

ddasari

Posts : 2
Join date : 2011-02-01

View user profile

Back to top Go down

Re: Multi-valued dimension with distinct attributes

Post  ngalemmo on Thu Feb 03, 2011 12:21 pm

Diagnosis is almost always a multi-valued dimension, you should also store DRG if it is available. If you maintain the facts at the physician/procedure level, adding diagnosis shouldn't be a problem.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multi-valued dimension with distinct attributes

Post  Jeff Smith on Thu Feb 03, 2011 12:46 pm

If Episode of Care is equivalent to an Office visit by a patient, or maybe an ER visit, then, a fact table at the grain of Episode of Care cannot contain procedure level data.

The Episode of Care is like a visit to a store and the procedures are like the items purchased.

If you need a fact table with the grain at the Episode of Care, then I think you need a second fact table at the procedure level. An Episode of Care dimension can be part of the fact table and the Episode of Care can include rollups from the procedure level fact table.

The bridge table would come into play if you to wanted to link the diagnosis to the procedures because the episiode of care can have multiple diagnosis and multiple procedures. Diagnosis and procedures becomes a many to many relationship.

I deal with dental claims. I have a claim fact table and a claim detail fact table. Some items, such as the copay, is at the claim level and not related to a specific procedure. In the claim fact table, I will aggregate information from the detail level, such as number of procedures, total dollar amount of the procedures, etc.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Multi-valued dimension with distinct attributes

Post  beatrixkiddo on Sat Aug 23, 2014 9:13 pm

Jeff Smith wrote:If Episode of Care is equivalent to an Office visit by a patient, or maybe an ER visit, then, a fact table at the grain of Episode of Care cannot contain procedure level data.

The Episode of Care is like a visit to a store and the procedures are like the items purchased.

If you need a fact table with the grain at the Episode of Care, then I think you need a second fact table at the procedure level.  An Episode of Care dimension can be part of the fact table and the Episode of Care can include rollups from the procedure level fact table.

The bridge table would come into play if you to wanted to link the diagnosis to the procedures because the episiode of care can have multiple diagnosis and multiple procedures.  Diagnosis and procedures becomes a many to many relationship.

I deal with dental claims.  I have a claim fact table and a claim detail fact table.  Some items, such as the copay, is at the claim level and not related to a specific procedure.  In the claim fact table, I will aggregate information from the detail level, such as number of procedures, total dollar amount of the procedures, etc.

Are you saying here that you would have Episode of Care as a dimension?  (see bold text above)

beatrixkiddo

Posts : 22
Join date : 2013-10-22

View user profile

Back to top Go down

Re: Multi-valued dimension with distinct attributes

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum