The Basics Of Caboodle

Question 1

What is a data warehouse?

Answer 1

A data warehouse is a relational database, made up of tables and columns, that can be queried using SQL.

(Aug. 2021, 2.4)

Question 2

Can Caboodle store data from multiple sources?

Answer 2

Yes. The Caboodle data warehouse can store data from Epic and non-Epic sources.

(Aug. 2021, 2.4)

Question 3

What is ETL?

Answer 3

Extract, transform, and load (ETL) is the process that moves data into the data warehouse.

(Aug. 2021, 2.4)

Question 4

What is the flow of data between Epic’s databases?

Answer 4

Chronicles => Clarity => Caboodle <= Non-Epic Data

Aug. 2021, 2.4

Question 5

Define Chronicles.

Answer 5

Hierarchical database with real-time data.

Aug. 2021, 2.4

Question 6

Define Clarity.

Answer 6

Relational database with a normalized data model that reduces data redundancy.

(Aug. 2021, 2.4)

Question 7

Define Caboodle.

Answer 7

Relational database with a dimensional data model for ease of reporting and data exploration.

(Aug. 2021, 2.4)

Question 8

What is Epic’s production database?

Answer 8

Chronicles.

Aug. 2021, 2.4

Question 9

What database contains definitions for how certain pieces of data may be stored and the relationships between those pieces of data?

Answer 9

Chronicles. It is the source for all Epic data.

Aug. 2021. 2.4

Question 10

Are data relationships that are mapped in Chronicles preserved through the ETL process?

Answer 10

Yes. The data relationships are preserved through both the Clarity and Caboodle ETL process.

(Aug. 2021, 2.4-2.5)

Question 11

What are the two databases within Caboodle?

Answer 11

1. The Staging Database
   Data first moves from its source into the staging database. Here the data is TRANSFORMED, CHECKED for data integrity issues, and CLEANED to resolve those issues. Once the data is ready, it is moved to the reporting database.
2. The reporting database
   This is the database used by Caboodle report writers to create reports.

(Aug. 2021, 2.5)

Question 12

What is an SSIS package?

Answer 12

SQL Server Integration Services (SSIS) packages are the MECHANISM used to move data during the Caboodle ETL process.

(Aug. 2021, 2.5)

Question 13

In Caboodle, what two Epic-released schemas are relevant to report writers?

Answer 13

1. dbo schema
2. FullAccess schema

(Aug. 2021, 2.6)

Question 14

What schema is the data source for SlicerDicer?

Answer 14

The dbo schema.

Aug. 2021, 2.6

Question 15

Which schema should be your default schema for reporting?

Answer 15

The FullAccess schema.

Question 16

What does the FullAccess schema have that the dbo schema doesn’t?

Answer 16

The FullAccess schema includes columns that define binary columns with a Yes and No instead of 1 and 0.

(Aug. 2021, 2.6)

Question 17

How would a report writer specify a schema in a SQL query?

Answer 17

By adding the schema name in front of the table name.
Ex. SELECT dbo.EncounterFact.EncounterKey
FROM dbo.EncounterFact

(Aug. 2021, 2.6)

Question 18

How can a report writer discover what the default schema is for their organization?

Answer 18

By running SELECT SCHEMA_NAME()

Aug. 2021, 2.7

Question 19

What is Epic’s main front-end program?

Answer 19

Hyperspace

Aug. 2021, 2.8

Question 20

What is Epic’s self-service reporting tool?

Answer 20

SlicerDicer

Aug. 2021, 2.8

Question 21

Does Caboodle enforce a naming convention?

Answer 21

Yes. The naming conventions are strictly enforced in Caboodle, allowing a table’s suffix to provide information about its structure and purpose.

(Aug. 2021, 2.9)

Question 22

What is a DMC?

Answer 22

A Data Model Component (DMC) consists of:

1. The reporting table
2. The package used to populate the reporting table
3. Underlying metadata tables that assist Caboodle Administrators with the ETL process

(Aug. 2021, 2.9)

Question 23

What DMC types does Caboodle use?

Answer 23

1. Dim for dimensions
2. Fact for facts
3. Bridge for bridges
4. DataMart for data marts
5. AttributeValueDim for EAV tables
6. (suffix)X for custom tables

(Aug. 2021, 2.10)

Question 24

What is a surrogate key?

Answer 24

Any -Key column where the column’s value does not exist in the source database and is created during the ETL process. In Caboodle all columns that end in -Key are surrogate keys.

(Aug. 2021, 2.10)

Question 25

Why does Caboodle use surrogate keys?

Answer 25

They are necessary because Caboodle can house Epic and non-Epic data.

(Aug. 2021, 2.10)

Question 26

What is a lookup column?

Answer 26

A column in one table whose value identifies at least one row in another table. Lookup columns in Caboodle will always end in -Key.

(Aug. 2021, 2.10)

Question 27

Are there columns in Caboodle that store source database identifiers?

Answer 27

Yes.

• EpicId
• EpicCsn

(Aug. 2021, 2.10)

Question 28

Why aren’t all lookup columns foreign keys?

Answer 28

A foreign key joins to the primary key of another table and lookup columns can join to a column that is not the primary key of the destination table.

Question 29

In Caboodle, what type of table centers around measurable events?

Answer 29

Fact tables.

It is more common to focus a report on measurable events (medication orders, patient encounters, billing transactions) than on the entities involved with those events (medications, patients, providers).

(Aug. 2021, 2.11)

Question 30

Why does the star schema center around fact tables?

Answer 30

Because a fact table contains all of the entities that were involved with each event, but little additional information about the entities. If a report needs that additional data, joins must be made to the appropriate dimension tables.

(Aug. 2021, 2.11)

Question 31

True or False: A column can either be a primary key or a surrogate key, but not both.

Answer 31

False. A column can be both a primary key (the column(s) used to uniquely identify a row) and a surrogate key (a key created during ETL that does not exist in the source database). In Caboodle, primary key columns are also surrogate keys.

(Aug. 2021, 2.15)