| Dimensional Warehouse Model Data Model |
| Description | Analysis of the product/product line makeup and loyalty of the member population by member characteristics. |
| Primary Key | |
 Member Retention Analysis PK |
|
| Dependencies | |
 NONE |
|
| Reverse Dependencies | |
| |
|
| Attribute Details |
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 |
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 |
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 |
Gestational Age Variation At Enrollment
| Description | The variation in gestational age, at enrollment, among female members, who had a newborn delivery, during the period of analysis. Formula: [(A by Pregnancy.Estimated Gestational Age Range at Enrollment)/A] (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 |
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 |
Member Geographic Diversity
| Description | The percentage of members, with known residential details, who are located in a given state or geographic area. Formula: [((A by Person.Primary Residential Address)/A)*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 Language Diversity
| Description | The percentage of members, with known spoken language details, that fall into one or more language groups. Formula: [((A by Person.Preferred Spoken Language)/A)*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 Race Or Ethnic Diversity
| Description | The percentage of members that fall into one or more race and/or ethnic groups. Formula: [((A by Person.Race and Person.Ethnicity)/A)*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 Retention Rate
| Description | The persistency of membership with the health plan. Formula: [1-(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 |
Number Of Member Months
| Description | The total number of months that a member has one or more active memberships, in one or more health benefits plans, during the period of analysis. This may be a point in time calculation of the number of members, at a specific date in every month (15th) or it may be rounded sum of the number of months based on each members enrollment dates. |
| 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 Membership Months
| Description | The total number of months that a member has an active membership, in one health benefits plan, during the period of analysis. This may be a point in time calculation of the number of memberships, at a specific date in every month (15th) or it may be rounded sum of the number of months based on each membership enrollment dates. |
| 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 Memberships
| Description | The total number of memberships, for the specific period of analysis. This may be the total number of active memberships at a given point in time during the period, or the average number of active memberships over the full period of analysis. A membership may be classified as enrollment by a member in a specific health benefits plan. |
| 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 Lost
| Description | Number of people who ceased to be members of the organization during the specified period. Formula: [A period end - A period start] (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 Members Requiring Interpretive Assistance
| 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 |
Percentage Of Members Requiring Interpretive Assistance
| Description | The percentage of members who have a known requirement for interpreter assistance when communicating with healthcare providers. 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 |
| Relationship Details |
Member Retention Analysis_Calendar Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Member Retention 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 Retention Analysis_Geographic Area Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Member Retention 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 Retention Analysis_Plan Member Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Member Retention 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 Retention Analysis_Condition Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Member Retention 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 |
Member Retention Analysis_Group Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Member Retention 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 Retention Analysis_Health Plan Product Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Member Retention 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 |
| Primary Key Details |
Member Retention Analysis PK
| Key Attribute | Plan Member Dk |
| Key Attribute | Condition Dk |
| Key Attribute | Health Plan Product Dk |
| Key Attribute | Calendar Dk |
| Key Attribute | Geographic Area Dk |
| Key Attribute | Group Dk |
| Dimensional Warehouse Model Data Model |