Accumulating Snapshot fact table

I am working on building a data model for claims data. The business want to know the complete history of transactions that happen on a claim along with current view. I was thinking of having this implemented as an accumulating snapshot fact table which would have some type 2 snapshot columns like snapshot effective date, snapshot end date, current row indicator. So if any of the claim line related attributes change, we insert a new record into the claim line fact table and expire the previous record by updating the snapshot end date and the current row indicator column. This would help track the history business wants to look at. And if the users want to look at only the current view, I can develop a view on top of this fact table using the current row indicator column of 'Y'.

I wanted to know if I am on the right track or is there any other better way of handling the claims data model?

Close. You need a transaction fact table with all the transaction history which you will use to populate your accumulating snapshot fact.

Thanks for the reply.

I am very new to modeling. Could you elaborate more on this one? Guess I don't seem to understand why do we need two set of fact tables one as transaction and the other as accumulating snapshot.

When I receive a claim for a service that was provided and say the provider was changed which resulted in inserting a new row. Can't I just have one fact table where you show the previous row with effective and end dates and then have a new row with new provider information and new effective and end dates with active flag set as Y?

rajeshwarr59 wrote:...So if any of the claim line related attributes change, we insert a new record into the claim line fact table and expire the previous record by updating the snapshot end date and the current row indicator column...

That's not a dimensional model. We only store metrics in a fact table. Things that provide context for the metrics go into dimension tables. That's also not how accumulating snapshots work. In an accumulating snapshot, you update dates that reflect the completion of events in a lifecycle process.

If you want to store all transactions, you'll need a transaction fact table. You then use the transaction fact to drive the loading of selected events in your accumulating snapshot fact.

In the case of a provider change, you would insert a new row in the provider dimension and update the fact table row. You would not insert a new row in the fact table.

If all you are worried about are changes in dimensional attributes, then implement the dimension as type 2.  You do not need to implement an accumulating snapshot fact. You will be able to retrieve any attribute value at any point in time.

If the changes involve measures and changes to the dimension reference then an accumulating snapshot fact is one solution.  Basically adding an effective period, with the effective timestamp as a member of the primary key, will allow you to capture versions of the fact over time.  There is a cost in the complexity of the update process, as you need to retrieve and expire the previous version, increases the time to load the fact.  This may or may not be an issue.

Depending on the frequency and volume of these changes, you may consider a 'current only' version of the fact to improve performance of what will probably be a majority of the queries.

Thank you all.

Yes, the requirement I have is to be able to track the changes in measures along with the dimension references. Considering the requirement, was I right with the design I talked about earlier of implementing it as an accumulating snapshot fact table. And build a materialized view over this fact table to provide the current view. So the fact table would have the following attributes- Claim Id, Claim Line Num, Claimand Id, Location Id, Service id,Provider id, Paid amount, Liability Amount, deductible amount, snapshot_eff_dttm, snapshot_end_dttm, active_flag. And for materialized I would just limit the records where active_flag='Y'.

Assuming I have a claim: 100 with two claim lines: if the claim was submitted by a provider:10 I would have a record in fact table :

Claim Id Claim Line Num Claimant Id Location id Service Id PrvrId Billed Amt Paid Amt Discount Amt Snapshot_eff_dt Snapshot_end_dt Active Flag
1000 1 1234 10 100 20 100 50 10 1/1/2010 12/31/9999 Y


100 1 1234 10 100 20 100 50 10 1/1/2010 1/17/2015 N
100 1 1234 10 100 20 75 50 10 1/18/2015 12/31/9999 Y


Current view

100 1 1234 10 100 20 75 50 10 1/18/2015 12/31/9999 Y

Is this the right approach how you model in situations like this?

This is not an example of Accumulating snapshot fact table. Looks like you want to create Fact Claim History keeping historical and current references to Claim attributes. Theoretically speaking, even though this is type 2 claim dimension, most of BI tools fails to manage dimension sizes greater than 2-5 million records and then you would also need to manage fragmentation if claim attributes are changing frequently than typical SCD. Approach seems to be fine as long as its Claim History Fact. I won't keep Active flag, rather create filter index where end date = 12/31/9999 or keep it NULL for current records.

For what it is worth, what is described is referred to as a 'timespan accumulating snapshot fact table' (see Design Tip #145 ).

Do not use NULL for current expiration dates, it makes queries more complex than they need to be. Having a high date value allows you to use BETWEEN when looking for a particular point in time. The current flag is fine. Again, it makes things clearer and easier to use.