Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Data Warehousing for Actuaries

Kevin Pledge
October 17, 2000

Data Warehousing for Actuaries

Kevin Pledge

October 17, 2000
Tweet

More Decks by Kevin Pledge

Other Decks in Business

Transcript

  1. What is a Data Warehouse? What should a Data Warehouse

    Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
  2. What is a Data Warehouse? What should a Data Warehouse

    Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
  3. Data Warehouse Definitions “a copy of transactional data structured specifically

    for query and analysis” “An infrastructure for analytical functions”
  4. What is a Data Warehouse? What should a Data Warehouse

    Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
  5. 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
  6. 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
  7. 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
  8. What is a Data Warehouse? What should a Data Warehouse

    Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
  9.   “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
  10. 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
  11. 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
  12. 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
  13. 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
  14. Improved distribution of information Wider access to actuarial data Dynamic

    management information Thin client distribution
  15. What is a Data Warehouse? What should a Data Warehouse

    Project deliver? Why undertake a Data Warehouse Project? How is this achieved?
  16. • Understanding of business processes and data warehouse design •

    Train actuarial staff to develop applications
  17. End User Expectations Report Reading Drill Down Customize Spreadsheet Modify

    Apps App Design Continuous overlap of user requirements
  18. Further Discussion • Enterprise warehouse integration • Web delivery •

    Risks and Limitations of a Data Warehouse Project • Process considerations
  19. Further Information Presenter Kevin Pledge (416) 429-2692 [email protected] Web •

    www.insightdecision.com Books and Articles • Ralph Kimball’s Data Warehouse Life-Cycle • Data Warehousing for Actuaries
  20. End

  21. 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
  22. 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
  23. 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
  24. 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
  25. 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