Download CDW110 Exam-with 1CDW110 Exam-with 100% verified solutions 100% verified solutions and more Exams Nursing in PDF only on Docsity!
CDW110 Exam-with 100%
verified solutions
(General Reporting Tips) If a query refers to more thanone table, all columns should be prefixed by a descriptor (table name or alias) Using descriptors ensures you have unambiguous column references, preventing issues that can occur when two tables contain columns with the same name. Chapter 1. (Study Checklist) Caboodle Console The Caboodle Console is a web application housed on the Caboodle server. It includes the following: Dictionary Dictionary Editor Executions Work Queue Configuration Chapter 1. (Study Checklist) Data Warehouse In a data warehouse, multiple sources may load data pertaining to a single entity. This means that more than one package may populate a given row in a Caboodle table. As a result, there may be multiple business key values associated with a single entity in a Caboodle table. Chapter 1. (Study Checklist) ETL Extract, Transform, Load Chapter 1. (Study Checklist) SSIS Package The architecture of Caboodle includes a staging database and a reporting database. Data is extracted from source systems (like Clarity), transformed in the staging database, and presented for users in the reporting database. This movement of data is realized via a set of SQL Server Integration Services (SSIS) packages. Chapter 1. (Study Checklist) Data Lineage
Generally, data lineage refers to the process of identifying the source of a specific piece of information. In Caboodle, data lineage is defined at the package level. Chapter 1. (Study Checklist) Star Schema The standard schema for a dimensional data model. The name refers to the image of a fact table surrounded by many linked dimension tables, which loosely resembles a star. The Caboodle data model structure is based on a "star schema" - where one central fact table will join to many associated lookup or dimension tables. This structure provides the foundation of the Caboodle data model. Chapter 1. (Study Checklist) DMC DATA MODEL COMPONENT No table in Caboodle "stands alone." Each is considered part of a Data Model Component, which refers to the collection of metadata tables that support the ETL process and reporting views stored in the FullAccess schema. Each DMC gets a type. Strict table naming conventions are followed in Caboodle, so that a table's suffix provides information about its structure and purpose. These suffixes are: · Dim for dimensions (e.g. PatientDim) · Fact for facts (e.g. EncounterFact) · Bridge for bridges (e.g. DiagnosisBridge) · DataMart for data marts (e.g. HospitalReadmissionDataMart) · AttributeValueDim for EAV tables (e.g. PatientAttributeValueDim) · X for custom tables (e.g. CustomFactX) Chapter 1. (Study Checklist) Staging Database The Caboodle database into which records are loaded by SSIS packages and stored procedures. Chapter 1. (Study Checklist) Reporting Database The architecture of Caboodle includes a staging database and a reporting database. Data is extracted from source systems (like Clarity), transformed in the staging database, and presented for users in the reporting database. This movement of data is realized via a set of SQL Server Integration Services (SSIS) packages.
Chapter 1. (Study Checklist) Identify key characteristics of the dimensional data model. MADE for report writers. · Simpler and more intuitive. · Easily extensible. · More performant.. Chapter 1. (Study Checklist) Identify documentation resources for reporting out of Caboodle Caboodle Dictionary Reporting with Caboodle document Caboodle ER diagram Chapter 1. (Study Checklist) Identify reporting needs that best fit Caboodle Custom data packages can be written by Caboodle developers to accommodate your organization's reporting needs. (General Reporting Tips) Add a filter to most queries to exclude Caboodle's special rows for unspecified, not applicable, and deleted records, which have surrogate keys of - 1, - 2, and - 3 Include only rows where the key is greater than 0. (General Reporting Tips) Caboodle has a numbers table, NumbersDim, that you can use as needed in your reports NumbersDim contains the integers from 1 to 1,000,000, which you can reference to help manipulate strings and complete other processes. If you need more than 1,000,000 rows to accomplish a task, you can refer to NumbersDim multiple times in your query. Chapter 1. (Study Checklist) How does Epic data flow into Caboodle Epic data moves between several databases before it gets to Caboodle.
CHRONICLES flows into CLARITY via ETL. After transformation, the data is stored in a relational database on a separate server. Even though the structure of the Chronicles and Clarity databases differ significantly, the ETL process preserves the relationships mapped in Chronicles. CLARITY flows into Caboodle data is extracted from Clarity, transformed in the staging database, and presented for users in the reporting database. This movement of data is realized via a set of SQL Server Integration Services (SSIS) packages. Chapter 1. (Study Checklist) How does Non-Epic data flow into Caboodle The Caboodle developer designs custom DMCs (Data Model Components) and writes SSIS packages to bring additional data into the warehouse. This may be additional Epic data from Clarity or non-Epic data from 3rd party sources. CHAPTER 1. (Reviewing the Chapter) What are the differences between a normalized and dimensional data model? In a normalized data model, the focus is on not repeating data, which reduces the size of the database. In a dimensional data model, the focus is on ease of reporting and uses the star schema, which focuses on a central fact table pertaining to a reportable event and surrounding dimension tables providing context for the event. CONTEXT...It gives CONTEXT CHAPTER 1. (Reviewing the Chapter) Briefly define the roles of the Caboodle report writer, administrator, and developer. · The Caboodle report writer queries data that already exists in the database. They use their knowledge of the tools and the source database(s) to conduct research into the necessary data points required for a given report. · The Caboodle administrator uses the Caboodle Console to manage and monitor the ETL process. They troubleshoot ETL errors and handle configuration steps for the database.
Chapter 1. (After-Class Exercise) True or False: Caboodle is considered a normalized database. False. Clarity is a normalized database. Caboodle has been denormalized in order to simplify reporting. Chapter 1. (After-Class Exercise) Name three documentation resources for Caboodle Reporting. Caboodle Dictionary Reporting with Caboodle document Caboodle ER Diagram Chapter 1. (After-Class Exercise) When writing reports, why is it better to use your organization's Caboodle console rather than the console used in training? The Console used in training will not reflect your organization's custom development. For that reason, it is best to use your Caboodle Console rather than Epic's training tools for writing reports. Chapter 1. (After-Class Exercise) Use the Caboodle Dictionary to identify the Caboodle tables and columns that extract data from the following items. If you encounter multiple results, write down 2 of them. A. CVG 410 (termination date): CoverageDim.CoverageTerminationDate B. FSD 1230 (whether a flowsheet value reading is abnormal) FlowsheetValueFact.Abnormal c. HSB 89210 (anesthesia start time): AnesthesiaRecordFact.AnethesiaStartInstant D. LPL 1164 (present on admisiion diadnoses): DiagnosisEventFact.PresentOnAdmission, ProblemListFact.PresentOnAdmission Chapter 2. (Study Checklist) Fact Table The core table types in a dimensional data model are facts and dimensions. A row in a fact table corresponds to the occurrence of some significant, measurable event, such as the ordering of a medication or the posting of a charge transaction.
A fact table typically contains lookup columns to several dimension tables. This arrangement somewhat resembles a star and so is referred to as a "star schema Fact tables typically join to dimension tables, but may also join to other types of DMCs, such as other fact tables and bridge tables. All fact tables have a Count column, which stores 0 for the negative rows, 0 for any row that represents deleted data, and 1 otherwise. The names of fact tables end with "Fact." All Fact Tables in Caboodle use a surrogate key as their primary key. Chapter 2. (Study Checklist) Dimension Table The core table types in a dimensional data model are facts and dimensions. A row in a dimension table represents a concrete entity which provides context for an event, such as the patient for whom a medication was ordered. A fact table typically contains lookup columns to several dimension tables. This arrangement somewhat resembles a star and so is referred to as a "star schema." Fact tables typically join to dimension tables, but may also join to other types of DMCs, such as other fact tables and bridge tables. It is possible, but not common, for a dimension table to contain lookup columns to other DMCs. All Dimension Tables in Caboodle use a surrogate key as their primary key. Chapter 2. (Study Checklist) Surrogate Key A system-generated identifier in Caboodle. Not inherently meaningful to report consumers.
unmatched values. ForeignKeys in Caboodle end in Key; called lookup columns. Column in a table that joins to another table. Points to another table. Chapter 2. (Study Checklist) Where do you look up the data lineage of a table? Table-level data lineage can be found in the Sources section of the Caboodle Dictionary for a specific table Chapter 2. (Study Checklist) Where do you look up the data lineage of a column? Column-level data lineage for each package is found by expanding that section under the description of each column. The columns value may reflect the same value as the source, or maybe not and modified by SSIS Chapter 2. (Study Checklist) How would you identify Epic data in Caboodle? Typically entered in Hyperspace and stored in Chronicle. Data moves from Chronicles to Clarity to Caboodle, so Clarity is a source database for Caboodle. Chapter 2. (Study Checklist) How would you identify non-Epic data in Caboodle? Instead of seeing "Clarity" as the source data, non-Epic data sources will show the name of the non- Epic database For Epic data, columns end in EpicId or EpicCsn Chapter 2. (Study Checklist) The difference between the terms 'lookup column' and 'foreign key' All lookup columns in Caboodle ending the word KEY. Some lookup columns are foreign keys that they join to the primary key of the destination table.
CHAPTER 2. (Reviewing the Chapter) Which of the following would you expect to be stored in a dimension table? A. Users B. ED Visits C. Diagnosis definitions A. Users C. Diagnosis Definitions Users and diagnosis definitions would be in their own dimension tables. ED Visits are considered reportable, measurable events and would be in a fact table. CHAPTER 2. (Reviewing the Chapter) Which of the following are possible in Caboodle? For each of the answers you select, use the Caboodle Dictionary to find an example. A. Joining a Fact table to another Fact table B. Joining a Fact table to a Dimension table C. Joining a Dimension table to a Dimension table D. Joining two different Fact tables to the same Profile Dimension table (Examples may vary) a. EncounterFact can join to HospitalAdmissionFact b. MedicationOrderFact can join to PatientDim c. PatientDim can join to ProviderDim Chapter 2. (Reviewing the Chapter) Which of the following can be populated by more than one package? Select ALL that apply. A. A single table B. A single row in a table A & B
MedicationKey Stores a surrogate key value generated by Caboodle to identify one row in MedicationDim. MedicationEpicId stores the ID of a medication record in Epic, or the ERX.1. Chapter 2. (In-Class Exercises) True or False: A maximum of one foreign key can exist between two tables in Caboodle? False CHAPTER 3. (Reviewing the Chapter) TRUE or FALSE: Any change to patient data in Clarity will generate a new row in PatientDim after the next extract. False. Assuming the data is extracted to Caboodle, only changes to Type 2 data would generate a new row in PatientDim. CHAPTER 3. (Reviewing the Chapter) What is a durable key? Type 2 tables have a durable key column which stores the same value for all rows in that table that correspond to the same entity. This allows for grouping or summarizing data by the entity. CHAPTER 3. (Reviewing the Chapter) Consider the following query: SELECT DurableKey FROM Which of the following statements are true? A. This query will only run if is Type 2 B. This query will run regardless of the Change Type of C. The value in the DurableKey column could repeat in multiple rows of the results D. There is no table in Caboodle for which this query will run a. This query will only run if is Type 2 c. The value in the DurableKey column could repeat in multiple rows of the results
CHAPTER 3. (Study Checklist) Change Type There are two change types: Type 1 and Type 2. Caboodle tables and columns are given a change type, which determines whether or not that table or column tracks historical data in the source. How a table tracks data over time. Type 1 = Current Type 2 = Storing data over time CHAPTER 3. (Study Checklist) Type 1 Data Type 1 data reflects the current content of the source as of the last Caboodle extract. In other words, Type 1 tables and columns in Caboodle do not track historical data. When a Type 1 value is updated in the source, all instances of that value in Caboodle are updated the next time the data gets extracted. CHAPTER 3. (Study Checklist) Type 2 Data In some cases, Caboodle will keep track of precious values after those values are no longer stored in the original source. Caboodle retains historical values by creating multiple rows for each entity, with each row containing the values for a particular time period. For example, an individual patient may appear in several rows of PatientDim, each representing what Caboodle knew about the patient for a different time period. CHAPTER 3. (Study Checklist) StartDate Column The StartDate column contains the starting date for which the Type 2 data in the row became valid in Caboodle. For a row that holds the earliest version of the data recorded in Caboodle the StartDate is set to the value of EarliestAllowableDate in the configuration table Config.ConfigurationValues.
CHAPTER 3. (Study Checklist) Identify the change type of a column? In the Caboodle dictionary it lists if the column is type 2 CHAPTER 3. (Study Checklist) Identify whether a change in the source data will trigger an update to an existing value or create a new row If the data is type 1, all values overwrite old values. If the data is type 2, new data will be created CHAPTER 3. (Study Checklist) Use the durable key lookup column to aggregate data The durable key lookup columns can also be used to aggregate rows for the same entity without joining to the dimension at all. For example, to count the number of encounters for each patient in EncounterFact, apply a GROUP BY using PatientDurableKey. CHAPTER 3. (Study Checklist) Join to current data in a Type 2 table
- Join the durable key lookup column in the starting table to the durable key of the target table.
- Filter to the current row of the Type 2 target table. CHAPTER 3. (Study Checklist) How Caboodle tracks changes in the source data Foreign keys reference the row that was current at the time of extract CHAPTER 3. (Study Checklist) How the granularity of a Type 2 table differs from the granularity of a Type 1 table Type 2 column values track historical data, Type 1 column values reflect the current source data. Type 1 data is overwritten in Type 2 tables to reflect current source data. Only changes to Type 2 are tracked. CHAPTER 3. (Study Checklist) The difference between the primary key and the durable key of a table ...
CHAPTER 3. (Study Checklist) The difference between a surrogate key lookup column and a durable key lookup column ... CHAPTER 3. (Study Checklist) The difference between a durable key lookup column and a durable key column ... CHAPTER 4. (Reviewing the Chapter) 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. CHAPTER 4 - A foreign key column in Caboodle contains the value -3. What could this mean? There are two possibilities: Either 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 foreign key columns are also -3. To know which is true, check the primary key value for the given row: a non- negative value indicates that this row represents deleted data in the source. CHAPTER 4-If a foreign key is null in Clarity, what will appear in the corresponding column in Caboodle? If the foreign key was null in the source, then a -1 will appear in Caboodle to represent an unspecified value. If the foreign key's data lineage was not defined by the source package that loaded data for a particular row, then a -2 will appear in Caboodle to represent a value that is not applicable.
process creates a new row in the target table and assigns a new surrogate key value as its primary key. Such a row is called an inferred row. Inferred rows are created when one table has more up-to-date information than another. Two common scenarios: A. Unmatched references in the source B. Testing or development Every table in Caboodle has three "extra" rows. These rows provide a place for foreign keys to point. CHAPTER 4 - The - 1 Row AS PART OF REFERENTIAL INTEGRITY When a foreign key value is NULL in the source system, the foreign key is said to be UNSPECIFIED. (Unspecified Foreign Key) In this case, the corresponding foreign key in Caboodle is set to -1. Since many date columns in Caboodle are foreign keys to the DateDim table, NULL dates from Clarity are often represented with a -1 value DateKey columns in Caboodle. CHAPTER 4 - The - 2 Row AS PART OF REFERENTIAL INTEGRITY When a foreign key is not applicable (N/A) as defined by the SSIS package, Caboodle sets the value to - 2. This is handled by the ETL process.
CHAPTER 4 - The - 3 Row AS PART OF REFERENTIAL INTEGRITY If an account was deleted in the source, all foreign key values are set to -3. When a record is hard-deleted in the source system or no longer passes the filters required for extraction, Caboodle tracks the deletion in one of two ways. · For Type 1 DMCs, the existing row is updated with default values representing the deletion. The primary key remains unchanged. · For Type 2 DMCs, a new row is added that represents the deletion. Existing Type 2 data remains, while Type 1 data is overwritten with the default values. The primary key﴾s﴿ and durable key will remain unchanged. CHAPTER 5 - What two columns do all bridge tables have? Every bridge table has a ComboKey to which other tables join and a Foreign Key used to join to its associated dimension table. CHAPTER 5 - TRUE or FALSE: AttributeValueDim tables are more granular than their associated fact table. True The AttributeValueDim tables can store multiple attribute-value combinations on separate rows for the same entity, making them more granular than their associated fact table CHAPTER 5 - Bridge Table Bridge tables exist to capture many-to-many relationships, such as the one between patients and the diagnoses on their problem list.
