CDW110v QUESTIONS & ANSWERS
Where does Caboodle get its data from?
Clarity ETL, non-Epic data
What happens in Caboodle's staging database?
1. data is transformed 2. checked for integrity 3. cleaned to resolve issues
How many databases does Caboodle have?
2 - one for staging and one for reporting
What is the cost of Caboodle's easy reporting?
Expensive ETL
What does SSIS stand for?
SQL Server Integration Services
What are SSIS packages?
Mechanisms by which data is moved during the ETL process
Schema
Collection of database objects
3 Epic schemas
1. dbo schema
2. FullAccess schema
3. FilteredAccess schema
dbo schema
Data source for SlicerDicer
Where can you go to research the dbo schema?
Data Dictionary
,FullAccess Schema
-Should be the default schema when reporting
-Contains everything from dbo schema and more
FilteredAccess Schema
-Similar to FullAccess, but results are filtered based on user security
-Commonly used with CommunityConnect
How to tell what schema you're reporting out of
SELECT SCHEMA_NAME()
What uses Caboodle?
1. SlicerDicer
2. Certain Metrics
3. Certain Workbench templates
Purpose of a key
identify a row in a table
In Caboodle, all key columns (including primary keys and lookup columns) are...
Surrogate keys
Surrogate key
A system-assigned primary key that does not exist in the source database
When are surrogate keys created?
During the ETL process
How to tell which columns are surrogate keys or lookup columns?
End in -key
Purpose of surrogate keys
To key Epic and non-Epic data
Lookup column
, A column in one table whose value identifies at least one row in another table. Most
tables in Caboodle have at least one.
DMC
Data Model Component. A table in the Caboodle reporting database and its supporting
infrastructure.
What is the primary key in PatientDim?
PatientKey
PatientKey in PatientDim
Surrogate key with no Chronicles identifying information
What ID(s) to use for validation
EpicID or EpicCsn
Dim tables
Contain 1 row for each entity in the set (Example: OrderDim = 1 order, PatientDim = 1
patient)
Fact tables
Contain 1 row for each occurrence of some specific, measurable event
Which has more data and fewer lookup columns - fact or dim tables?
Dim tables
Bridge tables
Model many to many relationships (Ex: 1 patient with multiple diagnoses =
DiagnosisBridge
DataMarts
Stores data points from several DMCs (Ex: HospitalReadmissionDataMart for
admissions and readmissions)
What is the purpose of the EAV Model?
To store data where the attributes change frequently
Where does Caboodle get its data from?
Clarity ETL, non-Epic data
What happens in Caboodle's staging database?
1. data is transformed 2. checked for integrity 3. cleaned to resolve issues
How many databases does Caboodle have?
2 - one for staging and one for reporting
What is the cost of Caboodle's easy reporting?
Expensive ETL
What does SSIS stand for?
SQL Server Integration Services
What are SSIS packages?
Mechanisms by which data is moved during the ETL process
Schema
Collection of database objects
3 Epic schemas
1. dbo schema
2. FullAccess schema
3. FilteredAccess schema
dbo schema
Data source for SlicerDicer
Where can you go to research the dbo schema?
Data Dictionary
,FullAccess Schema
-Should be the default schema when reporting
-Contains everything from dbo schema and more
FilteredAccess Schema
-Similar to FullAccess, but results are filtered based on user security
-Commonly used with CommunityConnect
How to tell what schema you're reporting out of
SELECT SCHEMA_NAME()
What uses Caboodle?
1. SlicerDicer
2. Certain Metrics
3. Certain Workbench templates
Purpose of a key
identify a row in a table
In Caboodle, all key columns (including primary keys and lookup columns) are...
Surrogate keys
Surrogate key
A system-assigned primary key that does not exist in the source database
When are surrogate keys created?
During the ETL process
How to tell which columns are surrogate keys or lookup columns?
End in -key
Purpose of surrogate keys
To key Epic and non-Epic data
Lookup column
, A column in one table whose value identifies at least one row in another table. Most
tables in Caboodle have at least one.
DMC
Data Model Component. A table in the Caboodle reporting database and its supporting
infrastructure.
What is the primary key in PatientDim?
PatientKey
PatientKey in PatientDim
Surrogate key with no Chronicles identifying information
What ID(s) to use for validation
EpicID or EpicCsn
Dim tables
Contain 1 row for each entity in the set (Example: OrderDim = 1 order, PatientDim = 1
patient)
Fact tables
Contain 1 row for each occurrence of some specific, measurable event
Which has more data and fewer lookup columns - fact or dim tables?
Dim tables
Bridge tables
Model many to many relationships (Ex: 1 patient with multiple diagnoses =
DiagnosisBridge
DataMarts
Stores data points from several DMCs (Ex: HospitalReadmissionDataMart for
admissions and readmissions)
What is the purpose of the EAV Model?
To store data where the attributes change frequently
