CDW110v
Study online at https://quizlet.com/_g4bx8a
1. Where does Caboodle get its data from? Clarity ETL, non-Epic data
2. What happens in Caboodle's staging data- 1. data is transformed 2. checked for in-
base? tegrity 3. cleaned to resolve issues
3. How many databases does Caboodle have? 2 - one for staging and one for reporting
4. What is the cost of Caboodle's easy report- Expensive ETL
ing?
5. What does SSIS stand for? SQL Server Integration Services
6. What are SSIS packages? Mechanisms by which data is moved
during the ETL process
7. Schema Collection of database objects
8. 3 Epic schemas 1. dbo schema
2. FullAccess schema
3. FilteredAccess schema
9. dbo schema Data source for SlicerDicer
10. Where can you go to research the dbo Data Dictionary
schema?
11. FullAccess Schema -Should be the default schema when re-
porting
-Contains everything from dbo schema
and more
12. FilteredAccess Schema -Similar to FullAccess, but results are fil-
tered based on user security
1 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
-Commonly used with CommunityCon-
nect
13. How to tell what schema you're reporting SELECT SCHEMA_NAME()
out of
14. What uses Caboodle? 1. SlicerDicer
2. Certain Metrics
3. Certain Workbench templates
15. Purpose of a key identify a row in a table
16. In Caboodle, all key columns (including pri- Surrogate keys
mary keys and lookup columns) are...
17. Surrogate key A system-assigned primary key that does
not exist in the source database
18. When are surrogate keys created? During the ETL process
19. How to tell which columns are surrogate End in -key
keys or lookup columns?
20. Purpose of surrogate keys To key Epic and non-Epic data
21. Lookup column A column in one table whose value iden-
tifies at least one row in another table.
Most tables in Caboodle have at least
one.
22. DMC Data Model Component. A table in the
Caboodle reporting database and its
supporting infrastructure.
2 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
23. What is the primary key in PatientDim? PatientKey
24. PatientKey in PatientDim Surrogate key with no Chronicles identi-
fying information
25. What ID(s) to use for validation EpicID or EpicCsn
26. Dim tables Contain 1 row for each entity in the set
(Example: OrderDim = 1 order, Patient-
Dim = 1 patient)
27. Fact tables Contain 1 row for each occurrence of
some specific, measurable event
28. Which has more data and fewer lookup Dim tables
columns - fact or dim tables?
29. Bridge tables Model many to many relationships (Ex: 1
patient with multiple diagnoses = Diag-
nosisBridge
30. DataMarts Stores data points from several DMCs
(Ex: HospitalReadmissionDataMart for
admissions and readmissions)
31. What is the purpose of the EAV Model? To store data where the attributes
change frequently
32. What does EAV stand for in EAV Dim Tables? Entity Attribute Value
33. How to recognize EAV tables? Typically, table name ends in -Attribute-
ValueDim
34.
3 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
Examples of EAV tables that don't end in the FlowsheetValueFact
usual suffix LabComponentResultFact
35. Structure of EAV model PatientDim has often changing attribut-
es
1. AttributeDim contains AttributeKey
and Attribute Name
2. PatientAttributeValueDim contains At-
tributeKey and Attribute Value
36. What is the AttributeDim table? List of all attributes used by the Attribut-
eValueDim system in Caboodle
37. What columns are in the AttributeDim table? AttributeKey and the name of the at-
tribute
38. How many rows does each attribute get in 1 row per attribute
AttributeDim?
39. What do AttributeValueDim tables store? The values relating to attributes from At-
tributeDim
40. tables that end in X Custom built DMC
41. What kind of data model does Caboodle use? Dimensional data model - accessible, ef-
ficient
42. Dimensional data models Arranges tables in a way that is easier
and more efficient to query with fewer
tables and joins. Caboodle uses one.
43. Star schema The way fact tables are surrounded by
dim tables in a join diagram
4 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
44. Relationship between SlicerDicer and Ca- SlicerDicer is Epic's self‐service reporting
boodle tool that dynamically queries Caboodle
data.
45. What does the Cogito data dictionary re- The dbo schema
flect?
46. Which has more data - full access or dbo Full access which is why reports should
schema be built from here
47. Can a column be both a primary and a sur- Yes. In Caboodle, primary key columns
rogate key? are also surrogate keys.
48. Are naming conventions enforced in Caboo- Yes (Fact and Dim tables for example)
dle?
49. What happens when you apply the Data Dic- You see only Caboodle and Clarity data-
tionary content type filter in Ana Cat? base objects
50. What's contained in the Overview section of Chips, granularity, description of the
a database object in Ana Cat? database object
51. How can you find all the data models that Search Record Viewer for FDS 1500
can use a certain filter record?
52. How to search the Data Dictionary for Ca- CB:
boodle only
53. How to search the Data Dictionary for Clarity CL:
only
54. How to join to 2 tables in Caboodle where Join on the durable key with IsCurrent =
one is a snapshot table 1
5 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
55. Does the Data Dictionary reflect customiza- Yes
tions an organization makes in Caboodle?
56. Packages Part of the Data Dictionary that lists the
Epic‐released and custom SSIS packages
that populate the table and their queries
57. What schema is a SlicerDicer query written dbo schema
in?
58. Join diagrams Visual representation of the tables, joins,
and columns involved in a query
59. What are ER diagrams used for? Show all possible joins from a specific
table
60. What is the format of an ER diagram? Classic star
61. Where can you find an ER diagram? Data Dictionary when researching a
database object
62. Is the information in an ER diagram specific No, it's simply a visual representation of
to the ER diagram? information held elsewhere in the data
dictionary.
63. Dependencies Part of the Data Dictionary that shows
you what database objects a table is de-
pendent on (usually Clarity tables but
could be other Caboodle tables)
64. Indexes Part of the Data Dictionary that shows
which columns (if any) are indexed in the
table. Using indexes will speed up your
query.
6 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
65. Reporting Availability Part of the Data Dictionary that shows
how a column can be used in a
SlicerDicer session.
66. Advanced Part of the Data Dictionary that shows
metadata tables that are part of this DMC
which are used during ETL
67. Troubleshoot button in SlicerDicer Click this button to see the underlying
query and know which database objects
are being used
68. Data Lineage Part of the Data Dictionary that shows
which Clarity column is used to populate
the Caboodle column
69. How many SSIS packages can populate a Ca- 1. Single
boodle table? 2. Multiple all from Clarity
3. Multiple from multiple sources
70. Snapshot data Caboodle's means of change tracking,
allowing both historical and current data
71. How do you identify a snapshot table? Snapshot Type 2 chip in the Overview
section of AnaCat for the table
72. When does Caboodle make a new snapshot If the source data for any snapshot col-
row? umn has changed
73. Can you use snapshot data for audit track- No. Snapshot tables do not show when
ing? a change was made, only when the ETL
occurred that captured the data.
7 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
74. What defines a snapshot table Has at least one snapshot column. Not all
columns contain snapshot data.
75. Change tracking Indicates how Caboodle will update a
table when there are changes to its
source data.
76. What are the 2 types of change tracking pos- 1. Snapshot (Type 2)
sible in Caboodle? 2. None (Non-snapshot, Type 1)
77. What do non-snapshot tables (type 1) show? The current content of the source data at
the time of the most recent extract.
78. In Type 1 tables, what happens if the source The new value overwrites the old value in
data gets updated? the next ETL.
79. What is the granularity of PatientDim (snap- A patient record for a date range (iden-
shot table)? tified by the PatientKey)
80. What does DurableKey represent in Patient- Unique patients in the table
Dim (snapshot table)?
81. Which key should you use in snapshot ta- DurableKey
bles?
82. DurableKey a Key that stores the same value for all
rows in a snapshot table that refer to the
same entity
83. What kinds of tables have durable keys? Snapshot tables
84. Start and end dates in a snapshot table Represent the ETL dates during which a
row's snapshot data was valid.
8 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
85. What start and end dates are assigned for 1/1/1979 and 12/31/2099
the earliest data in the table?
86. IsCurrent column Flag that stores the value of 1 if the row
holds the most current information and
0 otherwise in a snapshot table
87. How many rows contain a 1 in the IsCurrent exactly 1
column in a snapshot table?
88. What happens to a non-snapshot column The most recent value gets updated on
that gets updated in a snapshot table? all rows of the data
89. What causes a new row to get added to a A change to a snapshot column
snapshot table?
90. IsCurrent = 1 goes with Durable key in a snapshot table
91. What happens when a null or unmatched The ETL infrastructure assigns a default
value is loaded into the Caboodle staging value to the corresponding column in
database the reporting database based on the cir-
cumstances of the value.
92. 2 properties of databases that enforce refer- 1. Lookup columns will always have a
ential integrity value even if the source data is null
2. Lookup column values will always
have a matching value in the destination
table/column.
93. In which schemas does Caboodle enforce FullAccess and dbo schemas
referential integrity
94. When referential integrity is enforced, what
does that mean for your joins?
9 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
Inner joins will produce the same results
as left outer joins if you follow the Cogito
Data Dictionary documentation
95. Where is referential integrity NOT enforced? FilteredAccess Schema
96. Primary key or Lookup column = -1 Data is unexpectedly null
97. -1's partner in a text field *Unspecified
98. Example of a -1 Patient has no PCP. -1 would be in
the PatientDim table under PrimaryCare-
ProviderKey.
99. Primary key or Lookup column = -2 Data is null and we expect it to be null
100. -2's partner in a text field *Not Applicable
101. Example of a -2 Patient takes a med at home. Fill Phar-
macy is expected to be null because it's
only applicable for outpatient dispenses.
102. How to determine why a -2 value is in a key Investigate that column's data lineage
column and the table's queries.
103. Does Clarity have referential integrity? No
104. Inferred rows If the lookup values in Table1 do not exist
in the destination table (Table2), place-
holder rows are created in Table2 so that
lookup value in Table1 has a match in
Table2.
105.
10 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
When do inferred rows' default values get When the information is populated in
updated? the source database and ETL'd over to
Caboodle
106. What happens when inferred values are up- No new row is created, and the real val-
dated with real values in a snapshot table? ues overwrite the default values
107. How do you identify inferred rows in a table? _IsInferred column
108. What shows up as the default text value for *Unknown
an inferred row?
109. Primary key or Lookup column = -3 When a record is hard-deleted in the
source system
*Deleted
110. What happens to the rows in a non-snapshot The row is UPDATED with default values
table when a -3 is encountered? (no new row created):
PrimaryKey = unchanged
Strings = *Deleted
Lookup Columns = -3
Everything else = NULL
_IsDeleted = 1
111. -3 in a lookup column the entity represented by the row itself
was deleted, not the entity the lookup
column is referencing (Ex: -3 in Encoun-
terFact.PatientKey means an encounter
was deleted, not a patient)
112. What happens when an entity found in a A new row is still created
snapshot table is deleted? 1. Snapshot column data stays intact in
the old rows
11 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
2. Snapshot column data in new rows =
-3
2. Non-snapshot column data gets re-
placed with deleted default values
113. Count column Used to remove deleted rows from a Fact
table. It stores a 1 for all rows where the
surrogate key is greater than 0.
114. When data is deleted, where is the deletion In the table that stores the entity. Other
done? tables do not change even if they have a
lookup column pointing to that entity.
115. When deletion occurs in a snapshot table New row is created
(like PatientDim), what happens to the fol- PatientKey updates with ETL like usual
lowing: Durable Key stays the same as always
New row? Numbers become NULL
PatientKey Dates become NULL
Durable Key Text becomes *Deleted
Numbers Snapshot rows = -3
Dates
Text
Snapshot rows
116. When deletion occurs in a non-snapshot No new row is created
table (like EncounterFact), what happens to EncounterKey stays the same
the following: PatientKey = -3
New row? PatientDurableKey = -3
EncounterKey ProviderKey = -3
PatientKey
PatientDurableKey
ProviderKey
12 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
117. What are 3 special rows in Caboodle? Primary Key equal to:
-1 = *Unspecified
-2 = *Not Applicable
-3 = *Deleted
118. Why does every table have 3 special rows? To maintain referential integrity
119. Do the 3 special rows in every table repre- No.
sent an entity?
120. 2 ways to ensure you get accurate counts WHERE EncounterFact.EncounterKey > 0
or
WHERE EncounterFact.Count = 1
121. If a join is FROM a lookup column to a prima- Inner
ry key, what join type should you use?
122. If a join is from a primary key column TO a Outer
lookup column, what join type should you
use?
123. Where is referential integrity not enforced in FilteredAccess Schema since it's already
Caboodle? a filtered database
124. If a row has a primary key of -2, what will the lookup columns = ‐2
other values be alongside it? string columns = *Not Applicable
all other data type columns = NULL
125. Inferred row values primary key = positive number
lookup columns = -1
126. What kind of relationships do bridge tables many to many
represent?
13 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
127. Example of a bridge table pts and the diagnoses on their problem
list
128. How many columns does a bridge table 2
have? <Name>ComboKey (to which other ta-
bles join)
<Name>Key (lookup column to associat-
ed dim table)
129. How do you identify a bridge table? Ends in Bridge (Ex: DiagnosisBridge)
130. Ex: DiagnosisBridge - What does Diagnosis- Surrogate key that represents a combi-
ComboKey represent? nation of diagnoses
131. Ex: DiagnosisBridge - What does the Diagno- A lookup column to DiagnosisDim
sisKey represent?
132. <Name>ComboKey, <Name>Key - What The name of the table to which the
does <Name> mean? bridge has been formed
133. What does a lookup column to a bridge table ComboKey
end in?
134. What does joining to a bridge table do to the Increases granularity since bridge tables
granularity of your results? map a "to‐many" relationship
135. Difference in bridge tables in FullAccess? They are combined with the dimension
they link out to in a view that has the
same name as the bridge table.
136. Data mart Brings together data points from several
DMCs in one place for easier reporting
137. What is the structure of a data mart?
14 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
There is no set structure because each is
made for a specific reporting purpose
138. What does the load package look like for a There isn't one because data marts use a
data mart? stored
procedure that pulls Caboodle data to-
gether
139. Registry data mart Extract data from the Chronicles registry
framework
140. Where can you find registry data marts? In Clarity. Not Caboodle by default.
141. SetDim tables Dimension tables that store lists or sets
of records
from Chronicles or other sources
142. Grouper Records from a master file grouped to-
gether
143. Examples of SetDim tables Grps of departments -> DepartmentSet-
Dim
Grps of immunizations -> Immunization-
SetDim
144. VCG master file Groupers that are extracted to a SetDim
table
145. Relationship between SetDim tables and VCG groupers are the most common use
VCG groupers of the SetDim tables
146. Where can you find what records are in a Record viewer
VCG grouper?
15 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
147. EAV model Entity attribute value. For cases where
attributes change often.
148. AttributeDim table Stores the list of all attributes used by the
AttributeValueDim system in Caboodle
149. What is contained in the AttributeDim table? One row per attribute and contains the
name of the attribute
150. TEST: Which schema should be used for re- FullAccess schema on the reporting
port writing? database
151. TEST: T/F If a datapoint exists in Clarity but True
not in Caboodle, your Caboodle developers
can create a custom SSIS package to extract
the data point to Caboodle
152. TEST: How can you tell if a column is a lookup 1. ER diagram lists the column and what
column? table it joins to
2. The column will end in Key but not be
the primary key of the table
153. TEST: In the Packages section, which types of Clarity packages
packages will be loading Epic data?
154. TEST: Fact tables generally have more _____ Lookup columns
than dimension tables have
155. TEST: Typically, how many primary key 1
columns do fact and dim tables have?
156. TEST: Any table with a change tracking type durable
of snapshot will have one ______ column
16 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
157. TEST: Which type of table is more likely to Dim
have a change tracking type of snapshot -
fact or dim?
158. TEST: Is there a general trend as to which has No trend
more columns - fact or dim tables?
159. TEST: What is at the center of the star Fact table
schema
160. TEST: Why do fact tables have so many Point to dim tables that store information
lookup columns? about the entities
161. TEST: Which of the following types of A and B
columns exist in all fact and dimension ta-
bles?
A: Zero or more lookup columns
B: A surrogate key that acts as the primary
key of the table
C: An IsCurrent = 1 column
D: A DurableKey column
162. TEST: Fact tables have a _________ column but Count
dimension tables do not
163. TEST: Only Snapshot tables have __________ DurableKey and IsCurrent
and ____________ columns
164. TEST: Define change tracking Some tables retain values previously
stored for columns
165. TEST: What columns do Snapshot tables DurableKey, StartDate, EndDate, and Is-
have to help track changes? Current
17 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
166. TEST: When comparing snapshot entities, Durable key because there may be more
like providers, it's important to compare than one surrogate key value associated
______________values. Why? with a single entity.
167. TEST: Table A has a lookup column directed Only Table B
to Table B. An execution runs, which includes
a package that loads data to Table A but does
not contain any packages which will popu-
late Table B. Which table gets an inferred
row?
168. TEST: If the primary key of a row in a fact A and D
table is -1 then:
A: The lookup columns will store a -1
B: The lookup columns will store *Unspeci-
fied
C: The lookup columns will store *Unknown
D: Columns with a data type of varchar will
store *Unspecified
E: Columns with a data type of Date Time will
store '1979-01-01'
169. TEST: What kind of column is a ComboKey lookup column
170. TEST: A ComboKey is used as a lookup col- Bridge
umn to which kind of table?
171. A lookup column in CB contains the value -3. 1. The data that once populated this row
What could this mean? has been deleted
2. This is the row with -3 as its primary
key (all lookup columns = -3)
18 / 19
CDW110v
Study online at https://quizlet.com/_g4bx8a
If primary key is non-negative then op-
tion 1
172. T/F All inferred rows have a primary key of -1 False. Primary keys are non-negative for
inferred rows. Lookup columns are set to
-1.
19 / 19
