Practical Applications of Data Warehousing

Transcript

1. Practical Applications of Data Warehousing Kevin Pledge 14 June 2002
2. What is a Data Warehouse? How can they used be used in insurance? Earnings by Source case study
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? Source Systems ETL Metadata Third party systems Data Storage OLAP Cubes Users “Complete analytical infrastructure”
5. ETL
7. Data Warehousing in insurance
8. Project initiators  Response to problem  Limitations of existing systems  Process improvements  Time savings  Consistency of data  Confidence in data  Competitive edge  Increased flexibility  Sharing of information  Customer analysis
9. A natural fit for insurance business?  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
10.  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. Case Against Data Warehousing
11. The “Business Intelligent” Organization  Changes in methodology / approach  “Real” goal oriented  Shared information – common goals
12. Changes in approach  Flexible – real time investigation  Faster turn-around – real time benefits  Slice and dice to find cause
13. Critical success factors  Remove application risk  Recognize functional interrelationships  Organized to the business user
14. 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
15. Interrelationships Experience studies and earnings by source  Both use actual to expected  Definition of expected – fixed table vs. assumption  Incidences – last vs. all
16. 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).
17. 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-entry 1 new
18. CSF – Business User  Easily customized to meet individual needs  Don’t skim on detail  Range of delivery options  Familiar environment  Access speed  Include applications
19. Further Information Presenter Kevin Pledge (905) 475-3282 kpledge@insightdecision.com Web  www.insightdecision.com
20. 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 Focus cubes present limited sub-set
21. Star Schema PolicyFact PK PolicyKey PolicyID FK3 SmokerKey FK4 GenderKey FK7 StatusKey FK12 RatingKey FK13 ConvertiblePeriodTypeKey FK14 ParticipatingFundKey FK15 AccountingFundKey FK19 InsuredLivesKey FK21 PremiumPeriodTypeKey FK22 RenewalPeriodTypeKey FK23 PremiumFrequencyKey FK24 PremiumTypeKey FK27 ProvinceKey FK30 ProductKey ContractPeriod IssueAge PremiumPeriod IssueDate ExitDate SourceExtractDate Benefit ContractPremium FundValue CashValue StatutoryReserve Gender PK GenderKey GenderCode Gender Smoker PK SmokerKey SmokerCode Smoker Status PK StatusKey ExitModeCode ExitModeGroupCode ActiveCode ExitMode ExitModeGorup Active Rating PK RatingKey RatingCode Rating AccountingFund PK AccountingFundKey AccountingFundCode AccountingFund PremiumPeriodType PK PremiumPeriodTypeKey PremiumPeriodTypeCode PremiumPeriodType PremiumType PK PremiumTypeKey PremiumTypeCode PremiumType PremiumFrequency PK PremiumFrequencyKey PremiumFrequencyCode PremiumFrequency Province PK ProvinceKey ProvinceCode Province CountryCode Country Product PK ProductKey ProductCode Product Currency InsuranceEvent PolicyInsuranceType ProductType ProductGroup ProductLine
22. Star Schema - snowflaked PolicyFact PK PolicyKey PolicyID FK3 SmokerKey FK4 GenderKey FK7 StatusKey FK12 RatingKey FK13 ConvertiblePeriodTypeKey FK14 ParticipatingFundKey FK15 AccountingFundKey FK19 InsuredLivesKey FK21 PremiumPeriodTypeKey FK22 RenewalPeriodTypeKey FK23 PremiumFrequencyKey FK24 PremiumTypeKey FK27 ProvinceKey FK30 ProductKey ContractPeriod IssueAge PremiumPeriod IssueDate ExitDate SourceExtractDate Benefit ContractPremium FundValue CashValue StatutoryReserve Gender PK GenderKey GenderCode Gender Smoker PK SmokerKey SmokerCode Smoker Status PK StatusKey ExitModeCode ExitModeGroupCode ActiveCode ExitMode ExitModeGorup Active Rating PK RatingKey RatingCode Rating AccountingFund PK AccountingFundKey AccountingFundCode AccountingFund PremiumPeriodType PK PremiumPeriodTypeKey PremiumPeriodTypeCode PremiumPeriodType PremiumType PK PremiumTypeKey PremiumTypeCode PremiumType PremiumFrequency PK PremiumFrequencyKey PremiumFrequencyCode PremiumFrequency Province PK ProvinceKey ProvinceCode Province CountryCode Country Product PK ProductKey ProductCode Product FK5 InsuredEventKey FK6 PolicyInsuranceTypeKey Currency ProductGroup PK ProductGroupKey U1 ProductGroupCode ProductGroup FK1 ProductLineKey ProductType PK ProductTypeKey U1 ProductTypeCode ProductType FK1 ProductGroupKey ProductLine PK ProductLineKey U1 ProductLineCode ProductLine InsuredEvent PK InsuredEventKey U1 InsuredEventCode InsuredEvent PolicyInsuranceType PK PolicyInsuranceTypeKey U1 PolicyInsuranceTypeCode PolicyInsuranceType FK1 ProductTypeKey