Multi-valued dimension with distinct attributes

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!

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.

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 ?

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.

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 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)