Anaytics Reporting Solution
Data Warehouse and Business Intelligence Solution for a Large Joint Venture Life Insurance Company
In partnership with a large Indian System Integrator, we have built a Business Intelligence solution for a large Indo-Japanese Life Insurance joint venture company in India.
The solution consists of:
- Data warehouse of prospects, proposals and policies
- Operational Data Source (ODS) that supports a few Internet applications
- ETL- Extract, Transform and Load data from Life Asia, a Life Insurance application on IBM AS400 platform
- Internet based BI and Analytical Reporting
Built using Microsoft platform, the solution uses:
- Reporting Services
- Analysis Services
- Data Integration
Highlights of the solution are:
- Very high volume of data loaded into Data warehouse and ODS every night within permissible time limit
- Complex data transformations
- Multiple data sources – As 400 (DB2) database and customized internet application that captures prospects and proposals data
- Large number of business users
Loyalty Profiling of Life Insurance Advisors
A one-time Analytics service project, with one of the largest Life Insurance companies in India, aimed at improving relationship framework for Insurance Advisors.
The project involved loyalty measurements by multiple variables:
- Length of contract
- Length of ‘active’ period
- Spells of ‘active’ periods
- Average duration of a spell
- Business value
- Width of business value – number of policies
- Depth of business value – average value per policy
- Number of customers servicing
- Customer acquisitions
- Customer churn
Using five years policy sales along with Advisors’ demographic data, Advisors were ‘clustered’ into a few groups and each group was characterized and labelled based on the commonality of behaviour.
Interesting insights were developed by further analysis of contribution made by each group to the total business value.
Life Insurance Policy Sales Data Mart Design Workshops for a US Compamy
For a very large Life Insurance company in USA, we conducted two workshops, one for business users and the second for technical team.
Engaged along with a large Indian System Integrator, the goal was to develop a high level data mart design.
During a four- day business users workshop:
- Business users were informed about data available and useful for a potential Analytics Solution
- Business performance measurements were discussed
- Analysis dimensions and their hierarchies were explored
- Key Performance Indicators (KPIs) were developed
The findings were structured into a potential schema design and documented.
At the second workshop with the technical team, the schema design was discussed along with the relevance of different elements.
The outcome of the second workshop:
- Validation of schema design from technical perspective, including debate on star schema v/s snowflake
- Mapping with source data tables
- Rules for data transformation and computation of KPIs
A detailed documentation was the final outcome of the workshops.