Near Real Time Earnings

Business Challenge

To calculate earnings on an almost real time basis and to reuse that calculation in whatever tool users employ to surface the data such as spreadsheets, reports, dashboards or other business applications.

Earnings calculations were being repeated in different places depending on the tool that was being used to surface the data, such as in reports or spreadsheets. This meant that the calculations were inconsistent and hard to update when changes were made. Of greatest concern was the ability to depend on the numbers being reported as there seemed to be more than one version of "the truth".

Solution

Build a data warehouse that collects data from the Reinsurance transaction processing system. Transform the data to capture useful business metrics around Premiums, Claims, Expenses, Commissions and include dimensions for analysis such as Time, Treaty, Line of Business, Underwriter, Balance Type Gross/Ceded/Net, Business Unit, Product, Earnings Method and more.

Enable the client to report on underwriting activity from one reliable, up to date data source.

Solution Architecture

  • Integration Services packages that extract Reinsurance and submissions data many times every day.
  • SQL Server Data Warehouse to store the data transformed and aggregated for analysis.
  • Analysis Services cubes to allow slicing and dicing of data across different dimensions.
  • Reporting Services reports over the Data Warehouse to allow for operational and management reporting.
  • Excel spreadsheets over cubes to allow for data access and ad-hoc querying.

 

Business Value Realized

  • Data is available and up to date whenever a user needs it.
  • Calculations for earnings are standard, repeatable and the business logic is in one place making it easy and fast to maintain.
  • Everyone who consumes the data use the same data source, so results don't differ.
  • Financial and Underwriting data is combined to allow for deeper analysis and better decision support.
  • Reconciliation of data between reports is now possible.

Typical Cost Range: $60,000 - $80,000

Cost Savings/ ROI

  • ~$10,000 User's time is not spent on data acquisition and manipulation.
  • ~$50,000+ Accurate, timely data allows for more effective decision making.
  • ~$20,000-$30,000 SQL Server software was already installed and available for use in this project so no additional software costs were incurred, instead the client's existing software investment was leveraged to provide further value to the business.
  • $Priceless! Establishing a relationship with an on-island software vendor that understands the business challenges, can provide thought leadership and vision with solution architecture addressing current and future needs, and who is genuinely invested in a partnering type engagement with the client.

Future Capability

  • The Data Warehouse structure can be extended to bring in data from other systems or sources.
  • Data extracts or comparisons could be created to allow for faster General Ledger reconciliation.
  • Operational and Management Reporting off existing or extended cubes.
  • Dashboards over the cube data to allow for Operational monitoring, Executive information, Client facing data, collaboration, self-service business intelligence.

The Team


Architect


Project Manager


Business Intelligence Consultant


Business Intelligence Consultant



Know someone that could use this solution? Share this page