HealthViews Scripts

The scripts and the tables and views they create are as follows:

001_aa_t_hv_availability.sql - Script to record the start time of the HealthViews process, which can be used to track performance.

001_date_dimension.sql - Creates a materialized view for system dates in HealthViews.

002_time_dimension.sql - Creates a materialized view for system times in HealthViews.

003_t_codeset_lookup.sql - Creates a materialized view for all codesets in the system. This view is used by subsequent scripts to expand the code ID to include description, parent, and parent description.

003_t_dim_codemap_lookup.sql - Creates a materialized view for all codemaps in the system, including the type of code map, the concept (drug, diagnosis, and so on), the mapping Created (for example, SNOMED CT to ICD-9), and so on.

005_t_dim_patient.sql - Creates a materialized view for all patient records in the system, including demographic and contact information.

005_t_dim_patient_master.sql - Creates a materialized view for all patient records in the system, including demographic and contact information. Although the script is intended for mastered data, it will also support early stage deployments when patient mastering has typically not been implemented yet.

006_t_dim_provider.sql - Creates a materialized view for all provider records in the system, including contact and demographic information, as well as status (active, inactive, and so on). Although the script is intended for mastered data, it will also support early stage deployments when provider mastering has typically not been implemented yet.

007_t_dim_facility.sql - Creates a materialized view for all facility records in the system including location and status (active or inactive).

007_t_dim_facility_master.sql - Creates a materialized view for all mastered facility records in the system, including location, status, and type.

007_t_facility_location.sql - Creates a materialized view for all facility location records in the system.

008_t_dim_organization.sql - Creates a materialized view for all organization records in the system.

008_t_dim_organization_master.sql - Creates a materialized view for all mastered organization records in the system.

008_t_organizational_unit.sql - Creates a materialized view for all organizational unit records in the system.

010_t_encounter.sql - Creates a materialized view for all encounter records in the system, including the type of encounter, the patient, the arrival, and departure time, and so on.

010_t_provider_specialty.sql - Creates a materialized view for all provider specialty records in the system.

010_t_provider_specialty_master.sql - Creates a materialized view for all mastered provider specialty records in the system.

011_t_admission_event.sql - Creates a materialized view for all admission event records in the system, including the patient, provider, and facility, as well as the event time. All admission events are also joined to their corresponding encounters.

011_t_admission_event_provider.sql - Creates a materialized view of all admission event provider records in the system, including the admission event ID which can be used to join back to the event.

012_t_death_event.sql - Creates a materialized view for all death event records in the system, including the patient, provider, and facility, as well as the event time. All death events are also joined to their corresponding encounters.

012_t_death_event_provider.sql- Creates a materialized view for all death event provider records in the system, including the death event ID which can be used to join back to the event.

013_t_diagnosis_event.sql - Creates a materialized view for all diagnosis event records in the system including the patient, provider, and facility, as well as the event time. All diagnosis events are also joined to their corresponding encounters.

013_t_diagnosis_event_provider.sql - Creates a materialized view for all diagnosis event provider records in the system, including the diagnosis event ID which can be used to join back to the event.

014_t_discharge_event.sql - Creates a materialized view for all discharge event records in the system, including the patient, provider, and facility, as well as the event time. All discharge events are also joined to their corresponding encounters.

014_t_discharge_event_provider.sql - Creates a materialized view of all discharge event providers in the system, including the discharge event ID which can be used to join to the event.

015_t_procedure_event.sql - Creates a materialized view for all procedure event records in the system, including the patient, provider, and facility, as well as the event time. All procedure events are also joined to their corresponding encounters.

015_t_procedure_event_provider.sql - Creates a materialized view for all procedure event providers in the system, including the procedure event ID which can be used to join back to the event.

016_t_observation_event.sql - Creates a materialized view for all observation event records in the system, including the patient, provider, and facility, as well as the event time. All observation events are also joined to their corresponding encounters.

016_t_observation_event_provider.sql - Creates a materialized view of all observation event providers in the system including the observation event ID which can be used to join back to the event.

