| Dimensional Warehouse Model Data Model |
Description | Department of Health and Human Services (HHS) released interim final rules in December 1, 2010 for Health Insurance Issuers Implementing Medical Loss Ratio (MLR) requirement under the Patient Protection and Affordable Care Act (PPACA). The medical loss ratio (MLR) is an accounting statistic that, stated simply, measures the percentage of total premiums that insurance companies spend on health care and quality initiatives, versus what they spend on administration, marketing and profit. Insurers may increase spending on quality-promoting activities and programs. These programs, include Quality Reporting, Case Management, Care Coordination, Chronic disease Management and Medication Compliance, have the potential to create a societal benefit by improving outcomes and population health. HHS has established 80 percent MLR requirement nationwide for the individual and small group markets, and an 85 percent MLR requirement nationwide for the large group market.The HHS MLR is stated as: (Expenses to Improve Health Care Quality + Incurred Claims as of 31March) Medical Loss Ratio = ---------------------------------------------------------------------------------- (Earned Premium - Federal & State Taxes - Licensing & Regulatory Fees) Each of these components in the calculation has a number of sub-measures. MLR components include: . Incurred Claims . Activities that improve healthcare quality for enrollee . Quality reporting . Effective case management, care coordination, chronic disease management, and medication and care compliance initiatives . Prevent hospital re-admission . Improve patient safety and reduce medical errors . Improve wellness and health promotion . Designed towards enrollees to improve health quality . Based on evidence base medicine . Health Information Technology activities that are attributed to activities listed above in improving healthcare . Non-Claim cost . Federal and State taxes . Licensing and Regulatory fees The following are the major dimensions for MLR: 1. State - is the state where the insurer has license to issue/sell healthcare coverage 2. Licensed entity - is the legal entity doing the business in the state 3. Market - as defined for MLR a. Individual health plan b. Small group health plan c. Large group health plan 4. Time period - reporting period |
Primary Key | |
![]() |
Dependencies | |
![]() |
Reverse Dependencies | |
![]() |
Attribute Details |
Description | Medical Loss Ratio (MLR) adjusted for the credibility factor, base credibility and deductible credibility Formula: [A + B] (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 | Credibility Adjustments is based on Life Years and Deductibles. Base Credibility Adjustment is based on the size of the plan in terms of Life Years: - Non Credible Plans - Plans involving less than 1,000 Life Years are deemed to be too small to be credible for MLR calculations and are therefore not eligible for any calculation of rebates. - Fully Credible Plans - Plans involving 75,000 or more Life Years are deemed to be large enough not to require any credibility adjustment. The HHS Medical Loss Ratio is therefore used for the calculation of any rebates. - Partially Credible Plans - Credibility adjustments for licensed entities that have partially credible experience, that is, issuers with life years that are greater than or equal to 1,000 life years but less than 75,000 life years, based on the NAIC data for the previous year. So not all insurers are eligible for this credit and the credit is based on plan life years as listed in the table below: TABLE 1: Base Credibility Factors Life-Years Base Credibility Factor 1,000 No Credibility 1,000 8.3% 2,500 5.2% 5,000 3.7% 10,000 2.6% 25,000 1.6% 50,000 1.2% 75,000 0.0% The base credibility factor is determined by linear interpolation. |
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 claim payments made to healthcare providers. |
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 Credibility Adjustment Factor that depends on average per person deductible for the experience reported in the MLR for a particular market and State. The Deductible Factor is calculated based the following table: TABLE 2: Deductible Factor Health plan deductible Deductible factor less than $2,500 1.000 $2,500 1.164 $5,000 1.402 greater than or equal $10,000 1.736 The deductible credibility factor is determined by linear interpolation. |
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 | It is the sum of all monies paid by a policyholder as a condition of receiving coverage from a health insurance issuer less any federal and state taxes |
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 | Expenses incurred by the insurer to implement Health Information Technology (HIT) that is attributable to improving healthcare and wellness. These may include expenses related to: improving health care, preventing hospital readmissions, improving patient safety and reducing errors, or promoting health activities and wellness to an individual or an identified segment of the population, HIT implemented to control or contain costs for example to improve claim processing should not be considered as a quality improving 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 | Expenses incurred by the insurer to implement wellness and health promotion activities. These are: (1) designed to improve health quality; (2) designed to increase the likelihood of desired health outcomes in ways that are capable of being objectively measured and of producing verifiable results and achievements; (3) directed toward individual enrollees or incurred for the benefit of specified segments of enrollees or provide health improvements to the population beyond those enrolled in coverage as long as no additional costs are incurred due to the non-enrollees; and (4) grounded in evidence based medicine, widely accepted best clinical practice, or criteria issued by recognized professional medical associations, accreditation bodies, government agencies or other nationally recognized health care quality organizations. Formula: [A+B+C+D+E+F+G+H] (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 | Expenses incurred by the insurer in order to improve Health Care Quality for the enrollee Formula: [A+B+C+D+E] (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 | Expenses incurred by insurer to improve health outcomes. These include the implementation of activities such as: quality reporting, effective case management, care coordination, chronic disease management, and medication and care compliance initiatives, including through the use of the medical homes model |
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 | Expenses incurred by the insurer to implement activities to improve patient safety and reduce medical errors through the appropriate use of best clinical practices, evidence-based medicine, and health information technology under the plan or coverage |
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 | Expenses incurred by the insurer to implement activities to prevent hospital readmissions through a comprehensive program for hospital discharge. These activities include: patient-centered education and counseling, comprehensive discharge planning, and post discharge reinforcement by an appropriate health care professional |
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 | Experience rating refunds are retrospective premium adjustments arising from retrospectively rated contracts |
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 | Experience rating refunds are retrospective premium adjustments arising from retrospectively rated contracts. 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 | Federal taxes as all Federal taxes and assessments allocated to health insurance coverage excluding Federal income taxes on investment income and capital gains. State taxes and assessments that must be separately identified and reported to the Secretary include: Any industry-wide (or subset) assessments (other than surcharges on specific claims) paid to the State directly, or premium subsidies that are designed to cover the costs of providing indigent care or other access to health care throughout the State; advertising required by law, regulation or ruling, except advertising associated with investments; State income, excise, and business taxes other than premium taxes; State premium taxes plus State taxes based on policy reserves, if in lieu of premium taxes; State sales taxes, if the issuer does not exercise the option of including such taxes with the cost of goods and services purchased; and any portion of commissions or allowances on reinsurance assumed that represents specific reimbursement of premium taxes |
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 incurred but not reported (IBNR) reserve amount is an amount that is set aside to meet the cost of claims that have been incurred but have not yet been reported to the Health Plan 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 |
Description | The sum of all incentive payments. |
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 increase in reserves for outstanding claims and IBNR claims during the reference period. Formula: [Min((A end of the period - A start of the period), 0) + Min((B end of the period - B start of the period), 0)] (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 | Licenses & Regulatory Fees paid by the insurer as part of doing business in the state |
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 medical loss ratio (MLR) is an accounting statistic that, measures the percentage of total premiums that insurance companies spend on health care and quality initiatives, versus what they spend on administration, marketing and profit. The HHS MLR is stated as: (Expenses to Improve Health Care Quality + Incurred Claims as of 31March) Medical Loss Ratio = ---------------------------------------------------------------------------------- (Earned Premium - Federal & State Taxes - Licensing & Regulatory Fees) Formula: [(A + B)/(C - D - E)] (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 | This is the minimum MLR standard established by the state for a particular market. HHS has established 80 percent MLR requirement nationwide for the individual and small group markets, and an 85 percent MLR requirement nationwide for the large group market. This can be adjusted by the state with proper procedure with HHS |
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 | Medical loss included in paid and unpaid claims at 31 March of the year following the reporting year. Incurred claims is the sum of direct paid claims incurred in the MLR reporting year, unpaid claim reserves associated with claims incurred during the MLR reporting year, the change in contract reserves, reserves for contingent benefits, the claim portion of lawsuits, and any experience rating refunds paid or received Formula: [A+B+C+D+E+F-G] (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 | The change in amount of receivables |
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 provision made by the Health Plan Organization for future claim payments for claims received and reported but not yet adjudicated. That is, the current best estimate of the ultimate value of losses that will be paid in the future, for known claims. |
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 | Rebates relating to Medical Loss Ratio Health Plan Insurance Issuers must provide their enrollees a rebate if their MLR is less than 85 percent in the large group market or less than 80 percent in the small group market and individual market. This means that issuers must spend at least 85 or 80 percent, respectively, of each premium dollar, as adjusted for taxes and regulatory and licensing fees, on reimbursement for clinical services provided to enrollees and activities that improve health care quality. Some States may have different minimum MLR standards. In the States that have established under State law a higher MLR standard than that prescribed by HHS, such higher percentage applies to issuers in that State. In States that have established, under State law, a lower MLR standard than that of HHS standard MLR, the higher percentage set by HHS applies to issuers. For each MLR reporting year, an issuer must rebate to the enrollee the total amount of premium revenue received by the issuer from the enrollee after subtracting Federal and State taxes and licensing and regulatory fees multiplied by the difference between the MLR required and the issuer's calculated MLR Rebate amount = Where A = minimum MLR standard for a particular market B = adjusted State MLR for that market C = earned premium D = federal and state taxes E = licensing and regulatory fees For example, an issuer must rebate a pro rata portion of premium revenue if it does not meet an 80 percent MLR for the small group market in a State that has not set a higher MLR. If an issuer has a 75 percent MLR for the coverage it offers in the small group market in a State that has not set a higher MLR, the issuer must rebate 5 percent of the premium paid by or on behalf of the enrollee for the MLR reporting year after subtracting taxes and fees from the premium. In this example, an enrollee may have paid $2,000 in premiums for the MLR reporting year. If the Federal and State taxes and licensing and regulatory fees that may be excluded from premium revenue are $150 for a premium of $2,000, then the issuer would subtract $150 from premium revenue, for a base of $1,850 in premium. The enrollee would be entitled to a rebate of 5 percent of $1,850, or $92.50. Formula: [(A - B)*(C - D - E)] (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 | Reserves allocated for Experience Rating Refunds, for retrospective premium adjustments and claim law suites |
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 credibility adjustment to the MLR equals the base factor times the deductible factor. This is based on the life years experience of the insurer and the average per person deductibles payed by the enrollee in a given state and market 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 | Expenses associated with coaching, education programs and health promotion activities designed to change member behavior and conditions. Expenses associated with Coaching and/or education programs and health promotion activities designed to change member behavior and conditions for example providing programs and support to get rid off smoking habits, weight loss program to address obesity, etc., |
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 | Expenses associated with quality reporting and related documentation that are in non-electronic form for wellness and health promotion 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 | Expenses associated with wellness assessments of wellness and health promotion initiatives |
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 | Expenses associated with coaching programs designed to educate individuals on clinically effective methods for dealing with a specific chronic disease or condition |
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 | Expenses associated with health information technology to support all the wellness and health promotion 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 | Expenses associated with wellness/lifestyle coaching programs designed to achieve specific and measurable improvements |
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 | Expenses associated with Public health education campaigns that are performed in association with state or local health departments and agents |
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 | Expenses associated with and incurred as part of giving rewards, incentives, bonuses, reductions in co-payments (excluding administration expenses), that are not already reflected in premiums or claims |
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 |
Relationship Details |
Is Identifying Relationship | true |
Child Table | HHS Medical Loss Ratio and Rebate 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 | HHS Medical Loss Ratio and Rebate 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 | HHS Medical Loss Ratio and Rebate 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 | HHS Medical Loss Ratio and Rebate 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 | Market Segment 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 | Calendar Dk |
Key Attribute | Geographic Area Dk |
Key Attribute | Organization Dk |
Key Attribute | Market Segment Dimension Dk |
| Dimensional Warehouse Model Data Model |