MCHP Export data dictionary: Difference between revisions

Revision as of 15:45, 2015 April 29

This is the documentation for the Critical Care and Medicine data export for the Manitoba Centre for Health Policy.

table 1Main

field name	description
nm	descr

D_ID

Data Element (edit)
Field Name:	D_ID
CCMDB Label:	not stated
CCMDB tab:	not stated
Table:	L_CCI_Picklist table, L_CCI_Component table, L_Como table, L_Dxs table, L_ICD10 table, L_Labs_DSM table, L_Labs_Flowsheet table, L_Log table, L_PCH table, L_PHI table, L_Pharm_Flowsheet table, L_TISS_Form table, L_TISS_Item table, L_TmpV2 table, L_TISS76 table
Data type:	string
Length:	18
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	1

The unique identifier/index of records in the Critical Care and Medicine Database.

D_ID is used in CFE/Centralized_data.mdb. In CCMDB.accdb / CCMDB_data.mdb, Pat_ID is the equivalent. D_ID is the combination of the Service/Location and the Pat_ID.

Changing D_IDs

MB Health PHINs only

Manitoba Health PHIN numbers only.

If patient permanently resides outside of the province of Manitoba the Province field should not be MB and the PHIN field should be left blank.

Please make every effort to obtain this information and call medical records to help.

Possibility of adding other Province's PHINs

old discussion, result: not at this time
With Cognos EPR Report we now get PHINs from out of province with province prefix; do we want to start collecting these for out-of-province patients for cleaner matching, and to enable better matching for external users of our data such as MCHP? sure. will this affect any of the query? the data type will become text if you will add the province prefix. --JMojica 19:03, 2020 April 5 (CDT) We could just import the PHIN without the prefix, the fact it's a different province already shows. However, it would have a trickle-down effect of having to fix old pseudo-PHINs, and of having to update code that links patients across admissions, and likely more. So, let's leave it for now. Ttenbergen 09:21, 2020 June 3 (CDT) Cognos EPR Report does not provide data on a given line about where a pt was in a previous hospital. If the pt was a med or CC pt at that previous hospital in the previous 2 weeks, we would have rows for him from the other location. However, because we use PseudoPhins for many pts, I currently use the chart number for matching lines to a same patient, so records from other sites don't show up. Pro: there is no clutter of irrelevant info from other sites; Con: Even if data would be available for pts status at other site, I am hiding it. Starting to collect PHIN for all would help this for OOP pts, but would still not address it for legit-no-PHIN pts.

Special Cases

Location Manitoba and no PHIN

There are scenarios where a PHIN will not be available for a Manitoba patient, including but not limited to

immigrants and international students on visas
some prison inmates

In these and similar cases, use 999999999 as the PHIN. The data processor will assign a Pseudo PHIN.

International Students

Profiles of international students may or may not have a PHIN. At some point, students have at times been covered[1], then been required to get a specific U of M insurance[2], and as of 2021-07 appear to have some choice of how they insure, although insurance is mandatory [3]archive.

Location Outside Manitoba and no PHIN

PHIN must be entered by data collector as blank. A Pseudo PHIN will be entered by the data processor after file is appended to master database.

Canadian Forces patients

see Canadian Forces patients.

PseudoPHIN

Since the PHIN is used as a unique identifier for patients to detect repeat and continuous visits, a "pseudoPHIN" is assigned for patients who don't have a PHIN. At this point, pseudoPHINS are assigned by Pagasa, see see Generating PseudoPHINs.

Use started in 1999 and starts in '1' and as of Mar 18, 2013 our last Pseudo number was 7043.

We observed that there was a drop in the number of PseudoPHINs during the part of COVID. We confirmed that fewer out-of-province patients would have been accepted during this time, so that change is legitimate.

Multiple PHINs or PHIN changes over time

People may change PHINs over time or have multiple PHINs. This can occur in 3 main ways:

moving from OOP to MB,
moving from MB to OOP, and
a single individual having multiple PHINs (usually somethign went wrong in registration with MB Health)

