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


Note: Please submit an enhancement request with your Epic tech support to request that these tables be made available via standard KIT views in Caboodle.

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.

Note: Please place an enhancement request with your Epic tech support to request that calculated flowsheet row data for BMI be made available via the standardly released Caboodle KIT views.

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

 

Note: Please submit an enhancement request with your Epic tech support to request LQL Order questionnaire data be made available via the standardly released KIT Caboodle views

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

Note: Please submit an enhancement request with your Epic tech support to request that data from these clarity tables be extracted to Caboodle and made available via standard KIT Caboodle views.

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

Note: Please place an enhancement request with your Epic tech support to request the data from the below Clarity tables be extracted to Caboodle and made available via standard KIT Caboodle views.