017_t_medication_admin_event.sql - Creates a materialized view for all medication administration event records in the system, including the patient, prescription information, route for the medication, and so on. All medication administration events are also joined to their corresponding encounters.

017_t_medication_admin_event_provider.sql - Creates a materialized view for all medication administration event providers in the system, including the medication administration event ID which can be used to join back to the event.

017_t_medication_order_event.sql - Creates a materialized view for all medication order event records in the system, including the patient, provider, and facility, as well as the event time. All medication order events are also joined to their corresponding encounters.

017_t_medication_order_event_provider.sql - Creates a materialized view for all medication order event provider records in the system, including the medication order event ID which can be used to join back to the event.

018_t_diagnostic_test_order_event.sql - Creates a materialized view for all diagnostic test order events in the system, including the patient, the diagnostic test code, the diagnostic test procedure, and so on. All diagnostic test order events are also joined to their corresponding encounters.

018_t_diagnostic_test_order_event_provider.sql - Creates a materialized view for all diagnostic test order event providers in the system, including the diagnostic test order event ID which can be used to join back to the event.

019_t_diet_order_event.sql - Creates a materialized view for all diet order events in the system, including the patient, the diet specification code, the service period, and so on. All diet order events are also joined to their corresponding encounters.

019_t_diet_order_event_provider.sql - Creates a materialized view for all diet order event provider records in the system, including the diet order event ID which can be used to join back to the event.

020_t_movement_event.sql - Creates a materialized view for all movement events in the system, including the patient, the facility, the type of move, and so on. All movement events are also joined to their corresponding encounters.

020_t_movement_event_provider.sql - Creates a materialized view for all movement event providers in the system, including the movement event ID which can be used to join back to the event.

021_t_pharmacy_dispense_event.sql - Creates a materialized view for all pharmacy dispense events in the system, including the patient, the prescription, the amount dispensed, and so on. All pharmacy dispense events are also joined to their corresponding encounters.

021_t_pharmacy_dispense_event_provider.sql - Creates a materialized view for all pharmacy dispense event providers in the system, including the pharmacy dispense event ID which can be used to join back to the event.

022_t_medical_supply_order_event.sql - Creates a materialized view for all medical supply order events in the system, including part number, the quantity, and so on. All medical supply order events are also joined to their corresponding encounters.

022_t_medical_supply_order_event_provider.sql - Creates a materialized view for all medical supply order event providers in the system, including the medical supply order event ID which can be used to join back to the event.

023_t_procedure_order_event.sql - Creates a materialized view for all procedure order events in the system, including the procedure code, the patient, and so on. All procedure order events are joined to their corresponding encounters.

023_t_procedure_order_event_provider.sql - Creates a materialized view for all procedure order event providers in the system, including the procedure order event ID which can be used to join back to the event.

024_t_radiology_event.sql - Creates a materialized view for all radiology events in the system, including the patient, the body part to receive treatment, the modality, and so on. All radiology order events are joined to their corresponding encounters.

024_t_radiology_event_provider.sql - Creates a materialized view for all radiology event providers in the system, including the radiology order event ID which can be used to join back to the event.

025_t_referral_order_event.sql - Creates a materialized view for all referral order events in the system, including the patient, the referral description, the indicating condition, and so on. All referral order events are joined to their corresponding encounters.

025_t_referral_order_event_provider.sql - Creates a materialized view for all referral order event providers, including the referral order event ID which can be used to join back to the event.

026_t_service_order_event.sql - Creates a materialized view for all service order events in the system, including the patient, the service code, and so on. All service order events are joined to their corresponding encounters.

026_t_service_order_event_provider.sql - Creates a materialized view for all service order event providers in the system, including the service order event ID which can be used to join back to the event.

027_t_transfer_event.sql - Creates a materialized view for all transfer event records in the system, including the patient, the type of transfer (for example, admit), the destination facility, and so on. All transfer events are also joined to their corresponding encounters.

027_t_transfer_event-provider.sql - Creates a materialized view for all transfer event providers in the system, including the transfer event ID which can be used to join back to the event.