For the purpose of identifying "same person", and uniformity, we will enter the current/most PHIN or PseudoPHIN into the PHIN field of all encounters of that patient in our database. Earlier PHINs or PseudoPHINs will be retained in Alias ID collection. This may mean that we change an existing PHIN to a PseudoPHIN. This may trigger some errors in our cross checks; if so, appropriate records will be added to the L_Problem table so the error goes away.

Minimal Data Set

This entry is part of the Minimal Data Set. Special collection instructions apply, see that page for more info.

L_PHI vs L_Log

This field is stored in the L_PHI table in CFE but in the L_Log table in CCMDB.accdb. See L_PHI table for details why.

Data Integrity Checks (automatic list)

none found

L_PHI vs L_Log

This field is stored in the L_PHI table in CFE but in the L_Log table in CCMDB.accdb. See L_PHI table for details why.

Cross checks

Data Integrity Checks
Summary:	The minimum age allowed is 10, the maximum age allowed is 120.
Related:	Date of Birth
Firmness:	hard check
Timing:	complete
App:	CCMDB.accdb
Coding:	function Validate_DOB
Uses L Problem table:	not relevant for this app
Status:	implemented
Implementation Date:	2008-01-01
Backlogged:	true

CCMDB.accdb cross checks triggering for correct values

If the actual correct data leads to a cross-check error CCMDB.accdb will not allow you to send. In that case, enter a value that is acceptable and then email the Data Processor using the "generate email" button.

Data Integrity Checks (automatic list)

none found

Collection instructions

Leading Zeros

If your chart number system uses leading zeros, don't enter the leading zeros, start with the first non-zero number.

Dashes near the end of the number

EPR sometimes lists something like MRN 123456-1; in that case, we code 1234561, i.e. leave out the dash, but code the trailing number.

Unclear or changing chart numbers

See

Minimal Data Set

This entry is part of the Minimal Data Set. Special collection instructions apply, see that page for more info.

Data Processing

The Centralized_data_front_end.accdb has queries that make sure that the chart number for each Hospital site is the same for the same patient and make sure the chart number has valid format.

The Data Processor fixes any inconsistencies found from these queries by crosschecking from EPR and adds the alias identifier to the tmp project Alias ID collection.

In addition, the Statistician uses SAS to check key identifiers so they are consistent for same patient including Hosp chart etc.

L_PHI vs L_Log

This field is stored in the L_PHI table in CFE but in the L_Log table in CCMDB.accdb. See L_PHI table for details why.

Cross checks

Data Integrity Checks (automatic list)

none found

Data Use

Instructions for requesting a batch of data from DSM uses this.

Legacy Content

This page contains Legacy Content.

Explanation: stopped collection in Medicine
Successor: various tmp and dx codes relating to palliative care and End-of-life related data

Click Expand to show legacy content.

Legacy Content

This page contains Legacy Content.

Explanation: This is a legacy data field, its DataElementEndDate is in the past.
Successor: No successor was entered

Click Expand to show legacy content.

Data Element (edit)
Field Name:	R_dc_treat
CCMDB Label:	not stated
CCMDB tab:	not stated
Table:	L_Log table
Data type:	string
Length:	50
Program:	CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2022-05-10
Sort Index:	12

"DC" for critical care patients if life-support treatment is terminated, blank for all others.

This field was also collected in Medicine until 2013-07-04. Collection stopped because the distinction is not clear enough for coding on a medicine ward.

Defined as terminal withdrawal with expectation of death of:

invasive mechanical ventilation (ET tube or trach)
vasopressors or inotropes
ECMO, VV, ECMO, VA
VAD (Ventricular assistive device)

Log

2022-05-10 removed from collection screen

Collection Instructions

Choose the value for the "Province" field from the dropdown lists in CCMDB.accdb. The provincial abbreviations are the same as for mailing. In addition to the provinces you have the following options:

US United States
CF Canadian Funded (RCMP, Canadian Forces...)
NK Not Known / Not available
OS Outside of North America

Canadian Forces patients

see Canadian Forces patients

Data Integrity Check List

Data Integrity Checks (automatic list)

none found

Data Structure

The field draws its options from the S_Provinces table in CCMDB.accdb.

Sex changes over time

For those patients who are transgender or transsexual, document what their sex was at birth. This may mean that a record's FirstName field becomes inconsistent with their Sex field. In those cases, the contents of the Alias_ID_collection might shed lights on the reason.

EPR uses the currently used gender, as far as we could tell. We didn’t review what the policies are about this. Our records may therefore be inconsistent with EPR.

Data Integrity Checks (automatic list)

none found

Legacy Content

This page contains Legacy Content.

Explanation: This is a legacy data field, its DataElementEndDate is in the past.
Successor: No successor was entered

Click Expand to show legacy content.

Data Element (edit)
Field Name:	R_Type
CCMDB Label:	Pt Type
CCMDB tab:	Dispo
Table:	N/A
Data type:	string
Length:	10
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1997-04-01
End Date:	2018-05-09
Sort Index:	18

Service of the attending physician for medicine data, and the type of admit diagnosis for critical care patients.

The use of this field had changed over time, and it was found to no longer contain what was originally intended and was removed 2018-05-09.

Related data we continue to collect is:

Medicine Wards

The Patient Type on your registry page can take one of two values:

M-Medical Type

Patient who is admitted under the care of a Medical Service attending physician

S-Surgical Type

admitted from the OR but is under the care of the Medicine Service Attending Physician.
admitted from the RR and is under the care of the Medicine Service Attending Physician.

NOTE: if there is a surgical patient on an medicine ward bed that is under a Surgical Service care, we exclude from the database. This is not a medicine service care patient.

Critical Care Units

S-Surgical Type

admit from OR
admit from RR
all Trauma (fall, MVA, stabbing, etc)
all burns
all upper GI bleeds
all intracerebral bleeds
Pt who undergoes a surgery related to primary reason to ICU in the first 48 hrs of admission to ICU.
Pt admitted from a SURGICAL WARD
Pancreatitis if surgery < =48 hrs of admission to unit

M-Medical Type

Cardiac or respiratory arrest
Cardiogenic shock
Pancreatitis if surgery >48 hrs of admission to unit
don't fall into Surgical or Cardiac type category

C-Cardiac Type

STB in both MICU and CICU - if under the Care of Cardiology Service. This no longer applies when STB ACCU started July 6, 2016.
HSC in the MICU unit - if under the Care of Cardiology Service

Other ICU's

MI
rhythm disturbances
unstable angina / ACS
CHF
post angio/plasty
pacemaker insertions (temp or perm)

Note for ICU at HSC and STB only

Patients in MICU under MICU attending physician service that have a cardiac diagnosis should always be coded as medical type whether they are stable or not. The only exception is if a patient is a surgical patient, then mark as surgical type.

StB Cardiac patients

Special rules used to apply

Data Use

Population

CCMDB Data Integrity Checks

have been removed when collection discontinued

Removal of field

The discussion at task meeting in past was to wait until we migrate to ICU10 DX code then Julie and Garland can decide on pt type category.

2018-May-7: Steering Committee meeting - reviewed and agreed to stop Registry Pt Type. Field removed from CCMDB.accdb. See CCMDB.mdb Change Log 2018#2018-05-09.

}}

R_SurviveExpire

The Survive / Expired field used to track if a patient survived to be discharged. Options are Survive, Expire.

AdmDateTime

The concept of "Admit date and time" is not as simple as it might seem due to the way we use it in reporting, and how it has been stored over time.

Current definition as of 2021-04-21

The most recent version is Admit DtTm as derived by Created_AdmitDtTm query

Previous definitions

Legacy Content

This page contains Legacy Content.

Explanation: Created_AdmitDtTm query
Successor: Created_AdmitDtTm query

Click Expand to show legacy content.

Arrive DtTm and Accept DtTm

The Admit date and time used to designate the time a patient was admitted to the ward/unit. For critical care it designated the time the patient physically arrived on the unit, the one for medicine when the patient was accepted to the medicine service.

Collection instructions for this article were transferred to Arrive DtTm and Accept DtTm as appropriate and deleted from here to prevent accidental use of outdated information. See 2016-07-03 history of the article for old instructions, such as when this was encoded as field R_AdmDate.

