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

Practical Applications of Data Warehousing

Practical Applications of Data Warehousing

Kevin Pledge

June 14, 2002
Tweet

More Decks by Kevin Pledge

Other Decks in Business

Transcript

  1. What is a Data Warehouse? How can they used be

    used in insurance? Earnings by Source case study
  2. Data Warehouse Definitions “a copy of transactional data structured specifically

    for query and analysis” “An infrastructure for analytical functions”
  3. What is a data warehouse? Source Systems ETL Metadata Third

    party systems Data Storage OLAP Cubes Users “Complete analytical infrastructure”
  4. ETL

  5. 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
  6. 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
  7.  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
  8. The “Business Intelligent” Organization  Changes in methodology / approach

     “Real” goal oriented  Shared information – common goals
  9. Changes in approach  Flexible – real time investigation 

    Faster turn-around – real time benefits  Slice and dice to find cause
  10. Critical success factors  Remove application risk  Recognize functional

    interrelationships  Organized to the business user
  11. 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
  12. Interrelationships Experience studies and earnings by source  Both use

    actual to expected  Definition of expected – fixed table vs. assumption  Incidences – last vs. all
  13. 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).
  14. 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
  15. CSF – Business User  Easily customized to meet individual

    needs  Don’t skim on detail  Range of delivery options  Familiar environment  Access speed  Include applications
  16. 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
  17. 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
  18. 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