The ETL (Extraction, Transformation, and Loading) tool cleans and transforms the data, so that it meets the requirements of the warehouse; information about this process is stored as metadata. This metadata, together with business metadata (business rules and definitions), is available to end users of the system.
Data stored on the source systems is unlikely to be in the format required. Transformation can be as simple as converting "0" to "male" and "1" "female", or very complex involving multiple data fields to be evaluated simultaneously and logic applied to determine the desired value. This stage is critical as it provides users with confidence in the data they are using. It also provides system management benefits such as:
- An audit trail and documentation of the extract routine
- A formal loading process so that reloads are possible
- Better automation than ad hoc loading of data.
Data may also be fed to and from third-party systems, such as valuation systems, to make use of their specialist functions. OLAP or data cubes provide a simple and effective way to view data.
Data warehousing for actuaries cube can be thought of as the dimensions of the data – how the data is stored and viewed, such as by product line, time period, currency. The content of the cube is the item being measured, such as premium, reserve, or sum assured. It is easy to visualize how data is accessed, by identifying the content at the intersection of the selected dimensions.
But here the analogy breaks down; data views are not limited to only three dimensions as physical cubes are. In addition, intelligence can be built in the dimensions to help the user by identifying natural drill down paths, such as product lines and time periods. Data is usually presented to users in the form of pivot tables, and may also consist of preformatted reports, Web portal, or graphical interface. Data warehousing tools bring a number of natural strengths to the sort of analysis that is required for insurance.
Consistency is clearly enhanced with the introduction of a data warehouse. If the administration data is on multiple systems it is an opportunity to bring it together on one system, with consistent definitions for status, gender, and other demographic information that would be common across systems, but not necessarily stored consistently. Also, having assets and liabilities together on a single system allows consistent and controlled analysis of both sides of the balance sheet.
Multiple hierarchies may be used to meet the requirements of various reports and reporting bodies. These will be consistent at the lowest level of detail; reconciliation is then simple with the help of metadata and drill down. Similarly demographic groupings can be predefined. With the ability to drill down into the demographic groupings reports should be clearer and more consistent.