Fact table design for Financial items

View previous topic View next topic Go down

Fact table design for Financial items

Post  hurleystylee on Sun Jun 21, 2015 3:02 pm

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?

hurleystylee

Posts : 1
Join date : 2015-06-21

View user profile

Back to top Go down

Re: Fact table design for Financial items

Post  nick_white on Tue Jun 23, 2015 6:27 am

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.?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact table design for Financial items

Post  BoxesAndLines on Tue Jun 23, 2015 9:58 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Fact table design for Financial items

Post  zoom on Tue Jun 30, 2015 7:02 am

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.

zoom

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

View user profile

Back to top Go down

Re: Fact table design for Financial items

Post  mirsky72 on Sat Jul 18, 2015 9:31 am

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

View user profile

Back to top Go down

Re: Fact table design for Financial items

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