Same Data at different Granularity

View previous topic View next topic Go down

Same Data at different Granularity

Post  Sudip on Tue Feb 16, 2016 5:25 pm

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

Sudip

Posts : 5
Join date : 2016-02-16

View user profile

Back to top Go down

Re: Same Data at different Granularity

Post  ngalemmo on Tue Feb 16, 2016 5:57 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Same Data at different Granularity

Post  zoom on Wed Feb 17, 2016 10:49 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Thanks

Post  Sudip on Wed Feb 17, 2016 5:25 pm

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.

Sudip

Posts : 5
Join date : 2016-02-16

View user profile

Back to top Go down

Re: Same Data at different Granularity

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