Data Quality is the Biggest Challenge

With Solvency II firmly back on the radar following the recent pronouncement from the European Insurance and Occupational Pensions Authority (EIOPA), insurers are once again reenergizing their Solvency II programs. However, there are clearly many challenges ahead that they must overcome before they can establish a risk-based culture and derive business benefits from a Solvency II program. But perhaps the biggest challenge relates to data, as it is at the core of Solvency II.

Many insurers are good at calculating numbers, but those numbers are only accurate if the data fed into the actuarial engines is correct. Indeed, EIOPA stipulates that the data used for Solvency II purposes must be accurate, complete, and appropriate. It also mandates a data governance framework. Thus, data is critical for regulatory purposes. Insurers also need good data to support their decision-making processes and meet internal compliance requirements.

Historically, insurers have suffered from poor data quality, primarily caused by having vast amounts of unstructured data stored in a plethora of systems. Many of these systems are somewhat antiquated (so called legacy systems) and others are desktop based (actuarial software). The problem is compounded by new applications that have been added to these legacy systems, creating multi-layered and potentially redundant IT architectures. Additionally, there is a lack of common data models, data structures, and data definitions.

Spreadsheets are another problem as they store a significant amount of actuarial and finance data. For example, a large insurer may have hundreds of source systems organized in a number of discrete silos and thousands of spreadsheets from which data is needed. Often the same data is duplicated in different systems but stored in different formats. Standardizing data from external sources such as asset managers and reinsurers is also a challenge.

Insurers have tried to rationalize legacy systems and impose common data models and structures across these diverse systems. This has generally met with little success. The interaction between these legacy and desktop systems also creates its own problems. The complexity can be overwhelming as shown in Figure 1, which illustrates the overly complex interaction surrounding actuarial modeling.

Figure 1. Overly complex interaction surrounding actuarial modeling

Source: Moody's Analytics

Solvency II basically requires granular actuarial, finance, asset, and risk data, which is categorized as analytical data. Figure 2 illustrates, with some examples, the types of analytical data and from where that data may come. Analytical data primarily comes from systems that, in turn, require data from core administration, claims, CRM systems, etc. Thus, there is a link between analytical and operational data. This sophisticated data chain can be complex, but data quality throughout the chain is essential.

Perhaps the key issue is that analytical data is different in its structure from the operational data that insurers traditionally store in their warehouses. This is particularly evident in the actuarial arena, which is a new ground for IT. Traditionally, actuarial modeling has been the domain of desktop modeling systems and supplemented heavily with spreadsheets.

There are potentially many inputs into an actuarial model – mortality tables, economic scenario generator (ESG) files, assumptions sets, run parameters, policy data (potentially hundreds of inputs). So there is significant value in storing inputs in a repository, especially regarding enterprise access and management and audit controls. There is equal value in storing the output of actuarial models (essentially cash flows) in a sufficiently high level of granularity – something that is typically not covered in modeling technology – particularly for sensitivity analysis and populating the Technical Provision Quantitative Reporting Templates (QRT).

Good data governance and practice should already be in place as part of an insurer’s compliance program; however, recent regulation such as Solvency II, International Financial Reporting Standards (IFRS), and Dodd-Frank focuses significantly on data management and quality. Solvency II is an example of this increased focus on data.

Figure 2. Types of analytical data and related source systems

Source: Moody's Analytics

Pillars II and III of Solvency II introduce extensive data management and quality requirements. This not only involves the creation of new data sets and reports, but also data management standards and controls that must be transparent and fully auditable. Indeed, EIOPA requires a Data Quality Management Framework and Policy to be in place as part of the Internal Model Approval Process (IMAP), which is also relevant to the ORSA.

The purpose of this requirement is to ensure that all data used for Solvency II purposes is accurate, complete, and appropriate. It also establishes standards for data quality. A practical problem is that insurers are not always able to define accurate, complete, or appropriate. The Prudential Regulation Authority (PRA) in the UK noted that this was a particular issue with catastrophe exposure data, where underwriting teams did not always have an adequate understanding of the quality criteria or the point at which a data error could be considered material. Table 1 provides EIOPA’s interpretation.

EIOPA will finalize the data management requirements in the Omnibus 2 Directive, but insurers can commence their data management projects now as sufficient principles have already been established.

Table 1. EIOPA data quality requirements

Source: Moody's Analytics

The fact that raw analytical data can come from numerous sources (both internal and external) leads to questions regarding its quality, consistency, and reliability – particularly as the volume of data increases.

An example of this can be found in the multiple policy administration systems maintained by insurers – each of which may store a policyholder’s age and birth date in different formats. Reconciling data is also important. For example, premium data may come from both a policy administration system and the general ledger, but rarely are they the same number.

Figure 3 illustrates the data chain (or lineage) between source systems and ultimate reporting, and highlights the seven-step data quality process.

Improving the quality of data is a multi-faceted process. In essence, it takes raw data and subjects it to a range of tools that use algorithms and business rules, coupled with expert judgment, to analyze, validate, and correct the data as appropriate. Effective data quality tools have in-built data “logic” in terms of patterns, trends, and rules built up over a number of years against which data is tested. Simple errors can thus be automatically corrected. It also raises flags for data that requires expert judgment. The end result may not always produce perfect data (no process can do that), but the data should at least be fit for purpose. Table 2 looks at a typical seven-step process for improving the quality of data.

Figure 3. Source system and reporting data chain

Source: Moody's Analytics

Table 2. Seven-step quality process

