Understanding the Models

1
Understanding the Models

• Session 602

2
Agenda

• Models - common characteristics
• Support
• Claims
• Credit
• Card
• Admin
• Meta Data

3
Model Common Characteristics

• History Current View
• Frequency Daily, Monthly, special
• Optionally loaded tables
• Surrogate keys
• Natural keys
• Purge indicators
• indicates development status

4
Support

• Table Review
• Loaded nightly
• Some tables are system specific
• Many of the system tables have relationships with
  the support tables

5
Claims

• Items unique about Claims
• No Surrogate keys
• Claim base/coverage records
• Subject area
• Model review

6
Claim Data Warehouse - Sources / Frequency
Subjects Not Included
Customer
Address
Post File
Letter / Mail
Wrap File
Diagnosis
Claim
Payment
Status
Coverage
Daily Claim Master File Monthly Claim
Reserve File
Monthly Reserves
7
Claim Data Warehouse
8
Credit

• Items unique about Credit
• Monthly/ITD tables
• Purge summary information
• Subject areas
• Model review
• Tables optionally loaded

9
Credit Data Warehouse - Sources / Frequency
Customer
Address
Subjects Not Included
Claim Database
Wrap File
Status
Policy
Transfer
Accounting
Refund
Action
Cancel
Coverage
YEXT File
Letter
Daily Detail Files Month-End
MEXT and REXT
Monthly Numbers
Monthly Compensation
10
Credit Data Warehouse
11
CreditOptionally Loaded Tables

• Cred_Insured
• Cred_Coverage
• Cred_Cancel
• Cred_Action
• Cred_ActionLetter

• Cred_ActionStatus
• Cred_ActionCheck
• Cred_ActionLetter
• Cred_Address
• Cred_Transfer

12
Card

• Items unique about Card
• Monthly Billing tables
• Subject areas
• Model review
• Tables optionally loaded

13
Card Data Warehouse - Sources / Frequency
Customer
Claim
Subjects Not Included
Address
Transfer
Comment
Policy
Action
Wrap File data
Adjustment
Coverage
Compensation
Daily Card Detail File Per Billing Cycle
Card Billing File
Monthly - Billed Premium - Outstanding Balance
14
Card Data Warehouse
15
CardOptionally Loaded Tables

• Card_Customer
• Card_Coverage
• Card_AcctBalUpd
• Card_Acct CovBalUpd
• Card_Adjustment
• Card_Action

• Card_ActionLLetter
• Card_ActionStatus
• Card_ActionInfo
• Card_Address
• Card_Transfer
• Card_AcctMthlyBill
• Card_CovMthlyBill

16
Admin

• Items unique about Admin
• First Year tables
• Subject areas
• Model review
• Tables optionally loaded

17
Admn Data Warehouse - Sources / Frequency
Customer
Claim
Subjects Not Included
Underwriting
Address
Transfer
Pending Business
Policy
Wrap File
Billing Comp
Actions
Accounting
Coverage Riders
Daily Detail, Name, Action, Underwrite
Files Month-End MEXT and REXT
MEXT Reinsurance
18
Admin Data Warehouse
19
AdminOptionally Loaded Tables

• Admn_Coverage
• Admn_Cancel
• Admn_Transfer
• Admn_Reinstate
• Admn_BenefitChange
• Admn_UnderwriteInfo
• Admn_Action
• Admn_ActionLetter

• Admn_ActionStatus
• Admn_ActionAdjustment
• Admn_ActionRefimd
• Admn_ActionSurrender
• Admn_ActionReturnOfPremium
• Admn_Billing
• Admn_Payment
• Admn_BillingComp

20
Metadata

• Describes all the tables in the DW
• Another table describes the fields in each table
• Some system specific tables

21
Questions?
22
Thank you!