This section explains the background of OIDF, its functional architecture along with the differences from traditional warehouse architecture. OIDF product package consists of the Analytical Data Warehouse model. The individual components of the Physical Data Model are explained in detail.
Topics:
· Differences from Traditional Warehouse Architecture
It is important to note that OIDF architecture differs from 'traditional' data warehouse architecture.
OIDF was built to specifically address the key challenges of building a scalable, practical data management platform to support Insurance Analytics needs. The differences from traditional BI architecture are highlighted and explained in the following sections.
The following figure depicts the Data Foundation Functional Architecture in OFSAA.
Figure 1: Data Foundation Functional Architecture diagram
The OIDF architecture can be explained as two distinct, interacting components. The dark green cylindrical portions denote the data repository and the red portion denotes the management toolkit.
· A data repository whose physical structure is given by the OIDF physical data model (here Physical Data Model = OIDF Staging + OIDF Reporting). This user guide only details the structure and organization of the data repository that is covered by the data models in the OIDF.
· A management toolkit provided by OFSAAI that is used to manage the data repository, by providing a collection of tools and frameworks based on a common metadata foundation. This user guide does not cover the details of the Analytical Application Infrastructure, as that is a separate product with its own documentation.
The architecture illustrates the following key concepts:
· A unified data-sourcing area for analytics: The OIDF Staging Data Model provides the basis for a central, unified data-sourcing layer for a variety of analytical needs. The staging layer faces the operational (OLTP) and front office systems of an Insurance Company. It consists of tables to capture key business and operational data from these systems, which is then processed by one or more analytical engines.
· A unified reporting or consumption layer: Analytical results can be simple to complex, quantitative, and qualitative measures of a provider's Risk, Compliance, Customer, and Fund Performance. The OIDF Reporting data model is a dimensional data model spanning these key analytical functions. It forms the foundation of OFSAA Business Intelligence applications, but can clearly be used as the result data store for any equivalent engines and processes provided by other vendors, or custom-built solutions. By providing a single data repository for reporting needs, this layer provides a foundation for departmental and cross-departmental and cross-functional reporting.
· Additionally, the schema of the reporting area is specifically built for Insurance analytics. As an illustration, the reporting area has a 'Run dimension' that is shared across all BI or reporting use cases. Similarly, the aggregation of shared measures or reporting into a common set of output structures (Account Summary) allows for cross-functional reporting, which is increasingly the norm in Insurance institutions.
· Single point of control and operation: The Oracle Financial Services Analytical Applications Infrastructure is a separate Oracle product that offers a set of tools that are built on a common metadata foundation. These tools are used to control and manage the lifecycle of data from sourcing to reporting. There is a collection of frameworks to manage the following lifecycle steps of data within OIDF:
§ Metadata Management
§ Data Quality
§ Data Movement
§ Scheduling and runtime operations
§ Security or User management
§ Analytical Process Definition and Execution
· Processing Area: As explained earlier, the primary purpose of the OIDF is to serve as a container for analytical processing from sourcing to consumption. Such processing is usually delivered in the form of discrete units called analytical applications spanning different analytical use cases ranging from Policy to Claims to Fund Allocation.
· These applications consist of custom-built computational engines and numerical libraries and may execute processes on the data that range from simple aggregations to complex, multi-step stochastic processes such as Monte-Carlo simulation.
· Hence, analytical applications place varying demands on the data infrastructure in terms of volumes and speed and hence place different demands on data architecture. In practice, the normalized (3NF) design favored.
· Enterprise Data Warehouses often fails to be efficient or performant when it comes to analytical processing across a wide range of use cases.
· Therefore, the OIDF recognizes the need for distinct application-specific working stores, separate from the staging and reporting area.
ATTENTION:
The structure of what these processing area stores are decided by the actual analytical application and engine used. The OFSAA suite of applications is organized this way, with each application managing a specific set of tables or schemas within the processing area.
The processing area tables or schemas are not part of the OIDF. This is because the OIDF is intended to be an open platform. Other analytical applications and engines can equally provision data out of OIDF by mapping their input requirements appropriately to the OIDF staging area model.
This table summarizes the differences of the OIDF from a traditional Data Warehouse architecture.
Component |
OIDF |
Traditional BI Architecture |
|---|---|---|
Staging Layer |
Common Staging Area (CSA) where data from source systems are staged and is implemented as the database schema. |
Usually, a file system-based area where file-based extracts (operational images) from source systems are staged prior to loading into a target schema. |
3rd Normal Form Operational Data Store or Enterprise Data Warehouse |
Does not provide a physicalized model for a 3NF store. Operational or fine-grained reporting will be fulfilled from the reporting area. |
Notification was sent to all users captured as owners and users who submitted the definition. |
Data Marts or Reporting Model |
Set of star schemas with conformed dimensions (Ralph Kimball approach). |
Set of star schemas. |
With the preceding understanding in mind, the following sections describe the data models in the OIDF in detail.
The Subject Areas are organized to support detailed level analysis related to set higher-level analytical reporting solutions.
Within the above higher-level areas, there are over 165 subject areas consisting of star schemas supporting detailed analysis. Each Subject Area typically corresponds to one or possibly more star schemas, depending on the reporting need. There are currently about 500 fact and dimension tables in the reporting area. The details of the several naming conventions used in the OIDF Data Model are explained in APPENDIX: Naming Conventions Used in OIDF Data Model. For column-level details, see the Oracle Financial Services Analytical Applications (OFSAA) Data Model Document Generation Release 8.1.x, which details how to extract the data dictionary from erwin section.
Subject Area |
Description |
|---|---|
OIDF Legal Entity |
This subject area covers the legal entity or organization’s structure related tables. |
OIDF Party Contacts |
This subject area covers the party contact details such as party address, party phone details, and email details. |
OIDF Party Definition |
Party here refers to any person or organization who interacts with the company. All the parties associated with an insurance company under different roles like policyholder, producer, agents, issuer, and so on are loaded together in this entity. |
OIDF Party Identification |
This subject area covers the identification details of an individual Party or organization. Identification means the action or process of identifying someone or something, or the fact of being identified. |
OIDF Party Employment |
The subject area covers the employment details of an individual Party. This data applies only to Parties who are individuals. A single Party may have multiple employment details. |
OIDF Party Exam and Certificates |
The subject covers the details pertaining to the party's certification and related exams to it. An Exam is formal verification or validation of information learned or known, typically used to satisfy a requirement for licenses, registrations, or other firm education proof. Certification is the formal procedure by which an accredited or authorized person or agency assesses and verifies (and attests in writing by issuing a certificate) the attributes, characteristics, quality, qualification, or status of individuals or organizations, goods or services, procedures or processes, or events or situations, in accordance with established requirements or standards. |
OIDF Party Financials |
This subject area covers the financial information (Balance-Sheet, Profit and Loss Statement, and Ratios) of the parties. The balance sheet is prepared as of a particular date (Balance sheet creation date). |
OIDF Party Medical |
This subject area covers all the medical tables related to party medical details such as party medical test details, family medical test data, and party disability details. |
OIDF Producer and Producer Agreements |
This subject area covers Producer and Producer Agreement tables. |
OIDF Insurance Liability Contracts |
This subject area covers the insurance contract tables. The contract is defined as where one party (the insurer) accepts significant insurance risk from another party (the policyholder) by agreeing to compensate the policyholder if a specified uncertain future event (the insured event) adversely affects the policyholder |
OIDF Trading and Investments |
This subject area covers OIDF Trading and Investments related tables. |
OIDF Capital Borrowings |
This subject area covers OIDF Capital Borrowings related tables. Capital Borrowings refers to borrowing done by Insurance companies using debt instruments. |
OIDF Capital Instruments |
This subject area covers the capital Instrument details, it refers to an issue of a capital instrument to raise funds from the market. This primarily covers equity instruments. They may or not be traded in the market. |
OIDF Cargo and Inland Marine |
This subject area covers the details of the policy covered cargo, its storage and shipment. |
OIDF Fixed Assets |
This subject area covers OIDF Fixed Assets related tables. |
OIDF Financial Assumptions |
This subject area covers the details of the financial assumptions. An Assumption is an estimate of an uncertain variable input into a financial model, normally for the purposes of calculating premiums or benefits. This subject area covers the tables related to Insurance Assumptions such as Fact Insurance Lapse Rate Assumptions. |
OIDF Demographic Assumptions |
This subject area covers the details of the demographic assumptions. For example, assumption relates to predicting a person's lifespan, given their age, gender, health conditions, and other factors. |
OIDF Health Telematics |
This subject area covers OIDF Health Telematics related tables such as physical activity device information, Party physical activity details, and physical activity guidelines and goals for the Party. |
OIDF Market Data |
This subject area covers OIDF Market Data related tables. |
OIDF Payment Data |
This subject area covers OIDF Payment Data related tables. |
OIDF Accounting and General Ledger |
This subject area covers the accounting and general ledger tables associated with the insurance company, and OIDF Insurance Transactions related tables. |
OIDF Insurance Claims |
This subject area covers OIDF Insurance Claims related tables. |
IFRS Accounting |
This subject area covers the IFRS accounting-related tables such as contractual service margin and homogenous risk group tables. |
OIDF Insurance Actuarial Outputs |
This subject area covers OIDF Actuarial Outputs related tables. |
OIDF Special Event Insurance |
This subject area covers OIDF Special Events related tables. |
OIDF Solvency II |
This subject area covers OIDF Solvency II related tables. |
OIDF Sales and Marketing |
This subject area covers OIDF Sales and Marketing related tables. |
OIDF Underwriting Application |
This subject area holds the applications processed in each period, for analysis over time. |
OIDF Underwriting Quotes |
This subject area covers the insurance quote details. Quotes are subject to change depending on the information given at the time of the quote. |
OIDF Underwriting Crime Data |
This subject covers the criminal conviction details if the party is involved in any criminal activity. A criminal conviction is the outcome of a criminal prosecution which concludes in a judgment that the defendant is guilty of the crime charged. |
OIDF Underwriting Driving Violation |
This subject area covers the driving violation details related to the party identifier. Driving violation is any violation of vehicle laws that is committed by the driver of a vehicle, while the vehicle is moving. |
OIDF Underwriting Life Style Activity |
This subject area covers information about a party's lifestyle activities. This section captures details surrounding the activities a party engages in that may be considered risky by insurance companies. In the Lifestyle Activity object, the more specific experience sub-objects (for example, Aviation Experience, Racing Experience, Underwater Diving Experience, Climbing Experience, Air Sports Experience, and Foreign Travel) are optional, singly occurring and mutually exclusive. |
OIDF Commission |
This subject area covers tables related to OIDF Commission. |
OIDF Group Insurance |
This subject area covers tables related to Group Insurance. |
OIDF Insurance Coverage |
This subject area covers tables related to Insurance Coverage. |
OIDF Insurance Fund |
This subject area covers tables related to Insurance Fund. |
OIDF Reinsurance Contracts |
This subject area covers tables related to Reinsurance Contracts. |
OIDF Dwelling Insurance |
This subject area covers tables related to Dwell and Dwell Inspection. |
OIDF Auto Telematics |
This subject area covers tables related to IoT (internet of things) for Auto Insurance. |
The following table lists the solutions supported by the Results only OIDF Subject Area.
Subject Area Name |
Definition |
|---|---|
ALM |
Subject Areas corresponding to Asset Liability Management (ALM). |
Regulatory Capital Calculation |
Subject Area pertains to the Solvency II Regulatory Framework and its reporting requirements as specified in the framework. |
PFT |
Supports reporting related to Profitability analysis, part of the Enterprise Performance Management solution area. |
OIPI |
Supports reporting requirements of Insurance Analytics. |
Economic Capital |
Support for Aggregated Economic Capital Reporting based on risk assessments for Market, Credit, and Operational Risk. |
GL Reconciliation |
Support for reporting specific to the Oracle GL reconciliation module that is part of the OFSAA product line. This allows a reporting view of the reconciliation processes and outputs. |
CRM |
Part of the Corporate Credit Risk Solution allows reporting on Credit Limits. |
Market Risk |
Support for Market Risk analytics. |
IFRS 17 |
Supports the data requirement pertaining to the IFRS 17. |
Centralized Reporting |
Supports the centralized reporting for an insurance company covering insurance life cycle activities from underwriting to Claim settlement including risk management use cases as mentioned above. |
The OIDF Physical Data Model is the primary deployed structure in the OIDF. As detailed in the architecture section, it has the following key characteristics:
· Readily Deployable: The Physical Data Model is a readily deployable physical schema. It is provided as an erwin Data Model file (for details on erwin, see https://erwin.com/products/erwin-data-modeler/) and consists of tables grouped into distinct subject areas depending on function. The tables are either used to gather source data (Staging Area) or as containers of outputs or results from analytical processing and engines for reporting purposes (Reporting Area).
· Use-case Driven: The OIDF Physical Data model is driven by a set of clearly identified analytical use cases spanning Performance, Experience, and Compliance.
· Extensible: While the OIDF Physical Data Model satisfies a very large number of analytical use cases across Insurance Contracts, Claims, Underwriting, Actuarial Assumption, Financial Assumptions, Solvency, IFRS 17 subject areas, customers can find the need to customize the model for a specific installation.
These customizations can be done in accordance with guidelines published in the Using OIDF section of this guide.
The OIDF Physical Data Model is divided into the following two primary areas:
· Staging Data Model
The Common Staging Area Model (CSA) represents the point of entry of data into the OIDF. The CSA provides a simplified, unified data sourcing area for inputs required by analytical applications and engines. It consists of over 400 tables and nearly 9000 columns organized into distinct subjects. The salient features of the CSA are as follows:
· Mapping to Analytical Use Cases: Since the primary purpose of the OIDF is to be a data repository supporting analytics, each database object in the OIDF physical data model is necessarily mapped to a corresponding analytical use case.
· These mappings are captured in the data model, in the form of additional metadata called User-defined Properties (UDPs), and can be leveraged to reduce the scope of data gathering efforts by focusing on clearly-defined end-use cases such as Market Risk Analytics, and others.
· These mappings can readily be extracted into a Download Specification, which lists the data demands for a specific analytical use case. An example is shown as follows:
Figure 2: Download Specifications sample
· The Mappings can be generated from the OIDF erwin file using erwin's reporting tools.
· Schema Design: The data model of the Common Staging Area is designed to facilitate loading efficiency. This means that it is denormalized.
· The primary focus of the staging area is to efficiently deliver data from operational systems such as Policy Administration, Claims Processing, and Fund Management to multiple analytical applications that process this data.
· Typically, this data is extracted from source systems and loaded into OIDF directly, or alternatively into a pre-defined file-based operational image area from which it is subsequently loaded into the OIDF schema. In a large Insurance institution, it is not unusual to have 10s to 100s of millions of policies, and claims.
· Standard ETL (Extract, Transform, Load) approaches can fail to address this requirement adequately because of the complexity imposed by the target schema. If the target schema is increasingly normalized, then the ETL into this schema is correspondingly more complex, requiring careful load ordering to prevent integrity-related load failures, as well as integrity validation prior to loading. Such complex ETL processing is time-consuming and is prone to failure.
· To address this, the CSA is designed to support a simplified loading process. De-normalized tables allow efficient data loading, and subsequent transformations can be done to verify data integrity through a series of data quality checks. This represents an 'ELT (Extract Load Transform)' approach to data sourcing, which is far more suited for an analytical data repository.
· Application-managed Referential Integrity (RI): In conjunction with the database design of the staging schema, a key feature is the management of Referential Integrity primarily in the application tier, rather than within the database. Rather than imposing foreign key relationships in the database, which could cause complex loading order dependencies, relationships between staging tables are managed by the Data Quality (DQ) framework, a toolkit within the Analytical Application Infrastructure that captures the relevant data quality checks for a specific table in the staging model. These checks include:
§ Value checks (Nulls, Value ranges, business checks on numeric fields)
§ Referential Integrity checks (which are otherwise implemented as foreign key constraints)
ATTENTION:
This is also why the ER model of the staging area in erwin does not contain any relationships – the staging area is a physical data model, which is deployed using the Analytical Application Infrastructure, which manages it.
In summary – the design of the staging area data model is to allow efficient data loading for analytics. It thus has crucial differences from a general-purpose repository of operational or transactional data across an Insurance institution.
The CSA model consists of over 400 tables and nearly 9000 attributes. These tables are organized into multiple 'subjects', currently by analytical use case or solution need. However, it makes sense to first understand the staging area tables in terms of content before understanding how they map to analytical use cases.
There are two broad categories of staging data, regardless of the use case or the analytical application that requires it:
· Business Data: This set of tables captures the actual business events and the resulting state of an Insurance Company from those business events. The OLTP systems (or Transactional Systems) capture this information resulting from the execution of the provider's different business processes. Broadly, this information can be categorized as:
§ Events: Business transactions, whether financial or non-financial, represent business happenings (events) that are relevant for analytical purposes. For example, a financial transaction by a customer on a current account is a specific event.
§ Events happen at a specific point in time and are recorded by OLTP systems. In the staging area model, there are several transaction tables that capture this detail – for both financial and non-financial transactions.
§ State: The net effect of business transactions is to change the provider's overall financial or non-financial state. This state information is typically captured by product-specific systems in an Insurance Institution.
· Reference or Master Data: Events and states refer to 'business activities' of an Insurance institution. To provide more detail on these, the Insurance institution needs to capture additional data that provides context for these activities. This data can be called 'reference data' or 'master data', and covers several business dimensions of a given transaction or account. For example – an Insurance institution has a master list of products that it sells to customers (Product Master). Similarly, it has a list of customers (Customer master). These and other lists provide context for each business transaction or account. Insurance institutions typically maintain 'Master' data for this purpose.
· With this background, the following lists the key categories of business data and reference data in the staging data model.
The Reporting Data Model is the point where outputs of analytical processing are aggregated for reporting and Business Intelligence (BI) tools. Similar to the Common Staging Area is the foundation for data provisioning to analytical applications, engines, and processes, the Reporting Data Model is the common data store for the outputs of these processes.
Outputs are computed quantitative measures and Key Performance Indicators that involve simple to complex, mathematical, and statistical processing using the raw data, which is performed by specialized engines and computational models. In the OIDF design, the Reporting Data Model design ensures that the historical data is maintained.
The key features of the design of the Reporting Area model are as follows:
· Design: The Reporting Area data model is a dimensional data model. This means that it consists primarily of central fact tables (de-normalized), related to multiple dimension tables, also called a Star Schema. Additionally, the dimension tables are shared across the star schemas in the reporting mode, meaning they are Conformed Dimensions. This means that Drill-across reporting is naturally supported by the OIDF design.
· Additionally, in keeping with the key principle of the OIDF, the Reporting Model is organized by use cases to facilitate reporting and BI in a wide variety of areas.
· Support for multiple scenarios of analysis: As a result of the 2008 crisis, the Financial Services industry is moving towards scenario-based, forward-looking risk analysis instead of retroactive analysis. The reporting data model has been designed to support scenario analysis of the sort required by financial institutions that need to measure and report risk and performance under a variety of economic scenarios.
· To facilitate this, the Oracle Financial Services Advanced Analytical Infrastructure (OFSAAI) provides a Stress Testing framework, allowing risk analysis to be performed under a variety of known scenarios corresponding to different input parameter values to risk models.
· The reporting model provides support for this kind of analysis via a Run Dimension – it allows analytical engines to load multiple result sets identified by scenarios, and hence permits reporting related to baseline and stress conditions in economic terms.
· Support for Cross-Functional Reporting: The third critical feature of the Reporting area design is the support for cross-functional reporting.
· Typically, Business Intelligence and Reporting Solutions work off a dedicated, purpose-specific data store called a data mart. Data marts are function-specific data stores typically star schemas (for example Marketing Data Marts, Risk Data Marts, Customer Data Mart), that provide the necessary reporting and analytics relevant to a particular business function in the Insurance Institution.
· The trend is for cross-functional analytics and reporting. The majority of emerging needs relate to the analytical problems at the intersection of the distinct areas of Risk, Performance, Customer Insight, and Compliance.