www.pwc.com/us/insurance
Experience studies
data management
How to generate valuable
analytics with improved
data processes
An approach to managing data for experience studies October 2015
Table of contents
The need for faster and more dependable data ... 1
Why is managing data so difficult? ... 2
Four major data management activities ... 3
Data acquisition ... 3
Conforming and calculations ... 4
Data storage ... 4
Analytics ...5
Trends in data management ... 6
In conclusion... 7
The need for faster and more dependable data
“Not everything that counts can be counted, and not everything that can be counted counts.”
- Albert Einstein
Einstein’s words resonate with actuaries because experience studies are first and foremost about discovery. While companies have long-standing approaches to performing experience studies, the drive to principle-based reserves (PBR) is leading many insurers to re-visit experience studies.
In order to create the robust, repeatable processes that PBR demands, insurers are building experience studies on industrial-strength platforms that support speed and deliver control. Deploying solutions that provide transparency and flexibility requires a collaborative effort that leverages the skills and experiences of actuaries, business analysts, IT professionals, project managers and the evolving profession of data scientists. From a design perspective, decisions about how to develop capabilities and select components follow naturally from focusing on how users will access results, perform analytics, and use the studies.
Why is managing data so difficult?
Every user wants a data source that is complete, well controlled and intuitive. Beyond that, users also want reporting tools that are lightning quick. In reality, there are always challenges around data quality, content and history (i.e., how many years of data are available for analysis). In addition, constraints on speed tend to leave users dissatisfied with the functionality of reporting tools.
The illustration above illustrates the key challenges to establishing a data source and managing it over time. Volume and speed need to be evaluated up front as they will drive infrastructure decisions and database design, including history. Controls allow users to use data with confidence and should be automated where possible. Decodes and data typing are activities that help lead to the data being accurate and well understood. Calculations, fixing data, and filtered data pose challenges that impact data completeness and data quality.
In addition to coping with these issues, successful project teams strike a balance between flexibility and data knowledge. They do this by avoiding data requirements that are too onerous or too broad. In addition, they are careful to avoid bringing in too much data at one time, as excess data makes it difficult to manage the numerous snapshots of policy data and the experience study calculations. Finally, successful teams know the data intimately and remain nimble in the face of unexpected challenges in establishing the experience study database source.
Data
management
Database design
•
Volume
•
Speed
•
Data typing
Business rules
•
Data Completeness
- Calculations
- Filtering
•
Data Quality
- Fixing Data
•
Data Insights
- Decodes
Data
confidence
•
Controls
Four major data management activities
In most instances, we recommend the creation of an IT-supported database to support the experience studies process. There are certain key considerations for developing a well-designed source for experience studies: Data acquisition
Conforming and calculations Data storage
Analytics
The illustration above shows the sequence of activities leading to the goal of improved analytics for experience studies. We discuss all four below.
Data acquisition
For experience studies, we see many approaches to selecting the proper sources and acquiring data. Data can exist in flat files, in databases, or in legacy administrative systems from which it will need to be extracted. The ideal extraction utilizes pre-existing, controlled and unfiltered extracts from valuation or other processes. If this is not an option, or if all of the required information is not on pre-existing extracts, then experience study extracts will
Experience studies data management
Data acquisitionConform
and calculations Data storage Analytics
Operational Data Store Actuarial Data Warehouse Data Marts Databases ETL Calculations BI Semantic Layer Calculations Controls Legacy Systems Flat Files Internal or External Query and Reporting ___ ___ ___
need to be created. Data requirements should focus on necessary fields, including external data, which may provide additional insights into the studies. Data acquisition can be the most time consuming and costly aspect of any data project because it requires people familiar with the legacy systems to perform the extraction and decoding, to conduct testing, and to contribute to the future state design.
Conforming and calculations
Conforming data allows for the data design to have multiple sources and establishes an actuarial database that presents the data as if it came from one source with common definitions. Source data can exist in many formats, including dates, amounts, and text. It is time consuming to make many text fields conform because careful analysis is necessary to determine a common business meaning. Examples of this include status codes, product codes, underwriting classes, etc. In addition, there could be missing values or inconsistencies in the data that would require fixing the data.
Actuaries use data models to organize and conform data and to improve the performance of data extraction, reporting, and analytical tools. There are a pair of notable insurance industry data models to consider: 1. ACORD, the Association for Cooperative Operations Research and Development, provides an insurance
industry data model that standardizes data for life and P&C companies. The ACORD data model can be used to build an operational data store (ODS), and it allows for common objects, fields and code sets within fields. ACORD does not provide a star schema dimensional model as is typically used in actuarial data warehouses and for the storage of historical data. A star schema dimensional model is an understandable data structure designed for speed and consistency in definitions. As a result, the project team will need to build this model from scratch.
2. OIDF, Oracle’s Insurance Data Foundation, is another insurance industry data model providing staging tables; extract, transform and load (ETL) procedures; and a star schema dimensional data model. The benefit of OIDF is that it does not require designing, testing, and maintaining an ODS. In other words, it eliminates the intermediary step between the data sources and the data repository. It provides an ultimate data structure that is conducive to storing history with current period data, and is organized for speed and data consistency. The star schema provides table structures that are more readily consumed in the reporting and
analytics environment.
Regardless of data model, calculations provide data fields that may not exist in the source systems but are necessary for experience studies. They include fields such as exposures, mortality or lapse rates, credibility factors and actual to expected ratios. Some calculations can be executed seriatim while others are applied at a more aggregate level. Whenever possible, it is advisable to establish the calculations at the most granular level of detail.
There are numerous implementation options available for experience study calculations. One approach is to execute seriatim calculations using SQL code and to store the results with the base policy information. Another approach is to execute calculations as data is aggregated within the semantic layers of business intelligence (reporting) tools. Finally, when third-party valuation and/or modeling tools are used for experience studies, calculations can often be executed within the tools themselves.
Data storage
It is advisable to target data sources that are built via acquiring, conforming, and calculating data in an operational data store, in an actuarial data warehouse, or in data marts. Operational data stores typically stage snapshot data
area subsets of data that are generally aggregated. In addition to providing auditability back to the actuarial data warehouse, data marts are an excellent way to subset and organize the data in order to mitigate speed
issues downstream.
Regardless of how experience studies data is stored, the resulting database needs to be fast and intuitive to the user. It is essential to adhere to data fundamentals, such as descriptions for all code fields, building field hierarchies when possible, and making the controls process rules based and automated.
Analytics
Analytics enable the discovery and communication of meaningful patterns in data. For experience studies, we can discern meaningful patterns using new data sources. There are many types of reporting that can be implemented, but the most common are queries, reports and dashboards. When data sources are well built and designed to satisfy the required analysis, it makes the implementation of any reporting package easier.
As we mentioned previously, it is possible to make calculations in the semantic layer of reporting tools. Today’s reporting tools also allow for the creation of field aliases and groupings. When these types of fields are implemented within the database using ETL procedures, changes to groupings and aliases can become a maintenance issue. As a result, it is preferable to implement these features in the business intelligence (BI) reporting model (i.e. semantic layer).
In experience studies, the analytics should be consistent, understandable, and accurate. An example of an interactive dashboard for experience studies analysis is shown above. Views with tables of values, as well as associated graphs, can be assembled in common subject areas dashboard formats. Reviewers need to be able to filter results in order to interact with the views.
Trends in data management
Recent trends in data management include cost-efficient approaches to handling large volumes of unstructured data, tools that speed data access, and software that improves data visualizations.
Cloud-based solutions are changing the economics of data storage. In addition to evaluating the cloud as a place to store data, insurers are exploring it as a place to run models and host analytics. In fact, a number of companies in the BI space now offer cloud-based versions of their products. As a scalable, on-demand resource, the cloud has potential to provide surge capability. However, security and data transfer speeds remain a
concern for some.
Data platforms featuring in-memory databases are contributing to scalability thanks to the low cost of RAM. These platforms support faster queries and analysis because large volumes of data are managed within RAM rather than accessed from a drive.
Probably the most important development concerning data and BI is in data visualization tools. Data visualization can provide unique insights that users otherwise might overlook. Leading providers in BI continue to develop new graphics capabilities.
In conclusion
Properly defined and supported experience studies are an integral part of the monthly reporting process. Moreover, it is possible to realize them with a relatively small number of policy data fields and calculations. Prototyping of calculations and reports can further define requirements and help the data team properly design and implement data stores. Lastly, the addition of user-friendly analytics, as well as the newfound scalability and speed of data management, will help actuaries develop more meaningful insights from more robust experience studies.
For more information
For a more in-depth conversation about experience studies, please contact: Jeffrey Schlinsog, Principal
+1 414 212 1715
Joe Chou, Managing Director
+1 267 330 8301
Steve Bochanski, Director
+1 267 330 8445
Paul Brueggemann, Director
+1 617 530 7684
Greg Smith, Director
+1 860 241 7365
