CDW110 EXAM - ACTUAL EXAM 200

CDW110 EXAM - 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? - ANSWER- EpicId 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 1 / 4

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? - ANSWER- More 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

• / 4

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 - ANSWER-

False

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? - ANSWER- StartDate 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 3 / 4

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 - ANSWER- False, 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

• / 4