CCMDB Data Integrity Checks

Minimal_Data_Set#CCDMB_Data_Integrity_Checks for patients with Dispo DtTm before 2016-07-01

Date and time formats

see Date and Time Format

Data use

Among other things, the times are used to generate statistics about

Collection instructions

What is Transfer Ready

The status of "transfer ready" is about the date/time of an intent to transfer a patient to lower level of care in the Level of care hierarchy if there was a bed available. Whether or not the patient actually moves does not matter, just that at some point there was an intent to move the pt. It also does not matter whether after such a determination the care team changed their minds about such a desired transfer.
Obviously we don't always know the team's intentions, but if they do write them down, then use that info.
- In making this delineation, except as for the exceptions listed immediately below, only consider a clearly written intent that the team now desires the patient to be transferred to such a lower level of care.
  - In particular, when a ward patient is transferred (e.g. home) without any notes stating the team’s intention to do so in advance or even an order to discharge, collectors should not attempt to make educated guesses from the notes of when the patient was probably clinically ready to leave and the checkbox is checked.

See Level of care hierarchy for further information.

Specifically for ICU

In an ICU, take the following to indicate transfer ready to a lower level of care even if they have not written that explicitly:

Care is stepped down to ward frequency (q4hrs or less) of vitals AND off all forms of life support except possibly intermittent dialysis
HSC_IICU consult is written
patient is made ACP-C
for organ donors, see Guideline for coding organ donation after death
if the patient is a potential organ donor and then deemed not to be, the Transfer Ready tmp DtTm will be when that determination is made
for those patients who are declared brain dead, and do not become actual or potential organ donors, use the time of Brain death as the Transfer Ready DtTm tmp entry, and the time of cardiac death as the Dispo DtTm

Specifically for Medicine

On a medicine ward, take the following to indicate transfer ready to a lower level of care even if they have not written that explicitly:

For SBGH If there is no discharge order, then the DC summary date/time that the attending signs off can be used, however if the date and time is after the DC time then it may be documented in a nursing or allied health IPN. Also, for SBGH often bed utilization will document when they are waiting on a transfer to an LAU or other facility, or rehab services will document when they are on the central wait list, or long term care (LTC) will document when they are approved for a PCH bed.
For HSC if there is no discharge order, then check the IPN notes (nursing, allied health etc), often bed utilization will document when they are waiting on a transfer to an LAU or other facility, or rehab services will document when they are on the central wait list, or long term care (LTC) will document when they are approved for a PCH bed.
Order is written to change all iv meds to po AND monitor discontinued/vital sign frequency is reduced
Patient is made ACP-C
If a discharge order is written during the preceding day(s) prior to discharge:
- and a specific date and time for discharge is documented in that order, the transfer ready date and time would be the date and time specified in the order.
- and If the order is to discharge after a specific test or procedure/treatment ie. dialysis or last dose of antibiotics, then the transfer ready date and time would be the time they finish the treatment or procedure.
- and there is no specific date and time documented for discharge or another order for discharge is written, then check the checkbox or use that new discharge order date and time

The discharge medication reconciliation form should NOT be used as transfer ready date and time.

PT/OT Assessment: Before going home, some ward patients get a home safety evaluation from PT and OT, and if deemed safe for home get a homecare evaluation before going home. The transfer ready date/time in such a situation should be only after the PT/OT evaluation has deemed them safe to go home, i.e. before homecare has seen them. The rationale is that homecare evaluation can occur after discharge, but a hospitalized patient who “fails” their home safety evaluation will end up going to LTC, not home.
exception to this would be for those patients that are waiting for transfer to a rehab ward (Geri, stroke, amp, neuro etc) The date and time they are placed on the wait list for rehab can be used as their transfer ready date and time

Data entry instructions

A "Transfer Ready" line is automatically created for each Boarding Loc entry.
- Project: Transfer Ready DtTm
- Item: the only available item is "Transfer Ready DtTm", just like the project entry.
- Date and Time vs checkbox:
  - Collector needs to enter one of the following:
    - First Date and Time during the stay at this Boarding Loc that patient became transfer ready as per #What is Transfer Ready above

OR

checkbox checked if a clear transfer ready date and time are never documented, both must be present to be considered a valid Transfer Ready DtTm

Combining Transfer Ready DtTm tmp entry and Boarding Loc records

There needs to be one Transfer Ready DtTm tmp entry for each Boarding Loc and vice versa. To mark which entries belong together, use the same integer number in the "combiner" field in Patient Viewer Tab Cognos ADT2 for both records and in sequential order according to Boarding start_dt and start_tm. The presence of matching records is validated by query s_tmp_check_combined_Boarding_Loc_and_TransferReadyDtTm, and their sequential status by query s_tmp_check_combined_BL_and_TRDtTm_nonsequential.
the integer number should be entered at the time that the paired Boarding Loc and Transfer Ready DtTm tmp entry are entered, as Julie uses this data early to report on

Collection for each Boarding Loc

We currently only use the first entry per Level of care to calculate Transfer Delay, but we collect both because:

It gives us the flexibility to report per location if requested
To make it easier for data collectors. This way, collectors don't have to try and go back and figure out if there was or was not a transfer ready in a prior location. They only need be concerned about the notes and orders from THIS boarding loc.

Start DtTm/Legacy

We used the old Transfer Ready DtTm field for transfer ready dttms before 2020-10-15, and use this new entry for dttms after.

The data during the transition period for PatientFollow Project is inconsistent, so we use all the new and the old in Created TransferReady query.

Data Use / Purpose

Critical care and Medicine programs want to know this to better understand patient flow and bed utilization.

Used via Created_TransferReady query and Created_transferDelay table to generate Transfer Delay and Avoidable Days (Critical Care).

Background

This isn't so much a project as a change to Transfer Ready DtTm collection to allow us to collect more than one Transfer Ready DtTm per patient-program-stay. See Change from Service Location to Service, Boarding Loc and Transfer Ready DtTm tmp entry for why we needed to change to this.

Data Integrity Checks (automatic list)

none found

Log

2021-07-08 - Change from Awaiting/delayed dx codes to Transfer Ready DtTm for data back to 2021-07-01

Legacy

Similar to the old Transfer Ready DtTm field and Transfer Ready date and time, but we eliminated special cases and differences between medicine and critical care.

n

Age

See page

Indicators
Indicator:	MCHP Export data dictionary
Created/Raw:	not stated
Program:	Critical Care and Medicine
Start Date:	1988-07-11
End Date:
Reports:	No reports on this wiki list this as an indicator.

Data Element (edit)
Field Name:	age
CCMDB Label:	not stated
CCMDB tab:	not stated
Table:	Created_Variables_Common_2021 table, Created_Variables_CC_2021 table
Data type:	number
Length:
Program:	Med and CC
Created/Raw:	Created
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	3

The number of years between Date of Birth and the last birthday prior to or on (Admit DtTm otherwise).

Usually used in aggregate form as "per location" and/or "per timeframe", e.g. by day/month/quarter/year x Ward/Unit x Hospital.

Uses

Critical Care

Part of the APACHE II score where patient's age falling under an age group(i.e. ≤44,45-54,55-64,65-74,≥75) has corresponding score

Critical Care QI domain

Understanding the population by showing aggregate counts of patients per age group (i.e. <40,40-60,61-80,>80)

Medicine

Part of ALERT Scale as Age and AgexAge

Sampling Plan / Procedure

100 % of all patients admitted to Medicine or Critical Care

Inclusion Criteria

All

Exclusion Criteria

None

Definition and Derivation

Function to calculate Age

Both formula yields the same AGE values.

ACCESS

CCMDB_Age = excel.WorksheetFunction.Floor(CInt(DateDiff("yyyy", Date of Birth, Admit DtTm)) + Int(Format(Admit DtTm, "mmdd") < Format(Date of Birth, "mmdd")), 1)

SAS

Age=floor((intck('month',Date of Birth,Admit DtTm) - (day(Admit DtTm) < day(Date of Birth))) / 12)

