Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
1. What is a primary reason that snap- Snapshot data is tracked by the Caboodle ETL process
shot data should not be used for and does not track when a specific change took place.
audit reporting?
2. Snapshot columns retain... Previous values which been extracted to Caboodle and
have since changed in the source.
3. How might you be able to deter- Check the data lineage to determine the Chronicles
mine the date when a change took item and/or Clarity table and column that store this in-
place? formation. For non‐Epic data, work with your Caboodle
developers to determine relevant data lineage.
4. What 4 columns do snapshot tables Iscurrent, StartDate, EndDate, DurableKey
include?
5. DurableKey While there may be multiple rows in a snapshot table
for a given entity, each with a unique primary key value,
the DurableKey column stores the same value for all
rows in the table that refer to the same entity.
6. The StartDate and EndDate columns Store the date range for the snapshot data in the table.
refer to: For the rows that hold the earliest and most recent ver-
sions of the data recorded in Caboodle, the StartDate
and EndDate are set to a default start and end date
value of 1/1/1979 and 12/31/2099.
7. Lookup columns will join to either Whether that table uses change tracking.
the surrogate key or DurableKey de-
pending upon:
8. When joining to a snapshot table do Use the durable key lookup column in the starting table
these two things: to join to the durable key of the target table. Then, filter
to the current row of the snapshot table
1 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
9. Foreign keys that join to the surro- Was current at the time the data was loaded. Therefore,
gate key of a snapshot table point to such foreign keys could point to stale data in snapshot
the row that... tables.
10. True or False: There will rarely be a False. There will always be.
lookup column to the durable key of
a snapshot table
11. When comparing durable key val- The negative values: ‐1, ‐2, ‐3. Neglecting to do so may
ues, be sure to remove what? result in false positives.
12. True or False: Any change to patient False. Assuming the data is extracted to Caboodle, only
data in Clarity will generate a new changes to snapshot columns would generate a new
row in PatientDim after the next ex- row in PatientDim.
tract.
13. Consider the following query: A and C
SELECT DurableKey FROM <Table-
Name> Which of the following state-
ments are true?
A. This query will only run if <Table-
Name>is a snapshot table.
B. This query will run regardless of
whether <TableName> has change
tracking.
C. The value in the DurableKey col-
umn could repeat in multiple rows
of the results.
D. There is no table in Caboodle for
which this query will run.
14. Databases that use referential in- 1. Foreign key columns will always have a value even if
tegrity will always have 2 properties: the source data is null
2 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
2. Foreign key column values will always have a match-
ing value in the destination table/column
15. What value indicates unspecified? -1
16. What value indicates Not Applica- -2
ble?
17. What value indicates Deleted? -3
18. What does -1 indicate? A NULL value in the source system
19. Since many date columns in Caboo- NULL dates from Clarity are often represented with a
dle are foreign keys to the DateDim ‐1 value DateKey columns in Caboodle.
table ... ?
20. Give the example of an unspecified If a patient has not been assigned a primary care
data point that would show as -1. physician. When the patient's record is loaded into
Caboodle, the PrimaryCareProviderKey column is set to
‐1.
21. What example does the text provide A non-Epic source will not have an EpicCsn.
for a Not Applicable value?
22. To know why a ‐2 value is in a key You should investigate that column's data lineage in
column, what should you do? the Caboodle Dictionary.
23. A value of ‐3 in a foreign key column That the entity represented by the row itself was delet-
indicates what? ed, not that the entity the foreign key is pointing to was
deleted.
24. True or False: Only snapshot tables False
in Caboodle has the 3 special rows.
3 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
25. What are the 3 special rows in Ca- Rows with primary keys of -1, -2, and -3.
boodle tables?
26. In the special row of -1 primary key, *Unspecified
what value do strings show?
27. In the special row of -3 primary key, *Deleted
what value do strings show?
28. In the special row of -2 primary key, *Not Applicable
what value do strings show?
29. In the 3 special rows, other than NULL
keys and strings, what are the val-
ues in the other columns?
30. What happens when a foreign key An inferred row is created in the destination table.
value does not use one of the de-
fault values and also does not have
a match in the destination table?
31. When are inferred rows created When the ETL process loads the data into Caboodle.
(what step in the data process)?
32. If a column is a snapshot column, It replaces the " * unknown" with the right data rather
and an inferred row is created, how than creating a new row.
does Caboodle handle that?
33. In an inferred row, what value goes In the Caboodle Dictionary, the Properties section for a
into the inferred column? column shows the default value. This is the value used
to populate that column for an inferred row.
34. It shows an empty string rather than the usual * un-
known.
4 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
For an inferred row, what is unusual
about the PatientDim.PatientEpicId
column?
35. A foreign key column in Caboodle There are two possibilities: Either the data that once
contains the value ‐3. What could populated this row in Caboodle has been deleted from
this mean? the source or this is the row with ‐3 as its primary
key, for which all foreign key columns are also ‐3. To
know which is true, check the primary key value for the
given row: a non‐negative value indicates that this row
represents deleted data in the source.
36. A row in a Caboodle table has a B
primary key value of ‐2. What val-
ue will be stored in non‐foreign key
columns with a data type of "Date"
for this row?
A. ‐2
B. NULL
C. 12/31/1840
D. *NotApplicable
37. Every bridge table has what 2 They are called <Name>ComboKey and <Name>Key,
columns? where <Name> is the name of the table to which a
bridge has been formed. The <Name>Key column can
be used as a foreign key to link to the corresponding
dimension table. The <Name>ComboKey column is a
surrogate key that represents a unique combination of
<Name>Key values.
38. True or False: All inferred rows in False. Inferred rows in Caboodle have non‐negative
Caboodle have a primary key of ‐1. primary key values because they represent an entity
that Caboodle has inferred the existence of. The for-
5 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
eign key columns for such rows will be set to ‐1 until
the Caboodle ETL process updates the data.
39. Some tables have more columns in They will not; the dictionary only shows dbo columns.
the FullAccess schema than in the
dbo. Will these columns appear in
the Caboodle dictionary?
40. When writing reports, why is it bet- Custom DMCs that your organization has created will
ter to use your organization's Ca- not show up in the training version, but will show up
boodle Dictionary rather than the in your organization's Caboodle Dictionary.
one used in training?
41. You are writing a report in Caboo- Use the search box in the top‐right corner of the Ca-
dle about surgeries. A colleague on boodle Dictionary to search for "ORC 50".
the OpTime team tells you that the
patient is stored in I ORC 50. What
is the best way to determine which
Caboodle table and column extracts
this data?
42. A column in Caboodle does not con- Investigate the Data Lineage for Each Package section
tain the data you expect to see. To for the column in question.
determine if the issue is within the
source data or the ETL process, you
want to investigate the Clarity data.
How can you determine which Clar-
ity column is used to populate the
Caboodle column?
43. True or False: The ER Diagram con- False. The ER Diagram is a visual representation of
tains information that cannot be information that can be found on the Summary tab.
6 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
found anywhere else in the Caboo-
dle Dictionary?
44. Explain the relationship between The dbo schema is what is reflected in the Caboo-
the dbo and FullAccess schemas. dle Dictionary. The FullAccess schema contains almost
everything in the dbo schema (minus a few metada-
ta columns) plus additional columns in some tables.
Since FullAccess has more data, report writers should
use it when writing queries. However, report writers
should also be familiar with dbo due to its relationship
with the Caboodle Dictionary.
45. What is different about bridge ta- In the FullAccess schema, bridge tables are combined
bles in the FullAccess schema? with the dimension they link out to in a view that has
the same name as the bridge table.
46. In Epic, what is the difference be- Chief complaint information is stored in the HRV mas-
tween a chief complaint and a diag- ter file while diagnosis information is stored in the EDG
nosis? master file.
47. What is the difference between a Both bridge tables and MappingFact tables are used
MappingFact table and a Bridge to map many‐to‐many relationships. However, Bridge
table? tables use a combo key as part of their definition and
always link to a single dimension table while Mapping-
Fact tables may link to one or more fact or dimension
tables.
48. What is the AttributeDim table? The table that stores the list of all attributes used by the
AttributeValueDim system in Caboodle.
49. What type of Chronicles data goes SmartData elements from the HLV masterfile
well with EAV tables?
7 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
50. True or False: every EAV table name False, other DMCs, such as FlowsheetValueFact and
ends with AttributeValueDim? LabComponentResultFact also make use of the EAV
model to store their data.
51. True or False: AttributeValueDim ta- False
bles typically do not track changes.
52. How are DataMarts similar to fact Because they store data about significant, measurable
tables? events
53. What is the purpose of SetDim ta- Epic uses the naming convention SetDim to help iden-
bles? tify tables that store lists, or sets, of records. These sets
are typically records in another dimension table.
54. Give an example of a SetDim table. These diagnoses are grouped into lists, which are
stored in DiagnosisSetDim.
55. What are VCG Groupers? Lists of Chronicles records, ICD codes, or other con-
cepts used together frequently in Hyperspace. VCG
groupers are extracted to the SetDim tables, and in-
clude a key back to the dimension record included in
the grouper.
56. True or False: AttributeValueDim ta- True
bles are more granular than their
associated fact table.
57. What is the purpose of dimension They contain one row for each entity within the set.
tables? Compared to fact tables, dimension tables generally
contain more data and fewer foreign keys.
58. How can lookup columns be identi- Tables that link to or from a given table are listed under
fied in the Dictionary? Properties, and hyperlinks to the destination table are
provided next to the foreign key columns descriptions.
8 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
59. True or False: A maximum of one False
foreign key can exist between two
tables in Caboodle.
60. A column in Caboodle does not con- Investigate the Data Lineage for Each Package section
tain the data you expect to see. To for the column in question
determine if the issue is within the
source data or the ETL process, you
want to investigate the Clarity data.
How can you determine which Clar-
ity column is used to populate the
Caboodle column?
61. What is the Caboodle Console? Web based application for managing Caboodle.
Where the Caboodle dictionary is found.
62. The Caboodle Dictionary reflects the Each organization's database. Custom tables and
contents of what? columns are included.
63. What are the two key elements of 1. Foreign key columns always have a value
referential integrity? 2. Foreign key column values will always find a match-
ing value in the destination table
64. When a foreign key value is NULL in -1
the source, it is said to be unspeci-
fied. What will its value be in Caboo-
dle?
65. When a foreign key is not applicable -2
as defined by the SSIS package, Ca-
boodle sets the value to?
66. Updating the row with default values
9 / 10
Caboodle Data Model
Study online at https://quizlet.com/_f84bk4
When a record is hard-deleted in the
source system, Caboodle marks the
deletion by?
67. How can you identify records that Use IsDeleted = 1
may have been hard-deleted from
the system?
68. When a record gets deleted in the Columns with no change tracking reflect the deletion
source system, how does that effect in all records of the table.
non-snapshot columns?
69. When a record gets deleted in the Columns with snapshot change tracking retain histor-
source system, how does that effect ical values.
snapshot columns?
70. Inferred rows are created when? • A table with a foreign key to that table goes through
ETL
• Not when the table itself goes through ETL
71. The entity-attribute-value (EAV) Add data to existing Caboodle tables without changing
framework allows you to? the structure of those tables.
10 / 10
