The columns of a fact table contain relevant metrics and foreign keys, or lookup
columns, to link out to entities related to the event.
Lookup columns in Caboodle never contain null or
unmatched values.
ForeignKeys in Caboodle end in Key; called lookup
columns. Column in a table that joins to another table. Points to another table.
Give this one a try later!
, Chapter 2. (Study Checklist) Lookup Column
In the Caboodle dictionary it lists if the column is type 2
Give this one a try later!
CHAPTER 3. (Study Checklist) Identify the change type of a column?
Yes. For Example, MedicationOrderFact has a lookup column to EncounterFact.
Give this one a try later!
Chapter 2. (In-Class Exercises) In Caboodle, can a fact table have a lookup
column to another fact table?
Include only rows where the key is greater than 0.
Give this one a try later!
(General Reporting Tips) Add a filter to most queries to exclude Caboodle's
special rows for unspecified, not applicable, and deleted records, which
have surrogate keys of -1, -2, and -3
There are two change types: Type 1 and Type 2.
Caboodle tables and columns are given a change type, which determines whether or
,not that table or column tracks historical data in the source.
How a table tracks data over time.
Type 1 = Current
Type 2 = Storing data over time
Give this one a try later!
CHAPTER 3. (Study Checklist) Change Type
A surrogate key is any column or set of columns that can be declared as the primary
key instead of a "real" or natural key. Sometimes there can be several natural keys that
could be declared as the primary key, and these are all called candidate keys. So a
surrogate is a candidate key.
Give this one a try later!
DEFINITION-SURROGATE KEY
This is documented in the table Properties in the Dictionary.
Give this one a try later!
CHAPTER 3. (Study Checklist) identify the change type of a table
Bridge
Although it is the destination of the join, the combo key column of a bridge table is
not the primary key. Rather, multiple rows may have the same combo key value.
, Give this one a try later!
PRACTICE EXAM-A ComboKey is used as a foregin key to which of the
following types of DMCs?
CHOOSE ONLY ONE ANSWER
A. Fact
B. Dimension
C. SetDim
D. DataMart
E. AttributeValueDim
F. Bridge
When a foreign key value is loaded into Caboodle, the ETL process uses its Id and
IdType to determine the correct surrogate key value to store. If the referenced row
does not exist yet, the ETL process creates a new row in the target table and assigns a
new surrogate key value as its primary key. Such a row is called an inferred row.
Inferred rows are created when one table has more up-to-date information than
another.
Two common scenarios:
A. Unmatched references in the source
B. Testing or development
Every table in Caboodle has three "extra" rows.
These rows provide a place for foreign keys to point.
Give this one a try later!
CHAPTER 4-Inferred Row
The IsCurrent column is a flag that stores the value of 1 if the row holds the most
current information and 0 otherwise.
columns, to link out to entities related to the event.
Lookup columns in Caboodle never contain null or
unmatched values.
ForeignKeys in Caboodle end in Key; called lookup
columns. Column in a table that joins to another table. Points to another table.
Give this one a try later!
, Chapter 2. (Study Checklist) Lookup Column
In the Caboodle dictionary it lists if the column is type 2
Give this one a try later!
CHAPTER 3. (Study Checklist) Identify the change type of a column?
Yes. For Example, MedicationOrderFact has a lookup column to EncounterFact.
Give this one a try later!
Chapter 2. (In-Class Exercises) In Caboodle, can a fact table have a lookup
column to another fact table?
Include only rows where the key is greater than 0.
Give this one a try later!
(General Reporting Tips) Add a filter to most queries to exclude Caboodle's
special rows for unspecified, not applicable, and deleted records, which
have surrogate keys of -1, -2, and -3
There are two change types: Type 1 and Type 2.
Caboodle tables and columns are given a change type, which determines whether or
,not that table or column tracks historical data in the source.
How a table tracks data over time.
Type 1 = Current
Type 2 = Storing data over time
Give this one a try later!
CHAPTER 3. (Study Checklist) Change Type
A surrogate key is any column or set of columns that can be declared as the primary
key instead of a "real" or natural key. Sometimes there can be several natural keys that
could be declared as the primary key, and these are all called candidate keys. So a
surrogate is a candidate key.
Give this one a try later!
DEFINITION-SURROGATE KEY
This is documented in the table Properties in the Dictionary.
Give this one a try later!
CHAPTER 3. (Study Checklist) identify the change type of a table
Bridge
Although it is the destination of the join, the combo key column of a bridge table is
not the primary key. Rather, multiple rows may have the same combo key value.
, Give this one a try later!
PRACTICE EXAM-A ComboKey is used as a foregin key to which of the
following types of DMCs?
CHOOSE ONLY ONE ANSWER
A. Fact
B. Dimension
C. SetDim
D. DataMart
E. AttributeValueDim
F. Bridge
When a foreign key value is loaded into Caboodle, the ETL process uses its Id and
IdType to determine the correct surrogate key value to store. If the referenced row
does not exist yet, the ETL process creates a new row in the target table and assigns a
new surrogate key value as its primary key. Such a row is called an inferred row.
Inferred rows are created when one table has more up-to-date information than
another.
Two common scenarios:
A. Unmatched references in the source
B. Testing or development
Every table in Caboodle has three "extra" rows.
These rows provide a place for foreign keys to point.
Give this one a try later!
CHAPTER 4-Inferred Row
The IsCurrent column is a flag that stores the value of 1 if the row holds the most
current information and 0 otherwise.
