OIPI data model contains the staging tables from which data is loaded into the dimensions and fact tables. Staging tables include the master staging tables, detail staging tables, staging product processor tables, and so on. The user has to populate data into these staging tables.
Topics:
Dimension Data in the OIPI application is loaded from staging master tables using the Slowly Changing Dimensions (SCD) process. Data from source systems can be loaded into staging through flat file or source system interfaces. SCD process tracks the changes in the dimensional attributes and loads data into dimension tables. Examples of dimension tables that follow the SCD process are Product, Customer Type, Customer, and so on.
Some dimensions are static or maintained internally within the application and are not expected as a download from the source system. An example of such dimensions is the Reporting Line. These dimensions are maintained through the AMHM (Attribute Member Hierarchy Maintenance) component of OFSAAI or other framework components like DEFI.
Following are the list of Dimensions used in OIPI:
Dimension Entity Name |
Staging Entity Name(s) |
Loading/Maintenance Method |
|---|---|---|
Acquisition Channel Dimension |
Stage Channel Master |
SCD |
Attrition Dimension |
Stage Attrition Reason Master |
SCD |
Bands Dimension |
AMHM |
SCD |
Billing Account Dimension |
Stage Bill Plan Master |
SCD |
Business Class Dimension |
Stage Business Class Master |
SCD |
Campaign Dimension |
Stage Campaign Master |
SCD |
Catastrophe Events Dimension |
Stage Catastrophe Master |
SCD |
Claim Dimension |
Stage Claim Details |
SCD |
Country Dimension |
Stage Country Master |
SCD |
Coverage Status Dimension |
Stage Coverage Status Master |
SCD |
Coverage Type Dimension |
Stage Coverage Type Master |
SCD |
Dimension Policy |
Stage Property and Casualty Contracts |
SCD |
Dimension Policy Type |
Stage PolicyType Master |
SCD |
Dimension Reinsurance Risk Type |
Stage Reinsurance Risk Type Master |
SCD |
Dwelling Dimension |
Stage Dwelling Master |
SCD |
Employee Dimension |
Stage Employee |
SCD |
Geography Dimension |
Stage Geography Master |
SCD |
Insurance Coverage Dimension |
Stage Insurance Coverage Master |
SCD |
Lines of business Dimension |
Stage Lines of business Master |
SCD |
Litigation Dimension |
Stage Litigation Master |
SCD |
Loan Product Category Dimension |
Stage Product Category Master |
SCD |
Location Dimension |
Stage Location Master |
SCD |
Organization Structure Dimension |
Stage Organization Structure Master |
SCD |
Party Dimension |
Stage Party Master |
SCD |
Policy Deductible Dimensions |
Stage Policy Deductible Master |
SCD |
Producer Agent Dimension |
Stage Producer Agent Master |
SCD |
Producer Dimension |
Stage Producer Master |
SCD |
Product Dimension |
Stage Product Master |
SCD |
Product Type Dimension |
Stage Product Type Master |
SCD |
Quote Source Dimension |
Stage Quote Source |
SCD |
Quote Source Method Dimension |
Stage Quote Source Method |
SCD |
Quote Submission Method Dimension |
Stage Quote Submission Method |
SCD |
Quotes Declination Type Dimension |
Stage Quote Declination Type |
SCD |
Rate Modifier Dimension |
Stage Policy Rate Modifier |
SCD |
Rate Plan Dimension |
Stage Rate Plan Master |
SCD |
Region Dimension |
Stage Region Master |
SCD |
Report Type Dimension |
Stage Report Type |
SCD |
Risk Item Dimension |
Stage Risk Item Master |
SCD |
Underwriter Dimension |
Stage Underwriter Master |
SCD |
Vendor Dimension |
Stage Vendor Master |
SCD |
Allocation Expense Type Dimension |
|
Seeded |
Band Member Attributes |
|
AMHM |
Band Dimension Members |
|
AMHM |
Hierarchies for Bands |
|
AMHM |
Band Dimension Translation |
|
AMHM |
Claim Referral Reason Dimension |
|
Seeded |
Dimension Claim Status |
|
Seeded |
Claim Transaction Type Dimension |
|
Seeded |
Currency Dimension |
|
Seeded |
Date Dimension |
|
DT |
Generally Accepted Accounting Principles Dimension |
|
Seeded |
Location Hierarchy |
|
DT |
Dimension Policy Status |
|
Seeded |
Policy Transaction Type Dimension |
|
Seeded |
Quote Status Dimension |
|
Seeded |
Quote Type Dimension |
|
Seeded |
Recovery Type Dimension |
|
Seeded |
Reporting Line Member Attributes |
|
Seeded |
Reporting Line Dimension Members |
|
Seeded |
Reporting Line Hierarchies |
|
Seeded |
Reporting Line Member Translation |
|
Seeded |
Reporting Line Dimension |
|
DT |
Scenario Dimension |
|
Seeded |
Some of the stage data can also come from master data management interfaces. In such a case, data from the interface is loaded into staging interface tables and SCD is run on the interface tables. Mapping of dimensional attributes to staging can be obtained by querying SYS_STG_JOIN_MASTER and SYS_TBL_MASTER table in the atomic schema.
The following key dimensions are required for OIPI reporting as these dimensions are being directly consumed by the reports.
· Acquisition Channel Dimension
· Attrition Dimension
· Billing Account Dimension
· Campaign Dimension
· Dimension Policy Type
· Lines of business Dimension
· Loan Product Category Dimension
· Location Dimension
· Organization Structure Dimension
· Producer Agent Dimension
· Producer Dimension
· Product Dimension
· Product Type Dimension
· Quote Source Dimension
· Quote Source Method Dimension
· Quote Submission Method Dimension
· Quotes Declination Type Dimension
· Rate Plan Dimension
· Report Type Dimension
· Dimension Claim Status
· Currency Dimension
· Date Dimension
· Location Hierarchy
· Dimension Policy Status
· Quote Status Dimension
· Quote Type Dimension
· Recovery Type Dimension
· Reporting Line Dimension
· Scenario Dimension
Most of the Fact tables are mapped to staging counterparts through Table to Table (T2T) mappings. Data from source systems can be loaded into staging through flat file or source system interfaces. T2T process then loads data to fact tables. Examples include Fact Common Policy Summary, Fact IPA Policy Summary, and so on. Some of the Fact tables are loaded with processed fact information from other fact tables. Examples include Fact Policy Profitability, Fact Aggregate Producer Summary, and so on.
Fact Entity Name |
Source |
Source Entities |
Method of Populating Measures |
|---|---|---|---|
Fact Aggregate Producer Summary |
Fact |
Fact Common Policy Summary, Fact IPA Policy Summary, Fact PFT Policy Summary, Fact Insurance Quotes, Fact Insurance Quote Reports, and Fact Claim Details |
T2T/DT |
Fact Claim Claimant Mapping |
Stage |
|
T2T |
Fact Claim Details |
Stage |
Stage Claim Details |
T2T |
Fact Claim Referral Reason Mapping |
Stage |
Stage Claim Referral Reason Mapping |
T2T |
Fact Claim Transaction |
Stage |
Stage Claim Transactions |
T2T |
Fact Common Policy Summary |
Stage |
Stage Property and Casualty Contracts |
T2T |
Fact Insurance Quotes Report |
Stage |
Stage Insurance Quote with Reports |
T2T |
Fact IPA Policy Summary |
Stage |
Stage Property and Casualty Contracts |
T2T |
Fact PFT Policy Summary |
Stage |
Stage Property and Casualty Contracts |
T2T |
Fact Policy Coverage's |
Stage |
Stage Policy Coverage's |
T2T |
Fact Policy Profitability |
Fact |
Fact Common Policy Summary, Fact IPA Policy Summary, Fact PFT Policy Summary, Fact Insurance Quotes, Fact Insurance Quote Reports, and Fact Claim Details |
T2T |
Fact Policy Rate Modifiers Map |
Stage |
Stage Policy Rate Modifier |
T2T |
Fact Policy Relationship |
Stage |
Stage Policy Customer Relationship |
T2T |
Fact Policy Transactions |
Stage |
Stage Property And Casualty Policy Transactions |
T2T |
Fact Premium Transactions |
Stage |
Stage Premium Transactions |
T2T |
Fact Producer Appointments |
Stage |
Stage Producer Contracts |
T2T |
Insurance Quotes Fact |
Stage |
Stage Insurance Quotes |
T2T |
FCT_PARTY_INS_POLICY_ROLE_MAP |
Stage |
STG_PARTY_INS_POLICY_ROLE_MAP |
T2T |
FCT_PARTY_PARTY_RELATIONSHIP |
Stage |
STG_PARTY_PARTY_RELATIONSHIP |
T2T |
FCT_PARTY_FINANCIALS |
Stage |
STG_PARTY_FINANCIALS |
T2T |
FCT_PARTY_FINANCIAL_DETAIL |
Fact |
FCT_PARTY_FINANCIALS |
T2T |
The BI data model is a star schema for the fact tables FCT_INSURANCE_QUOTES, FCT_INSURANCE_QUOTE_REPORTS, FCT_COMMON_POLICY_SUMMARY, FCT_IPA_POLICY_SUMMARY, FCT_PFT_POLICY_SUMMARY, FCT_CLAIM_DETAILS, FCT_CLAIM_TRANSACTIONS, FCT_AGG_PRODUCER_SUMMARY, FCT_PRODUCER_APPOINTMENTS, and so on.
Following are the subject areas in the ERwin data model:
Figure 3: Insurance Quotes and Reports
Figure 4: Policy Summary
Figure 5: Policy Coverage's
Figure 6: Policy Profitability
Figure 7: Aggregated Producer Summary and Producer Appointments
Figure 8: Claim Details
Figure 9: Claim Transactions
