Cost Table Changes (Add Person_id, Dates and normalize)

link to github issue

Proposal Owner: Gowtham Rao, Chris Knoll, Klaus Bonadt

Discussion: http://forums.ohdsi.org/t/proposal-for-cost-table-adjustment/1842/12

Cost table description: http://www.ohdsi.org/web/wiki/doku.php?id=documentation:cdm:cost

Proposal overview:

1. Add person_id
2. Add billed_datetime and paid_datetime
3. Change Cost table structure - to “normalized” version

1. Add Person_id to Cost table

• Design justification: We have a design principle that all domain related data tables are person-centric where for each record the person_id and date are captured at a minimum. We deviate from this design principle in the COST table. Addition of person_id will make the SQL statement in database systems faster. A missing person_id prevents other lightweight non-database systems to process all transaction tables chunk wise. In certain database environments (specifically Massively Parallel Processing (MPP) that we see in redshift and MS PDW (and possibly HiveDB on Hadoop)) we want to be able to hash on a field of the table. By NOT having a person id in the cost table, we can't hash the data such that the data for a given person can be co-located on the same cluster. By HAVING this person_id field, we can define the table as hashed on person_id.
• Discussion for and against: However, adding the person id and date will further de-normalize the OMOP CDM Schema. Local deployments of OMOP may add person_id on their own. This was countered by: but that will fail the standardization principle. The query optimizer may not leverage a hashing column if the column isn't applied in the query - standard OHDSI applications will not use person_id if it is not a standard OMOP CDM standard. While trying to calculate an average cost of something by person from the cost table, we have to go to the drug_exposure table, join back to cost (on the domain=“drug”) and the drug_exposure_id (neither of these are hashed). This will cause a sort of 'shuffle' move of data from the cost table (which can only be hashed on a cost_id) UNLESS we say where cost.person_id = drug_exposure.person_id. Cost-table is already denormalized with many cost entries (allowed amount, paid by payer, paid by patient etc). Alternative would be to redesign the cost table to cost_type and one cost_value instead of many COST entries. Addition of person_id may not impact the already denormalized table, but would rather enable us to process huge datasets without the need of spending cost expensive hardware and software resources for building up complex queries thru multiple inefficient joins.
• Data integrity checks: To avoid the possibility of database integrity conflicts, e.g. the person_id passed through the event table might be different from the one in the COST table. We need to add it to the constraints (slow), a warning in the description or data quality tools like ACHILLES HEELS, which should watch that kind of thing.

2. Add incurred_date, incurred_datetime, billed_datetime, paid_datetime:

• Add three new datetime fields in the cost table. billed_datetime and paid_datetime
• Incurred_date or service_date (match event table) - This is in-line with the design principle.
• Both fields are optional (Required = No), they are date time fields (Type = DateTime - following OMOP conventions)
• Costs are associated with a visit_occurrence, procedure_occurrence, drug_occurrence, observation, device etc.
• There are generally three types of dates associated with costs. Incurred date, Billed Date and Paid date.
• Incurred date is the date of the service. They are captured in the respective visit, procedure etc.
• Billed date and paid date are not captured in OMOP CDM. Use cases for these are listed below

Use cases:

• Health economics and actuarial analysis use incurred date, billed date and paid date in the formulas
• Incurred But not reported: https://en.wikipedia.org/wiki/Incurred_but_not_reported amount owed by an insurer to all valid claimants who have had a covered loss but have not yet reported it. Very important for claim reserves estimation (represent the money which should be held by the insurer so as to be able to meet all future claims arising from policies currently in force and policies written in the past.)
• Completion factor trend analysis

Analytic questions:

• Trend analysis for operational efficiency - claims adjudication rate, completion factor, IBNR, claims reserve estimation

Importance:

• New use cases around financial and actuarial departments of organizations
• This will expand the OHDSI/OMOP footprint

Consequence of doing it:

• Adoption of Cost table may increase.
• New use cases that serve the needs of financial entities in an organization will expand the adoption of the OMOP CDM.

Consequences of not doing it:

• Query optimization is difficult.
• Analyst has to write complex queries. Development of standardized tools may be delayed

3. Leverage cost_type_concept_id and remove cost type columns

• Proposed structure of new table is below
• The key idea here is that instead of making each cost-type a column (i.e. wide representation), lets convert to long representation. We will leverage cost_type_concept_id for this.
• cost_type_concept_id will be used to identify standard cost types: total_charge, total_cost, total_paid, paid_by_payer, paid_by_patient, paid_patient_coinsurance, paid_patient_deductible, paid_by_primary, paid_ingredient_cost, paid_dispensing_fee, payer_plan_period_id, amount_allowed. This allows to generalize the cost table and we represent arbitrary number of cost types. We can also represent international and custom use cases.
• Should cost_domain_id be an integer or string? Currently it is a string.