028_t_transfusion_order_event.sql - Creates a materialized view for all transfusion order events in the system, including the patient, the blood type, the amount of blood, and so on. All transfusion order events are joined to their corresponding encounters.

028_t_transfusion_order_event_provider.sql - Creates a materialized view for all transfusion order event providers, including the transfusion order event ID which can be used to join back to the event.

029_t_vaccination_admin_event.sql - Creates a materialized view for all vaccination administration events, including the patient, the vaccine, the manufacturer, and so on. All vaccination administration events are joined to their corresponding encounters.

029_t_vaccination_admin_event_provider.sql - Creates a materialized view for all vaccination administration event providers, including the vaccination admin event ID which can be used to join back to the event.

030_t_account.sql - Creates a materialized view for all account records in the system, including account type, the associated patient, and so on.

031_t_account_guarantor.sql - Creates a materialized view for all account guarantor records in the system.

033_t_account_transaction.sql - Creates a materialized view for all account transaction records in the system, including the amount of the transaction, the payer ID, the account associated with the transaction, the date and time of the transaction, any adjustments, and the cost center associated with the transaction.

036_t_charge.sql - Creates a materialized view for all charge records in the system, including the charge amount, the cost center, the associated account, and so on.

037_t_provider_claim.sql - Creates a materialized view for all provider claim records in the system, including the account, the patient, the claim amount, the status, and so on.

038_t_patient_health_plan.sql - Creates a materialized view for all patient health plan records in the system, including the plan number, the subscriber, the relation of the subscriber to the patient, and so on.

039_t_provider_practice_specialty_master.sql - Creates a materialized view for all mastered provider specialty practice records in the system, including whether they are a primary care physician, accept walk-ins, and so on.

040_t_provider_practice.sql - Creates a materialized view for all provider practice records in the system, including the provider, the physical and mailing address, the provider number, and so on.

040_t_provider_practice_master.sql - Creates a materialized view for all mastered provider practice records in the system, including the provider, the physical and mailing address, the provider number, and so on.

041_t_daily_census.sql - Creates a materialized view for all daily census records in the system, including facility ID, the admissions, the discharges, and so on.

042_t_cart_item.sql - Creates a materialized view for all cart items in the system including the item, the fulfillment facility, the return facility, and so on.

043_t_patient_preferred_provider.sql - Creates a materialized view for all patient preferred provider records in the system, including the provider ID and the start and end dates.

043_t_patient_preferred_provider_master.sql - Creates a materialized view for all mastered patient preferred provider records in the system, including the patient master ID, the provider master ID, and the start and end dates.

044_t_provider_license.sql - Creates a materialized view for all provider license records in the system, including the license type, the licensing authority, the license status, and so on.

044_t_provider_license_master.sql - Creates a materialized view of all mastered provider license records in the system, including the license type, the licensing authority, the license status, and so on.

045_t_provider_identifier.sql - Creates a materialized view of all provider identifiers in the system, including the type of identifier, the issuing authority, the start and end date, and so on.

045_t_provider_identifier_master.sql - Creates a materialized view of all mastered provider identifier records in the system, including the type of identifier, the issuing authority, the start and end date, and so on.

046_t_provider_privilege.sql - Creates a materialized view for all provider privilege records in the system, including the privilege code, the start and end date, the facility, and so on.

046_t_provider_privilege_master.sql - Creates a materialized view for all mastered provider privilege records in the system, including the privilege code, the start and end date, the facility, and so on.

047_t_encounter_health_plan_coverage.sql - Creates a materialized view for all encounter health plan coverage records in the system, including the insurance coverage type code and the patient health plan ID.

080_t_health_plan.sql - Creates a materialized view for all health plan records in the system, including the plan number, plan payer, financial code, and so on.

260_t_guarantor.sql - Creates a materialized view for all guarantor records in the system, including the payer, the relation of the payer to the patient, and so on.

290_t_payer.sql - Creates a materialized view for all payer records in the system, including the payer name and contact information.

