Fact table design for Financial items
5 posters
Page 1 of 1
Fact table design for Financial items
My company creates comprehensive software for healthcare companies and to start off our Data Warehouse, I'm focusing on Financials. I've done some DW work and have read Kimball books and been to their classes.
How do I handle the Fact table design for the financial business processes? I don't know if all of these items should be in one fact table or multiple fact tables. If the latter, should any of them be combined? I'm not worried about snapshot or aggregates for now, just granular details.
1. Charges (how much is owed by insurance or patient to the clinic)
2. Payments (from insurance or patient)
3. Voided Payments (cancel previous payment)
4. Write-offs
5. Voided Write-offs (cancel previous write-off)
6. Transfer remaining balance from one insurance to another
7. Bill was issued to insurance (non-financial, but needs to be included in some reports)
One Fact table? Multiple Fact tables?
How do I handle the Fact table design for the financial business processes? I don't know if all of these items should be in one fact table or multiple fact tables. If the latter, should any of them be combined? I'm not worried about snapshot or aggregates for now, just granular details.
1. Charges (how much is owed by insurance or patient to the clinic)
2. Payments (from insurance or patient)
3. Voided Payments (cancel previous payment)
4. Write-offs
5. Voided Write-offs (cancel previous write-off)
6. Transfer remaining balance from one insurance to another
7. Bill was issued to insurance (non-financial, but needs to be included in some reports)
One Fact table? Multiple Fact tables?
hurleystylee- Posts : 1
Join date : 2015-06-21
Re: Fact table design for Financial items
If facts have the same grain, as defined by the dimensions associated to them, you can put them in the same fact table; if they don't have the same grain you can't.
Given the above caveat, whether you put facts with the same grain in the same fact table is up to you. Does it make querying easier or more difficult; do you end up with massive volumes in one fact table that would be easier to manage if split across multiple fact tables;etc.?
Given the above caveat, whether you put facts with the same grain in the same fact table is up to you. Does it make querying easier or more difficult; do you end up with massive volumes in one fact table that would be easier to manage if split across multiple fact tables;etc.?
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Fact table design for Financial items
These will be different fact tables. Look at the primary key for each of these types of transactions and that will tell you how many fact tables you will need.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Fact table design for Financial items
You need 1 transaction fact table which save all the thing you listed out. Patient payment transactions have different codes for example "bill send", "bill accepted", "charges rejected" etc.
Patient bill has header items where bill payment is summarized for a specific visit or "encounter". Bill also has line items where specific health service charges are itemized. So you need 2 fact tables to store that detail. Your 1 fact tables grain should be at patient, visit date, and hospital/clinic level. Your 2nd fact tables grain should be at patient, visit date, and hospital/clinic and line item level. Once you have this information then you can provide data SNAP summary as per business need.
Patient bill has header items where bill payment is summarized for a specific visit or "encounter". Bill also has line items where specific health service charges are itemized. So you need 2 fact tables to store that detail. Your 1 fact tables grain should be at patient, visit date, and hospital/clinic level. Your 2nd fact tables grain should be at patient, visit date, and hospital/clinic and line item level. Once you have this information then you can provide data SNAP summary as per business need.
zoom- Posts : 97
Join date : 2010-08-23
Location : Chicago
Re: Fact table design for Financial items
Are you sourcing your data from EDI X12 835/837 transactions, a patient accounting system, a claims system, or some other kind of system such as your own proprietary product? Additionally, who are your primary client types (hospitals? Home health companies? Payors? Employer Groups?)?
mirsky72- Posts : 2
Join date : 2012-12-03
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact table design
» Fact Table Design
» Fact Table Design
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact table design
» Fact Table Design
» Fact Table Design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum