CDW 100V Caboodle

Question 1

Schemas

Answer 1

agreed upon logical division of database objects in a database.

‘a collection of tables”

Question 2

ETL order

Answer 2

Chronicles to clarity to caboodle (last)

Question 3

dbo Schema

Answer 3

Used by computers:

Data dictionary, Slicerdicer (gets data from caboodle via dbo schema)

Question 4

Full access schema

Answer 4

Used by people

Contains everything for report writers including the dbo schema. Should be your default schema for reporting!

Question 5

Filtered access schema

Answer 5

Used by people- filtered by user access security

Version of full access that has all the same columns, but different rows. Used most often when you have several organizations that are in one instance of caboodle
Ex. community hospital that has smaller arms of hospitals, dont want to share all info to everyone
- would only give data for just their site.

Question 6

SSIS

Answer 6

mechanism to move data during the ETL process

Question 7

Why Caboodle

Answer 7

1. Has both epic and non epic data
2. data source for SLICER DA DICER
3. enforced naming conventions (standardization)
4. simplified report writing

Question 8

Surrogate key

Answer 8

made up by ETL process
- used to identify something in a database
does NOT exist in source database

Does this bc there can be some external data that doesnt have any EPIC data so need surrogate keys to label all things that may be coming in

Question 9

foreign key

Answer 9

lets you link out to other tables

“Look up columns” in EPIC

Question 10

Chronicles identifier

Answer 10

look for columns that end in “-EpidID” or -EpicCsn

Question 11

Fact tables

Answer 11

represent the occurrence of a specfic, measurable event

medicationdispense is an ex

fact tables join OUT

Question 12

Star schema

Answer 12

Centered around events and thus around fact tables

Start query with central fact table and join OUT to peripheral tables

Ex: MedicationOrderFact
would have peripheral details like - when was the med dispensed TimeofdayDim
who was it dispensed by ProviderDim
who was the patient it was dispensed for etc (PatientDim)

Question 13

Dim Table

Answer 13

Dimension table

contains one row for each entity within the set

Generally contain more data and fewer lookup columns

Question 14

Fact Table

Answer 14

contains one row for each occurence of some significant, measured event

Encounterfact
MedicationorderFact

Question 15

Bridge Table

Answer 15

used in caboodle to model many to many relationships

Question 16

Caboodle

Answer 16

uses dimensional data model

Makes report writing easier, but bc of this tradeoff is ETL is more complicated to maintain

Question 17

Granularity

Answer 17

Will tell you what youre looking at in each table
ex: an encounter

Question 18

Chips

Answer 18

in data dictionary- individual attributes

Question 19

Data dictionary Overview

Answer 19

description of the table

Question 20

ER Diagram

Answer 20

entity relationship diagram

shows a table and the other tables that it joins to

caboodle tables organized in ER diagrams by its tables

only fact table and datamar tables. have ER diagrams

Question 21

Reporting when you don’t know the table but know the subject

Answer 21

Go to galaxy and go to reporting with caboodle

Question 22

Reporting when you have slicerdicer query

Answer 22

look through the slicer dicer data models

then go to troubleshoot (top right on screen)

then find the root table definition in the query and will show the table name

Can also go to record viewer to investigate

Question 23

reporting when you don’t have much info

Answer 23

you can go to the data dictionary in anal cat and search by materfile and the code

Question 24

Change tracking

Answer 24

feature in caboodle where we sometimes hold on to old data
- want to avoid them when doing joins

2 types
1. Snapshot change tracking (type 2)
2. None (type 1)

Question 25

Snapshot change tracking

Answer 25

have extra rows to store old data, snapshots of entities in a time period
- can identify them in the data dictionary by the CHIP

Question 26

Non snapshot change tables

Answer 26

if source data gets updated, then the data shown in the table gets updated as well (onedrive file)

snapshot tracking- shows old rows with old data

all items in the column will change in each row, not just the most recently changed stuff

Question 27

snapshot column

Answer 27

retains old data in a snapshot table

Will have multiple rows and variable results in each row post change. Will not have all the same info like non snapshot would (Name change)

Question 28

when a change in snapshot table took place

Answer 28

does not show when new data was input, just the ETL date
- need to check the DATA LINEAGE to determine the chronicles item or clarity table and column that store this info

Question 29

Durable key

Answer 29

surrogate key in caboodle

identifies unique entities in a table
- ex. pts with the same name, would have unique ids to show theyre 2 diff people

only found in snapshot tables

Question 30

snapshot tables

Answer 30

have both snapshot and non snapshot columns
- if its a snapshot column, new data will keep the old rows
- if its a non snapshot column, no new row added, all the data in that column gets updated

Question 31

Referential Integrity

Answer 31

1. Lookup columns (key columns) will always have a value (isnotnull)
2. Lookup column values will always find a matching value in the destination table

Not enforced in filteredaccess schema

Question 32

unspecified values

Answer 32

-1; expected, but null in the source = unspecified

-1 contains nulls ONLY in KEY COLUMNS
*Unspecified in Strings
“NULL” = date, integer columns

Question 33

not applicable (NA) values

Answer 33

-2

ALSO ONLY IN KEY COLUMNS- IF COLUMN ENDS IN KEY

Question 34

Inferred row

Answer 34

Created to maintain 2nd part of referential integrity- WHEN THERE IS A MISMATCH
- that a column will always find a match

Does this by creating an INFERRED ROW when needed ( when one table has more up to date info than the other)
Add 3 special rows to every table

Question 35

Inferred rows are created when

Answer 35

a table with a LOOKUP COLUMN goes through ETL , not when the table itself goes through ETL
- some tables updated less often than nightly bc theyre not used as much

Question 36

Deleted Values referential integrity

Answer 36

Only thing that will remain is the PRIMARY key

will show as -3 for lookup columns

*deleted (star deleted) for strings

metadata column _isdeleted column = binary 1

date/ time = “NULL”

Question 37

Writing the query

Answer 37

1. First, outline the tables that you will need that has all the columns needed
   - can search data dictionary or anal cat to find the tables
   - this is your select clause- “Table.column”
   check to see and label which is a snapshot table vs non snapshot table
2. From _Table
3. Join Clause
   a. map out tables to find where you will join: “key to key”
   b. see what tables are snapshot and what are non snapshot
   - join durable key to durable key for snapshot tables
   - typically the key table is the center of the star schema to link to other dim tables
   c. Join all the above tables.columns in the select clause laid out
4. write in condition to iscurrent=1 to consider snapshot table data that may have duplicate older rows

b. could also do a where clause
“where
table.count = 1
- snapshot table that would have the rows = 1
count only exists in FACT tables, not dim tables

Question 38

Bridge table

Answer 38

used to model (in joins) many to many relationships
ex pt and dx

Question 39

Bridge table columns

Answer 39

2 columns

1. <name> Combokey- lookup column for the fact
   </name>
2. <name> key - lookup column for the dimension
   </name>

Question 40

Combo key

Answer 40

know youll need a bridge table when you see a combokey column

bridged to bridge tables

Question 41

Data Mart

Answer 41

collection of data on a topic
- makes it easier to write reports
- designed for a specific reporting need

Should save time to write a report

group of data/topics together

Question 42

SetDims

Answer 42

Tables with lists in them

almost like what we’re doing with groupers

Ex. Give me a list of all meds that put me to sleep
- tranqs

Ex. Give me a list of all outpatient departments