CLR 110 Comprehensive Exam V2 | Clarity Data Model Fundamentals | 2026/2027 Academic Year – Questions with Correct Answers - WGU

CLR 110 Comprehensive Exam V2 | Clarity Data Model Fundamentals | 2026/2027 Academic Year – Questions with Correct Answers - WGU 2026 / 2027 Academic Year Q: Compared to views, derived tables generally take less time to run because the data has been stored on the DB Answer True, views are saved queries, their use can affect performance. Derived tables are stored procedures and thus perform better Q: After receiving a report request, you consult Clarity resources such as Clarity Compass and the Clarity Dictionary without consulting Chroncile resources such as Item Editor and record viewer. What problems may you run into? Answer Analytics catalog Database object search Column search Clarity Dictionary on the Q: A lab manager tells you he wants a report on lab specimens, and shows you the field where he can input various information. What method would you use to find the INI + Item numbers of the data he's looking for? Answer Ctrl + Click Q: I IDM 130 is a networked item that is not filled in for this record. What value will be extracted to Clarity for this item, and other Chronicles items that are not filled out? Answer Null Q: What value will be extracted to Caboodle for this item, and other networked items that are not filled out in Chronicles? Answer if the column is a key column, Caboodle will have a ‐1 letting report writers know that this value was not populated in the source, while still enforcing referential integrity. Q: T/F columns that are non primary keys contain null values Answer True, even foreign key may be null. Q: Why do null values exist in Clarity Answer Not all fields are populated in Chronicles ETL data cleansing deletes incorrectly formatted data Data is deprecated y no longer extracted !Appendix E: Record Viewer Tip Sheet contains more information on the filtering options available in the record viewer Q: Item Editor Answer Shows the definition of chronicle items. Shows information about how the item behaves for all values Q: Hot Keys Answer Opening Chart Search - Ctrl + Space Q: What is a category list? Answer a list of defined values that can be stored in a Chronicles item Q: Benefits and limitations of using category as a filter Answer 1. more efficient 2. cannot only see first 100 values in item editor - must use category list maintenance to see complete list Q: add types Answer no add response each time lookback Q: what is an add type Answer describes how long a piece of data is valid Q: no add Answer considered true for all cases. if changed then old value is replaced example: SSN, birthday Q: response each time Answer data stored separate on each contact record if blank then it is considered unknown example: weight, blood pressure Q: lookback Answer store each item separately but if blank considers last value to be true Example: age, address Q: No‐Add Answer add type stored on record, not contact. considered true for all contacts !Since both respond each time and lookback items store data on the contact, these add types are referred to as overtime add types. No‐Add data is stored at the record level Overtime data is stored at the contact level. Q: Response Each Time Answer Response type stored separately on each of the records contacts if blank, should be considered unknown Q: Lookback Answer determines how many lines an item can hold at one time - Single Response Answer add type stored separately on each contact assume that last value entered remains true Q: Response Type - Multiple Response - Related Group !Since both multiple response and related group items store more than one type of data, these response types may collectively be referred to as multiple response types. Single data stores one line of data, while Multiple data stores multiple lines of data. Q: A clinical administrator requests a query to include data for a patient's registries. She's not sure of the INI and Item number, but she knows patient is on the Lung Cancer Screening registry. What tool would you use to find the INI and item number given this information? Answer record viewer Q: In Clarity, which of the following columns COULD contain a NULL value? A. A foreign key column that would store the patient's primary care provider B. A date column that would store the discharge date for an encounter C. A string column that stores a patient's email address D. A numeric column that stores the patient's age in years E. A column that is the primary key of a table Answer All but E. In Clarity the only column that CANNOT be NULL is a column in the primary key of a table. Q: Item Information Window Answer window that populates after control click Q: Item Editor Answer shows information on how the item behaves for all values useful for investigating properties on a given item type item editor in chart search records Answer Information about individual entities within a master file For example, a record in the patient master single patient. contacts Answer information about points in time for a record. Tools for Chronicles research ▪ Item Information window (ctrl + click ▪ Record Viewer ▪ Item Editor ▪ Category List Maintenance A Chronicles item can store one or more options from a limited list. Which of the following could account for this behavior? ﴾Choose all that apply﴿ A. Single Response B. Multiple Response C. Networked D. Overtime E. No‐Add F. Category B, C and F. If a Chronicles item can store one or more options it is a multiple response item. If the options come from a limited list, that item may be a category item, where the category list is the list of options, or networked, where the records or contacts in the destination master file is the list of options A Chronicles item stores a single value, but can store a separate value for each contact on a record. Which of the following could account for this behavior? ﴾Choose all that apply﴿ A. Single Response B. Multiple Response C. Networked D. Overtime E. No‐Add F. Category A and D. If a Chronicles item can store only one value it is a single response item. If a separate value is stored on each contact the add type is Overtime. Clarity Compass ○ definitions for how Chronicles items will translate into Clarity ○ define the structure of your Clarity tables (ETL) view the mapping. Clarity Tools - OAC ○ Clarity Compass ○ Analytics Catalog ○ Online: Clarity Dictionary & Reporting Repository Conducting Clarity Research 1. Find the INI + Item number 2. Use the INI + Item number to find the Clarity table & column 3. Join together using foreign key True or False: The Clarity Console stores definitions for the database objects that can be extracted to and stored in the Clarity database. False. The Clarity Compass stores these definitions. Which tool would you use to find the table and column that extracts EPT 18864: Attending Provider? Column Search, to enter the Clarity Compass What happens when a column is marked as Deprecated? Data is no longer extracted to the column In which of the following scenarios would it be appropriate, and even necessary, to reach out to your Clarity ETL administrator? A. You need to use CLARITY_DEP_CUST_FIELDS in a report. You found the table in the Clarity Compass, but cannot query the table on your database. B. You found a view that looks useful in your reports. You query the view, get data returned, but you want to know how this view fits in with your reports. A: CLARITY_DEP_CUST_FIELDS likely has not been extracted to the Clarity database. Your ETL administrator can enable this for you. Reviewing a colleague's report, you see an unfamiliar table and want to find out more about it. However, you try looking it up, but can't find it in the Clarity Dictionary. Why might this happen, and where should you look instead? To find all customizations, access the Clarity Compass within Hyperspace. Column Search way to access Clarity Compass search by Chronicles INI and Item number or a Clarity column name opens the columns tab in Clarity Compass Database Object Search shows the Clarity Compass information that is relevant to the entire table. Search by table name, or the name of another db object opens the Summary tab in Clarity Compass Report Repository reference info for Epic-released y customer-written reports Clarity Dictionary located on user web contains table/column names table/column descriptions index info primary/foreign keys category values for Epic released category list _C Column ○ naming convention for columns that extract category values ○ each row stores a number value that correlates to a name in the prefixed ZC_ table Category List Table ﴾ZC_Table﴿ Contains the name of the associated category value ETL Process Extract, Transform, and Load ○ Chronicles access using M programming language ○ extract & transform takes place simultaneously ○ Clarity console runs KB_SQL queries over Chronicles data Deprecation data is no longer extracted to a column Clarity Console ○ Controls ETL ○ Schedules the extract of defined tables ○ Displays information on previous extracts Why some items in the Chronicles Info portion of the Clarity Compass show single, multiple, or no items. ○ not all data extracted to clarity ○ clarity will expunge data not meeting etl definition The structure and purpose of category tables ○ categories extracted as values in _c table ○ associated labels found in ZC_ ○ no enforced naming conventions The role of the Clarity ETL administrator Load Frequencies Identifying Changes between Versions Enabling extract to Clarity How the ETL process can impact the data in Clarity Strings become varchars numbers become numerics categories may be extracted as Integory or Varchar Dates, times and instances become datetimes Why will you see empty grids in Column Search The column extracts data not stored in a Chronicles item The column extracts calculated data Does not mean column will extract no data How are date times stored Dates are extracted as midnight on the date Times are extracted as the time on 1/1/1900 Database Object Search useful columns Overview Section - load frequency, master file, db type Organization Secific table notes Groupted Tables ZC_Category List extraction format [Category List Name]_C Name Title Abbr Internal ID !Be sure to look up category list tables in the Columns section of the dictionary and not rely on the naming convention. There is no enforced naming convention in Clarity like you see in Caboodle. !The Foreign Key section of the Clarity dictionary on the UserWeb does NOT include a full list of every possible join between tables in Clarity. It should primarily be used to identify category list tables. Tools used in the Clarity ETL Process Clarity Compass Clarity Console Clarity Compass' role in ETL process ~ contains a list of all possible db object that can be extracted ~ defines what prod data will extracted ~ defines the structure of Clarity tables including primary keys Clarity Console role in ETL process schedules the extract from Chronicles into Clarity displays info about previously run extracts used to monitor the ETL process Load Frequiences how often data is extracted from Chronicles Incremental - occurs nightly Weekly Monthly Weekly Full execution takes place on Sunday night to minimize performance issues Since different tables can have different frequencies, some of your data in Clarity may be more up‐to‐date than other data. Make sure you know the extract frequencies of the tables that you use. It is important to understand how the data might be affected when tables in a single report extract data at different frequencies left outer join may be needed to make sure the report returns the necessary data How to you determine if a table in clarity compass is in the Clarity db Query it in SQL, look for C records in the Clarity Compass, check to see there are executions listed on the Summary form CL: keyword Filter Cogito Dictionary for instances of keyword in clarity CB: keyword filter cogito dictionary for instances of keyword in caboodle E Records record for every db object in Clarity db in E0B - contains table info found in clarity compass during extraction create copy of E renaming it c c records extracted by console check custom field in Clarity Compass to check if c record created in db Why the clarity data model ○ Simplified process for translating data ○ Fewer errors ○ Predictable data mapping ○ Easier to Calculate relationships between tables .Why doesn't one Chronicles master file translate into one Clarity table? designed to ○ Minimize redundant data ○ Maintain consistent granularity What Chronicles item characteristics allow multiple values to be stored for a single record? (Table granularity) Add type and response type via table's primary key Key Item Characteristics Add type ○ indicates if data stored on record o contact ○ categorized as no-add o overtime (Res ech tm or lckbk) ○ no-add stored on record. always considered current ○ overtime stored on contact Response type ○ indicates how many lines of data item can hold ○ Categorized as single or multiple (multiple response or related group) ○ Single ○ Multiple: What are Chronicles record identifiers ○ Internal identifier: stored in the .1 item called record id ○ External identifier: stored in other items y not necessarily unique What are Chronicles contact identifiers ○ Contact Serial Number (CSN) - unique to all contacts in MF ○ Contact Date Real (CDR) date in Epic's internal format followed by dos decimal places ○ decimals start at .00 ○ increment for each additional contact on the misma fecha How do you identify unique contacts ○ Use only CSN o ○ Usa record id and CDR Fundamental concepts about contacts in MF ○ meaning of a contact differs between MF. ○ MF contain contacts that change meaning within the MF, ○ some master files only ever use one contact per record. Data Model Chart All data in a single extracted table in Clarity typically come from the same Chronicles MASTER FILE All data in a single extracted table in Clarity typically come from items with the same ______________ and ____________ add type response type Multiple response Chronicles items typically extract to _______________ Clarity tables, unless they are part of the same related group SEPARATE Define the cardinality of a relationship It describes the numerical relationship between tables. ‣ 1 to 1 ‣ 1 to many ‣ many to 1 !A key question to ask when writing a report is "what is the meaning of one row in my results?" When constructing your query, take note of how cardinality of a join or presence of a filter may modify your report's granularity !better to write reports from the most granular level and to move back up. Ways to find INI that network item points to ‣ Item information window - ctrl + click - click more to view ‣ Record Viewer -hover item or hover over value to show network db ‣ Item Editor - network section, target ini !Most networked items point to records. However, some networked items point directly to a contact within a record. ‣ Identify whether a networked item links to a record or a contact by looking at the Network section within the Item Editor. ‣ If the first field within that section reads Record pointer? then the link is to a record. ‣ If instead, the first field reads Contact pointer? then the link is to a contact. I ORD 226: Patient CSN is an example of a networked item that points to a contact. Category list maintenance Allows users to see a complete list of category values. Item editor only displays the first 100 values KB_SQL knowledge based sql translates data stored in M script to relational tables and columns of clarity Chronicle contact identifiers Contact Serial Number (CSN) Contact Date Real, (CDR) types of master files Static Dynamic Primary key for Overtime add type, Single response CSN or Record_ID, CDR Primary key for Single Response Type, No Add response Record_ID Primary key for Overtime add type, Multiple/Related response Record-ID, Line Primary key for Overtime Response Type, Mulitple/Related response CSN, Line OR Record_id, CDR, Line Joining to no-add multiple table to no add single table (record) Joining to overtime single table to no add single table (record-CSN) Joining overtime single table to no add single table (record-cdr) joining overtime multiple table to no add single table (record-csn) joining overtime multiple table to overtime single table (contact- csn) joining overtime multiple table to overtime single table (contact- cdr) FInd Neetwork INI Item information window (control + click) Record Viewer Item Editor ER Diagram visual tool that shows relationships between db objects. Contain: Common tables & links to a reporting area Relationship cardinality of frequent joins How do you find ER diagrams search in Galaxy for "application ER Diagram htm" ER Diagrams will not document all possible joins between Clarity tables. More research is needed to understand the contents of each table and column and its relevance within a report. Where do you see the Clarity radar sql metrics query Analytics Catalog Search Name Reporting Template Add Collection Method = SQL Metric ‐ Automatic Data Collection form list source Parser form shows logic How do you find Clarity/Caboodle load packages Analytics Catalog Search for caboodle table Go to queries tab How to you find Clarity/WB templates that use Cogito SQL Search Engine Analytics Catalog Search Name Reporting Template Add search engine = SQL Open Query form template Open SQL query form Views a stored query that puls data from different tables typically begin with V_", but not enforced Derived Tables also called materialized views populated from queries that run against data already contained in the databases stored procedures called during the Clarity ETL process can take the place of several tables in one report Since views in Clarity can be complex. Any logic it contains will execute alongside your code, which can affect performance. If query performance is an issue, try querying tables directly or copying a portion of the view script for use within your own reports. Epic SQL Functions User Defined Functions ﴾UDFs﴿ developed and released by Epic Epic SQL Functions are unique to Clarity and will be found in the Clarity Compass but they are not found in the Clarity Dictionary or in the Analytics Catalog. You will need to use the Database Object Search to research Epic SQL Functions. Additional considerations of views and derived tables Not constrained by Primary Key understand underlying data structure to expand your report and join to additional db objects Views in Clarity typically begin with V_ Derived tables follow the dimensional data model: fact tables begin with F_ and dimension tables begin with D Caboodle dependencies Entity A person, place, thing, event, or concept about which information is kept. A db object in the Clarity Data Model. Relationship An association between two entities. A foreign key in the Clarity Data Model. Parent An entity that contributes a foreign key to another entity. The "one" side of a one‐to‐many relationship. Child An entity that inherits a foreign key from another entity The "many" side of a one‐to‐many relationship Cardinality numeric relationship between rows in the parent table and rows in the child table. Existence Also called optionality whether or not the child entity must have a parent entity two kinds of existence mandatory optional Mandatory Existence Not possible for the foreign key in the parent entity to have a null value in the child entity Cardinality is always "one to X" 1 on the parent side example: encounter without a patient Optional Existence Possibility for the foreign key in the parent entity to have a null value in the child entity Cardinality is always "zero or one to X" drawn with '0' on the parent side Type How the parent entity's primary key is used in the child entity. Three types of relationships: identifying non‐identifying non‐specific Identifying Relationship (ERD) Primary key of parent is contained in the primary key of child entity Used to define relationship Child may not equal (have more columns in its primary key Indicated by solid lines Non‐Identifying Relationship (ERD) Primary key of the parent entity is not contained in the primary key of the child entity Indicated by dashed line Non‐Specific Relationship Many‐to‐many relationships Rare in the Clarity Data Model Resolved by the introduction of an intermediate table. Mandatory Cardinality Optional Cardinality What are "dependent database objects", listed in the Clarity Compass? How can you use this information? list of database objects upon which this object depends. help determine what extracted tables and columns a view or derived table can be used in place of. V_PAT_FACT is based on the PATIENT table. What logic has been applied to PATIENT in this view? How can you find this information? V_PAT_FACT uses Case statements in to calculate patient age. The logic can be viewed from the Scripts tab of the Clarity Compass for V_PAT_FACT. Cogito SQL Reporting Workbench Templates Radar SQL Metrics ﴾IDN records﴿, that query query a SQL database. the metric is querying a SQL database you are able to see the query that is run to determine those values Why the structure of a view or derived table does not necessarily follow the Clarity data model built to satisfy In Caboodle, what type of columns track changes to the data? A snapshot table will have one or more snapshot columns which track changes If you read the column description for PatientDim.City you will notice that this column will be changed in the future to no longer have change tracking. When this change is completed this column will only store the current values for the city a patient lives in. At that time you can use AddressHistoryFact to find historical address information PAT_ADDR_CHNG_HX will be storing the audit trail for changes made in Epic. The dates here should not be considered the date that the patient moved. When Caboodle's Change Tracking Can Be Used There is no other audit trail available A snapshot column exists for the data in question The column has been regularly extracted during the time period covered by the report It is not important to have the full change history of the data It is sufficient to say values at the end of day Values changed multiple times in 1 day will only store the final value on any particular day. The StartDate and EndDate columns in Caboodle won't always represent when data for the row was accurate. They are specifically the ETL timestamps, whose purpose is to show what the contents of a column looked like in the past. However, data may not have been updated exactly when its real‐world counterparts changed, so investigation is still required. AUDIT_ITM tables begin with AUDIT_ITM. designed to audit any item in Chronicles for which auditing is turned on the audit trails themselves are not stored in Chronicles items stored separately on the operational server in globals once it is purged the data would only exist in Clarity. ACCESS_LOG and ACCESS_WRKF When you have a report request that sounds something like "I need to see what a specific user is doing in the system" or "I need to see who accessed certain patient's data" you may need to use Epic's generic event auditing structure When you have a report request that sounds something like "I need to see what a specific user is doing in the system" or "I need to see who accessed certain patient's data" you may need to use Epic's generic event auditing structure A manager needs to know who has made changes to patient demographics in the last six months. Will Caboodle likely be able to meet this report request? No. snapshot change tracking does not track who made a change. How can data from two related groups in Chronicles be joined together in Clarity? One related group can contain an item that stores the line number of a separate related group. True or False: Chronicles uses one structure for all audit trails. False. Although audit trails are often stored in related groups, the structures of audit trails vary AUDIT_ITM tables Record ID Change_time Item User_ID Columns ending in _value (Old new & new values) What tool You have an INI and item number, and would like to see the Clarity table and column it extracts to. Clarity Compass, Column Search, Clarity Dictionary, or Analytics Catalog Hyperspace activity, or UserWeb Data Handbook What tool: You would like to find a specific category value for a given Chronicles item. Category List Maintenance, Item Editor, or Clarity Dictionary (Epic released values only) Hyperspace activity, or UserWeb Data Handbook What tool: You received a query request with the requester providing instructions for how to access the field in Hyperspace that stores the data they need. Item Information Window (Ctrl + Click) What tool: You know a patient that has a birth date of 8/13/2011, and want to know the item # of the item that stores birth date. Record Viewer What tool: You find an unfamiliar table on a colleague's report and would like to learn more about it. Clarity Compass: Database Object Search, or Clarity Dictionary Hyperspace activity, or UserWeb Data Handbook What tool: You want to find existing Epic reports available for a specific reporting need. Report Repository or the Analytics Catalog UserWeb Data Handbook or Hyperspace activity Big Picture If you find a table in the Clarity Compass, will it necessarily be on your Clarity database? No, The Clarity Compass will show all of the tables that are possible but you might not have every table extracted How to you find which tables have been extracted to Clarity Query it in SQL, look for C records in the Clarity Compass, check to see there are executions listed on the Summary form Which of the following tools can you use to learn more about clarity tables and columns report repository on the web analytics catalog clarity compass database object search clarity compass column search clarity dictionary on the userweb Multiple clarity tables might extract data from a single record in Chronicles true - Example EPT can have data extracted to Patient, Pat_ENC, HSP_ATND_PROV and OTHER_COMMUNICATION How often are incremental tables typically extracted to Clarity Daily - Incremental Tables are typicall extracted to Clarity every night during ETL A join needed in your query may not be a documented for foreign key of the Clarity Dictionary You open a patient encounter in the record viewer and find item EPT 10115 as pictured. What will be stored for this patient encounter in the clarity column that extracts this item? Column Search Which of the following statements accurately describes contact date real Is represents the number of dates since 12/21/1840 It uniquely identifies a contact within a master file after receiving a report request you consult clarity compass and the clarity dictionary without consulting Chronicles resources such as Item Editor and the Record Viewer. What problems may occur People requesting reports may only tell you where data goes in Hyperspace, without knowing where data is stored in Clarity Epic SQL functions must be used in place of Oracle or Microsoft SQL function sin order to return from Clarity False What is the best way to determine whether a given db object is a derived table derived tables follow the dimensional data model fact tables begin with F_ and dimension tables begin with D_ Which of the following tools can be used to learn more about clarity tables and columns Report Repository Analytics Catalog Clarity Compass Database Object Search Clarity Compass Column Search Clarity Dictionary on the UserWeb Which of the following can be found in clarity compass Deprecated columns A list of Epic-released crystal reports that use a clarity table The version when the table was created All foreign keys between clarity tables Deprecated columns The version when the table was created Report Repository contains a What accurately describes Contact Date real? It represents the number of days since 12/31/1840 Different records can have contacts with the same Contact Date Real Is used as a contact identified with the record ID. It is the number of days since 12/31/1840 followed by a two digit decimal that identifies multiple contacts on the same day ACCESS_LOG_METRIC event logging framework built on Event Definition ﴾E1M﴿ records and METRIC_ID To find out what action a user performed, link ACCESS_LOG or ACCESS_WRKF.METRIC_ID to ACCESS_LOG_METRIC.METRIC_ID. ACCESS_LOG_METRIC columns metric_id - E1M .1 metric_name - E1M .2 metric_desc - E1M 60 access_log_type_c - E1M 102 !If this column is populated, then the event definition is tracked using Access History and populates the ACCESS_LOG table. metric_wrkf_typ_c - E1M 106 !If this column is populated then the event definition is tracked using Workflow Activity Logging and populates the ACCESS_WRKF table. event_action_type_c - E1M95 !If both the ACCESS_LOG_TYPE_C and METRIC_WRKF_TYP_C columns are populated, then the event definition is tracked using both types of event logging, so one event populates both ACCESS_LOG and ACCESS_WRKF. Keep this in mind, particularly if you are creating a report that counts rows in each of these tables. ACCESS_LOG contains only events with an Access History log type ACCESS_WRKF contains only events with a Workflow Activity log type ACCESS_LOG and ACCESS_WRKF The table structures are the same, but the tables focus on different events. What is the difference between ACCESS_LOG and ACCESS_WRKF's access_time and ACCESS_INSTANT Access_time - The date and time when the workflow activity event happened. After completing the primary key conversion detailed in SLG 2591452, use this column to filter any query on ACCESS_WRKF. Access_instant - The UTC instant when this event was logged into the system; stored as an internal format There are two columns that contain information about when an event occurred: ACCESS_TIME and ACCESS_INSTANT. ACCESS_TIME is in human readable format ACCESS_INSTANT is an internal representation of a time. The two columns may not always contain the same exact time. ACCESS_TIME is the time at which the user accessed the data: when the event took place. ACCESS_INSTANT is the time at which the event was logged to the event logging data structure. For example, if a home health nurse saw a patient at 8:00 am, but doesn't sync her laptop until 10:00 pm, ACCESS_TIME, the time the event took place, would be 8:00 am ACCESS_INSTANT, the time the event was logged to the event log, would be 10:00 pm. general strategy for audit reporting The general strategy for audit reporting is to use items in Chronicles for audit reporting if they are available. The item auditing tables should be used as a last resort. There are occasions when reporting on current data is not sufficient, so for reporting purposes Epic _______________ audits changes made to many items. Knowing the _______and the ________ of the information you want to report on will make finding the Clarity tables and columns easier. INI ; item number A no-add single response item in Chronicles will only store the single ______________ for an item within a record. current value Some audit trails are stored outside of Chronicles items. These audit trails are often large. As a result, once enough data has accumulated in the audit trail, it may be purged from the operational server, after which it can only be found in _______________ Clarity. A manager needs to know who has made changes to patient demographics in the last six months. Will Caboodle likely be able to meet this report request? No. snapshot change tracking does not track who made a change. How can data from two related groups in Chronicles be joined together in Clarity? One related group can contain an item that stores the line number of a separate related group. True or False: Chronicles uses one structure for all audit trails. False. Although audit trails are often stored in related groups, the structures of audit trails vary.