conformed dimension issue

View previous topic View next topic Go down

conformed dimension issue

Post  cpeterson on Tue Feb 14, 2012 1:25 pm

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

cpeterson

Posts : 9
Join date : 2011-11-26

View user profile

Back to top Go down

Re: conformed dimension issue

Post  ngalemmo on Tue Feb 14, 2012 6:57 pm

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
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: conformed dimension issue

Post  cpeterson on Wed Feb 15, 2012 12:14 pm

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.

cpeterson

Posts : 9
Join date : 2011-11-26

View user profile

Back to top Go down

Re: conformed dimension issue

Post  Jeff Smith on Wed Feb 15, 2012 12:45 pm

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.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: conformed dimension issue

Post  ngalemmo on Wed Feb 15, 2012 1:54 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: conformed dimension issue

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