Hospital Billing and Collections Model

View previous topic View next topic Go down

Hospital Billing and Collections Model

Post  beatrixkiddo on Wed Aug 13, 2014 10:04 pm

Building a warehouse with very little input from the business users.  Sort of a getting our feet wet and combining data from multiple hospitals into one data source.  First bit of requirements includes payments and adjustments.  This is what I have so far; a transaction fact.



Where I'm getting stuck is I have bills and patient episodes related to these transactions.  These seem like events that I'd also build fact tables for with their respective measures, but they also seem like dimensions I should add to the transaction fact.  Or should bill_id and episode_id be degenerate dimensions in the transaction fact?

Thanks

beatrixkiddo

Posts : 22
Join date : 2013-10-22

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  nick_white on Thu Aug 14, 2014 7:29 am

I guess it depends on what you are trying to report on...

I would start simply. Identify your event types (transactions, bills, episodes, ...) and create fact tables for them:

1. What are your measures? Do Bills and Patient Episodes have measures? If they do then you you should have fact tables for them (and I would consider taking out BilledDateKey from your transaction fact and putting it in your billing fact)

These will allow you to answer standard analytical questions by querying each fact independently and then combining the result sets using the conformed dimensions e.g. Show me the number of transactions and amount billed by hospital location in July.

Looking at your question about putting Bill Id and Episode ID on your transaction fact table, remember a dimensional model is there to answer analytical questions rather than transactional questions - so you wouldn't normally use it to answer a question such as "For transactions X how much did we bill?" - and therefore you don't need these individual IDs as you wouldn't normally join records at the transaction level using these IDs.
One reason for putting IDs like this on a fact table is to give the fat table the required grain - suppose you need a record per transaction but your Dim FKs on the fact don't make it fact record unique you could include the ID as part of the Fact table PK

If you are trying to report on a process that has a defined set of steps (an episode is followed by a transaction is followed by a bill) then consider creating an accumulating snapshot fact table - but if you do this you would almost certainly also want to create the individual fact tables for each step as well.

If you have a M:M relationship between dimensions then you can model this using a bridge table e.g. an episode can be covered by multiple bills and a bill can cover multiple episodes - so you can then filter your episode fact by bill attributes and your bill fact by episode attributes.

Does this help at all?

nick_white

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

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  beatrixkiddo on Thu Aug 14, 2014 8:45 am

Nick!

This helps tremendously.  Thank you!  

Yes, Bills and Episodes have all sorts of measures.  After some reading and thinking last night, I started designing Bill and Episode facts.  Somehow, I wandered down the right path.  It makes more sense now.  

One requirement I have is Avg Collection Cycle (billed to collected day lag) which is what prompted me to include BilledDate.  Your explanation makes sense.  Combine bill and transaction facts into an accumulated snapshot.

Regarding Episodes, another requirement is discharged but not billed.  These are both flags.  This has had me confused on where to put Episodes, fact or dim or factless fact.  Flags and statuses belong in a dimension, right?

beatrixkiddo

Posts : 22
Join date : 2013-10-22

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  nick_white on Thu Aug 14, 2014 9:02 am

Glad that I helped more than I confused!

Yes - flags go into dimensions. However...
You can put them on the fact table as DDs if they have no obvious real dimension to assign them to
If you have a number of 'orphaned' flags then you can put them all in a single Junk dimension - but just make sure you know how many records might be created in the junk dimension and it won't be too large
e.g. 2 flags that can each only take 2 values would only ever result in 4 records in your Junk dimension
10 flags, each of which could take any letter from A-Z, would give more rows than you would probably want in a Dim

nick_white

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

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  beatrixkiddo on Thu Aug 14, 2014 12:19 pm

These flags are bits, so I will put them in the fact as JD's for now.  Really appreciate the help Nick.

This is fun stuff.  I keep getting distracted by the big, EDW, picture I guess you could say, when I probably need to be focused more on the facts and business processes.  Hopefully, I'll get more input from the business users as the project progresses.

Thanks again!

beatrixkiddo

Posts : 22
Join date : 2013-10-22

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  beatrixkiddo on Wed Aug 20, 2014 1:34 pm

On Patient Episodes and Transactions again,

I have a Transaction fact (see first post), and now a Patient Episode fact, with some conformed dimensions.  I did not include the EpisodeID in the transaction fact thinking Episode was it's own fact.  What if I want to see transactions by Episode?  

I will have an accumulated fact for Overall Episode information that will have Total Payments, but obviously that's not at the per payment grain.  Episode seems like a fact and dimension in this case.  

Is there case where Episode can be both dim and fact?

beatrixkiddo

Posts : 22
Join date : 2013-10-22

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  nick_white on Thu Aug 21, 2014 7:13 am

An Episode record in your source system can have both attributes and measures and therefore this data will go to different tables in your Dim - if that's what you are asking? An Episode table in your DM can't be both a Fact and a Dimension table, obviously.

Regarding your question "What if I want to see transactions by Episode?" - I assume you are building a DM for analytical purposes so what is your actual analytical requirement here (I'm guessing you don't really want to just list transactions by episode)? Do you want to be able to analyse the number of transactions per episode? If so then "No. of transactions" could be a measure on your epsiode fact table

nick_white

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

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

Post  beatrixkiddo on Thu Aug 21, 2014 3:55 pm

nick_white wrote:Regarding your question "What if I want to see transactions by Episode?" - I assume you are building a DM for analytical purposes so what is your actual analytical requirement here (I'm guessing you don't really want to just list transactions by episode)? Do you want to be able to analyse the number of transactions per episode? If so then "No. of transactions" could be a measure on your epsiode fact table

I have had the request to see transactions by episode, specifically payments by payment date.  So maybe it isn't necessary or appropriate to look at it at that level.  Would it be better to include lag day measures in my accumulated snapshot?  e.g. Discharged to First Payment Date Days.

If they did want to look at a given episode's payments by date, is that not a good question for the DM?

beatrixkiddo

Posts : 22
Join date : 2013-10-22

View user profile

Back to top Go down

Re: Hospital Billing and Collections Model

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