Data Acquisition Table/View List: Epic (Caboodle)

Introduction

Medisolv’s ENCOR solution requires data acquisition from your EHR data source and/or data warehouse. For clients using the Epic Caboodle environment there are certain steps the client must take which allow Medisolv to acquire data from your Epic Caboodle environment. Your team must implement the following Caboodle KIT tables/views. Furthermore, there are known gaps in data that the Caboodle environment does not have available. These gaps are important to ensure proper measure results. This guide summarizes the tables your team must implement and the known gaps and provides solutions to overcome them.

Enabling Medisolv to Acquire Data from the Epic Caboodle Environment

To acquire data from Epic Caboodle to Medisolv’s ENCOR solutions, Epic clients must submit a request for download of the Medisolv ENCOR application in Epic’s App Orchard. After this is completed, Medisolv will enable the keys for the Epic client and the App Orchard will send an email to the client’s App Orchard Point Person confirming the app download. Within 12 hours, the client records with the app’s client ID/keys are created in the Epic customer’s environments and subsequently will be ETL’d into Caboodle.

Once there, the ENCOR application will appear in the Kit section of the client’s Caboodle console, where the client can create the views and provision access to Medisolv’s Caboodle service account. Check out this Galaxy guide (customer-viewable link) for more information. Customers can also pull in their Cogito Systems TS for additional guidance.

Epic Caboodle Table/View List

#	Schema	Table Name
1	Kit	AddressDim
2	Kit	AddressHistoryFact
3	Kit	AllergenDim
4	Kit	AllergyFact
5	Kit	AnesthesiaRecordFact
6	Kit	AnesthesiaTimingEventFact
7	Kit	AttendingProviderFact
8	Kit	AuthorizationFact
9	Kit	BillAreaDim
10	Kit	BillingAccountEncounterMappingFact
11	Kit	BillingAccountFact
12	Kit	BillingProcedureDim
13	Kit	BillingServiceAreaMappingDim
14	Kit	BillingTransactionFact
15	Kit	BillingUserServiceAreaMappingFact
16	Kit	BirthAnesthesiaBridge
17	Kit	BirthAttributeValueDim
18	Kit	BirthAugmentationBridge
19	Kit	BirthAugmentationIndicationBridge
20	Kit	BirthCervicalRipeningBridge
21	Kit	BirthCesareanIndicationBridge
22	Kit	BirthEpisiotomyBridge
23	Kit	BirthFact
24	Kit	BirthInductionBridge
25	Kit	BirthInductionIndicationBridge
26	Kit	CancerStagingFact
27	Kit	CareAreaDim
28	Kit	CareManagementCaseFact
29	Kit	CareManagementCaseTypeBridge
30	Kit	CareManagementTeamFact
31	Kit	CareManagementTraitFact
32	Kit	CareTeamFact
33	Kit	CategoryDim
34	Kit	CategoryMappingDim
35	Kit	ChiefComplaintBridge
36	Kit	ChiefComplaintDim
37	Kit	ClinicalNoteFact
38	Kit	ClinicalNoteTextFact
39	Kit	CodedProcedureFact
40	Kit	ComplicationFact
41	Kit	CostCenterDim
42	Kit	CoverageDim
43	Kit	DateDim
44	Kit	DepartmentBridge
45	Kit	DepartmentDim
46	Kit	DiagnosisBridge
47	Kit	DiagnosisDim
48	Kit	DiagnosisEventFact
49	Kit	DiagnosisTerminologyDim
50	Kit	DrgDim
51	Kit	DrgEventFact
52	Kit	EdVisitFact
53	Kit	EmployeeAuthorizedServiceAreaBridge
54	Kit	EmployeeDim
55	Kit	EncounterFact
56	Kit	EpisodeEncounterMappingFact
57	Kit	EpisodeFact
58	Kit	FlowsheetRowDim
59	Kit	FlowsheetRowMappingDim
60	Kit	FlowsheetTemplateDim
61	Kit	FlowsheetValueFact
62	Kit	GuarantorDim
63	Kit	HospitalAdmissionFact
64	Kit	ImagingFact
65	Kit	ImagingTextFact
66	Kit	ImmunizationDim
67	Kit	ImmunizationEventFact
68	Kit	LabComponentDim
69	Kit	LabComponentResultFact
70	Kit	LabComponentResultTextFact
71	Kit	LabContainerTypeDim
72	Kit	LabDim
73	Kit	LabFlagBridge
74	Kit	LabMapping
75	Kit	LabTaskFact
76	Kit	LabTestComponentResultMappingFact
77	Kit	LabTestFact
78	Kit	LabTestingSourceDim
79	Kit	LabTestTextFact
80	Kit	MappingDim
81	Kit	MappingTableColumnBridge
82	Kit	MappingTableColumnDim
83	Kit	MedicationAdjudicationFact
84	Kit	MedicationAdministrationFact
85	Kit	MedicationCodeDim
86	Kit	MedicationComponentBridge
87	Kit	MedicationDim
88	Kit	MedicationDispenseComponentFact
89	Kit	MedicationDispenseFact
90	Kit	MedicationEventFact
91	Kit	MedicationOrderComponentFact
92	Kit	MedicationOrderFact
93	Kit	ModifierBridge
94	Kit	ModifierDim
95	Kit	NumbersDim
96	Kit	NumbersDim
97	Kit	PatientDim
98	Kit	PatientLocationEventFact
99	Kit	PatientRaceBridge
100	Kit	PharmacyDim
101	Kit	PlaceOfServiceDim
102	Kit	PostalCodeDim
103	Kit	PregnancyFact
104	Kit	ProblemListFact
105	Kit	ProcedureBridge
106	Kit	ProcedureDim
107	Kit	ProcedureEventFact
108	Kit	ProcedureMappingDim
109	Kit	ProcedureOrderFact
110	Kit	ProcedureTerminologyDim
111	Kit	ProviderBridge
112	Kit	ProviderDepartmentMappingFact
113	Kit	ProviderDim
114	Kit	ReactionBridge
115	Kit	ReferralFact
116	Kit	RequisitionDim
117	Kit	ResourceDim
118	Kit	SpecialtyPharmacyEnrollmentFact
119	Kit	SpecialtyPharmacyMedOrderMappingFact
120	Kit	StandardDim
121	Kit	SurgicalCaseFact
122	Kit	SurgicalDiagnosisBridge
123	Kit	SurgicalProcedureEventFact
124	Kit	SurgicalSupplyDim
125	Kit	SurgicalSupplyUseFact
126	Kit	TerminologyConceptDim
127	Kit	TimeOfDayDim
128	Kit	VendorDim
129	Kit	VisitFact
130	Kit	VisitTypeDim

