Dimensional Warehouse Model Data Model

Entity

Entity Intermediary Dimension

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 Cd
Achievement Level Code Descr
Achievement Level Code Sk
Authority Level Cd
Authority Level Code Descr
Authority Level Code Sk
Authority Level Dt
AWM Load Info Sk
Book of Business Val
Business Objective
Claims Management Authority
Commercial Targets
Current Ind
DWM Load Info Sk
Education Support
Effective From Dt
Effective To Dt
Health Plan Group Dk
Health Plan Group Sk
Independence Category Cd
Independence Category Code Descr
Independence Category Code Sk
Infrastructure Support
Intermediary Dimension Bk
Intermediary Dimension Dk
Intermediary Dimension Sk
Intermediary Revenue
Intermediary Status Cd
Intermediary Status Code Descr
Intermediary Status Code Sk
Intermediary Type Cd
Intermediary Type Code Descr
Intermediary Type Code Sk
Marketing Support
Party Role Dk
Party Role Sk
Prospects Quota
Seniority Level Cd
Seniority Level Code Descr
Seniority Level Code Sk
Service Level
Source Cd
Source Code Descr
Source Code Sk
Tenant Sk
Tenure in Industry
Training Level Cd
Training Level Code Descr
Training Level Code Sk
Underwriting Authority Level Cd
Underwriting Authority Level Code Descr
Underwriting Authority Level Code Sk
Underwriting Authority Start Dt
Valid From Ts
Valid To Ts

Relationship
Intermediary Dimension_Health Plan Group Dimension_FK
Intermediary Dimension_Party Role Dimension_FK

Primary Key
Intermediary Dimension PK

Dependencies

Reverse Dependencies

Attribute Details

Achievement Level Cd

Description	Indicates the status of the achievement of the intermediary in monetary terms.
Data Type	Standards - Data Domains.ddm/Data Domains/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Achievement Level Code Descr

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

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 Cd

Description	Indicates the authority level of the intermediary. For example: Selling Servicing
Data Type	Standards - Data Domains.ddm/Data Domains/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Authority Level Code Descr

Description	Indicates the authority level of the intermediary. For example: Selling Servicing
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
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

AWM Load Info Sk

Description	The surrogate key of the load information entry describing the details regarding the loading of the row in the atomic data warehouse.
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

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

Current Ind

Description	Set to 'Y' if this is the most recent row of a group having the same Surrogate Key.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

DWM 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

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

Health Plan Group Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Health Plan Group Sk

Description	A numeric surrogate key used to uniquely identify entities in the atomic model.
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

Independence Category Cd

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/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Independence Category Code Descr

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/Description [VARCHAR(250)]
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 Dimension Bk

Description	Concatenated values of a set of business attributes that uniquely identify the row.
Data Type	Standards - Data Domains.ddm/Data Domains/Business Key [VARCHAR(255)]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Intermediary Dimension Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
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	true

Intermediary Dimension Sk

Description	A numeric surrogate key used to uniquely identify entities in the atomic model.
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

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 Status Cd

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/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Intermediary Status Code Descr

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/Description [VARCHAR(250)]
Is Part Of PrimaryKey	false
Is Required	false
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 Cd

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/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Intermediary Type Code Descr

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/Description [VARCHAR(250)]
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

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

Party Role Dk

Description	A numeric surrogate key used to uniquely identify entities in the dimensional model.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Party Role Sk

Description	A numeric surrogate key used to uniquely identify entities in the atomic model.
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

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 Cd

Description	Indicates the level of seniority of the intermediary.
Data Type	Standards - Data Domains.ddm/Data Domains/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Seniority Level Code Descr

Description	Indicates the level of seniority of the intermediary.
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
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 Cd

Description	The code that corresponds to the Source Code Sk.
Data Type	Standards - Data Domains.ddm/Data Domains/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Source Code Descr

Description	The description that corresponds to the Source Type Cd.
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
Is Part Of PrimaryKey	false
Is Required	true
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 Cd

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

Training Level Code Descr

Description	Indicates the level of health insurance business education successfully completed by the intermediary.
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
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 Cd

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/Tenant Common Code [VARCHAR(80)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Underwriting Authority Level Code Descr

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/Description [VARCHAR(250)]
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	false
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 Dimension_Health Plan Group Dimension_FK

Is Identifying Relationship	false
Child Table	Intermediary Dimension
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	Health Plan Group Dimension
Parent Multiplicity	ZERO_TO_ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Intermediary Dimension_Party Role Dimension_FK

Is Identifying Relationship	false
Child Table	Intermediary Dimension
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	Party Role Dimension
Parent Multiplicity	ZERO_TO_ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Intermediary Dimension PK

Key Attribute

Intermediary Dimension Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model