CDW 110v Caboodle Data Model Fundamentals
Questions with verified answers
"_Current" View Ans✓✓✓
A foreign key column in Caboodle contains the value -3. What could this mean?
Ans✓✓✓ Either the data that once populated this row was deleted from the
source, or this is the -3 row, for which all foreign key columns are also -3.
Caboodle Console Ans✓✓✓
Caboodle is considered a normalized database. Ans✓✓✓ False
Caboodle is designed to... Ans✓✓✓ Store Epic data, store non-Epic data, make
reporting easy, interoperate with SlicerDicer.
Data Warehouse Ans✓✓✓
DBO (Reporting Tables reside in this Schema) Ans✓✓✓ DBO are a way to
organize tables. Import tables used by developers to store data.
Define " Surrogate key" Ans✓✓✓ represents an entity in a moment in time.
Define "Bridge DMC" Ans✓✓✓ Bridge are used to model many-to-many
relationships. For example, a patient can receive many diagnoses (DiagnosisDim)
during an office visit (VisitFact). DiagnosisBridge connects the visit to the
,collection of diagnoses to which it is associate. The names of bridge DMC end
with "Bridge"
Define "Business key" Ans✓✓✓ The business key of a record is the unique
identifier it had in its source.
In Caboodle, business keys are stored as a combination of two data points:
"IdType" and "Id"
Define "Conformed dimension" Ans✓✓✓ There are many fact DMC's in
Caboodle. Rather than define a complete set of dimensions for each, it's wise to
reuse them wherever possible. For example, most fact DMC's require a dimension
describing patients, and they all reference the same table (PatientDim) to do so.
Dimensions that provide context to multiple facts in this way are said to be
conformed dimensions.
Define "Deleted Foreign Key" Ans✓✓✓ Every DMC in Caboodle has a row with a
surrogate key of -3
This row provides a place for foreign keys in deleted rows to point
Define "Dimensional data model" Ans✓✓✓ 1. The data is more accessible
2. Efficiency
3. Fewer tables and simpler joins
Define "Durable key" Ans✓✓✓ Every type 2 DMC has a column named
DurableKey. This column contains the same value for all the rows that belong to a
specific entity. For example, in PatientDim, all the rows corresponding to a
, specific patient will have the same durable key. (each row will have a unique
surrogate key)
Define "IdLookup table" Ans✓✓✓ you can find all the business keys that belong
to a single record by querying Caboodles metadata. Lookup tables are found in
the staging database under the Epic schema and follow the naming convention
<DMC Name>_IdLookup. These tables are not generally for reporting but are
critical to the operation of Caboodles infrastructure.
Define "IdType" Ans✓✓✓ the IdType column contains a string value that
specifies the type of entity loaded
Define "Inferred row" Ans✓✓✓ When a foreign key value exists but doesn't
correspond to a row in the target table, an inferred row is created.
These default values are updated when the expected row is eventually loaded.
Define "Normalized data model" Ans✓✓✓ Normalized databases avoid
redundancies in the data to make ETL safe and simple
Define "Profile dimension" Ans✓✓✓ A "Profile Dimension" holds dimensional
data that is specific to a single fact DMC.
Define "Referential integrity" Ans✓✓✓ for ease and efficiency of report writing,
foreign key columns in Caboodle never contain NULL or unmatched values. When
a NULL or unmatched foreign key value is loaded into Caboodle, the ETL
infrastructure still assigns a value to the corresponding foreign key column in the
reporting database.
Questions with verified answers
"_Current" View Ans✓✓✓
A foreign key column in Caboodle contains the value -3. What could this mean?
Ans✓✓✓ Either the data that once populated this row was deleted from the
source, or this is the -3 row, for which all foreign key columns are also -3.
Caboodle Console Ans✓✓✓
Caboodle is considered a normalized database. Ans✓✓✓ False
Caboodle is designed to... Ans✓✓✓ Store Epic data, store non-Epic data, make
reporting easy, interoperate with SlicerDicer.
Data Warehouse Ans✓✓✓
DBO (Reporting Tables reside in this Schema) Ans✓✓✓ DBO are a way to
organize tables. Import tables used by developers to store data.
Define " Surrogate key" Ans✓✓✓ represents an entity in a moment in time.
Define "Bridge DMC" Ans✓✓✓ Bridge are used to model many-to-many
relationships. For example, a patient can receive many diagnoses (DiagnosisDim)
during an office visit (VisitFact). DiagnosisBridge connects the visit to the
,collection of diagnoses to which it is associate. The names of bridge DMC end
with "Bridge"
Define "Business key" Ans✓✓✓ The business key of a record is the unique
identifier it had in its source.
In Caboodle, business keys are stored as a combination of two data points:
"IdType" and "Id"
Define "Conformed dimension" Ans✓✓✓ There are many fact DMC's in
Caboodle. Rather than define a complete set of dimensions for each, it's wise to
reuse them wherever possible. For example, most fact DMC's require a dimension
describing patients, and they all reference the same table (PatientDim) to do so.
Dimensions that provide context to multiple facts in this way are said to be
conformed dimensions.
Define "Deleted Foreign Key" Ans✓✓✓ Every DMC in Caboodle has a row with a
surrogate key of -3
This row provides a place for foreign keys in deleted rows to point
Define "Dimensional data model" Ans✓✓✓ 1. The data is more accessible
2. Efficiency
3. Fewer tables and simpler joins
Define "Durable key" Ans✓✓✓ Every type 2 DMC has a column named
DurableKey. This column contains the same value for all the rows that belong to a
specific entity. For example, in PatientDim, all the rows corresponding to a
, specific patient will have the same durable key. (each row will have a unique
surrogate key)
Define "IdLookup table" Ans✓✓✓ you can find all the business keys that belong
to a single record by querying Caboodles metadata. Lookup tables are found in
the staging database under the Epic schema and follow the naming convention
<DMC Name>_IdLookup. These tables are not generally for reporting but are
critical to the operation of Caboodles infrastructure.
Define "IdType" Ans✓✓✓ the IdType column contains a string value that
specifies the type of entity loaded
Define "Inferred row" Ans✓✓✓ When a foreign key value exists but doesn't
correspond to a row in the target table, an inferred row is created.
These default values are updated when the expected row is eventually loaded.
Define "Normalized data model" Ans✓✓✓ Normalized databases avoid
redundancies in the data to make ETL safe and simple
Define "Profile dimension" Ans✓✓✓ A "Profile Dimension" holds dimensional
data that is specific to a single fact DMC.
Define "Referential integrity" Ans✓✓✓ for ease and efficiency of report writing,
foreign key columns in Caboodle never contain NULL or unmatched values. When
a NULL or unmatched foreign key value is loaded into Caboodle, the ETL
infrastructure still assigns a value to the corresponding foreign key column in the
reporting database.