Reported as

Mean Age, Median Age, Minimum Age, Maximum Age for a given time-frame and/or location
Counts and % of total patients based on age group for a given time frame and/or location

Frequency

Time frame can be monthly, quarterly, yearly and can be based on admission dates or discharge dates

@@ Line 3: / Line 3: @@
 == [[table 1Main]] ==
+{| class="wikitable sortable"
+|-
+!| field name
+!| description
+|-
+|| nm
+|| descr
+|}
 === D_ID ===
 {{:D_ID}}
@@ Line 52: / Line 63: @@
-{| class="wikitable sortable"
-|-
-!| field name
-!| description
-|-
-|| nm
-|| descr
-|}

Projects
Active?:	active
Program:	CC and Med
Requestor:	internal
Collection start:	2020-10-15
Collection end:

Data Element (edit)
Field Name:	PHIN
CCMDB Label:	PHIN
CCMDB tab:	Top Row
Table:	L_Log table, L_PHI table
Data type:	number
Length:	Long Integer
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	4

Data Element (edit)
Field Name:	Birth
CCMDB Label:	DOB
CCMDB tab:	Top Row
Table:	L_Log table, L_PHI table
Data type:	date
Length:	not stated
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	5

Data Element (edit)
Field Name:	Chart
CCMDB Label:	Chart
CCMDB tab:	Top Row
Table:	L_Log table, L_PHI table
Data type:	string
Length:	8
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	6

Data Element (edit)
Field Name:	SentDtTm
CCMDB Label:	not stated
CCMDB tab:	not stated
Table:	L_Log table
Data type:	date
Length:	not stated
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	6

Data Element (edit)
Field Name:	R_Province
CCMDB Label:	Province
CCMDB tab:	Dispo
Table:	L_Log table
Data type:	string
Length:	2
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	16

Data Element (edit)
Field Name:	R_Sex
CCMDB Label:	Sex
CCMDB tab:	Dispo
Table:	L_Log table
Data type:	string
Length:	7
Program:	Med and CC
Created/Raw:	Raw
Start Date:	1988-07-11
End Date:	2300-01-01
Sort Index:	17

Related articles:

Related articles:

Related articles:

Related articles:

MCHP Export data dictionary: Difference between revisions

Revision as of 15:45, 2015 April 29

table 1Main

D_ID

Changing D_IDs

Related Articles

First and Last Names

PHIN

MB Health PHINs only

Possibility of adding other Province's PHINs

Special Cases

Location Manitoba and no PHIN

International Students

Location Outside Manitoba and no PHIN

Canadian Forces patients

PseudoPHIN

Multiple PHINs or PHIN changes over time

Minimal Data Set

L_PHI vs L_Log

Data Integrity Checks (automatic list)

Related articles

Birth

L_PHI vs L_Log

Cross checks

CCMDB.accdb cross checks triggering for correct values

Data Integrity Checks (automatic list)

Related articles

Chart

Collection instructions

Leading Zeros

Dashes near the end of the number

Unclear or changing chart numbers

Minimal Data Set

Data Processing

L_PHI vs L_Log

Cross checks

Data Integrity Checks (automatic list)

Related articles

SendDtTm

Data Use

See also

Start_time and Start_date

Site, Ward, Program

r_dc_treat

Legacy Content

Legacy Content

See Also

Log

Related articles

AdmitFrom and DischargedTo

HospitalPrevious

r_province

Collection Instructions

Canadian Forces patients

Data Integrity Check List

Data Integrity Checks (automatic list)

Data Structure

Related articles

r_Sex

Sex changes over time

Data Integrity Checks (automatic list)

Related Articles

R_Type

Legacy Content

Medicine Wards

M-Medical Type

S-Surgical Type

Critical Care Units

S-Surgical Type

M-Medical Type

C-Cardiac Type

Note for ICU at HSC and STB only

StB Cardiac patients

Data Use

CCMDB Data Integrity Checks

Removal of field

R_SurviveExpire

AdmDateTime

Current definition as of 2021-04-21

Previous definitions