Business Intelligence Solutions for the Insurance Industry DAT – 13 Data Warehousing Rasool Ahmed

1. Business Intelligence Solutions for the Insurance IndustryDAT – 13 Data WarehousingRasool Ahmed

2. Business Intelligence Questions: • BI - What is it? What would I do with it? Why do I need another system to do it? • BI supplier selection evaluation criteria.

3. Executive Analysis • Query • Reporting • Data Mining • Claim • Sales & Marketing • Financial • Underwriting • Third Party Data • Other Int. Systems • Multi-Company • Multi-Line • Multi-Source • Multi-Dept/Function • Transaction Level Business Intelligence Access Data Warehouse Judy Ann Brown Female July 20, 1945 Financial Consultant Good Credit History Income > 100,000 One Claim Filed Closed Without Payment Two Tickets 1999 One DUI Load Transform Extract Operational Data Sources Policy J Brown Female July 20, 1945 Financial Consultant Claim Judy Brown One Claim Filed CWP MVR Brown, Judy Ann Two Tickets 1999 One DUI External Judy Jackson Good Credit History Income > 100,000

4. BI - What would I do with it? • Interactive • Intuitive • Mgt Review • Loss Triangles • Risk Assessment • New Business • Exposure Evaluation • Pattern Recognition • Predictive Modeling • Risk Scoring • “Fraud with 85% accuracy” Data Modeling “…predict with 82% accuracy those customers that Will cancel their policies.” “… The special Investigation Unit can now prioritize and catch 66% more fraudulent claims per referral.” Detailed Analyses Exec / Mgt Profiling

5. What can do with it? • Executive Browser

7. Why do I need another system to do it? • Data organized for OLTP, not analysis • Inability to slice and dice – geared for management reports • Unintelligible coding structures; no meta data • Not a complete picture (multiple systems); can’t merge • Inability to augment data • 87% of all insurance master files are non-relational • Inability to profile trends

8. Insight knowledge & insight gathered by insurer • Executive Analysis • Query • Reporting • Data Mining • Corporate Detail • Corporate Summary • Line of Business • Policy • Claim • Rating • Claim • Sales & Marketing • Financial • Underwriting • Third Party Data • Other Int. Systems Business Intelligence Access Insure Marts™ Aggregate solutions provided by Thazar Insurance Warehouse™ • Reinsurance • And more Load Transform Extract Data Sources Information source data provided by insurer

9. Business Intelligence How Much? 199X2000 $$ $3+ M 1/4% NWP Time > 3yrs 3 – 5 months Function Reports Profiling/Predictions

10. Business Intelligence For Example…….: Fraud Detection Losses are 70% of NWP; 10-20% of Losses are Bad Faith Identifying <4% of Bad Faith claims pays back cost of DWH Retention Poor Average Good 25% 35% 45% (after 4yrs) |----------------------------------- (2%) Loss Ratio |----------------- (1 ½%) Loss Ratio Increasing Retention by 1 ½% pays back cost of DWH (reduced losses only) New Business Costs of Sales can vary from 5% to 20% of NWP Moving 5% of business to channel that is 5% more efficient pays back cost

11. Why Companies are DoingBusiness Intelligence Better Quality of Data 35% Better Understanding of the Business 20% More Timely Decisions 30% Exploit New Market Opportunities 15% Over 400% ROI in less than 3 years! Meta Group Survey of 300 Companies Implementing Warehouses

12. Why Companies are DoingBusiness Intelligence Revenue Growth / Expense Control • Questions You Need Answered Now • Questions You Have Not Thought About • Acquisitions

13. Personal Auto – Retention Analysis • Policy Holder Characteristics • Loss Attributes • Policy Attributes • Distribution Analysis • Dimensions

14. Homeowners – New Business Analysis • Policy Level Analysis • Risk Characteristics Analysis • Home Feature Analysis • Time Views

15. Engagement Overview • wk 1-2 Plan / Organize • wk 3-4 Data Analysis Workshop • wk 5-7 ETL Development • wk 8-9 Test & Balance • wk 10-11 Load Warehouse & Marts • wk 12 Go Live

16. BI Solutions Provider Project Manager Business Analyst* Data Analyst* Technical Analyst * Insurance knowledge & experience is critical Client Project Manager Business Analyst Data Analyst Implementation Roles / Responsibilities

17. Critical Success Factors... SupplierBothYou Executive Sponsorship & Vision Functional Executive commitment Information Systems Team involvement BI Insurance Experience & Methodology Pre-defined Models, Templates, Marts & Executive / End User Browser

18. Critical Success Factors... Supplier BothYou Scope & definition study - phased Implementation Expectations & Results understood Business & I/S experts to implement Training and skills transfer Easily Supported & Maintained Add additional departments, enhancements & applications

19. Lessons Learned Don’t: • build “boil the whole ocean” • oversell to end-users • build something that can’t be maintained and extended Do: • start small “phased” - prioritization by LOB • ensure data has integrity and is balanced • define measurable objectives Keep At It !!! • deliver “baseline” results early & continue to build

20. Rasool AhmedThazar Solutions Corporationrmahmed@thazar.com816-760-5119