Source: Moody's Analytics

Using data profiling, insurers can examine the data available within an existing data repository and assess its quality, consistency, uniqueness, and logic. This is one of the most effective techniques for improving data accuracy in an analytical repository. A number of proprietary data profiling tools are available from vendors in the market.

Data profiling uses different kinds of descriptive techniques and statistics – such as minimum, maximum, mean, mode, percentile, standard deviation, frequency, and variation, as well as other aggregates, such as count and sum – to analyze data according to known patterns. Using these, an expert can find values that are unexpected and therefore potentially incorrect. Profiling can help insurers identify missing values, which can then be replaced by more logical values generated by data augmentation algorithms.

A key part of the process is data standardization, which fundamentally relates to the execution of a number of data quality rules against the data. Various vendors offer data quality tools that include thousands of rules. These comprise a number of generic rules, together with some specific to the insurance industry. Additionally, such tools also enable insurers to define supplementary rules specific to their own lines of business or function. Table 3 provides examples of the types of data quality rules.

While it is the role of the IT department to actually use data quality rules, it is up to the practitioners in the business to provide the “logic,” in conjunction with rules that are specific to a particular set of data. When discussing this logic with IT, carefully consider what the ultimate usage of the data is. For example, in terms of policy data, the input required for actuarial modeling is primarily around the type of contract, benefits/coverage, premiums, term, etc. These inputs have to be correct as they impact the accuracy of the cash flows. Other policy-related data, such as postal code, phone number, etc., are not relevant for these purposes and, if incorrect, have no impact on accuracy.

Table 3. Data quality rules

Source: Moody's Analytics

No review of analytical data quality would be complete without considering the role of spreadsheets. Spreadsheets are now commonly considered a part of the wider group of technology assets called end user computing (EUC) – that is, any technology asset which may be created, updated, or deleted outside the purview of the IT department or standard software development lifecycle management processes. Other assets in this class include MS Access databases, CSV files, MatLab scripts, etc. However, spreadsheets tend to be the most prolific and problematic of all EUCs because of their flexibility and familiarity to most users – particularly actuaries.

The ability of a spreadsheet to act as a data source (e.g., data connections/links and expert judgment), a data manipulator (e.g., data joining, restructuring, and translation) and as an application (e.g., formulas and macros) create a variety of data quality issues. When combined with the additional uncertainty of ownership and access rights over specific spreadsheets, it is not surprising that spreadsheet control issues have received specific mention in data thematic reviews conducted by the FSA (now PRA).

Spreadsheets pervade almost all financial and actuarial processes, but regulatory focus under Solvency II has been drawn particularly to those that hold and manipulate data prior to consumption by the internal model. It is common to find extensive “webs” of thousands of spreadsheets connected by “links” that may have an impact on data quality. In practice, many of these are dormant, but their presence and the possibility of erroneous updates create uncertainty and risk in the modeling process.

Spreadsheets are not going away – they will remain for the foreseeable future. So one viable solution is to embed spreadsheets into specialist software that effectively provides a control framework. This software operates according to three steps: discovery, triage, and control.

Discovery is the process by which businesses can evaluate their current dependence on spreadsheets. Such a “health check” will consider the scope and complexity of spreadsheet usage. It can be done manually, but may be accelerated using technology.

Once the spreadsheet landscape is mapped and analyzed, the future of identified spreadsheets and spreadsheet-supported processes can be triaged for different forms of improvement. This may simply be a matter of training users to further exploit existing solutions, or require the adoption of new software capabilities through integration or third-party vendor solutions.

It is likely that the spreadsheet triage process will produce a road map for process improvement that will take some years to complete, so that business-critical spreadsheets will continue to exist in the business for a lengthy period. In addition, constant business innovation will undoubtedly continue to create more spreadsheets. Both these factors mean that spreadsheet elimination should be seen as a continuous process, rather than a planned destination. Businesses are therefore increasingly turning to technology to provide on-going spreadsheet control in the form of enterprise spreadsheet management software. This provides the opportunity to detect and report user activity that is outside pre-determined tolerance levels across the key risk areas of data, functionality, and security.

1. Analytical data (actuarial, finance, risk, and asset) is very different in character compared to the transactional data insurers traditionally use and store.
2. When considering the analytical data, the business needs look to the ultimate usage of the data – usually reports and dashboards – and level of granularity and drill-through required.
3. IT may utilize Online Analytical Processing (OLAP) techniques to provide sophisticated multi-dimensional views of data; however, a clear definition about the outcome is required.
4. Not all data may be relevant for a particular purpose; therefore, it is important to express why accuracy is needed. Otherwise, valuable effort can be wasted on improving data quality that has little materiality. Basically, identify the data with the highest impact.
5. Understand that data lineage needs to be a living process and must be updated as systems and processes change. Equally, the ability to track and audit data lineage should be available on demand and be built into the data quality solution.
6. Tools can help improve the quality of data, and are a combination of techniques and expert judgment – these tools should be used wherever possible.
7. Business rules are an important part of the data quality process. While there are many pre-built generic rules, it will be critical to supplement these with user-defined rules that reflect unique business considerations – this requires practitioner input.
8. Improving data quality is an ongoing process – not just when the data is initially loaded. Data is constantly changing. It should be embedded in the data management and governance framework.
9. Spreadsheets remain an important element of analytical data and should be carefully managed and controlled.
10. Most data-related projects do not fail because of the technology – they fail because practitioners cannot precisely define what data they need.