Data Warehousing (NFCA)

Presentation on data warehousing given in 2002 to the National Fraternal Congress of America (NFCA), now the American Fraternal Alliance. Three sections:

1. What is a Data Warehouse?

2. How can they be used in insurance?

3. Pitfalls and Problems

Transcript

1. NFCA meeting
3. What is a Data Warehouse? How can they used be used in insurance? Pitfalls and Problems
4. Data Warehouse Definitions “a copy of transactional data structured specifically for query and analysis” “An infrastructure for analytical functions”
5. The Data Warehouse Structure
6. What should the Data Warehouse project deliver?
7. What is a data warehouse? Source Systems ETL Metadata Third party systems Data Storage OLAP Cubes Users “Complete analytical infrastructure”
8. ETL
9. Data Warehousing in insurance
10. a natural fit for the insurance industry • The industry is built on information and statistics • Critical for departments to work together to deliver complete customer experience • Companies typically have multiple specialized systems • Insurance products are longer term than the administration systems that support them
11. The case against • Cost and reputation • The breadth of any such project • Skill sets of existing staff • Insurance data warehousing has more in common with CRM analytics than retail data warehousing.
12. Critical success factors • Remove application risk • Recognize functional interrelationships
13. The “business intelligent” company The “Business Intelligent” Organization • Changes in methodology / approach • “Real” goal oriented • Shared information – common goals
14. Changes in approach • Flexible – real time investigation • Faster turn-around – real time benefits • Slice and dice to find cause
15. Interrelationships Movement reporting vs. experience studies • Both use some measure of status change and inforce • Inforce over period vs. inforce at point in time • Start/end value vs. value at incidence
16. Interrelationships Experience studies and earnings by source • Both use actual to expected • Definition of expected – fixed table vs. assumption • Incidences – last vs. all
17.   “Spider web” extracts created “Spider web” extracts created   Inconsistent information Inconsistent information   Redundant extracts Redundant extracts   High maintenance cost High maintenance cost   Too personal, each step owned by a person Too personal, each step owned by a person Operational Operational Systems Systems Operational Operational Data Data Spider Web Spider Web Extracts Extracts Hand Hand- -coded coded Logic Logic “Point” DSS “Point” DSS Solutions Solutions Vantage CAPSIL CAMRA Extract1: APL Extract2: COBOL Extract3: APL ALM Mortality Lapse TAS Access APL Valuation Axis TAS APL
18. Reasons you may need a Data Warehouse – Broad headings Single source of consistent data Process Improvements Limitations of existing systems Improved distribution of information Savings
19. Temporal Analysis Allows analysis to be analysed by • State duration (e.g. Policy duration inforce), • Temporal Selection (e.g. inforce at a specific time), and • Transition detection (e.g. new business).
20. Events over time Jan Mar Feb Policy 1 Policy 3 Policy 2 Exit Re-entry Exit Exit Exit Re-entry New New Monthly Over Quarter Policy 1 2 exits, 1 re-entry 1 exit Policy 2 1 new, 1 exit 1 new, 1 exit Policy 3 1 new, 1 exit, 1 re- 1 new
21. Dimensionalized measures Measures are dimensionalized: Analytic (9)– e.g. Sum at start, entries, A/E Analysis by (10) – e.g. Policy Count, Premium, Risk Amount Ratio by (6) – e.g. Policy Count, Sum Assured Three dimensions instead of 540 measures
22. Single source of consistent data Remove personal ownership of data extracts Add (consistent) definitions to data Combine data from various source systems Combine asset and liability data into one system
23. Process improvements Improve integration between various functions Provide data to actuarial applications Ability to track and organize data Predictable volume of data Reproducibility of results
24. Limitations of existing systems Extract improvements Ability to access enterprise data Additional or improved applications Combine existing data marts into a single strategy Ability to expand published results Audit-ability
25. Improved distribution of information Wider access to actuarial data Dynamic management information Thin client distribution
26. Savings Cost savings Control workload on staff
28. Before After
29. System Overview – End User Easily customized to meet specific needs. Delivery Options :  Web  Excel  Third-party tools
30. Further Discussion • Enterprise warehouse integration • Web delivery • Risks and Limitations of a Data Warehouse Project • Process considerations
31. Further Information Presenter Kevin Pledge (416) 429-2692 kpledge@insightdecision.com Web • www.insightdecision.com
32. End
33. Star Schema BACK Policy Fact Table Extract Date (FK) Product Code (FK) Jurisdiction (FK) Policy Id Date of Birth Issue Age Issue Date (FK) Sum Assured Annual Premium Reserve Time Dimension (1) Extract Date (PK) Year Quarter Month Product Code Dimension Product Code (PK) Product Name Product Description Product Type Product Fund Product Group Jurisdiction Dimension Jurisdiction (PK) State or province Sales Area Country Time Dimension (2) Issue Date (PK) Issue Year Band Year Quarter Month
34. Star Schema - snowflaked BACK Policy Fact Table Extract Date (FK) Product Code (FK) Jurisdiction (FK) Policy Id Date of Birth Issue Age Issue Date (FK) Sum Assured Annual Premium Reserve Time Dimension (1) Extract Date (PK) Year Quarter Month Product Code Dimension Product Code (PK) Product Name Product Description Product Type (FK) Jurisdiction Dimension Jurisdiction (PK) State or province Sales Area Country Time Dimension (2) Issue Date (PK) Issue Year band Year Quarter Month Product Type Dimension Product Type (PK) Product Fund Product Group
35. Metadata Implementation Have plan, but have no yet implemented (21%) Recognised the importance, but no plan (46%) Not Addressed, no plan (14%) Have implemented metadata (3%) Have plan and started to implement (16%) BACK
36. ETL Success Rate Extraordinary (1%) Great success (22%) Success (45%) Minimal success (23%) Failure (9%) BACK
37. OLAP Market share Vendor 1999 1998 1997 1 Hyperion Solutions 23% 29% 25% 2 Oracle 11% 17% 21% 3 Cognos 11% 10% 11% 4 MicroStrategy 8% 7% 5% 5 Microsoft 8% - 6 Business Objects 5% 4% 4% 7 Comshare (incl Essbase resales) 3% 5% 8% 8 Applix 3% 3% 3% 9 IBM 3% 2% 10 Sterling Software 3% 3% 2% BACK
38. Benefits of the System • Time savings: – automation, – set-up done once for all staff, – removes the need for cross checking • Consistency of data: – Everyone using the same definitions and same underlying data. – The same data can feed the valuation system – External business rule set can make up for short comings in the administration system. – Transformations in one place, universally agreed and easy to understand.
39. Benefits of the System • Comprehensive reporting • Flexible – Preformatted reports can be produced on a regular basis, – Can also be customized by the individual user. • Sharing of information • Customer analysis • For experience studies: – You don’t need to set the parameters in advance, – Monitor experience from the same system that you use for the analysis
40. Extract, Transformation and Loading • Manages and monitors all data moves and transformations • Removes personal ownership of data extracts • Provides audit trail • Clearly defined extract process
41. Organized Storage • Must be scalable and expandable • Combine asset and liability data into one system • Star schema Policy Fact Table Extract Date (FK) Product Code (FK) Jurisdiction (FK) Policy Id Date of Birth Issue Date (FK) Sum Assured Annual Premium Reserve Time Dimension (1) Extract Date (PK) Year Quarter Month Product Code Dimension Product Code (PK) Product Name Product Description Product Type Product Fund Product Group Jurisdiction Dimension Jurisdiction (PK) State or province Sales Area Country Time Dimension (2) Issue Date (PK) Issue Year Band Year Quarter Month
42. Metadata “data about data” • Consistent definitions applied to data • System data
43. Integrated suite of reusable applications • Appropriate data design • Various types of OLAP tools – ROLAP, MOLAP etc • Various methods of distribution to suit various end-users Policy Fact Table Extract Date (FK) Product Code (FK) Jurisdiction (FK) Policy Id Date of Birth Issue Age Issue Date (FK) Sum Assured Annual Premium Reserve Time Dimension (1) Extract Date (PK) Year Quarter Month Product Code Dimension Product Code (PK) Product Name Product Description Product Type Product Fund Product Group Jurisdiction Dimension Jurisdiction (PK) State or province Sales Area Country Time Dimension (2) Issue Date (PK) Issue Year Band Year Quarter Month
44. What is a Data Warehouse? What should a Data Warehouse Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
45. Improved processes • Clearly defined processes • Better process integration • Easier staff rotation and cross training