Data Warehousing for Actuaries

Transcript

1. Kevin Pledge 17 October 2000 Canadian Institute of Actuaries General Meeting WS-47 Data Warehousing
2. What is a Data Warehouse? What should a Data Warehouse Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
3. What is a Data Warehouse? What should a Data Warehouse Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
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 is a Data Warehouse? What should a Data Warehouse Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
7. What should the Data Warehouse project deliver?
8. 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
9. 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
10. Metadata “data about data” • Consistent definitions applied to data • System data
11. 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
12. Improved processes • Clearly defined processes • Better process integration • Easier staff rotation and cross training
13. What is a Data Warehouse? What should a Data Warehouse Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
14.   “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
15. 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
16. 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
17. 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
18. 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
19. Improved distribution of information Wider access to actuarial data Dynamic management information Thin client distribution
20. Savings Cost savings Control workload on staff
21. What is a Data Warehouse? What should a Data Warehouse Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
22. • Understanding of business processes and data warehouse design • Train actuarial staff to develop applications
23. Where do you start?
24. Where do you start? Source System
25. Where do you start? End-user requirements
26. Where do you start? Logical Data Model
27. End User Expectations Report Reading Drill Down Customize Spreadsheet Modify Apps App Design Continuous overlap of user requirements
28. Further Discussion • Enterprise warehouse integration • Web delivery • Risks and Limitations of a Data Warehouse Project • Process considerations
29. Further Information Presenter Kevin Pledge (416) 429-2692 kpledge@insightdecision.com Web • www.insightdecision.com Books and Articles • Ralph Kimball’s Data Warehouse Life-Cycle • Data Warehousing for Actuaries
30. End
31. 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
32. 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
33. 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
34. ETL Success Rate Extraordinary (1%) Great success (22%) Success (45%) Minimal success (23%) Failure (9%) BACK
35. 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
36. 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