Insurance Domain Competency

Capabilities and Experience
This document demonstrates the Insurance Domain Competency of Rightpath Computer Technologies Pvt.Ltd. The competency spreads across various IT functions of the domain covering development and maintenance of Business Intelligence Suite, internet based policy management systems, consumer facing self service websites & various operational workflows. This document lists and describes some of the key projects Rightpath has executed and successfully delivered for the Insurance domain.
2016 Rightpath Computer Technologies Pvt.Ltd. 12/1/2016.

Business Intelligence Suite
Client Introduction:

Rightpath has developed a complete business intelligence suite for a specialty insurance company operating for more than 6 years. They have a diversified portfolio of property and casualty coverage and surety bonds serving niche markets.. Rightpath has executed several projects as part of the Business Intelligence development for this customer. These projects have been listed below sequentially to show the progress of the suite.

Project: Oracle to SQL Server Migration
Business Case: Reduction in licensing cost and consolidation

The requirement of the project was to move the existing Data Warehouse from Oracle to Microsoft SQL Server. The existing ETL job stream loading the Oracle Data Warehouse built in Business Objects Data Integrator was needed to be switched to loading the new SQL Server Data warehouse. In terms of execution timeline the migration took 3 months to complete and in terms of efforts it needed about 700 Hours involving resources with technical expertise in SQL Server and Oracle Database, Business Objects DI and Business Objects Reporting.

Challenges:

The challenges that were faced during the execution and implementation of the project were

Once all the issues related to the database migration and the ETL tool to load the data in the SQL server database were resolved, both systems were run in parallel for 4 weeks to ensure 3.
there are no differences between the Oracle and the SQL Server warehouses. The Oracle warehouse was shut down once the data integrity was achieved.

ETL Projects using SSIS

To ensure maximum ROI for the customer from migration from Oracle to SQL Server, Rightpath proposed the usage of SSIS as the ETL platform for all upcoming ETL projects. Rightpath Architected the system in such a manner that the current BO DI ETLs would continue to run and new ETLs to be developed in SSIS integrated well into the Data Warehouse landscape of the client. Once the client is convinced that SSIS can deliver better performance, the migration of all the BO DI ETLs to SSIS would commence. Below is the overall architecture that was proposed for the Data Warehouse

The Solution

The data warehouse architecture for the ETL’s is designed in a manner to collect data from multiple sources and systems into staging area in the form of daily snapshots of the source systems. A very accurate data history is built using these daily snapshots of the source data. This history is the core foundation for the ETL process. It gives the flexibility of reloading any period of data into the warehouse. The data is moved from these history tables to a Dimension-Fact based data model. For reporting, Business Objects XI R2 is used where a BO universe is defined for all the entities and attributes present in the warehouse. The dataflow to the warehouse is represented in the picture below.

Dimension Processing

Dimension processing is the first unit of execution. The dimension needs to be populated first so that the fact records can be associated with the respective dimension keys. The daily snapshot of the data is first bought in the staging area call Execution Control Region. The history is built using the daily snapshot. Each dimension is classified into.

Type 1: The values associated with keys are updated if a change is encountered.
Type 2: A new record is entered with new dimensionality when a change is encountered. This record is effective based on current transaction date. The previous record associated with this business key is closed using the current transaction date. This ensures that for each business key there is a proper timeline maintained. The SRC area is used to maintain the timelines for the records. This holds the information for each business key’s effective period and its association with the warehouse surrogate key.

Fact Processing

Fact processing starts after the dimension processing is over. In this stage all the business data related to $$$$ amounts is processed and is stored into the key file. The key file contains the transnational data with their business keys replaced by dimension keys by doing lookup on the SRC region to associate the correct keys. This association is time based to ensure whenever the jobs are run they get the correct warehouse key considering the transaction date and time.

Enterprise Reporting Data mart

This is the final schema where the business data is finally stored in a true fact-dimension model with aggregation. Here also the dimension data is processed first followed by the key file data to ensure referential and data integrity is maintained. There is a Business Objects universe that is defined on top of this schema which gives the users a business terminology based interface to generate/create the required reports from the warehouse. There are some standard static reports that are refreshed on a daily basis after the ETL is over and are emailed to the respective groups and departments.

