CDW110 EXAM ACTUAL EXAM 200 QUESTIONS AND CORRECT DETAILED ANSWERS|AGRADE

CDW110 EXAM 2023-2024 ACTUAL EXAM 200

QUESTIONS AND CORRECT DETAILED

ANSWERS|AGRADE

How to see what schema is being used for reports? - ANSWER- SELECT

SCHEMA_NAME()

Which Cogito tools query from Caboodle? - ANSWER- Mainly SlicerDicer, but

also certain custom Radar metrics and Workbench templates

True or false: all tables and columns in Caboodle are written in PascalCase -

ANSWER- True

True or false: all key columns in Caboodle are surrogate keys - ANSWER- True

Surrogate key - ANSWER- The column's value does not exist in the source

database and is created during the ETL process. This is necessary because

Caboodle contains both Epic and non-Epic data

Lookup column - ANSWER- A column in one table whose value identifies at least

one row in another table. Most tables in Caboodle have at least one

Compared to Fact tables, Dimension tables usually contain.. - ANSWER- More

data and fewer lookup columns

True or false: lookup columns end in -Key - ANSWER- True

Where to look for identifying information for validation in Caboodle? - ANSWEREpicId or EpicCsn columns

DMC - ANSWER- Data Model Component. Consists of the table, the packages

used to populate the table, and underlying metadata tables that assist with ETL

Dimension tables - ANSWER- One row for each entity within the set. Examples

are ProviderDim, PatientDim

Fact tables - ANSWER- One row for each occurrence of some significant,

measurable event. Examples are MedicationOrderFact and EncounterFact

Bridge tables - ANSWER- For many to many relationships

DataMart - ANSWER- Report on data points from several DMCs in one place

What does it mean when a DMC name ends with X.? - ANSWER- It's custom

made

What are some of the pros of Caboodle being a dimensional data model? -

ANSWER- Intuitive, quicker to write a report, quicker run times due to fewer

tables and joins

What is the downside of Caboodle being a dimensional data model? - ANSWERMore complicated ETL process to maintain

Star schema - ANSWER- Fact table surrounded by and linked to dimensions

Where is the Caboodle data dictionary found? - ANSWER- Analytics catalog

Data dictionary overview section - ANSWER- Description, type of database

object, granularity

Data dictionary column section - ANSWER- Name, data type, availability in

SlicerDicer, links to (lookup columns), chronicles info, description

Data dictionary lineage section - ANSWER- Available in expanded column.

Contains chronicles INI nd item numbers and how the item can be used in

SlicerDicer sessions (FDS records)

Data dictionary ER diagram section - ANSWER- Classic star schema for

investigating joins. Only for Fact and DataMart. Non join columns can also be

listed by clicking the key to expand into full column list

Data dictionary dependencies section - ANSWER- Indicates what database objects

a table is dependent on. Clarity or caboodle tables and columns

Data dictionary queries section - ANSWER- Lists SSIS packages that populate the

table and their queries

True or false: it is possible for more than one SSIS package and more than one data

source to populate a table in caboodle - ANSWER- True

Change tracking - ANSWER- A property of a table that informs us how Caboodle

will be updated when changes are made to the source databases

Change tracking types - ANSWER- 1) None, 2) Snapshot Change Tracking

How to tell if a table is using snapshot change tracking? - ANSWER- Chip in the

overview section of the table

Non snapshot table - ANSWER- Reflect the current content of the source data at

the time of the most recent extract

Non Snapchat table granularity - ANSWER- One row per entity extracted to the

table

Snapshot table - ANSWER- Use change tracking which allows to store but current

and historical data

Snapshot table granularity - ANSWER- One row per entity for a date range

When using snapshot tables, additional filtered is needed to ensure that... -

ANSWER- Only current data is returned

True or false: snapshot data tracks when a specific change took place - ANSWERFalse

What is the granularity of PatientDim? - ANSWER- A patient record for a date

range

What does the DurableKey identify in a table? - ANSWER- Unique entities

Which columns show the date range for a Snapchat column? - ANSWERStartDate and EndDate

What are the default start and end dates? - ANSWER- 1/1/1979, 12/31/2099

True or false: for a given DurableKey value, there is exactly one row where

IsCurrent = 1 - ANSWER- True

What is the correct way to join a snapshot table? - ANSWER- Join on DurableKey

and filter IsCurrent = 1

True or false: DurableKey only exist in snapshot tables - ANSWER- True

Referential Integrity - ANSWER- Lookup columns will always have a value and

that value will find a match in the destination table

True or false: due to referential integrity, there are no nulls in Caboodle tables -

ANSWER- False. Nulls can exist in non-lookup columns

True or false: Caboodle enforces referential integrity in all 3 schemas - ANSWERFalse, only dbo and FullAccess, not in FilteredAccess

What is the lookup value or unspecified values? - ANSWER- -1

What is the lookup value for non-applicable values? - ANSWER- -2

What is the lookup value for deleted values? - ANSWER- -3

How to filter out deleted data from a Fact table? - ANSWER- Use the Count

column. It stores a 1 when the row should be included in a calculation and a 0

when not

What happens to the Durable Key when there is a deletion? - ANSWER- It

remains the same in the new row

What happens string cells when there is a deletion? - ANSWER- *Deleted

What happens to all other cells when there is a deletion? - ANSWER- NULL

How can you tell a column is lookup? - ANSWER- Ends in -Key

True or false: in a Snapshot table, PrimaryKey and DurableKey remain untouched

- ANSWER- True

Inferred row - ANSWER- Placeholder rows that are added to the destination table

when the lookup column value does not have a match in the destination table