Dimensional Warehouse Model Data Model

Entity

Entity Member Service Optimization And Expense Analysis

Description

Analysis of the organization's member service activities to monitor expenditure, optimize capital efficiency and manage enterprise risk.

Attributes
Average Cost Per Member Service Request Amount
Average Member Problem Resolution Cycle Time
Calendar Dk
Geographic Area Dk
Group Dk
Health Plan Product Dk
Insurance Policy Dimension Dk
Intermediary Dimension Dk
IT Costs Allocated To Member Service Amount
Job Dk
Member Information Management Cost Amount
Member Information Management Personnel Utilization
Member Interface Management Cost Amount
Member Interface Management Personnel Utilization
Member Problem Resolution Cycle Time
Member Service Capex As Percentage Of Total
Member Service Capital Expenditure Amount
Member Service Cost Amount
Member Service Personnel Utilization
Members Lost Due To Service Issues
Number Of Member Service Requests
Organization Capital Expenditure Amount
Organization Dk
Party Role Dk
Plan Member Dk
Provider Dk

Relationship
Member Service Optimization And Expense Analysis_Calendar Dimension_FK
Member Service Optimization And Expense Analysis_Geographic Area Dimension_FK
Member Service Optimization And Expense Analysis_Plan Member Dimension_FK
Member Service Optimization And Expense Analysis_Provider Dimension_FK
Member Service Optimization And Expense Analysis_Group Dimension_FK
Member Service Optimization And Expense Analysis_Job Dimension_FK
Member Service Optimization And Expense Analysis_Party Role Dimension_FK
Member Service Optimization And Expense Analysis_Intermediary Dimension_FK
Member Service Optimization And Expense Analysis_Insurance Policy Dimension_FK
Member Service Optimization And Expense Analysis_Health Plan Product Dimension_FK
Member Service Optimization And Expense Analysis_Organization Dimension_FK

Primary Key
Member Service Optimization And Expense Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Average Cost Per Member Service Request Amount

Description	Average cost for delivery of member service against a single customer request. Formula: [A/B] (see dependencies and their labels for measures used in the formula)
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

Average Member Problem Resolution Cycle Time

Description	A measure of the anticipated resolution time for a customer problem based on historic information Formula: [A/B] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Calendar 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	false

Geographic Area 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	false

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	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Health Plan Product 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	false

Insurance Policy 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	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	false

IT Costs Allocated To Member Service Amount

Description	Portion of the IT costs that is allocated to the member service process.
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

Job 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	false

Member Information Management Cost Amount

Description	Total of the costs allocated to the member information management activities.
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

Member Information Management Personnel Utilization

Description	Actual utilization of human resources (personnel) for the member information management activities expressed in full time equivalent (FTE) days.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Member Interface Management Cost Amount

Description	Total of the costs allocated to the member interface management activities.
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

Member Interface Management Personnel Utilization

Description	Actual utilization of human resources (personnel) for the member interface management activities expressed in full time equivalent (FTE) days.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Member Problem Resolution Cycle Time

Description	Measures the elapsed time for member problem inquiry and resolution activities, measuring from initial recording of the member problem until the resolution of the problem.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Member Service Capex As Percentage Of Total

Description	The capital expenditure (CAPEX) for the member service processes expressed as a percentage of the total capital expenditures of the company. Formula: [(A/B) * 100] (see dependencies and their labels for measures used in the formula)
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Member Service Capital Expenditure Amount

Description	The amount of capital expenditure (CAPEX) incurred that is attributable to member Service.
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

Member Service Cost Amount

Description	Total cost of performing the member service processes, including management of the interfaces with the member, managing member's information, and, member service delivery.
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

Member Service Personnel Utilization

Description	Actual utilization of human resources (personnel) for the member service activities expressed in full time equivalent (FTE) days.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Members Lost Due To Service Issues

Description	The count of members lost because of actual or perceived deficiencies in service.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Number Of Member Service Requests

Description	The number of requests for services received from members. A series of communications about the same issue might be treated as a single request.
Data Type	Standards - Data Domains.ddm/Data Domains/Count [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Organization Capital Expenditure Amount

Description	Overall capital expenditure (CAPEX) figure for the organization.
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

Organization 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	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	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Plan Member 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	false

Provider 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	false

Relationship Details

Member Service Optimization And Expense Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Calendar Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Geographic Area Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Plan Member Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Provider Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Group Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Job Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Job Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Party Role Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Intermediary Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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 Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Insurance Policy Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Insurance Policy Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Health Plan Product Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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 Product Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Member Service Optimization And Expense Analysis_Organization Dimension_FK

Is Identifying Relationship	true
Child Table	Member Service Optimization And Expense Analysis
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	Organization Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Member Service Optimization And Expense Analysis PK

Key Attribute	Plan Member Dk
Key Attribute	Provider Dk
Key Attribute	Job Dk
Key Attribute	Party Role Dk
Key Attribute	Intermediary Dimension Dk
Key Attribute	Insurance Policy Dimension Dk
Key Attribute	Health Plan Product Dk
Key Attribute	Organization Dk
Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Group Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model