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?
Kevin Baker
President
Data Services Partners
Hi, I’m Kevin.
As former Director of Data Services at Ensign Services, a publicly traded company, I have an extensive background and expertise in the Skilled Nursing sector and the needs of both the management and information technology sides of the business. We’re experts in the systems and metrics that support Skilled Nursing and Post Acute Healthcare businesses.
My passion is helping businesses transform their data into actionable information to make better business decisions. We are developers you can trust. Data is our passion.
If you have a question about this article, or want to have a discussion about it, let’s chat!
Did you like what you read?
Keep up with our analysis, coverage, and helpful information regarding the Skilled Nursing sector by joining our mailing list.
We are developers you can trust. Data is our passion.
Your email address is safe with us. We never pass your information to third-parties and you can opt out at any time.
Looking forward to connecting with you!