The Managing Agent, which ran both general insurance and life syndicates, identified a need for a Data Management strategy and a Data Register, and commissioned a Discovery & Profiling and Management Information System project as part of its preparations for Solvency II compliance and EDW development.
My role was to complete Discovery & Profiling and subsequently help to develop some of the specific deliverables required for Solvency II purposes:
Glossary / Data Dictionary
Data Quality Management System
Internal Model Data Management System
Discovery & Profiling for the Global Property, Personal Lines, Life and Reinsurance divisions.
I had been asked by Jubilee to complete a Discovery and profiling exercise for each Jubilee major business area, mainly for Solvency II purposes but also to support the planned Electronic Data Warehouse (EDW) development by the MI workstream. Using the IMM (Integrated Modelling Method), I completed the underwriting and reinsurance areas, and delivered PowerPoint presentations covering these topics:
Business Function decomposition
Conceptual Data Models
Key Business Entity Life Cycles
External Documents and Electronic Messages
MI Catalogue within the business area
Business Process use of Systems
Application Systems used within business area
Systems Schematic
Key Data Controls
Initial set of Data Quality Rules
About 40 process and systems control risks and issues
For each underwriting area, CRUD diagrams (cross referencing major business functions and entities)
Glossary / Data Dictionary
As part of the Discovery and Profiling exercise, I compiled a business glossary for all the terms encountered for Solvency II compliance purpose. Subsequently this was extended to include most underwriting system data items, and to cover the more technical metadata detailed below. The purpose was not only to meet Solvency II requirements but also to help to develop the tests required for the Data Quality Management System implementation. They were developed within the Solvency II submission timetable. The Glossary I compiled covered the following areas:
Global Property, Personal Lines, and Life Underwriting, Reinsurance, Claims and the Internal Model
About 600 Lloyd's, insurance, accounting and actuarial business terms were defined, with the internal or external source of the definition
About 600 underwriting system data items were documented with the source underwriting system screen location and database table
Data Quality Management System (DQMS) tests definition and implementation
During the initial Discovery and Profiling exercise, I agreed with the underwriters an initial set of Data Quality Rules and Tests for their data. These tests were subsequently extended for Claims. I also tested and implemented most of the tests within the Solvency II submission timetable, and subsequently further refined the Life Tests. In summary, the following Tests on Solvency II critical policy data items were developed:
14 Reinsurance Tests
27 Global Property Tests
24 Personal Lines Tests
36 Life Tests
16 Claims Tests
Internal Model Data Management System (supporting the Calculation Kernel)
Initially I helped to develop the first evolution of data documentation of the Internal Model by providing quality assurance of the data model products which were maintained in an Embarcadero repository. Subsequently, many of the underlying spreadsheets changed, and I developed a simpler approach using SharePoint and control sheets within the spreadsheets, which will be easier for the business to operate for future SCR submissions. I also set up all the control sheets required for the 31/10/2011 dry run submission. In summary:
New Internal Model SharePoint library system was designed to capture spreadsheets feeding the Calculation Kernel
New Spreadsheet Control Sheet design was developed to support Solvency II evidential and control requirements, including DQMS data quality
All Spreadsheets were cross referred to the Data Lineage Diagram
90 spreadsheets were corralled for the dry run Final SCR submission, and all Control Sheets completed with the process and data control evidence
Business benefits:
The following business benefits were achieved:
The Managing agents’ use of key application systems is documented at a business function and process level and key control risks and issues logged
The underwriting processes use of data, both external and internal, is much better understood and the key control risks and issues logged
Business and data terms are documented in an easily accessible Glossary meeting both Solvency II and general business requirements
Underwriting data is fully documented at conceptual, logical and physical level meeting both Solvency II and EDW development requirements
The DQMS system was adopted by the Reinsurance and Life teams, rules enhanced, and erroneous data corrected. Other UW teams will follow
Internal Model spreadsheet based processes, controls and library management system are documented for Solvency II compliance purposes
90 Internal Model Spreadsheets for the Solvency II dry run Final SCR submission were fully documented
Lessons learnt:
Discovery and Profiling needs to be extended to the new Political Risk underwriting team, and updated to reflect the new Global Property underwriting strategy
The DQMS approach to improving Data Quality is the single most important outcome of the Solvency II compliance programme and needs to be fully embedded
The syndicate needs feedback from regulators before changing any of the meta data documentation or the developed Internal Model Spreadsheet systems
It’s important to start eliminating some of the 90 plus Internal Model spreadsheets and resist the temptation to gold plate the spreadsheet control system
The priority should be to streamline the Earnings, Triangles and Reserving data streams, then the Business Planning area which is less data intensive and time critical