| Dimensional Warehouse Model Data Model |
Description | Measures campaign success from the perspective of the volume and values of policies and premiums sold. This includes: new members gained, existing members cross-sold to, members who purchased new products but cancelled prior policies, members contacted who did not buy new products, and the premiums earned from each category. This analysis is applicable to group or individual member types. |
Primary Key | |
![]() |
Dependencies | |
![]() |
Reverse Dependencies | |
![]() |
Attribute Details |
Description | That part of the new premiums, purchased during the campaign by existing members that relates to increases to existing policies. May be applied to group or individual members as appropriate to the campaign. |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Description | The elapsed time between generation of a lead and making first contact with the prospect or member. |
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 |
Description | Indicator of the success of the new member acquisition process whether aimed at group or individual level. Expressed as the ratio between the number of new members gained (including cross-sales) and the number of targeted new members. 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 |
Description | That part of the new earned premium, purchased during the campaign by existing members that relates to the purchase of new policies. May be applied to group or individual members as appropriate to the campaign.. |
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 |
Description | The total earned premium purchased by members who owned one or more products prior to the campaign, who either increased their premiums or who purchased further products. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | The total new earned premium due from members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | The number of complaints made by members that can be directly attributable to the campaign activity. |
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 |
Description | Number of telephone calls that can be directly attributable to the campaign activity. |
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 |
Description | The total number of communications issued by parties contacted in the context of marketing activities. |
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 |
Description | The number of members who owned one or more products prior to the campaign, who canceled their premium on these existing products following cross-selling. |
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 |
Description | The number of members who owned one or more products prior to the campaign, who either increased their holdings / premiums or who purchased further products. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | Total number of contacts made between the member or prospect and the insurance company within a campaign. |
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 |
Description | The number of members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | Total number of contacts made between the insurance company and the member or prospect within a campaign. |
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 |
Description | The number of products canceled by members who owned one or more products prior to the campaign, following cross-selling. |
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 |
Description | The number of new products sold to members that were previously identified as campaign targets, who bought through the campaign, and, who owned one or more products prior to the campaign. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | The number of new products sold to members that were previously identified as campaign targets, who bought through the campaign, and, who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | The number of new products sold to members who owned one or more products prior to the campaign, who purchased further products during the campaign. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | The number of new products sold to members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | Of the members included in the campaign, the number who had policies (i.e. played an ownership role on one or more in-force or active policies). May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | The number of new members in the campaign target list (including cross-sales targets). May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | Number of targeted new members (sales leads including cross-sales) that are actually engaged in the sales execution process, independent of whether the lead results in a sale. May be applied to group or individual members as appropriate to the campaign. |
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 |
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 |
Description | Number of existing members in the targeted list as a percentage of the number of targeted members overall. May be applied to group or individual members as appropriate to the campaign. 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 |
Description | Of the new earned premiums from new products sold during the campaign, the percentage sold to existing members. Gives an indication of whether existing members tend to purchase smaller premiums in follow-on purchases. May be applied to group or individual members as appropriate to the campaign. Formula: [(A/(B+C))*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 |
Description | Of the new products sold in the campaign, the percentage that was sold to existing members. May be applied to group or individual members as appropriate to the campaign. Formula: [(A/(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 |
Description | Measures the percentage of total new premium sold to existing members, including increases to existing policies. May be applied to group or individual members as appropriate to the campaign. Formula: [A*100/(B+A)] (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 |
Description | The number of existing members who purchased new products in the campaign expressed as a percentage of the number of existing members in the targeted list. May be applied to group or individual members as appropriate to the campaign. 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 |
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 |
Description | The total amount of premium income written during the period. |
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 |
Description | Total amount of actual income produced by new business that is concluded with new members (including cross-sales). May be applied to group or individual members as appropriate to the campaign. |
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 |
Description | Total amount of actual income produced by new business from new products. |
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 |
Description | The proportion of the revenue that is produced by acquiring new members. May be applied to group or individual members as appropriate to the campaign. Formula: [A/B] (see dependencies and their labels for measures used in the formula) |
Data Type | Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)] |
Is Part Of PrimaryKey | false |
Is Required | false |
Is Derived | false |
Is Surrogate Key | false |
Description | The proportion of the revenue that is produced by selling new products. Formula: [A/B] (see dependencies and their labels for measures used in the formula) |
Data Type | Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)] |
Is Part Of PrimaryKey | false |
Is Required | false |
Is Derived | false |
Is Surrogate Key | false |
Description | Total number of existing and new members in the campaign target list. May be applied to group or individual members as appropriate to the campaign. Formula: [A+B] (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 |
Relationship Details |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 | Campaign Dimension |
Parent Multiplicity | ONE |
Parent Referential Integrity: On Delete | NONE |
Parent Referential Integrity: On Insert | NONE |
Parent Referential Integrity: On Update | NONE |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Is Identifying Relationship | true |
Child Table | Campaign Sales 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 |
Key Attribute | Plan Member Dk |
Key Attribute | Calendar Dk |
Key Attribute | Group Dk |
Key Attribute | Campaign Dimension Dk |
Key Attribute | Geographic Area Dk |
Key Attribute | Intermediary Dimension Dk |
Key Attribute | Health Plan Product Dk |
Key Attribute | Organization Dk |
| Dimensional Warehouse Model Data Model |