Practice Questions Flashcards
True or False: One record in Chronicles may have several Clarity tables extracting data from it
True
How often does the incremental tables get extract to Clarity
Daily; Nightly
You are in the Record Viewer opened to a patient encounter and see item EPT 10115 as shown below. For this patient encounter what would be stored in the Clarity column that extracts this item Discharged [3]
3
You want to add item SER 39: Primary Department to a report. Which of the following is the quickest way to find what Clarity table and column retrieves this item?
Data Dictionary
You are looking at a colleague’s report and you notice a table you have never used before named CLARITY_ROM. Where in Hyperspace do you go to open the Data Dictionary for this table?
Analytics Catalog
Item HNO 1024: Notes Linked to Document has an Add type of No-Add and a Response type of Multiple. What is the primary key to the table this item is extracted to?
NOTE_ID and LINE
After being asked to write a report, you lookup resources in Clarity, without looking up resources in Chronicles, such as Item Editor and the Record Viewer. What are some problems you could have?
Those asking for reports can only tell you where data goes in Hyperspace, and won’t know where the data is stored in Clarity.
A join that is necessary in your query may not be a documented foreign key of the Clarity Dictionary.
List values for an attribute of a custom type are not documented in the Clarity Dictionary.
You cannot query attributes of the source object.
True or False: Derived tables compare to views in general run faster since the data has been stored on the database.
True
True or False Epic SQL Functions must be used in place of Oracle or Microsoft SQL server functions in order to return data from Clarity
False, Epic SQL Functions are not required. You may use Oracle or Microsoft SQL Server functions
Which of the following is the best way to determine whether a given database object is a derived table?
Look for a chip in the overview section of the data dictionary.
Which of the following can be found when viewing a clarity table in the data dictionary
A. A list of Epic- Released Crystal Reports that use the Clrity Table
B. Whether a column is deprecated
C. The version when the table was created
D. The add-type of the Chronicles items extracted to the table
B. Whether a column is deprecated
C. The version when the table was created
Which of the following statements is a correct description of Contact Date Real?
A. Only patient (EPT) records can have a Contact Date Real
B. Different records can have contacts with the same Contact Date Real
C. it represents the internal Epic date, followed by a two digit decimal
D. It is randomly generated
E. It uniquely identifies a contact within a master file
B. Different records can have contacts with the same Contact Date Real
C. it represents the internal Epic date, followed by a two digit decimal
What will be extracted to Clarity for Chronicles items that are not filled out (blanks)?
NULL
What is a Category List?
Category list is a system, or Epic list, it is not editable. When using a filter use the value itself rather than the name.
A defined value that be stored in a chronicles item
Investigate list using item editor
Investigate list using Category List Maintenance
A Chronicles item can store one or more options form a limited list. Which of the following could account for this behavior?
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 values, 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 one value, but it can store a different value for each contact on a record. Which of the following could explain this?
Single Response, Overtime
A lab manager tells you he needs a report about lab specimens and brings you to the field where he can fill in various information. What would you do to determine the INI and item numbers of the data he is requesting?
CTRL & Click
A clinical administrator requests a query to include data for a patient’s registries. She is not sure of the INI and item number but she knows the patient is on the Lung Cancer Screening registry. What tool would you use to find the INI and item number given this
information
Record Viewer
If you find a table in Clarity Compass will it necessarily be in your Clarity database?
Not always the case. The Clarity Compass will show all of the tables that are possible but at your organization you might not have every table extracted.
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 of the following tools would you utilize in order to determine the table and column that extracts EPT 18864 Attending Provider?
Enter “CL: EPT 18864” into the Analytics Catalog
What happens to a column that is marked Deprecated?
No more data will be extracted into that 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 Data Dictionary but cannot query the table on your database.
B. You include the column that extracts I EPT 60, Patient City, in your query but it only returns a number.
C. CLARITY_ROM exists on your database, but it stores no rows.
D. A patient exists in the PATIENT table whose BIRTH_WRIST_BAND value isn’t documented.**
A: CLARITY_DEP_CUST_FIELDS likely has not been extracted to the Clarity database. Your ETL administrator can enable this for you.
Define the Cardinality of a relationship
It describes the numerical relationship between entities in two tables
If the add type of an item is no add and the response type is single what element will be in the primary key of the table that extracts the item?
Record ID
What is stored in a networked item?
The.1 of a Chronicles record or the CSN of a contact on a Chronicles contact
You have a patient query and would like to display the name of the patients’ providers. Item EPT 18867: Admitting Provider is a networked item that points to the SER master file. How can you use the Data Dictionary to help display the name of the admitting provider?
Search the Data Dictionary for I EPT 18867 to find the foreign key column in the starting table. You can also search on I SER .2 to find the table that stores provider names. Use the foreign key of the starting table to join to the primary key of the destination table.
Would it be possible to have more than one copy of the same table in one report? Why or why not?
Yes, because Chronicles master files can reference each other multiple times, there are multiple ways to link Clarity tables together.
If an items response type is multiple what element will be found in the primary key of the table extracting the item?
LINE
If an item’s add type is overtime, what element will be present in the primary key of the table that extracts the item?
CSN or RECORD_ID and Contact Date Real
True or False: The SQL column title and SQL description fields in the item information window do not relate to Clarity or Caboodle columns
True: These fields relate to the KB_SQL columns utilized in the Clarity ETL process
When you are filling a field in Chronicles that looks up a record you can use the = key to bring in the last record used.
In this case if you set the INI to EPT and insert = into the record it will automatically bring in your patient.
Since both response each time and lookback items store data on the contact these add types may collectively be referred to as over time add types
True
A Category list table is often referred to as
a ZC Table
true or false: Epic SQL Functions are unique to Clarity and they are not found in the Analytics Catalog. You will need to use the Database Object Search to research Epic SQL Functions.
True
is the time when the user accesses the data, the time when the event actually took place.
ACCESS_TIME
Example: If a home health nurse saw a patient at 8am but doesnt sync her laptop until 10pm ACCESS_TIME the time the event took place would be 8am and ACCESS_INSTANT the time the event was logged to the event log would be 10pm.
is the time when the event was logged to the event log
ACCESS_INSTANT
Example: If a home health nurse saw a patient at 8am but doesnt sync her laptop until 10pm ACCESS_TIME the time the event took place would be 8am and ACCESS_INSTANT the time the event was logged to the event log would be 10pm.
If both the ACCESS_LOG_TYPE_C and METRIC_WRKF_TYP_C columns are populated then the even definition is tracked using both types of event logging so one event populates both ACCESS_LOG and ACCESS_WRKF
Keep this in mind if you are creating a report that counts rows in each of these tables.
The Clarity data base is largely determined by the structure of -
Chronicles
Upon receiving a report request what four things do you need
- Identify the data elements you will need for the report
- Find the INI and Item numbers of the data points to use on the report
- Use the INI and item numbers to find the appropriate Clarity tables and columns
- Join the Clarity tables together using the correct foreign key information
The INI and item number is the Chronicles address for a piece of data.
It is with this Chronicles address that you can map the data point to the appropriate table and column in Clarity.
Activities to look up Chronicles data information:
Item Information window CTRL Click
Record Viewer
Item Editor
Category List Maintenance
Chronicles Research
This window indicates the INI and item number where the data is stored along with other information about the item as a whole.
Click more for item details such as add type, response type and networked INI if the item is networked.
Ctrl Click Item Information Window
Once you know the INI, Record ID and Contact for a piece of data you can use __________ to find the INI and item number
Record Viewer
is an activity in Hyperspace that provides a read only view of
raw data in Chronicles record. Shows all items on a particular contact of a particular record.
Record Viewer In order to view data you must specify the Masterfile, Record and Contact for the data you want to see.
Record Viewer is the most convenient place to find _______ for a piece of data -
INI and Item number
Items that are part of a related group are displayed next to each other in a table.
Category items display both the value and name of the category they hold.
Networked Items display both the ID and the name of the record they network to.
Record Viewer Information
Null Values in Clarity
Clarity columns that are NOT primary keys can be null.
Null Values in Clarity exist for several reasons
Not all fields are populated in Chronicles
ETL data cleansing deletes incorrectly formatted data
A column may no longer extract data because it is deprecated.
shows the definitions of a Chronicles item. It doesnt show a particular value stored in the item it shows information about how the item behaves for ALL values.
Item Editor
Category List Maintenance
When using a category list value as a filter the query can run more efficiently when filtering on the category value itself rather than the name in the category list table. Gives full detail regarding category list
To view a category list
Category values can be shown in the item editor in the Category section. only the first 100 values in the list.
To see the complete list, access Category List Maintenance in Hyperspace.
Item Editor : Category List
Item editor is only able to display small subset of potential choices.
Release Range
Indicates which category values are released by EPIC and cannot be modified
Describes how long a piece of data is valid. Determines whether
an items value is stored on each contact or directly to the record
Add Type
Stored directly on a record. Value should be considered as true
for all contacts on the record. If item does get changed the value is replaced. Example: Patient Date of Birth
No - Add
stores item values separately on each contact but assumes the
last value is true until a new value is entered. Example: Patients age, only updates if there is a change from the last time the patient
was seen.
Lookback
Overtime Add types
response each time and lookback items
Stores the value separately on each of the record contacts.
Example: Blood pressure collected on each visit. If not collected value will return blank
response each time
Three steps: Investigate Clarity
- Find the INI and item number of the data point to use on the report.
- Use the INI and item number to find the appropriate Clarity table and column.
- Combine the Clarity tables using the appropriate foreign key
Clarity Tools
Clarity Console, Clarity Compass
Utilized to set definitions of how Chronicles items will
translate into Clarity. Utilized by ETL admin to define structure of Clarity tables. Useful to BI developers to view mapping. Provides organization specific information about the Clarity Database.
Clarity Compass
data types used by Clarity
Strings are VARCHARS
Numbers are NUMERIC
Categories are INTEGER or VARCHAR
Dates, times and instants are DATETIME
Dates are pulled in as midnight on the date.
Times are pulled in as the time on 01/1/1900
When you see an empty grid -
The column extracts data not stored in Chronicles item or the column extracts calculated data.
Search by table name or the name of another database object.
Database Object Search
What tab in Data Dictionary shows information about Clarity Columns?
Summary Tab
What four attributes are most useful for BID developers in the Overview Section?
Type of Database Object
Load Frequency
Master File
Grouped Table
Type of table: Extracted, Derived Table, View and Function
Type of Database Object
Refers to how often data gets loaded into a specific Clarity table. The most common load frequencies are INCREMENTAL, WEEKLY_ FULL and MONTHLY
Load Frequency (Database Object Search)
Grouped Tables (Database Object Search)
Grouped tables are extension tables created when an original table has enough columns to warrant splitting it into different tables.
Grouped tables have the same primary key and generally have the same table name with a suffix of _2 _3
Description (Database Object Search)
An Epic released description of the table
Notes (Database Object Search)
The tables notes are organization specific documented at your organization by the ETL Admin or another user with the proper security
Release Version (Database Object Search)
The version of Epic software with which the table was first released. Birth date of the table
Primary Key (Database Object Search)
The primary Key section will enumerate the column(s) that would be utilized in order to identify a row uniquely in a table. Also gives out the level of granularity a table has
Columns (Database Object Search)
The Columns section lists the columns present in the table along with some of its properties. Each column name links to that
column’s information, which is inside the column tab of the table workspace.
Indexes (Database Object Search)
The indexes section outlines the columns indexed in the table. Using these as filters in query can help query performance and speed of the query. Keeping the inventory of indexes documented is a responsibility of your Clarity Admin
Category List Tables
If a table is returned with a column C in a query the results will be numbers. Numbers are returned because C columns stores the category value from a category list. To translate values into names join to a table with prefix ZC A category LIST table it often referred to as a ZC table. The Category list table for an item extracts the entire category list and follows a set format ZC[Category List Name]
Online Resources
Clarity Dictionary or Report Repository
On the Userweb within Data Handbook, is the single source
of reference information about the Clarity data model. Presents information about released Clarity tables with out requiring the BI developers to have access to Hyperspace. Contents:
Tables and Column Names’
Table and Column descriptions
Index Information
Primary and Foreign keys
Category Values for Epic released category lists
Search by typing a keyword, Clarity Table name or column
Clarity Dictionary
within the data handbook, contains reference information for Epic released and customer written reports. It is a storage location for reports written using a variety of reporting tools. As a BI Developer the Report repository is a useful resource to investigate Clarity tables used in a similar report request.
Report Repository
ETL Tools
Clarity Compass and Clarity Console
Defines what Chronicles production data will be extracted
into which Clarity tables and columns. Defines the structure of your Clarity tables including primary keys.
Clarity Compass
Schedules the extract of defined tables from Chronicles to
Clarity. Displays information about previously run extracts. Primarily a tool for Clarity ETL admin and Clarity Database admin
Clarity Console
Since different tables can have different frequencies some of your data in Clarity may be more up to date than other data. If you are joining two tables with different frequencies a LEFT OUTER JOIN may be needed to make sure the report returns necessary data.
Different Load Frequencies
You can account for an instance where not all of the required data populates in clarity by adding in a case in your SQL to pull “*Unknown name [Room ID]
Different Clarity Table with different frequencies
Reasons for Deprecation
Most commonly due to change in underlying Chronicles data
Column may no longer be in use or may not behave as desired so a different item will be configured to replace it.
Issue may reside in Clarity column itself perhaps the data is not extracted in a standard format.
Other times a column may be deprecated in order to guide report writers to a certain reporting structure that will better serve the end user’s understanding.
will flag obsolete columns
Deprecated
True or False: Clarity Compass stores a list of all possible database objects that can be extracted to the Clarity Database.
True
If you find a table in Clarity Compass it will be in the Clarity database
False not necessarily, your organization may not have every table extracted.
How can you find out if a table is extracted?
look for a chip in the overview section of the data dictionary
tables for each application in Epic that contains the majority of reportable data points populated by workflows for that application
Core Tables
You have an INI and item number and would like to see the Clarity table and column it extracts to
Data Dictionary
You would like to find a specific category value for a given Chronicles item
In Hyperspace use Category List Maintenance or Item Editor or Clarity dictionary on the Userweb
For a given reporting topic you would like to see the most commonly used tables as well as the connections between them
UserWeb: Galaxy Search: ER Diagrams
You are given a query request where the requester gives instruction for how to access the field in Hyperspace that stores the data they need.
Go to the Hyperspace location: Ctrl Click for Item Information
You know a patient that has a birthdate of 8/13/2011 and want to know the INI and item number of the item that stores the birth date.
Hyperspace: Record Viewer
You find an unfamiliar table on a colleagues report and would like to learn more about it
Database Object Search
You want to find existing Epic reports available for a specific reporting need
UserWeb : Data Handbook: Report Repository
Why doesn’t one Chronicles Master file translate to one Clarity Table?
Avoid duplicate data and Use consistent granularity
What kind of Chronicles item property types allows multiple values can be kept per record
Add type and Response type
Indicates for how long a piece of data is valid.
Add Type
valid for entire record
No-Add
valid for a limited period of time
Overtime
Indicates how many lines of data an item can hold.
Response Type
holds only one line of data
Single
holds multiple lines of data
Multiple
Unique with the RECORD
Also need the record ID to uniquely identify the contact
Exists in every contact
Contact Date Real (CDR)
R Record ID (.1) : Unique within the Master File
Records
Unique within a masterfile.
Only exists in certain master files.
Contact Serial Number (CSN)
Unique within the item
Lines
**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 and Response Type
Multiple response Chronicles items typically extract to _clarity tables unless they are part of the same related group.
Separate
Numerical relationship between tables
Cardinality of a relationship
If an item has an add type of Look Back and a response type of Single what would be the primary key of the table that stores this data?
CSN or Record ID and CDR (Contact Date Real)
Queries stored in the database
Do not store results
Usually begin with V_
Views
Populated by stored procedures (Stored SQL statement run during ETL)
These are materialized views
Usually begin with F_ or D_
Derived Tables
Are there any visual indicators in the ER Diagram that distinguish views from other database objects, such as extracted tables?
Yes Views appear with a brownish/orangish background whereas extracted tables appear with a white background. This draws your attention toward the views.
Built to meet specific reporting needs.
Can simplify your report
Views and Derived tables
Which occupies more room on your Clarity server a Derived Table or a View?
Derived tables
When CANT an audit report be done using Caboodles type 2 data?
the Type 2 column is too new
ETL disruptions at critical points
Someone wants to know origin of the change:
When CAN an audit report be done using Caboodles type 2 data?
A type 2 column exists
Dont need full change history
Dont need to know much about the change
An end of day value is sufficient
True or False: Every single item in Chronicles is audited the exact same way
False: There is no one way that information is audited
Starting a join with the most granular table will maintain a consistent granularity with each subsequent join
Linking between tables
in Chronicles define relationships between an item in one master file and a record or contact
Networked Items
Each networked item will contain
the ID of a Chronicles record or contact.
You have a patient query and you want to show the name of the patients providers. Item EPT 18867 Admitting Provider is a networked item that points to the SER master file.
How would you use Column Search help to display the name of the admitting provider
Use Column Search on EPT 18867 to find the foreign key column in the starting table. You can also use Column Search on SER.2 to locate the table storing provider names. Use the foreign key of the starting table to join to the primary key of the destination table
If the response type of an item is multiple what element will be present in the primary key of the table that extracts the item?
LINE
What is the most common database object in Clarity
Extracted tables are the most common database object in Clarity.
simplifies the Clarity ETL process allowing data to move efficiently from Chronicles to Clarity.
The Clarity data model
Subset of data designed to meet the needs of specific reporting
area.
Data Mart
True or False: Views and Derived tables are considered SQL based data marts which use information already stored in Clarity database.
True
Section of the Summary tab. Lists Clarity tables and columns and even views and derived tables that comprise the underlying structure of a given database object.
Dependent Database Objects
True or False: Clarity Console checks to see if all dependent database objects have completed their data loads before executing the scripts that will populate derived tables.
True
A person, place or concept about which information is kept
Entity
An association between two entities
Relationship
An entity that contributes a foreign key to another entity. The one
side of a one to many relationship
Parent
An entity that inherits a foreign key from another entity. The many side of a one to many relationship
Child
The numeric relationship between rows in the parent table and
rows in the child table. This usually denoted in the “M to N” statement. For example one to one or zero to one to one or more
Cardinality
refers to whether or not the child entity must have a parent entity. There are two kinds of existence mandatory and optional. Also known as optionality.
Existence
Ifs not possible for the foreign key in the parent entity
to have a null value in the child entity. For example the relationship between PAT_ENC.PAT_ID and PATIENT.PAT_ID is mandatory because there cannot be an encounter for a patient without having the patient. The cardinality of such a relationship is always one to X so such relationships are drawn with a 1 on the parent side.
Mandatory Existence
It is possible for the foreign key in the parent entity to
have a null value in the child entity. For example because an encounter may not be closed when the Clarity extract process occurs the relationship between PAT_ENC.ENC_CLOSED_USER_ID and CLARITY_EMP.USER_ID is optional. The cardinality of such a relationship is always zero to one to X such relationships are drawn
with a 0 on the parent side.
Optional Existence
The type of a relationship is determined by how the parent entity primary key is used in the child entity. There are three types of relationships: identifying and non-identifying and non-specific.
Type
The primary key of the parent entity is contained in
the primary key of the child entity and is used to define the relationship between them. Note all the primary keys of the parent and child entity do not have to be equal the child
can potentially have more columns in its primary key Such relationships are always mandatory and are drawn with solid lines.
Identifying Relationship
The parent entity’s primary key is not included
in the child entity’s primary key. Such relationships are drawn as dashed lines.
Non Identifying Relationship
Many to many relationships and are rare in the Clarity Data Model. In almost all cases where such a relationship could arise. it has been resolved by the introduction of an intermediate table.
Non Specific Relationship -
How is age calculated for an inpatient admission?
It can be calculated in either years or days at the time of an encounter. You can tell this by the Scripts tab in a column or by reading the column information.
What are dependent database objects listed in Data Dictionary How would you use this information?
List of database objects this object depends upon. This list can help to determine what the 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 the 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 queries tab of the data dictionary for V_PAT_FACT
How does Clarity join data from two related groups in Chronicles?
One related group can have an item to store the line number of a different related group.
True or False Chronicles uses one structure for all audit trails
False Although audit trails are most often stored in related groups the structures of audit trails do vary.
only contains events with an Access History log type
ACCESS_LOG
contains only events with a Workflow Activity log type.
ACCESS_WRKF
the date and time when the workflow activity even
happened. Use this column to filter any query on ACCESS_WRKF
ACCESS_TIME
the unique ID of the M process for Hyperspace connection to Chronicles
PROCESS_ID
The unique ID of the metric that specifies what action has been
taken by the user.
METRIC_ID
Unique ID of the user who was logged in when this access history
even occurred. This column is frequently used to link to the CLARITY_EMP table
USER_ID
The identifier of the workstation on which this access
history even occurred. Links to CLARITY_LWS.WORKSTN_IDENTIFIER
WORKSTATION_ID
is the time at which the user accessed the data when the
even took place. human readable format
ACCESS_TIME
the time at which the even was logged to the even logging data structure is an internal representation of time
ACCESS_INSTANT
Additional pieces of information associated with an
event.
Event Mnemonics
Once a row is generated in the table the information
contained in it will not change.
Append only tables
You find an unfamiliar table on a collegues report and would like to learn more about it -
Database Object Search or Data Dictionary
You want to find existing Epic reports available for specific reporting need -
Report Repository
Where Found: UserWeb > Data Handbook
CTRL Click - ANSWER Why used
Retrieve INI and item # from a field
Where: Hyperspace field
Database: Chronicles
Record Viewer - ANSWER Why used:
View raw data in Chronicles
Where: Hyperspace activity
Database: chronicles
Item Editor - ANSWER Why used
lookup item characteristics
Where: Hyperspace activity
Database: Chronicles
Category List Maintenance - ANSWER Why used
To lookup category list values
Where Hyperspace activity
Database Chronicles
Database Object Search - ANSWER Why used:
to get more information about a database object (table view, Epic function) such as the primary key.
Where Hyperspace activity
Database Clarity
Report Repository - ANSWER Why used:
To find table used in similar existing report
Where: UserWeb > Data Dictionary : Report Repository
Database: Chronicles, Clarity and Caboodle
Why is it a good practice to start research in Chronicles
Customers requesting reports are often poorly educated about database instead they might give you just example data point for what they have put into Hyperspace.
ER Diagrams Foreign Key sections may not be useful because they don’t have complete set of possible Clarity tables and joins.
With Chronicles, you can look at the data at its source and therefore help validate the data and investigate the links between the different data points.
What is a common Clarity naming convention for columns that extract from a networked items?
The column name ends in _ID
True or false: every master file contains CSNs
False, only some
What level is a CDR unique on?
Record level. So we also need Record ID to uniquely identify the contact
What does the Item Editor show?
Definitions and properties of chronicles item. Doesn’t show the value
Category list maintenance would reveal how many categories?
All values and not just first 100
How do data types change when ETL transform data from Chronicles to Clarity?
Strings become VARCHAR, numbers become NUMERIC, categories become INTEGER or VARCHAR, dates times and instants become DATETIME
What does Database Object Search provide?
Table Summary Tab shows info relevant to the entire table
True or false: the foreign key section of the clarity dictionary includes a full list of every possible join between tables in Clarity
False. Should be primarily used to identify category list tables
What join would you utilize to for two tables that have a different load frequencies?
Left outer
True or false: columns and tables can be deleted in clarity
false
Where to see changes of database version?
Compass Comparison or Data Model Change Analyzer
True or false: one chronicles master file translates into one clarity table
False. They have different granularity
What are the two possible identifiers for contacts in Epic?
Contact serial number (CSN) or contact date real (CDR) combined with record ID
What are the two master file categories
Static and dynamic
Which column becomes part of the primary key in multiple response items?
LINE
What is the primary key of single, no add item? -
Record ID
What is the primary key of single, overtime item?
CSN or Record ID + CDR
What is the primary key of multiple, no add item?
Record ID + LINE
What is the primary key of multiple, overtime item?
CSN or Record ID + CDR + LINE
True or False: All data in a single extracted table in Clarity are typically from the same chronicles master file
True
True or False: All data in a single extracted table in clarity come from items with the same add type and response type
True
True or false: multiple response chronicles items typically extract to separate clarity tables
True, unless they are part of the same related group
How do you connect master files to each other?
Networked items
True or false: a particular networked item will always reference the same master file
True
If you joined to the same table more than once in a query, what would you have to do?
Alias the table in each join
Where would one find the ability to investigate views?
Queries section
Where would you go to find the underlying data for a view or derived table?
Dependent Database Objects section of the summary tab. Or Dependencies section
True or false: views and derived tables are SQL-based data marts
True
True or False views and derived tables can store or retrieve data at any desired granularity
True
Which of the Reporting Workbench templates examines other Reporting Workbench templates?
Report Template Audit -> Template Editor
What is the unique number assigned to a contact in a master file?
CSN - Contact Serial Number
items store data directly on a record, not a specific contact
item should be considered true for all contacts on that record.
No-Add
items store values separately on each of a record’s contacts.
if item is blank for a certain contact, it should be considered unknown.
Response Each Time
store values separately on each contact but assume that the last value entered remains true until a new value is entered.
Lookback
How can you specifically search the Analytics Catalog for only Clarity database objects?
In the Content Type field of the Advanced Search options, select CLARITY (CL); alternatively, include Clarity: or CL: in your search
No-Add and Single
Record_ID
No-Add & Multiple/Related
Record_ID, LINE
Overtime & Single
CSN or Record_ID and CDR
Overtime & Multiple/Related
CSN,LINE OR Record_ID, CDR, & LINE
Which part of the join does the Chronicles networked item map to?
The lookup column of the source table.
True or False: All metric definitions (IDN records) will reference the Clarity database.
False. IDN records may reference many possible data sources including Clarity or Caboodle.
Results of a Cogito SQL Reporting Workbench report that is searching on patient IDs could return:
A. Any PAF that is displaying Chronicles information
B. Any PAF that is displaying a column from the select clause of the SQL query
C. Any PAF that is displaying a column from the source database (Clarity or Caboodle)
A. Any PAF that is displaying Chronicles information
B. Any PAF that is displaying a column from the select clause of the SQL query
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
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.
What is in the Column Section of the Data Dictionary?
Name, Data Type, Category Lookup, Chronicles Info, Description
True or False: Multiple Clarity tables might extract from a single record in Chronicles
True
