Same Data at different Granularity

Hi All,
I have a situation here, we get two type of claims from hospital (establishment) . One set of data is at Episode level - Unique ID for each visit including clients details, cost per visit etc. The next set of claim is aggregate level where hospital send us claim at aggregate level with its own claim key for number of episode with total amount (no of claims for many clients and visit).
The business requirement is to see both at episode level, aggregate level and Both combine. specially total cost per establishment, total claim per status as they both claim goes through number of status (recieved,accept, reject) etc. Since they at different level grain thinking of creating two different fact one at episode level, and one at aggregate level. the complexity is combining data to be able to get total cost by status, establishment. Can you please suggest the best option.

In past with a similar situation . we treated aggregated data each row as each episode and issue a fictious number, it did work at that time however kimball always says No to fact with different grain. just trying to find out if any other option out there.

thanks

I would have a single fact with a 'number of episodes' measure.

I would assume the aggregate data you receive only contains dimensional information for the total, not the individual episodes, such as having one date for all the episodes. So, you can't create something you don't have. It does not make any sense to allocate these values as you have nothing to allocate to. But if you do need to allocate, knowing the number of episodes the measures represent will allow you to do that.

Because data is in different level of detail, you cannot combine it. If aggregate data is made out of detail data then why do you want to save aggregate data you get from hospital? A hospital calculates expected payments it expects to get from an insurance company. If that aggregate total amount has total cost and that “calculated expected payments” then you cannot simply use detail data to get it, because it is calculated based on detail terms of an insurance contract.

Creating 2 fact tables gives you more flexibility to run your reports as you described.

Thank you guys for your suggestion, As suggested i went with two different fact (episode fact- contains details at episode level & summary fact- all episode rolled up to establishment + aggregate data) . so they could retrived total combined cost per establishment/state for each individual episode details they need to use episode fact)

Truly appreciated for response.