Claims Processing: Transaction Fact Question

[vinny75]

Folks

Reading up on Kimball DW Toolkit and I am trying to understand the part where he suggests the methodology for Claims Processing:

Businss Process is Claims Transactions with three fact tables, Claim Transactions, Claim Accumulating Snapshot, and Accident Event. The dimensions are the same except that there is no Employee associated with the Accident Event. In addition, there are dimensions for Claim, and Claimant (or Claim Party(ies))

My question is, if I follow star schema and point the surrogate keys from each dimension to the Claim Transaction Fact, am I creating new fact record everytime a change happens with an associated dimentional attribute? Wouldnt that grow exponentially if history is kept on the fact?

A claim can have many claimants and each claimant can have many transactions (Receivables/Payables/Salvage). So a change in some claim attribute will result in regeneration of every claimant and their transaction record in the transaction fact. Did I understand this correct?

Also what does he mean by "Transaction Type Dim"? Is this a single view of all kinds of Transactions such as Receivables, Payables Salvage etc?

Please advice.

Thanks
Vinny

 

[johnherman]

Working backwards on your question list.

The TransactionType dimension contains valid types of transactions as you have noted. It will depend on your business process as to what constitutes a transaction (appraisal may/maybe not a transaction. Telephone contact with witness may/maybe not a transaction.)

The fact table is each individual transaction on each claim, so each transaction for each claimant for each claim (and perhaps for each coverage type) will have a transaction. These transactions are linked via key to the value of the dimensions at the time the transaction occurred. If the value of one of the attributes (dimensions) changes, this would be a new transaction.

The claim transaction fact table will grow and grow rapidly. You need to contact your legal department and user community to determine the retention period for these records. As you know, claims can be reopened at any time during the statute of limitations for additional payments, recoveries, or litigation.


-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[vinny75]

Hello

Thanks for your response. I plan to check the retention time requirement from the business. If I understand this correctly, the Transaction Type Dim is essentially a VAL TABLE (Lookup Table sortof) which lists the possible transactions types that can happen with regards to a claim. So it will be like Receivable (Salvage, Refund of Expense,Collateral etc) and Payments (Salvage Expense, Loss, Punitive damages etc). That said, I should have individual Dimentions defined for attributes related to Reserve, Receipt, Payment, Salvage respectively and then link to Claim via transaction fact along with the Transaction Type Key from the Transaction Type Dim. Fact will have the transaction amount whose context will depend on the key to transaction type dim.

Am I correct?
one way of controlling growth on this fact is to identify dimentional attributes that can be Type1 instead of Type2. This way we wont create new Dim record and hence wont need to recreate facts linking all other unchanged dimentions.

Thanks again. Please confirm.

Regards
V

 

[johnherman]

Yes. I think you're definitely on the right track. Be careful to make your transaction types as granular as possible, and, if necessary, group them using a "transaction group" or "transaction category" into Payments, Expenses, Receivables, Non-financial, etc.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[vinny75]

Hi

Thanks again. I am beginning to like the methodology. We recently implented a system for policy handling (without claims portion) and I think this approach will be a lot better than that.

If I am going to create RECEIPT, PAYMENT, RESERVE AND SALVAGE dimensions, wont the rowid of these dimensions be NULL in the transaction fact when the transaction type is not related to it? Is that ok? I could have 10 payment transactions to a claimant and all those transaction fact records will have null rowid value for receipt, reserve and salvage except for payment. In the context of transaction type it may be immaterial but is this acceptable?

Thanks
V

 

[johnherman]

yes, acceptable

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[vinny75]

Hi John

In the same context, events within the check processing such as INVOICE_DATE,PAYMENT_STOPPED_DATE, CHECK_VOIDED_DATE, PROCESS_DATE etc..these dates can be included in the fact table and filled in depending on Context of the transaction. I could create a payment transaction type with subcategories for each of the above events. Would that make sense?

or should I just treat these dates as attributes of payment dimension and just keep the amount field in the fact?

Thanks
v

 

[johnherman]

Dates which signify events can be part of a fact table. If there is no money, they can be known as factless fact tables. I prefer to call them event facts. These dates will have a surrogate key to the Time Dimension.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[vinny75]

John,

I an in te process of creating the accumulating snapshot for claim that contains several claim relasted business metrics such as Gross, Incurred and Net Reserves etc. Some are at claimant level and some are at claim lever. In creating the snapshot, do we need to create a dimention with hierarchy establishing claim and child claimants?

Ive never done this but I wonder if it makes sense.

Please advice
V

 

[johnherman]

If you want to create a single accumulating fact table, you will need to unify the claim and claimant into a single hierarchical dimension; a parent(claim) and child (claim party or claimant).



-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[vinny75]

John

I may need some additional clarification regarding claim accumulating snapshot. In my current design, I do not include Claim Status in the claim dimension. Instead, my accumulating snapshot has all the details of the claim including current status, when opened, closed, all current financial numbers roll forwarded at the the end of the day. Now my thought was that this accumulating snapshot should be built everyday for every claim that is not closed yet.

But when I read the book from kimball (page 323) datawarehouse toolkit 2nd edition, he says

"Claim Accumulating snapshot row is created once when the claim is opened and then is updated throughut the life of the claim until it is finally closed".

I do not update my snapshot table. Instead, the row is created everyday holding the claim picture as of that day. did I miss something here?

 

[vinny75]

John

I went ahead and implemented a small change to the claim accumulating snapshot. I removed child level measures from the above snapshot so that this table will only hold all claim centric financial numbers for every claim everyday.

The above measures are also reported granular level of claimant and vendor. TO capture that I am building a separate fact table. This fact will have a TYPE2 process written around it and wont be a daily build for every claimant. It will be rebuilt if there was a change for that specific claimant or vendor. This way, the volume is kept on check. What do you think?

Offocourse if you add all claimant and vendor level measure, it will rollup and match with the number reported under claim snapshot.


V

 

[johnherman]

If it works, it works. What I think is less important than whether it meets the needs of the user commumnity. If its working for them, its working properly.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[vinny75]

Thanks. Well I dont know what the user response will be after I roll out reports written on top of this datamart. At this time, all I am doing is conceptually looking ahead on what they may want to report on and at the same time, keeping in mind the volume. Hope the above plan proves to be good.