Business Intelligence – What Actuaries Need to Know Mark S. Allaben, FCAS, MAAA VP and Actuary Information Delivery Services CAS Seminar on Reinsurance June 6 ‐ 7, 2011
Presentation Structure � Background • Information Architecture • Data Warehouse • Information Delivery � Business Intelligence Less the Hype � Real World Examples • Actuarial, Claim, and Sales 3 3
Introduction to get our Brains working! Start Video Clip IDSTV
Terms � Business Intelligence Tools � Data Governance � Data Warehouse � Dimensional Data � Master Data Management � Metadata � Metadata Repository � Relational Data � Staging 5
Data Requirements Solving for five data requirements is critical to the success of any initiative Data Description Requirements � Increased usage and appetite for additional data elements from other parts of the Scalability enterprise and from 3rd party sources will initiate a virtuous circle - increased use of data will lead to more sophisticated questions which will lead to the need for more data to make decisions, complete transactions, and conduct research. Increased capacity in people, process, and technology will enable capture of additional data at decreasing marginal costs. Scalability enables a shift from being extremely parsimonious in our data capture to capturing all potentially useful data � Knowledge of what data exists, where it is located, and confidence that the quality Trustworthy level is sufficient for conducting analysis and making decisions � Easier and speedier access to existing data. All 2010 workstreams assume that data, Accessibility 3 rd party and internal, will be available wherever and whenever needed in the future processes � Data acquired by the customer interaction processes (New Business, Claims, etc.) and Granularity 3 rd party providers are detailed enough to meet research and transactional needs of product, marketing, sales, and pricing � Ability to link data across the enterprise and from 3rd parties at a granular vs. Connectivity summary level, to enable research, analysis and transactional processing Achieving the five data requirements will make data available and useable across the enterprise. 6
Information Architecture Typical Multi ‐ line Insurer Current Data Architecture 7
Data Warehouse Environment Example of Issues Information Management Illustrative H 2. Data 1. Data 4. Data Access & Data Key Observations Transformation 3. Data Manufacturing & Storage Sources Delivery Consumer & Integration Data Data Analytic Reports Marts / A. Multiple Sources of Warehouses Tools HIG Apple Views Data Systems Vi e B. Multiple BKF w Business Objects Transformation & Informatica Integration (ETL) D Tools M PLDW D C. Redundant storage of A M data D D D. Uncontrolled Access M CDW to data D E. All data stored on the M same tier / type of External D CDF (3 rd Party) storage M G Data B E Application F. Data marts not always F ‘in sync’ with data C sources D M G. Multiple BI Tools SAS ETL H. No ‘Single version of D M Storage & the truth’ – No SAS Application Archiving systemic D reconciliation back to M source systems D M Redundancy in data, infrastructure, storage, and software 8
Five Elements of Data Management Conceptual Data Warehouse Architecture 2. Data 1. Data 3. Data Manufacturing & 4. Data Access Transformation & Sources Storage & Delivery Integration Reports Mart E xtract BI Data T ransform Tools Warehouse L oad Universe/Cube 5. Metadata Repository 9
Data Sources Data Sources from a Source System Refers to any electronic repository of information that contains data of interest for management use or analytics. Operational / Transactional Databases Databases used to manage and modify data (add, change or delete data) and to track real- time information. Source Systems Multiple Sources of the Same Data Billing Quote Customer (i.e. lack of authoritative data source) SNAQS QTI (QHF/THF/ PLA/PAVE • Personal lines premium is ‘Sourced’ TABS DQF) DBME CCC/CS-MCM PLIARS/ICON CLA from three different sources ASPIR • PAVE policy admin system for CDF OMNI Claims External/Vendor • CIDER for BKF Source AIF Experian CI • Corporate Actuarial for HSDM InfoUSA CCPS Reference data Questerra BLC (Loss) ITMS MarketStance DI Vendor data External Policy Agent/Agency Axciom PLA/PAVE CAPIS ISO DBME EAP Choicepoint CLA hartSource … ASPIR PASCE OMNI IMPACT NPPS (Premium) Marketing Business Financial database TM1-Expense 10
Data Transformation & Integration (ETL) ETL (Extract, Transform and Load) is a common 3 step process designed for this purpose 1 2 3 Extract Transform Load • • • Extract data from Works with the extracted Inserts / updates the data multiple legacy sources data set warehouse database tables • • • Extract may be via Applies business rules to Intelligently add new data to the convert to desired state system • Intermediate files • Cleanse and standardize • Databases data • Directly connecting to sources • Multiple extract types • Full extract (refresh) • Incremental extract 11
Data Manufacturing & Storage Atomic Data Store Data Mart A shared, analytic data A shared, analytic structure that supports data structure that multiple subjects, generally supports Data Warehouse Architecture applications, or a single subject There are different types of data departments area, application, warehouses and platforms, e.g.: or department ■ centralized vs. federated ■ Superdome v. Teradata v. Exadata Atomic Data Data Marts Store Profit Analysis PL BI Growth Analysis Customer Agent Inv. Tracking Potential Issues Product F & S Other Exposure Analysis Redundant Storage of Data Claims/Loss Fraud Uncontrolled Access to Data Policy/Premium APG All data stored on the same tier / type of storage Work. Comps Billing Data marts not always in-sync with data sources SAM Book Profiling Quote/Price LDS Risk IBA Reserving Analysis CEMS TM1 Online Other Others… 12
Data Access & Delivery Business Intelligence (BI) Potential Issues An umbrella term that encompasses the processes, Multiple BI Tools tools, and technologies required to turn data into • Five Business Intelligence tools are in use information, and information into knowledge and • Reports and Analytics cannot be easily reused plans that drive effective business activity. BI • Dueling “Truths” encompasses data warehousing technologies and • Reconciliation Efforts processes on the back end, and query, reporting, analysis, and information delivery tools (that is, BI tools) and processes on the front end Purpose Usage Reports that require infrequent structural changes, and can be Provides a pre-made document to provide Standard information needed by user easily accessed electronically Reports Provides ability to data using a pre-defined Research, analysis and reporting query, or on an ad hoc basis Queries Provides ability to easily access key Monitoring and accessing performance Analytical performance indicators or metrics Applications Alerts users to pre-defined conditions that Research and Analysis OLAP occur Analysis Provides ability to perform summary, detailed or Exception Notification without the need to perform detailed analysis trend analysis on requested data. Based Reporting Ability to discover hidden trends with the Research and analysis of hidden trends with in the data Data data Mining 13
Metadata Metadata can provide a semantic layer between IT systems and business users—essentially translating the systems' technical terminology into business terms—making the system easier to use and understand, and helping users make sound business decisions based on the data (i.e. A Data Yellow Pages) A metadata repository is: the logical place to uniformly retain and manage corporate knowledge (meta data) within or across different organizations in a company Various types of meta data include: Potential Issues Data Definitions • List of common data elements and standard definitions No Single Version of the Truth – Business Rules No systemic reconciliation back to source system • Rules define data use, manipulation, transformation, • Metadata is the crux of many of our data problems calculation and summarization • Time would note be wasted • Business rules are mainly implemented by the ETL and • Less reconciliation reporting tools in a metadata dictionary • Not gathering useless / redundant data Data Standards • Less storage • Rules and processes on data quality Data context • Use of and dependencies on data within business units and processes Technical Metadata • Information on configuration and use of tools and programs Operational metadata • Information on change/update activity, archiving, backup, usage statistics 14
Recommend
More recommend