Caboodle KIT View Identified Data Gaps

Below are the known data gaps that exist within Caboodle’s standard KIT views.

Gap 1

The following Caboodle tables are not available via standard KIT views.

• TerminologyConceptDim – this table contains standard code mappings
  • Impact: Inpatient and Ambulatory Quality Measure Reporting
  • Solution: Create a custom table or view in Caboodle and populate with all data from the TerminologyConceptDim table. Column names and data types should match the original table. Table should be made part of the Medisolv schema and should be named: ‘Medisolv. TerminologyConceptDim’
• FlowsheetRowMappingDim – this table contains flowsheet row mapping data
  • Impact: Inpatient and Ambulatory Quality Measure Reporting
  • Solution: Create a custom table or view in Caboodle and populate with all data from the FlowsheetRowMappingDim table. Column names and data types should match the original table. Table should be made part of the Medisolv schema and should be named: ‘Medisolv. FlowsheetRowMappingDim’
• LoincDim - this table contains LOINC (Logical Observation Identifiers Names and Codes) records and is not available via standard KIT Caboodle views
  • Impact: Inpatient and Ambulatory Quality Measure Reporting
  • Solution: Create a custom table or view in Caboodle and populate with all data from the LoincDim table. Column names and data types should match the original table. Table should be made part of the Medisolv schema and should be named: ‘Medisolv. LoincDim’
• Epic’s Health Maintenance data is not available via standard Caboodle KIT Views
  • Impact: Inpatient and Ambulatory Quality Measure Reporting
  • Solution: Create a custom table in Caboodle in the Medisolv schema for relevant Health Maintenance data.
    The custom table should consist of these columns:

HealthMaintenanceCompletionKey bigint NULL,

PatientDurableKey bigint NULL,

PatientSourceDataDurableKey bigint NULL,

HealthMaintenanceTopicName nvarchar(max) NULL,

HealthMaintenanceCompletionStatus nvarchar(max) NULL,

