Atomic Warehouse Model Data Model

Entity

Entity Intermediary Detail

Description

The role of a party in an intermediary agreement. The intermediary represents the marketing, selling or servicing of agreements. The role player can be a person (an agent or a broker), or an organization (an agency). Every channel role can play multiple selling channel and servicing channel roles, one for each agreement.

For example:
Broker
Call center
Tied agent

Attributes
Achievement Level Code Sk
Authority Level Code Sk
Authority Level Dt
Book of Business Val
Business Objective
Claims Management Authority
Commercial Targets
Education Support
Effective From Dt
Effective To Dt
Independence Category Code Sk
Infrastructure Support
Intermediary Revenue
Intermediary Sk
Intermediary Status Code Sk
Intermediary Type Code Sk
Load Info Sk
Marketing Support
Prospects Quota
Seniority Level Code Sk
Service Level
Source Code Sk
Tenant Sk
Tenure in Industry
Training Level Code Sk
Underwriting Authority Level Code Sk
Underwriting Authority Start Dt
Valid From Ts
Valid To Ts

Relationship
Intermediary Detail_Intermediary_FK

Primary Key
Intermediary Detail PK

Dependencies

Reverse Dependencies

Attribute Details

Achievement Level Code Sk

Description	Indicates the status of the achievement of the intermediary in monetary terms.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Authority Level Code Sk

Description	Indicates the authority level of the intermediary. For example: Selling Servicing
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Authority Level Dt

Description	The date upon which the current Authority Level was assigned to the intermediary.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Book of Business Val

Description	Total value of the rights to the book of business held by the intermediary. For example: 50,000 USD
Data Type	Standards - Data Domains.ddm/Data Domains/Free Text [VARCHAR(1024)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Business Objective

Description	A goal set by management to be achieved by a certain date. The success criteria for attaining the goal must be clearly defined and measurable. For example: The 31 July 1992 target of 100 policies sold to new customers.
Data Type	Standards - Data Domains.ddm/Data Domains/Free Text [VARCHAR(1024)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Claims Management Authority

Description	The textual description explaining the authority of the intermediary with respect to the management of claims.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Commercial Targets

Description	The textual description explaining the commercial targets such as the growth rate of a portfolio, a portfolio retention rate.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Education Support

Description	The textual description explaining the education support provided to the intermediary such as a general sales training.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Effective From Dt

Description	Establishes a period where a set of attributes are true according to the business.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Effective To Dt

Description	Ends a period of effectivity.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Independence Category Code Sk

Description	Indicates the nature of the business relationship between the intermediary and the modeled organization. For example: Employed Fully independent Limited independence Tied
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Infrastructure Support

Description	The textual description explaining the infrastructure support provided to the intermediary such as a laptop.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Intermediary Revenue

Description	The annual revenue of a party who plays an intermediary role in the insurance business. For example: 2,000,000 USD annually
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Intermediary Sk

Description	The generated surrogate key of the Intermediary anchor.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Intermediary Status Code Sk

Description	Indication of the status of the intermediary's agreement with the health plan organization. For example: Active Suspended Terminated
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Intermediary Type Code Sk

Description	Indicates the nature of the relationship between the intermediary and the health plan organization (including tied agent, broker and call centre employee). For example: Tied agent
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Load Info Sk

Description	The surrogate key of the load information entry describing the details regarding the loading of the row.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Marketing Support

Description	The textual description explaining the marketing support provided to the intermediary such as mailing, basic advertising brochures, advertising in local newspapers and gifts.
Data Type	Standards - Data Domains.ddm/Data Domains/Text Medium [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Prospects Quota

Description	The maximum number of prospects that a intermediary is allowed to manage at any one time.
Data Type	Standards - Data Domains.ddm/Data Domains/Number Integer [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Seniority Level Code Sk

Description	Indicates the level of seniority of the intermediary.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Service Level

Description	A free-text statement used to explain the service level a partner guarantees for all services performed.
Data Type	Standards - Data Domains.ddm/Data Domains/Free Text [VARCHAR(1024)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Source Code Sk

Description	The origin of the data identifying the actual load source, vendor, manual key entry, or context of the data in a specific row in the database.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Tenant Sk

Description	The surrogate key of the entry identifying the legal owner of the data.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Tenure in Industry

Description	How long has this agent been an insurance agent. For example: 5 years
Data Type	Standards - Data Domains.ddm/Data Domains/Small Counter [SHORT]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Training Level Code Sk

Description	Indicates the level of health insurance business education successfully completed by the intermediary.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Underwriting Authority Level Code Sk

Description	Indicates the restriction that applies to the intermediary with respect to deciding the terms on which an agreement will be accepted. For example: L (Limited) N (None) P (Preferred)
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Underwriting Authority Start Dt

Description	The date upon which the Underwriting Authority Level was granted.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Valid From Ts

Description	Establishes a period where a set of attributes are true in the source system. This would be populated with the transaction timestamp and would be used for the snapshot date.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid To Ts

Description	Ends a period of validity.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Relationship Details

Intermediary Detail_Intermediary_FK

Is Identifying Relationship	true
Child Table	Intermediary Detail
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Intermediary
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Intermediary Detail PK

Key Attribute	Intermediary Sk
Key Attribute	Valid From Ts

Overview

Top

Package

Entity

Atomic Warehouse Model Data Model