The strength of this system lies not only in the architecture but also in the manner the ETL’s are designed. The ETL’s were made smart enough to ensure there is no data corruption if the jobs are re-run. So in effect it is like data synchronization between History data and the warehouse data. By implementing this feature in the ETL the loads were almost hassle free as even if any job goes down due to external reasons like link down, the jobs can be re-run and they would synchronize the data.

Various ETL projects (with their effort and execution timeline) we have implemented so far in SSIS for this client are listed below:

Project:
OLAP and Reporting Solution using Microsoft SQL Server Services
Going forward with the intent to maximize the ROI, Rightpath proposed to build the cube solution on Microsoft SQL Server Analysis Services (SSAS). The issues with the current reporting system as stated by the client were

The intent was to develop a cube based reporting system built in SSAS and Microsoft SQL Server Reporting Services (SSRS) that provides multidimensional view of the data over the period of ten years. Thus, the cubes will help the client management to create different reports related to Submissions, Claims and Premium data that can effectively be used for Business Intelligence.

The Solution:
Rightpath developed OLAP cubes that provided a consolidated policy data for Submissions, Premium and Loss (Claim). The designed cubes cater for aggregating high volume data for optimal performance of adhoc reporting. The reports developed using SSRS provided user with interactive reports for management for various reporting and analysis needs. The users we provided with an online tailor made report creation facility using multidimensional cubes. Process to automatically refresh the cubes was implemented minimizing user intervention
The cubes are intended to provide following comparative information over the period of ten years. The reporting period can be Monthly, Annual or Year to Date:

The reporting solution is currently being enhanced to meet the BI needs of the client

Project: SharePoint Projects

Some of the SharePoint based operational workflows that were built for this client are described below:
SOX Compliance Enabler/Internal System of Controls:

Internal System of Controls is SharePoint (MOSS 2007) based intranet application that tracks audit results for SOX (Sarbanes-Oxley) compliance. The application is primarily intended to be used by SOX auditors, who are responsible for reviewing, testing and archiving test details of various SOX processes in the organization. This application provides the ability to define various processes by departments, identify and track risks, define tests and track test results. Features of this application include – various dashboards for quick summary of details, allows easy access to various utilities like document comparison, print preview, exporting data to Excel and taking system snapshot. The application uses SharePoint’s Active Directory based authentication and implements user profile-based access control.

Leave & Absence Manager:

This SharePoint (MOSS 2007) application manages absences for all employees of a company. The absences cover both types, that is, absences which are leaves (that are accounted for) and also that merely are meant to inform (of absences). The absence types can also be set to have approvals. Department assistants can apply for and approve absences on behalf of other employees. Emails can be sent informing of events (leave applied for, leave approved etc). A calendar view shows the leave status by users and by departments. This application has been heavily customized with added workflows and views to meet the needs to medium to large organizations

Training:

A SharePoint (MOSS 2007) site that can be used to manage training events. The users can register for training, and the registrations can be set so that they need supervisor’s approval. Training events can also be set so that only a subset of users can view and register for such events. A registration adds the event to the user’s calendar

Incentive Program for Management

A SharePoint (MOSS 2007) based application that lets users set goals for a year. At any time they can also set progress against each of these goals, which can be used to calculate incentives. The goals are set in InfoPath forms so that they can be saved multiple times before submitting for approval. The InfoPath forms can be opened in a browser, eliminating the need to have an InfoPath client on each user’s machine. Periodic review (approve/reject) cycles of all the forms can be done per year. An admin group can create and also do “on behalf of approvals” of the forms in the absence of employees to keep the process going. Alerts via emails help keep the approval process moving. Various views help everyone know the status of form filling and approval. A tight MOSS 2007 based security helps restrict views of the forms

Succession Planner:

Every organization needs to maintain a succession plan. This SharePoint (MOSS 2007) application maintains a succession plan, with a slick tree view. A very strong security feature (by encryption) ensures restriction of the view of the succession plan even to the SharePoint administrator. Each member of the succession plan can fill in an experience and development profile that can be approved by executives.

Forms Application:

A very important feature of MOSS is the ability to fill in feedback forms. However it’s very hard to be able to choose a large number of users from the AD who needs to fill the form. This application lets a user easily add/edit a set of users to multiple document libraries and then pass on a common link to these users, so that they can fill the forms. Views let the administrator see who have filled the forms, and who are yet to fill. This process can be repeated every year. A user cannot see the contents of the forms of the other users.

Project:

