Dimensional Warehouse Model Data Model

Entity

Entity Referrals And Authorizations Claims Analysis

Description

Analysis of referrals and authorizations, considering reasons as well as member and provider characteristics. This analysis enables the health plan to identify opportunities for modifying its referral and authorization rules to improve efficiency and effectiveness.

Attributes
Authorization Activity Dimension Dk
Calendar Dk
Claim Dk
Condition Dk
Diagnosis Code Dk
Geographic Area Dk
Group Dk
Number Of Authorization Pends Per 1000 Claims
Number Of Authorizations
Number Of Authorizations Per 1000 Claims
Number Of Authorizations Per 1000 Members
Number Of Claims
Number Of Claims Pended For Authorization
Number Of Claims Pended For Referral
Number Of Members
Number Of Referral Pends Per 1000 Claims
Number Of Referrals
Number Of Referrals Per 1000 Claims
Number Of Referrals Per 1000 Members
Number Of Retroactive Authorizations
Number Of Retroactive Referrals
Number Of Services Requiring Authorization
Number Of Services Requiring Referral
Percentage Retroactive Authorizations
Percentage Retroactive Referrals
Plan Member Dk
Practitioner Dk
Procedure Code Dk
Provider Dk
Referral Reason Dk
Service Dk

Relationship
Referrals And Authorizations Claims Analysis_Calendar Dimension_FK
Referrals And Authorizations Claims Analysis_Geographic Area Dimension_FK
Referrals And Authorizations Claims Analysis_Plan Member Dimension_FK
Referrals And Authorizations Claims Analysis_Provider Dimension_FK
Referrals And Authorizations Claims Analysis_Practitioner Dimension_FK
Referrals And Authorizations Claims Analysis_Condition Dimension_FK
Referrals And Authorizations Claims Analysis_Group Dimension_FK
Referrals And Authorizations Claims Analysis_Diagnosis Code Dimension_FK
Referrals And Authorizations Claims Analysis_Claim Dimension_FK
Referrals And Authorizations Claims Analysis_Procedure Code Dimension_FK
Referrals And Authorizations Claims Analysis_Service Dimension_FK
Referrals And Authorizations Claims Analysis_Authorization Activity Dimension_FK
Referrals And Authorizations Claims Analysis_Referral Reason Dimension_FK

Primary Key
Referrals And Authorizations Claims Analysis PK

Dependencies
NONE

Reverse Dependencies

Attribute Details

Authorization Activity 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

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

Claim 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

Condition 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

Diagnosis Code 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

Number Of Authorization Pends Per 1000 Claims

Description	Number of claims pended for authorization reasons per 1000 claims. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
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 Authorizations

Description	Total number of approved service authorizations.
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 Authorizations Per 1000 Claims

Description	Number of authorizations per 1000 claims. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
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 Authorizations Per 1000 Members

Description	Number of authorizations per 1000 members. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
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 Claims

Description	Total number of healthcare claims.
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 Claims Pended For Authorization

Description	Total number of claims that pend for authorization reasons, such authorization not requested or not approved, service details do not match authorization, missing authorization number, or unable to match claim to authorization.
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 Claims Pended For Referral

Description	Total number of claims that pend for referral reasons, such as referral not requested or not approved, service details do not match referral, missing referral number, or unable to match claim to referral.
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 Members

Description	The total count of members, during the period of analysis. This may be the total number of people with one or more active memberships at a given point in time during the period, or the average number of people with active memberships over the full period.
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 Referral Pends Per 1000 Claims

Description	Number of claims pended for referral reasons per 1000 claims. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
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 Referrals

Description	Total number of referrals.
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 Referrals Per 1000 Claims

Description	Number of referrals per 1000 claims. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
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 Referrals Per 1000 Members

Description	Number of referrals per 1000 members. Formula: [(A/B)*1000] (see dependencies and their labels for measures used in the formula)
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 Retroactive Authorizations

Description	Total number of authorizations approved after a service is rendered and effective retroactively so that a service can be covered.
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 Retroactive Referrals

Description	Total number of referrals issued after the date of 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 Services Requiring Authorization

Description	Total number of medical services that require an authorization.
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 Services Requiring Referral

Description	Total number of medical services that require referral.
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

Percentage Retroactive Authorizations

Description	Percentage of total authorizations that are retroactive authorizations. 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

Percentage Retroactive Referrals

Description	Percentage of total referrals that are retroactive referrals. 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

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

Practitioner 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

Procedure Code 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

Referral Reason 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

Service 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

Referrals And Authorizations Claims Analysis_Calendar Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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

Referrals And Authorizations Claims Analysis_Geographic Area Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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

Referrals And Authorizations Claims Analysis_Plan Member Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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

Referrals And Authorizations Claims Analysis_Provider Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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

Referrals And Authorizations Claims Analysis_Practitioner Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Practitioner Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Referrals And Authorizations Claims Analysis_Condition Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Condition Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Referrals And Authorizations Claims Analysis_Group Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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

Referrals And Authorizations Claims Analysis_Diagnosis Code Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Diagnosis Code Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Referrals And Authorizations Claims Analysis_Claim Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Claim Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Referrals And Authorizations Claims Analysis_Procedure Code Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Procedure Code Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Referrals And Authorizations Claims Analysis_Service Dimension_FK

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

Referrals And Authorizations Claims Analysis_Authorization Activity Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Authorization Activity Dimension
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Referrals And Authorizations Claims Analysis_Referral Reason Dimension_FK

Is Identifying Relationship	true
Child Table	Referrals And Authorizations Claims 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	Referral Reason 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

Referrals And Authorizations Claims Analysis PK

Key Attribute	Calendar Dk
Key Attribute	Geographic Area Dk
Key Attribute	Plan Member Dk
Key Attribute	Provider Dk
Key Attribute	Practitioner Dk
Key Attribute	Condition Dk
Key Attribute	Group Dk
Key Attribute	Diagnosis Code Dk
Key Attribute	Claim Dk
Key Attribute	Procedure Code Dk
Key Attribute	Service Dk
Key Attribute	Authorization Activity Dimension Dk
Key Attribute	Referral Reason Dk

Overview

Top

Package

Entity

Dimensional Warehouse Model Data Model