Updating the Fact on Claims/Pre-Authorizations

I am getting claims and pre-authorizations. I get the record in whatever state - it can be in mid process, post process, pre-processed, whatever. As the Claim/Pre-Auth progreeses thru processing and it gets updated, I can update the dimension table.

But - what do I do with the Approved Amount? The Approved Amount is $0 until the claim/Auth gets to a certain point in the processing when it is given an approved amount. The approved amount acts like a dimension because it can change. But it's a fact because I need to aggregate the Approved Amount.

My gut says to put the Approved Amount on the fact and update it.

Hi,

just to understand a bit more about what you are trying to model:
1. are these states actual values in a "status" field (or can be determined by other field values)?
2. If they are actual values then does the record go through a fixed process from state to state - or can it sit in a loop between two or more states before moving on towards the end state?
3. Does the approved amount change in line with a state change - or can it be changed without any other update to a record?
4. Do you need to report on how the Approved Amount changes?

Thanks,

There is a status field that will change. Regarding the Approved Amount - it can change depending on the progess of the the Auth. It will enter the system as $0. At some point, it will either change to >$0 or stay at $0.

Auth comes into the system. Status is "processing". Approved amount is initially $0. At some point in the processing state, it could change to >$0.
Auth completes processing. Status is "Approved or denied". Approved Amount can by any amount. Record will no longer change.
Auth goes into Pending Status because or several reasons. Will eventually get kicked loose and be approved or denied.

Business wants to see approved amounts, they want to see # of Auths in Pending status, in Process, Denied, Approved. Was planning to track counts with an aggregate table.

Two options I can think of - and you could implement one or other or both depending on your reporting requirements.
1. Create a fact table where state is part of the dimensional information - so everytime there is a change of state you create a new fact record. Approved amount would be a measure on this fact table.

2. Create an accumulating snapshot fact table that has measures specific to the states where you want to capture that measure's value e.g. "Processing Approved Amt", "Pending Approved Amt", "Approved Approved Amt" etc. You then update this fact table every time the state changes

BTW - it's common practice to implement the individual fact table when creating an accumulating snapshot table. So if you choose option 2 it is likely you would also implement option 1 as well