Business Intelligence Dashboard and Reporting application built on SharePoint

To build a BI application which provides strategic and operational information for the decision-makers of the company on the company intranet built on SharePoint (MOSS 2007)

The specific requirements for the project are listed below:

The Solution

The solution was architected using SharePoint as the presentation layer, SSRS (SQL Server Reporting Services) to build complex reports which are in turn composed of multiple sub-reports catering to user needs. SSAS (SQL Server Analysis Services) was used for building OLAP Cube for quick response for the custom reports. The reports accepted parameters from the user interface/presentation layer (SharePoint) and provide the output in the desired format chosen by the user. The cubes are refreshed using a schedule processed through workflows developed in SSIS (SQL Server Integration services)

Insurance Domain Competency Rightpath.

Policy Management System for Underwriting and Claims management Client Introduction
This client is one of the major Insurance carriers in New Zealand, operating in Workers Compensation coverage for the healthcare industry.

Business Case:

To build a comprehensive Insurance Policy Management System with features to manage business tasks for underwriting and claims management.

The Solution:

The system was designed to govern the layout of the individual application sub-systems of policy system. It also focuses on the design process that the individual sub-systems will implement, as well as the common architectural components that will be shared/ utilized across multiple sub-systems of the portal.

Insurance Domain Competency Rightpath.

IPMS system is developed using N-Tier architecture using latest MS .Net platform and uses cutting edge Microsoft technology. The project size is estimated at ~15000 person hour’s effort and took close to 20 months to implement this.

The functions that this system covers are listed below:

Dashboard
Quote Search
Quote Information
Renew Quote
Quote Endorsement
Quote History
Guaranteed Cost Quote
Quote for Large Dollar Deductible (LDD), Small Dollar Deductible (SDD), Retention, Advantage, Dividend and Retrospective

Policy with

Policies for Guaranteed Cost, Large Dollar Deductible (LDD), Small Dollar Deductible (SDD), Retention, Advantage, Dividend and Retrospective
Cancel Reinstate or non renewals
Policy Change History
Bureau Reporting
Standard and Change Endorsements
Printing

Manage Loss Prevention for

Facility Evaluation
Service Log
Scorecard
SMAP

Audit which includes

Premium Change Summary
Audit Diary

Billing with

Billing Summary
Retro Billing
Cash Application
Collection
Check Request
Deposits

Insurance Domain Competency Rightpath.

Insurance Domain Competency Rightpath.
Management System for Third Party Administrators & Insurance Agents Client Introduction

One of the largest brokers in Illinois, they provide full range of brokerage services and products encompassing employee benefits, property and casualty and retirement programs. They are one of the largest licensed third party insurance administrators and general agents.

Business Case:

Build a web based internet application catering to Life, property, Firefighters and Employee Benefits insurance used by brokers, underwriters and support staff.

The Solution Rightpath built the application in Microsoft .NET implementing the following key features into the application

Insurance Domain Competency Rightpath.
Self Service Consumer facing website for one of the largest insurers in Australia Client Introduction:

One of largest insurers in Australia and South Africa, they were the first Australian insurer to offer the 10% of premium paid over three years as cash back bonus under in case of three years in a row without a claim. They have many such firsts to their credit, another being the first insurer to provide a complete self service website to the customers.

Business Case:
Build a web based application with two parts:

• Public Site – Caters to public viewing with generalized information, premium quoter, and public marketing campaign
• Private Site – Caters to members and business needs

• Account access
• Targeted Marketing campaigns
• Online customer service
• Payment processing
• Product/service offers

The Solution:

The application consists of two main physical sites, namely the public and private sites.

The Presentation layer for the overall solution provides both .NET web-based forms for the Self Service website and windows-based forms solution.

Insurance Domain Competency Rightpath

Copyright 2016 Rightpath believes the information in this publication is accurate as of its publication date; such information is subject to change without notice. Rightpath acknowledges the proprietary rights of the trademarks and product names of other companies mentioned in this document
Rightpath is a truly global, full service, IT consulting, services and project delivery organization. We combine superior project management, functional and technical expertise with our process driven global delivery capabilities to deliver high quality, cost-effective guaranteed solutions to enterprises worldwide.
Our consistent track record of meeting and exceeding customer expectations is the result of our customer-centric philosophy. This philosophy establishes a value proposition focused on long-term relationships and delivery of value added services.