311_t_episode.sql - Creates a materialized view for all episode records in the system, including the start and end date, the episode treatment group, the episode number, and so on.

316_t_specimen.sql - Creates a materialized view for all specimen records in the system, including the collection method, the source, the amount, and so on.

322_t_surgery_case.sql - Creates a materialized view for all surgery case records in the system, including the facility, the related encounter ID, the patient, pre-operation tests, type of surgery, and so on.

324_t_surgery_case_cart.sql - Creates a materialized view for all surgery case cart records in the system.

326_t_surgery_procedure.sql - Creates a materialized view for all surgery procedure records in the system, including the type of procedure, the scheduled time for the procedure, the location, the anesthesia requirements, and so on.

325_t_surgery_movement.sql - Creates a materialized view for all surgery movement records in the system, including provider being relieved, the reason for the relief, and so on.

333_t_care_plan.sql - Creates a materialized view for all care plan records in the system, including the care plan status, the start date, and the end date.

334_t_accessibility_appointment.sql - Creates a materialized view for all accessibility appointment records in the system, including scheduling information, the location of the appointment, and so on.

335_t_practice_facility.sql - Creates a materialized view for all practice facility records in the system, including legal name for the practice and the address and phone number for the practice.

336_t_provider_practice.sql - Creates a materialized view for all provider practice records in the system, including practice name and the physical and mailing address of the practice.

337_t_referral_source.sql - Creates a materialized view for all referral source records in the system, including the patient, the provider, the requested service, and so on.

322_t_surgery_case.sql - Creates a materialized view for all surgery case records in the system, including the facility, the related encounter ID, the patient, pre-operation tests, type of surgery, and so on.

324_t_surgery_case_cart.sql - Creates a materialized view for all surgery case cart records in the system.

326_t_surgery_procedure.sql - Creates a materialized view for all surgery procedure records in the system, including the type of procedure, the scheduled time for the procedure, the location, the anesthesia requirements, and so on.

904_t_hvtabstats.sql - Registers record counts for all tables in HealthViews, which can be used to track database growth over time.

905_ohd_counts.sql - Records the record counts for all tables in the omnihealthdata database.

999_t_hv_availability.sql - Records the stop time and calculates the duration for the HealthViews process.

HealthViews has its own code set table, t_dim_codeset_lookup, which is used to expand upon the codes provided in Omni-HealthData tables. For example, the Omni-HealthData table, og_patient, contains a column for tax_id_type_code. HealthViews, in the v_dim_patient view, expands upon the code ID to also include the code description and the parent code ID and description. As a result, HealthViews views will contain more columns than the Omni-HealthData table from which they are derived.

Certain subject areas with Omni-HealthData and HealthViews can contain mastered data, These include Patient, Provider, Facility, and Organization. Typically, customers do not master Patient or Provider at the beginning of their deployments. The mastering rules take time to develop and there needs to be a sufficient number of patients and providers for mastering to be meaningful. HealthViews represents the master ID for Patient and Provider, but will substitute the instance ID if those subjects have not been mastered. When mastering is implemented, no change in code is necessary. HealthViews will automatically switch to using the mastered values for those fields.

In most Omni-HealthData deployments, daily data loads do not update every subject area. Logic is built into select HealthViews scripts to determine whether there is new data that needs to be loaded or not. If there is no new data, the script does not run. The logic compares record counts, the maximum omni_created_date, and the maximum omni_modified_date, to determine whether there are new records. This capability is currently found in the following four scripts:

• 030_t_account.sql
• 031_t_account_guarantor.sql
• 033_t_account_transaction.sql
• 036_t_charge.sql

Whether or not to check for new data is controlled through the set check_source= parameter in build_all.cmd. If this is set to Y, then the script will first check for the need to run. If it is set to anything other than Y, the script will run regardless of whether there is new data or not.

Many scripts include one or more reporting indexes that are intended to improve the performance of other Omni-HealthData products, such as Cohort Builder. These indexes can be commented out if they are not needed and building the indexes impact performance. All reporting indexes are prefixed with a comment line indicating where they start.