Share
Embed
Transcript
36 slides
-
Kevin Pledge 17 October 2000 Canadian Institute of Actuaries General
Meeting WS-47 Data Warehousing -
What is a Data Warehouse? What should a Data Warehouse
Project deliver? Why undertake a Data Warehouse Project? How is this achieved? -
What is a Data Warehouse? What should a Data Warehouse
Project deliver? Why undertake a Data Warehouse Project? How is this achieved? -
Data Warehouse Definitions “a copy of transactional data structured specifically
for query and analysis” “An infrastructure for analytical functions” -
What is a Data Warehouse? What should a Data Warehouse
Project deliver? Why undertake a Data Warehouse Project? How is this achieved? -
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 -
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 -
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 -
Improved processes • Clearly defined processes • Better process integration
• Easier staff rotation and cross training -
What is a Data Warehouse? What should a Data Warehouse
Project deliver? Why undertake a Data Warehouse Project? How is this achieved? -
“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 -
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 -
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 -
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 -
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 -
Improved distribution of information Wider access to actuarial data Dynamic
management information Thin client distribution -
What is a Data Warehouse? What should a Data Warehouse
Project deliver? Why undertake a Data Warehouse Project? How is this achieved? -
• Understanding of business processes and data warehouse design •
Train actuarial staff to develop applications -
End User Expectations Report Reading Drill Down Customize Spreadsheet Modify
Apps App Design Continuous overlap of user requirements -
Further Discussion • Enterprise warehouse integration • Web delivery •
Risks and Limitations of a Data Warehouse Project • Process considerations -
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 -
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 -
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 -
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 -
ETL Success Rate Extraordinary (1%) Great success (22%) Success (45%)
Minimal success (23%) Failure (9%) BACK -
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 -
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