HealthMaintenanceCompletionType nvarchar(max) NULL,

Comments nvarchar(max) NULL,

DateKey bigint NULL,

Count int NULL,

_CreationInstant datetime2(7) NULL,

_LastUpdatedInstant datetime2(7) NULL,

_IsInferred int NULL,

_IsDeleted int NULL,

_PrimaryPackageToImpactRecord nvarchar(max) NULL,

_MostRecentPackageToImpactRecord nvarchar(max) NULL,

_NumberOfSources int NULL,

_HasSourceClarity int NULL,

_DeletedFromSourceClarity int NULL

Gap 2

Flowsheet records that are tagged as calculated values are not available in the FlowsheetValueFact and FlowsheetRowDim KIT views in Caboodle.(e.g., Body Mass Index Flowsheet row data)

• • Impact: Inpatient and Ambulatory Quality Measure Reporting
  • Solution: Create a custom table in the Medisolv schema using the table schema that Medisolv will provide and populate flowsheet row data. BMI, Depression Screening, Falls Risk Screening.

Gap 3

Epic’s LQL questionnaire data is not available via KIT Caboodle views. Information documented on questionnaires in the order entry activity are not extracted into Caboodle. Some examples of the types of data that may be missing are VTE risk assessment information or reasons why an order was not placed.

• • Impact: Inpatient Quality Measure Reporting
  • Solution: Create a custom table or view in Caboodle and populate with data extracted from the clarity tables CL_QQUEST_OVTM, CONCEPT_MAPPED, EXTERNAL_CNCPT_MAP, ORD_SPEC_QUEST and SNOMED_CONCEPT. Below is the schema of the custom table that should be created in Caboodle in the Medisolv schema. The custom table should be called: Medisolv.Medisolv_LQL and should consist of these columns:

PAT_ENC_CSN_ID decimal(18, 0) NULL,
ORDER_ID decimal(18, 0) NOT NULL,
ProcedureKey bigint NOT NULL,
DESCRIPTION varchar(254) NULL,
ORDER_TIME datetime NULL,
ORD_QUEST_ID varchar(18) NULL,
QUESTION varchar(1000) NULL,
ORD_QUEST_RESP varchar(3000) NULL,
SNOMEDCT varchar(50) NULL,
ProcedureLocalTerm varchar(50) NULL,
ProcedureLocalDescription varchar(255) NULL,
ETL_Dttm datetime NULL

The below clarity tables and columns should be used to populate this custom table. The logic used in in your ETL scripts will depend on your Epic system build and documentation workflow.

Clarity Table	Clarity Column
CL_QQUEST_OVTM	QUEST_ID
CL_QQUEST_OVTM	QUESTION
CONCEPT_MAPPED	CONCEPT_ID
CONCEPT_MAPPED	MAPPING_ID
EXTERNAL_CNCPT_MAP	ENTITY_INI
EXTERNAL_CNCPT_MAP	ENTITY_SEC_VALUE
EXTERNAL_CNCPT_MAP	ENTITY_VALUE_NUM
EXTERNAL_CNCPT_MAP	MAPPING_ID
ORDER_PROC	DESCRIPTION

Clarity Table	Clarity Column
ORDER_PROC	ORDER_PROC_ID
ORDER_PROC	ORDER_TIME
ORDER_PROC	PAT_ENC_CSN_ID
ORD_SPEC_QUEST	ORD_QUEST_ID
ORD_SPEC_QUEST	ORD_QUEST_RESP
ORD_SPEC_QUEST	ORDER_ID
SNOMED_CONCEPT	CONCEPT_ID
SNOMED_CONCEPT	FULLY_SPECIFIED_NM

 

Gap 4

Certain Medication Orders that are resumed at discharge are not identifiable via standard KIT Caboodle views.

• • Impact: Inpatient Quality Measure Reporting
  • Solution: Create a custom table or view in Caboodle and populate with data extracted from clarity to identify medications resumed at discharge that are not reflected in Caboodle.

Below is the schema of the custom table that should be created in Caboodle in the Medisolv schema. The custom table should be called: Medisolv. MedisolvDischargeMedRec and should consist of these columns:        

PAT_MRN_ID varchar(102) NULL
PAT_ID varchar(18) NULL
PAT_ENC_CSN_ID decimal(18, 0) NOT NULL
MedicationKey bigint NULL
MedicationEpicID decimal(18, 0) NULL
ORDER_ID decimal(18, 0) NULL
ORDER_DESC varchar(255) NULL
DischargeMedRecActionDate datetime NULL
ETL_Date datetime NOT NULL

