| Dimensional Warehouse Model Data Model |
Description | Analysis of the marketing processes to manage expenditure, improve profit margin, optimize capital efficiency and manage enterprise risk. |
Primary Key | |
![]() |
Dependencies | |
![]() |
Reverse Dependencies | |
![]() |
Attribute Details |
Description | The total cost of an activity or 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 |
Description | Number of actual member responses. |
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 | Average cost for acquiring a new member 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 |
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 cost related to call center activities including for member service, provider service, and other call centers. |
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 | The actual costs incurred in campaign execution 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 |
Description | Actual manpower in full time equivalent (FTE) used to execute the 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 commission cost including commissions paid to employees, brokers, agents and other intermediaries. |
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 sum of all associated costs in new member acquisition. |
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 forecasted total premium income resulting from the new policies that will be sold by the insurance company. |
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 amount the organization planned to spend on market research 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 |
Description | Number of member responses forecast. |
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 | 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 total cost associated with marketing 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 |
Description | The actual costs incurred in marketing planning 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 |
Description | Actual manpower in full time equivalent (FTE) used in marketing planning 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 actual costs incurred in marketing research 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 |
Description | The accuracy of the forecasting of marketing research costs, expressed as the ratio between actual and planned marketing research cost. 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 | Actual manpower in full time equivalent (FTE) used in market research 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 | Full cycle time of the marketing process starting from the initiation of the marketing research till the completion of the campaign execution. |
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 | Measures the accuracy of the member response forecasting as a ratio between number of actual member responses versus forecasted member responses for a 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 rate of customer responses in relation to marketing expenses. Depending on the nature of the campaign this might be modified to represent, for example, the number of responses per $1000 spent on the campaign. Formula: [A/B] (see dependencies and their labels for measures used in the formula) |
Data Type | Standards - Data Domains.ddm/Data Domains/Decimal 9 [DECIMAL(19,9)] |
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 | 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 costs associated with execution of campaigns expressed as a percentage of the total Selling, General and Administrative (SGA) cost. 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 | The costs associated with marketing planning expressed as a percentage of the total Selling, General and Administrative (SGA) cost. 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 | The costs associated with marketing research expressed as a percentage of the total Selling, General and Administrative (SGA) cost. 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 accuracy of the forecasting of marketing research costs, expressed as the ratio between actual and planned marketing research cost. 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 amount of revenue generated by the marketing 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 proportion of the revenue realized as a result of the marketing campaign against the total cost of the marketing process. 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 |
Description | Selling, General and Administrative expenses (SGA) are the total of all direct and indirect selling expenses and all general and administrative expenses of a Health Plan Organization, as reported on a company's income statement. Direct selling expenses are expenses that can be directly linked to the sale of a specific unit such as a broker commission for the sale of a health insurance policy. Indirect selling expenses are expenses which cannot be directly linked to the sale of a specific unit, but which are proportionally allocated to all units sold during a certain period, such as telephone, interest and postal charges. General and administrative expenses include, among other things, salaries of non-sales personnel, rent, heat and lights. |
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 cost related to policy administration, finance, IT, HR and other support functions. |
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 | Measures the total elapsed time between activity start and activity completion. Actual time spent on the creation of a marketing plan measured in 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 |
Description | Measures the time spent on a business activity. Actual time spent on the execution of the marketing 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 | Actual manpower in full time equivalent (FTE) used to execute the marketing process. |
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 | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 | 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 | Marketing 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 | Marketing Activity 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 | Marketing 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 |
Is Identifying Relationship | true |
Child Table | Marketing 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 |
Primary Key Details |
Key Attribute | Plan Member Dk |
Key Attribute | Marketing Activity Dimension Dk |
Key Attribute | Organization Dk |
Key Attribute | Party Role Dk |
Key Attribute | Calendar Dk |
Key Attribute | Geographic Area Dk |
Key Attribute | Group Dk |
Key Attribute | Health Plan Product Dk |
Key Attribute | Insurance Policy Dimension Dk |
Key Attribute | Campaign Dimension Dk |
| Dimensional Warehouse Model Data Model |