Using MSBI for Claim Reconciliation

Using MSBI for Claims Reconciliation

Many insurance companies have recognized the need to improve the efficiency of their claims management process. They have streamlined processes, eliminated paper-based forms and redistributed work to match the demands to skills. The objective of their efforts is to lower costs, while also increasing overall throughput.

Scenario:
The customer is a huge multi-national insurance company and has a claims group to manage the claims process. When we talk about thousands of insurances being provided to various customers (general public and companies), we are also looking at thousands of claims being tracked and processed every day. And as we explained above, there needs to be a stringent process of claims analysis to do Claims Reconciliation.

The Objective:
The customer was looking for a solution that could solve his problem (explained below). He found a lot of tools that could solve only his individual problems but proved tough when it came to integrating with other tools. He wanted to look into the Microsoft BI Suite of products if that was a solution as he had seen one of the demos in tech-ed where the speaker repeatedly mentioned Microsoft Integrated BI Platform. Luckily I got a chance to work with this client on a proof of concept and in a 2 weeks effort we were able to solve his problems using the MS BI suite.
I wanted to share my experience working on this specific problem for the reconciliation process.

The Process:
Claims data was collected from 4 different sources. Details collected included low level details from Checks issued for a Claim to higher level data like the Account and the Program the claim belongs to.
Once data was received from these sources, the Analyst did the following activities:
· Used excel to extract data dumps from sources using queries.
· Find out the Programs that showed inconsistencies across the different source data.
· Then manually drill down from Program to Account to Claim and then to the Check level detail to find out the transaction to be observed.
· It was first assessed by the backend data analysts to see if the inconsistency was not due to any bad data load or extract.
· If not, it was reported to the concerned manager who passed it through the reporting hierarchy to the Program manager who then had to take some action if it was of concern.

The Problem:
As you can see, the process involved
· A lot of manual effort from the user to aggregate the data and to find out the inconsistent transactions from the thousands of transaction coming in everyday.
· The process was too cumbersome and time consuming as most of the things were done manually.
· By the time the results reached to the relevant people, it was too late further delaying the decision making process.

The Solution:
An integrated data warehouse was built collecting data from the 4 different sources. Confirmed dimensions were built for Claim, Account, Program so that it could be analyzed across the data. We used Integration Services to do the ETL.

Now came the challenging part of building the Analysis services Cube for this.
There was a lot of data to be reconciled meaning loaded, analyzed, corrected and re-loaded again before being viewed by peer groups. During the correction process, the analysts wanted to make changes on the fly and and wanted to see this changes reflected in the reports. If we had built just a MOLAP storage structure, it would have taken a lot of time due to frequent corrections and reloading leading to frequent processing, so we followed the below process:
·Created 4 measure groups for data retrived from each of the 4 sources.
·Created Month wise partitions across all measure groups with MOLAP storage and the latest month partition with ROLAP storage. This reduced great amount of time and effort in re-processing the partition whenever changes were made.
·Standard Summary reporting services reports were created on top of the integrated cube showing the aggregated check amount at higher and lower levels. Once data was loaded into the data warehouse and the cube processed, the reports could be viewed by any authticated user. The logic of integrating, aggregating and finding anomalies were all built into the solution. Analyst now had to just view the reports which had alerts showing these anomalies and not worry about manually finding them. This speeded the process of responsible stake holders to take faster actions and resolving issues quickly.
·Drill down reports were used to get into the respective claims and to the checks very easily and do the correction or report it.
·To get away with the requirement of analysts manipulating the amounts for the respective checks, we created Actions in the cube calling an asp .net web page passing respective parameters where they did the changes. The data manipulated directly reflected the data in the Data Warehouse and was soon available for reporting as the latest month data was stored in ROLAP model so the partition processing was very negligible.
·All this analysis was done from the Sharepoint interface where all the standard reports/views were deployed once the data was available. All users used this portal to view the application.

Conclusion
·The Actions in analysis services come in quite very handy. You can perform a variety of things like call any web page, drill down to database, etc. Certain complex requirements can be very easily handled.
·The data storage mechanism in analysis services provides designers to make a very robust and flexible design providing real time data changes to the data.
Hope you all enjoy reading this. Thanks