The below clarity tables and columns should be used to populate this custom table. The logic used in in your ETL scripts will depend on your Epic system build and documentation workflow.

Clarity Table	Clarity Column
CLARITY_DEP	DEPARTMENT_ID
CLARITY_DEP	SPECIALTY_DEP_C
ED_IEV_EVENT_INFO	EVENT_ID
ED_IEV_EVENT_INFO	EVENT_TIME
ED_IEV_EVENT_INFO	EVENT_TYPE
ED_IEV_PAT_INFO	EVENT_ID
ED_IEV_PAT_INFO	PAT_CSN
ED_IEV_PAT_INFO	TYPE_ID
PAT_ENC_HSP	ADT_PATIENT_STAT_C

Clarity Table	Clarity Column
PAT_ENC_HSP	DEPARTMENT_ID
PAT_ENC_HSP	DISCH_DISP_C
PAT_ENC_HSP	HOSP_ADMSN_TIME
PAT_ENC_HSP	HOSP_DISCH_TIME
PAT_ENC_HSP	PAT_ENC_CSN_ID
PAT_ENC_HSP	PAT_ID
PATIENT	PAT_ID
PATIENT	PAT_MRN_ID

Gap 5

Medications that are not administered and the reason they are not administered are not extracted into Caboodle.

• • Solution: Create a custom table or view in Caboodle and populate with data extracted from clarity to identify medications that are not administered and the reason why they were not administered.

Below is the schema of the custom table that should be created in Caboodle in the Medisolv schema. The custom table should be called: Medisolv. MedisolvMARReason and should consist of these columns:

PAT_MRN_ID varchar(102) NULL,
PAT_ID varchar(18) NULL,
PAT_ENC_CSN_ID decimal(18, 0) NOT NULL,
MedicationKey bigint NULL,
MedicationEpicID decimal(18, 0) NULL,
ORDER_ID decimal(18, 0) NOT NULL,
LINE numeric(18, 0) NULL,
ACTION_TIME datetime NULL,
ORDER_DESC varchar(255) NULL,
REASON_CAT_ITEM varchar(50) NULL,
REASON_CAT_NAME varchar(100) NULL,
ORD_MAR_REASON varchar(254) NOT NULL,
SNOMEDCT varchar(50) NULL,
PROCEDURELOCALTERM varchar(254) NULL,
PROCEDURELOCALDESCRIPTION varchar(254) NULL,
ETL_Date datetime NOT NULL

Populate this MedisolvMARReason custom table with data extracted from the below Clarity tables and columns. Your documentation workflow will dictate the logic to use in your clarity scripts when loading this custom table.

Clarity Table	Clarity Column
CONCEPT_MAPPED	CONCEPT_ID
CONCEPT_MAPPED	MAPPING_ID
EXTERNAL_CNCPT_MAP	ENTITY_INI
EXTERNAL_CNCPT_MAP	ENTITY_ITEM
EXTERNAL_CNCPT_MAP	ENTITY_VALUE_NUM
EXTERNAL_CNCPT_MAP	MAPPING_ID
MAR_ADMIN_INFO	LINE
MAR_ADMIN_INFO	MAR_ACTION_C
MAR_ADMIN_INFO	ORDER_MED_ID
MAR_ADMIN_INFO	REASON_C
MAR_ADMIN_INFO	TAKEN_TIME
ORDER_MED	DESCRIPTION
ORDER_MED	ORDER_INST

 

Clarity Table	Clarity Column
ORDER_MED	ORDER_MED_ID
ORDER_MED	PAT_ENC_CSN_ID
ORDER_MEDINFO	DISPENSABLE_MED_ID
ORDER_MEDINFO	ORDER_MED_ID
PATIENT	PAT_ID
PATIENT	PAT_MRN_ID
SNOMED_CONCEPT	CONCEPT_ID
SNOMED_CONCEPT	FULLY_SPECIFIED_NM
ZC_MAR_RSLT	NAME
ZC_MAR_RSLT	RESULT_C
ZC_MAR_RSN	NAME
ZC_MAR_RSN	REASON_C

• • Impact: Inpatient and Ambulatory Quality Measure Reporting