When you first get access to the data relay from PointClickCare, you will find a database with 475 tables & 409 views! That is a lot to figure out and understand. PCC provides a database dictionary but it is a 300+ page PDF with little guidance on how to build operational metrics.
TIP: whenever possible, only use views instead of tables. This is important because the views are custom developed by PCC and handle complexities of their data.
Whenever possible use the views starting with view_ods.
From my experience, if you are hoping to tap into some metrics like average daily census, skilled mix, average length of stay, the list of views below are a great starting point.
Descriptive Views (these are the source for dimension tables in a data warehouse)
- view_ods_payer List of all Payers & Payer Types (Primary Key PayerID join on PayerID)
- view_ods_bed List of all Beds & Certs for Beds (Primary Key BedID join on BedID)
- view_ods_external_facility List of all admission & discharge locations (Primary Key ExternalLocationID can join on view_ods_patient_census.AdmitDischargeLocationID)
- View_ods_census_codes List of Census Codes (Primary Key ItemID) can join on view_ods_patient_census.ActionCodeID & view_ods_patient_census.StatusCodeID
- View_ods_provider List of Doctors with demographic data (Primary Key ProviderID join on ProviderID)
- View_ods_facility_patient List of all Patients with some demographic data (Primary Key PatientID join on PatientID)
Data Views (these are the source for fact tables in a data warehouse)
view_ods_daily_census This table contains a record for each patient for each active day per building, this is a great table to calculate census.
view_ods_patient_diagnosis This table contains each diagnosis coded to a patient, this is a great many to many join to answer questions like how many residents have cancer.
View_ods_patient_census This table contains census changes, this is a great table to find the history of a resident and to build patient & payer stays.
view_ods_ar_transaction This table contains all of the AR, Billing & Cash transactions, use this for Agings, Cash Collections, DSO, Bad Debt, etc.
What tables do you find most useful? What tables have you had the most trouble with when doing operational reporting off of this data relay?