OVERALL-Enforcing Referential Integrity
Give this one a try later!
Caboodle uses three mechanisms for enforcing referential integrity:
· Every table in Caboodle contains three rows with surrogate keys of ‐1, ‐2,
and ‐3. These rows serve as the destination for lookup columns in other
tables.
· When source data is unavailable to populate a lookup column, Caboodle
supplies a default value of ‐1, ‐2, or ‐3. Which value is used depends on the
circumstance behind the missing information.
· When data loaded in one Caboodle table is more up‐to‐date than data in
another table, the Caboodle ETL process generates an inferred row in the
delayed table. This row represents the entity being referenced by the more
up‐to‐date table. Inferred rows act as place holders until the relevant
information is loaded into Caboodle.
CHAPTER 4-The -1 Row
,Give this one a try later!
AS PART OF REFERENTIAL INTEGRITY
When a foreign key value is NULL in the source system, the foreign key is
said to be UNSPECIFIED.
(Unspecified Foreign Key)
In this case, the corresponding foreign key in Caboodle is set to ‐1.
Since many date columns in Caboodle are
foreign keys to the DateDim table, NULL dates from Clarity are often
represented with a ‐1 value DateKey
columns in Caboodle.
CHAPTER 4-Suppose you're creating a report using EncounterFact and ProviderDim
that displays the name of each provider for each encounter. If an encounter's data
loads into EncounterFact, but that provider's data hasn't been extracted to
ProviderDim, what value would appear in ProviderDim.Name for this encounter?
Give this one a try later!
There would be an inferred row in ProviderDim, therefore
ProviderDim.Name would show "*Unknown" for the encounter.
CHAPTER 1-Data Warehouse
Give this one a try later!
, In a data warehouse, multiple sources may load data pertaining to a single
entity. This means that more than one package may populate a given row in
a Caboodle table. As a result, there may be multiple business key values
associated with a single entity in a Caboodle table.
CHAPTER 4-The -3 Row
Give this one a try later!
AS PART OF REFERENTIAL INTEGRITY
If an account was deleted in the source, all foreign key values are set to ‐3.
When a record is hard‐deleted in the source system or no longer passes
the filters required for extraction,
Caboodle tracks the deletion in one of two ways.
· For Type 1 DMCs, the existing row is updated with default values
representing the deletion. The primary key remains unchanged.
· For Type 2 DMCs, a new row is added that represents the deletion.
Existing Type 2 data remains, while Type 1 data is overwritten with the
default values. The primary key﴾s﴿ and durable key will remain unchanged.
PRACTICE EXAM-How can you tell if a column is a foreign key in Caboodle?
CHOOSE ALL THAT APPLY
A. The column is marked as Type 2
B. The ER diagram lists it as a foreign key
C. The column is contained in a fact table
D. The destination table is listed to the right of the column description
Give this one a try later!
, B. The ER diagram lists it as a foreign key
D. The destination table is listed to the right of the column description
Type 1 tables do not track changes, so do not need a DurableKey or
IsCurrent column.
OVERALL-EAV
Give this one a try later!
Entity Attribute Value
The three major components of the EAV data model are entities, attributes,
and values.
Let's define these terms by looking at a particular example: suppose your
organization is tracking patient hair color. In this case, an ENTITY is a patient
and the ATTRIBUTE is hair color. A VALUE would be a particular patient's
hair color, such as red.
OVERALL-Surrogate Key
Give this one a try later!
Surrogate Key is a key where the values don't exist in the source data.
These values are NOT in Clarity. They are generated in the ETL
process...They are created when moved.
OVERALL-ER Diagram
Give this one a try later!
Give this one a try later!
Caboodle uses three mechanisms for enforcing referential integrity:
· Every table in Caboodle contains three rows with surrogate keys of ‐1, ‐2,
and ‐3. These rows serve as the destination for lookup columns in other
tables.
· When source data is unavailable to populate a lookup column, Caboodle
supplies a default value of ‐1, ‐2, or ‐3. Which value is used depends on the
circumstance behind the missing information.
· When data loaded in one Caboodle table is more up‐to‐date than data in
another table, the Caboodle ETL process generates an inferred row in the
delayed table. This row represents the entity being referenced by the more
up‐to‐date table. Inferred rows act as place holders until the relevant
information is loaded into Caboodle.
CHAPTER 4-The -1 Row
,Give this one a try later!
AS PART OF REFERENTIAL INTEGRITY
When a foreign key value is NULL in the source system, the foreign key is
said to be UNSPECIFIED.
(Unspecified Foreign Key)
In this case, the corresponding foreign key in Caboodle is set to ‐1.
Since many date columns in Caboodle are
foreign keys to the DateDim table, NULL dates from Clarity are often
represented with a ‐1 value DateKey
columns in Caboodle.
CHAPTER 4-Suppose you're creating a report using EncounterFact and ProviderDim
that displays the name of each provider for each encounter. If an encounter's data
loads into EncounterFact, but that provider's data hasn't been extracted to
ProviderDim, what value would appear in ProviderDim.Name for this encounter?
Give this one a try later!
There would be an inferred row in ProviderDim, therefore
ProviderDim.Name would show "*Unknown" for the encounter.
CHAPTER 1-Data Warehouse
Give this one a try later!
, In a data warehouse, multiple sources may load data pertaining to a single
entity. This means that more than one package may populate a given row in
a Caboodle table. As a result, there may be multiple business key values
associated with a single entity in a Caboodle table.
CHAPTER 4-The -3 Row
Give this one a try later!
AS PART OF REFERENTIAL INTEGRITY
If an account was deleted in the source, all foreign key values are set to ‐3.
When a record is hard‐deleted in the source system or no longer passes
the filters required for extraction,
Caboodle tracks the deletion in one of two ways.
· For Type 1 DMCs, the existing row is updated with default values
representing the deletion. The primary key remains unchanged.
· For Type 2 DMCs, a new row is added that represents the deletion.
Existing Type 2 data remains, while Type 1 data is overwritten with the
default values. The primary key﴾s﴿ and durable key will remain unchanged.
PRACTICE EXAM-How can you tell if a column is a foreign key in Caboodle?
CHOOSE ALL THAT APPLY
A. The column is marked as Type 2
B. The ER diagram lists it as a foreign key
C. The column is contained in a fact table
D. The destination table is listed to the right of the column description
Give this one a try later!
, B. The ER diagram lists it as a foreign key
D. The destination table is listed to the right of the column description
Type 1 tables do not track changes, so do not need a DurableKey or
IsCurrent column.
OVERALL-EAV
Give this one a try later!
Entity Attribute Value
The three major components of the EAV data model are entities, attributes,
and values.
Let's define these terms by looking at a particular example: suppose your
organization is tracking patient hair color. In this case, an ENTITY is a patient
and the ATTRIBUTE is hair color. A VALUE would be a particular patient's
hair color, such as red.
OVERALL-Surrogate Key
Give this one a try later!
Surrogate Key is a key where the values don't exist in the source data.
These values are NOT in Clarity. They are generated in the ETL
process...They are created when moved.
OVERALL-ER Diagram
Give this one a try later!
