conformed dimension issue

I have an issue trying to conform a dimension across two fact groups. I am not sure if this is even doable.

Fact Group 1
Claims Summary with Paid/Reserve Amounts

Fact Group 2
Hospital Event

Both have medical record in common. I thought I could conform this dimension but I don't think I can.
It is possible to have more than one hospital event (different locations) with the same medical record number and that same medical record can be tied to one claim. If I conform the dimension I run the risk of overstating my claim paid/reserve amounts.

Any ideas?

Thanks,

Craig

It's not clear what you want to do. What does hospital event represent? Are they procedures?

Does medical record number represent the patient? And why would the patient being treated in two locations cause a problem?

The relationship between two fact tables is ALWAYS many-to-many. Even if you can give me an example where they are not, you must still always consider it to be many-to-many. Given that premise, there is a basic pattern to combine information between two fact tables:

1. Summarize each fact table individually along common dimension attributes (summarizing changes the relationships to one-to-one)
2. Join or union the two sets from step 1

The event at the hospital could be refered to as an "adverse" event. Maybe a fall/patient injury. This event requires a series of information gathered around it (report). One of the dimensions associated with this event is location (hallway. ER etc..). This particular hospital wants to try to associate this event information with their claims information. The only data element the two share in common that is reliable is the medical record number. If I conform the dimension on medical record and I have 4 events with the same medical record number and one claim summary transaction with the same medical record number when I go to put elements from both areas on a report I could overstate my claims measure.

Are you trying to join 2 facts tables on the conformed dimension? Having conformed dimensions doesn't automatically mean you can use the dimension key to join 2 fact tables. If you are combining measures from different fact tables on a Dimension Key, then the grain of the facts have to be at the level of the common dimension key(s).

You can't join 2 fact tables when a many to one relationship exists and use measures from both facts.

If I conform the dimension on medical record and I have 4 events with the same medical record number and one claim summary transaction with the same medical record number when I go to put elements from both areas on a report I could overstate my claims measure.

You need to construct the query as I desribed in the previous post.

If you want to show the claim as a header in the report followed by the events, you need to handle it (summing and such) in the report. Different reporting tools allow you to do this in different ways.