CDW 110v Caboodle Data Model
Fundamentals
"_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
associated. 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.
\Define "Type 1 data" - ANS-Historical values are NOT retained for type 1 data
\Define "Type 2 data" - ANS-Caboodle retains historical values for type 2 data by creating
multiple rows for each entity, with each row containing the values for a particular time period.
\Define Attribute - ANS-
\Define Bridge - ANS-
\Define Business Key - ANS-
\Define Conformed Dimension - ANS-
\Define Data Lineage - ANS-
\Define Data Model Component - ANS-No table in Caboodle "stands alone". They are
surrounded and supported by a collection of various metadata tables.
\Define Deleted Rows - ANS-
\Define Dictionary - ANS-
\Define Dimension Table - ANS-
\Define Durable Key - ANS-
\Define Entity - ANS-
\Define Fact Table - ANS-
\Define ID - ANS-
\Define IdType - ANS-
\Define Inferred Rows - ANS-
\Define Not Applicable Rows - ANS-
\Define Profile Dimension - ANS-
\Define Unspecified Rows - ANS-
\Define Value - ANS-
\Differentiate between Clarity and Caboodle. - ANS-
\DMC DATA MART COMPONENTS - ANS-REFER TO THE COLLECTION OF METADATA
TABLES
\Durable Key - ANS-(Look up IsCurrent) I think durable key is the one that is current data,
not historical data??
\EndDate Column - ANS-
\Epic (Schema for Staging DB) - ANS-Metadata tables used by Caboodle's infrastructure.
Contain _IDLookup tables which map to source IDs to system-generated Caboodle primary
keys in the reporting database
\ETL - ANS-
\Explain foreign key references from Type 2 tables. - ANS-
Fundamentals
"_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
associated. 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.
\Define "Type 1 data" - ANS-Historical values are NOT retained for type 1 data
\Define "Type 2 data" - ANS-Caboodle retains historical values for type 2 data by creating
multiple rows for each entity, with each row containing the values for a particular time period.
\Define Attribute - ANS-
\Define Bridge - ANS-
\Define Business Key - ANS-
\Define Conformed Dimension - ANS-
\Define Data Lineage - ANS-
\Define Data Model Component - ANS-No table in Caboodle "stands alone". They are
surrounded and supported by a collection of various metadata tables.
\Define Deleted Rows - ANS-
\Define Dictionary - ANS-
\Define Dimension Table - ANS-
\Define Durable Key - ANS-
\Define Entity - ANS-
\Define Fact Table - ANS-
\Define ID - ANS-
\Define IdType - ANS-
\Define Inferred Rows - ANS-
\Define Not Applicable Rows - ANS-
\Define Profile Dimension - ANS-
\Define Unspecified Rows - ANS-
\Define Value - ANS-
\Differentiate between Clarity and Caboodle. - ANS-
\DMC DATA MART COMPONENTS - ANS-REFER TO THE COLLECTION OF METADATA
TABLES
\Durable Key - ANS-(Look up IsCurrent) I think durable key is the one that is current data,
not historical data??
\EndDate Column - ANS-
\Epic (Schema for Staging DB) - ANS-Metadata tables used by Caboodle's infrastructure.
Contain _IDLookup tables which map to source IDs to system-generated Caboodle primary
keys in the reporting database
\ETL - ANS-
\Explain foreign key references from Type 2 tables. - ANS-
