Caboodle: Referential Integrity

True or False: All inferred rows in Caboodle have a primary key of ‐1.

False. Inferred rows in caboodle have non-negative primary key values b/c they represent an entity that caboodle has inferred the existence of. Lookup columns for such rows will be set to -1 until the Caboodle ETL process updates the data.

If a lookup column is null in Clarity, what will appear in the corresponding column in Caboodle?

If the lookup column was null in the source and the SSIS package defines the data lineage, then a ‐1 will appear in Caboodle to represent an unspecified value. If the look up column's data lineage was not defined by the SSIS package that loaded data for a particular row, then a ‐2 will appear in Caboodle to represent a value that is not applicable.

A row in a Caboodle table has a primary key value of ‐2. What value will be stored in columns with a data type of "Date" for this row that are not lookup columns?

NULL. For the row with a primary key of ‐2, lookup columns will store a value of ‐2, string columns will store '*Not Applicable', and all other data type columns will store NULL.

Inferred Row

Placeholder rows that are added to the destination table when the lookup column value does not have a match in the destination table

Referential Integrity

means that lookup columns will always have a value and that value will find a match in the destination column. When a null or unmatched value is loaded into Caboodle staging database, ETL infrastructure assigns a default value to corresponding column in the reporting database. A property of SQL databases with two important properties- 1. Lookup columns will always have a value even if the source data is null. 2. Lookup column values will always have a matching value in the destination table column. Even if they are negative, lookup column values must find a match.

Log into Hyperspace as the Cogito Tools Administrator, open the Patients data model in SlicerDicer, and click Troubleshoot. What is the purpose of the WHERE clause in this data model?

The WHERE clause (WHERE subq.DurableKey > 0) filters out the rows with negative durable key values from PatientDim. Since these rows do not represent actual patients, they should not be included in the total count of patients in the Patients data model.

A lookup column in Caboodle contains the value ‐3. What could this mean?

The data that once populated this row in Caboodle has been deleted from the source or this is the row with -3 as its primary key, for which all lookup columns are also -3. Check the primary key value for the given row to know which is true: non-negative value indicates that this represents deleted data in the source.

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?

There would be an inferred row in ProviderDim, therefore ProviderDim.Name would show "*Unknown" for the encounter.