CLR110 FINAL PAPER QUESTIONS AND ANSWERS GRADED
A+
✔✔A clinical administrator requests a query to include data for a patients registries.
Shes 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.
✔✔How can you find out if it is? - ✔✔Query in SQL, look for C records in Clarity
Compass check to see if there are executions listed on the summary form.
Open SQL Management Studio
Write a query to show data from PAT_ENC
Include the primary key, the patient ID and the column that extracts EPT 30
✔✔What do you notice about the column that extracts EPT 30? How should this data be
displayed to a report customer? - ✔✔It is a category value, you should display the name
to your report customer.
Modify your query to display the name of the encounter type.
Hint: check the foreign key information sections of PAT_ENC in the Clarity Dictionary
✔✔What is the primary key of PAT_ENC - ✔✔PAT_ENC_CSN_ID
✔✔What column in Clarity extracts the patient name? - ✔✔PATIENT.PAT_NAME
✔✔What column in Clarity extracts the patient sex EPT130? - ✔✔PATIENT.SEX_C,
Modify your query to display the patient name and patient sex. You will need to join your
tables to do so
✔✔What is the primary key of the PATIENT table? - ✔✔PAT_ID
✔✔What Chronicles INI and item does it extract? - ✔✔1 EPT .1
✔✔Is there a column in PAT_ENC that extracts the same Chronicles item? -
✔✔PAT_ENC.PAT_ID
✔✔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 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? - ✔✔You need to use
CLARITY_DEP_CUST_FIELDS in a report. You found a table in Clarity Compass but
cannot query the table on your database.
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 can enable this for you.
✔✔Reviewing a colleagues report you see and unfamiliar table and want to find out
more about it. However you try looking it up but cant find it in the Clarity Dictionary. Why
might this happen and where should you look instead? - ✔✔The Clarity Dictionary from
the UserWeb contains only Epic released definitions. Any modifications made by your
database admin will not be reflected. To find all customizations access the Clarity
Compass in Hyperspace.
✔✔What Chronicles item characteristics allow multiple values to be stored for a single
record? - ✔✔Add type and Response Type:
All data in a single extracted table in Clarity typically come from the same Chronicles
MASTERFILE.
All data in a single extracted table in Clarity typically com from items with the same ADD
TYPE and RESPONSE TYPE.
Multiple response Chronicles items typically extract to SEPARATE Clarity tables unless
they are part of the same related group.
✔✔Define the Cardinality of a relationship - ✔✔It describes the numerical relationship
between entities in two tables
✔✔What is the primary key of ORDER_PROC? - ✔✔ORDER_PROC_ID
✔✔Based on the primary key what is the add type of the items extracted to
ORDER_PROC? - ✔✔NO ADD
✔✔Based on the primary key ORDER_PROC_ID what is the response type of the items
extracted to this table? - ✔✔Single
,✔✔What is the primary key of ORDER_RESULTS? - ✔✔ORDER_PROC_ID,
ORDER_DATE_REAL and LINE
✔✔Based on the Primary key what is the add type of the items extracted to
ORDER_RESULTS? - ✔✔Overtime
✔✔Based on the primary key what is the response type of the items extracted to this
table? ORDER_RESULTS - ✔✔Multiple Response
✔✔IF you were joining these tables together using the linking diagram what should the
join be? - ✔✔FROM ORDER_RESULTS
INNER JOIN ORDER_PROC
ON ORDER_RESULTS.ORDER_PROC_ID = ORDER_PROC.ORDER_PROC_ID
✔✔What is the granularity of the PATIENT table? - ✔✔One row per patient
✔✔What is the granularity of the result set returned by joining PATIENT to
PATIENT_DOCS? - ✔✔One row per document per patient
✔✔Describe the result set for the query by joining PATIENT to PATIENT_DOCS -
✔✔For each patient find the list of documents that the patient has provided.
✔✔Would using a left outer join change the result set of PATIENT to PATIENT_DOCS?
- ✔✔Yes it would include patients who have not turned in any documents
✔✔What is the granularity of the result set returned by joining PATIENT_DOCS to
PATIENT? - ✔✔One row per document provided per patient
✔✔Would using a left outer join change the result set of PATIENT_DOCS to PATIENT?
- ✔✔No because PATIENT_DOCS extracts EPT data every row has a match in the
PATIENT table
✔✔What is the result set when HSP_ATND_PROV and PAT_ENC_HSP are joined on
their PAT_ID columns - ✔✔For every attending provider the query would return each of
the hospital encounters the patient had even if they were not attending provider for that
encounter.
✔✔What foreign key column should be used to join from HSP_ATND_PROV to
PAT_ENC_HSP? - ✔✔PAT_ENC_CSN_ID
✔✔What is the primary key of PAT_ADDRESS? - ✔✔PAT_ID and LINE
✔✔What is the primary key of OTHER_COMMUNCTN? - ✔✔PAT_ID and LINE
, ✔✔What is the result when PAT_ADDRESS and OTHER_COMMUNCTN are joined on
their PAT_ID Columns? - ✔✔One row per combination of a line in a patients address
and phone number for a patient.
✔✔What is the result when PAT_ADDRESS and OTHER_COMMUNCTN are joined on
their PAT_ID and LINE columns? - ✔✔The first line of the patients address is matched
to the first listed phone number, the second line of the address is matched to the
second listed phone number etc. IF there are more lines in one table than in another
those will be filtered out by an inner join.
If a report required both patient addresses and patient phone number how would you
handle this data?
Pivot the data, such that each phone number was listed in a separate column. Limit the
results to only the first 2 lines of the address and the first phone number. Clarify the
report request to determine whether all lines were needed or only the first few lines.
✔✔If an items add type is no add and the response type is single what element will be
present in the primary key of the table that extracts the item? - ✔✔Record ID
✔✔What value is stored in a networked item? - ✔✔The .1 of a Chronicles record or the
CSN of a contact on a Chronicles record.
✔✔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 column search to help 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 find the
table that stores provider names. Use the foreign key of the starting table to join the
primary key of the destination table.
✔✔True or False: Would it be possible to have more than one copy of the same table in
one report? Why or why not? - ✔✔True 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 present in the primary key
of the table that extracts the item? - ✔✔LINE
✔✔Why is it good practice to conduct research in Chronicles? - ✔✔Report requestors
may only be able to provide a workflow or field in Hyperspace.
In Chronicles you can open the record viewer and validate the data in the source and
explore links between data points.
The most reliable way to find the Clarity column that reflects the data point is to use the
information stored in Clarity Compass. Understanding how data is structured in
Chronicles will allow you to better understand the structure of Clarity.
A+
✔✔A clinical administrator requests a query to include data for a patients registries.
Shes 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.
✔✔How can you find out if it is? - ✔✔Query in SQL, look for C records in Clarity
Compass check to see if there are executions listed on the summary form.
Open SQL Management Studio
Write a query to show data from PAT_ENC
Include the primary key, the patient ID and the column that extracts EPT 30
✔✔What do you notice about the column that extracts EPT 30? How should this data be
displayed to a report customer? - ✔✔It is a category value, you should display the name
to your report customer.
Modify your query to display the name of the encounter type.
Hint: check the foreign key information sections of PAT_ENC in the Clarity Dictionary
✔✔What is the primary key of PAT_ENC - ✔✔PAT_ENC_CSN_ID
✔✔What column in Clarity extracts the patient name? - ✔✔PATIENT.PAT_NAME
✔✔What column in Clarity extracts the patient sex EPT130? - ✔✔PATIENT.SEX_C,
Modify your query to display the patient name and patient sex. You will need to join your
tables to do so
✔✔What is the primary key of the PATIENT table? - ✔✔PAT_ID
✔✔What Chronicles INI and item does it extract? - ✔✔1 EPT .1
✔✔Is there a column in PAT_ENC that extracts the same Chronicles item? -
✔✔PAT_ENC.PAT_ID
✔✔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 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? - ✔✔You need to use
CLARITY_DEP_CUST_FIELDS in a report. You found a table in Clarity Compass but
cannot query the table on your database.
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 can enable this for you.
✔✔Reviewing a colleagues report you see and unfamiliar table and want to find out
more about it. However you try looking it up but cant find it in the Clarity Dictionary. Why
might this happen and where should you look instead? - ✔✔The Clarity Dictionary from
the UserWeb contains only Epic released definitions. Any modifications made by your
database admin will not be reflected. To find all customizations access the Clarity
Compass in Hyperspace.
✔✔What Chronicles item characteristics allow multiple values to be stored for a single
record? - ✔✔Add type and Response Type:
All data in a single extracted table in Clarity typically come from the same Chronicles
MASTERFILE.
All data in a single extracted table in Clarity typically com from items with the same ADD
TYPE and RESPONSE TYPE.
Multiple response Chronicles items typically extract to SEPARATE Clarity tables unless
they are part of the same related group.
✔✔Define the Cardinality of a relationship - ✔✔It describes the numerical relationship
between entities in two tables
✔✔What is the primary key of ORDER_PROC? - ✔✔ORDER_PROC_ID
✔✔Based on the primary key what is the add type of the items extracted to
ORDER_PROC? - ✔✔NO ADD
✔✔Based on the primary key ORDER_PROC_ID what is the response type of the items
extracted to this table? - ✔✔Single
,✔✔What is the primary key of ORDER_RESULTS? - ✔✔ORDER_PROC_ID,
ORDER_DATE_REAL and LINE
✔✔Based on the Primary key what is the add type of the items extracted to
ORDER_RESULTS? - ✔✔Overtime
✔✔Based on the primary key what is the response type of the items extracted to this
table? ORDER_RESULTS - ✔✔Multiple Response
✔✔IF you were joining these tables together using the linking diagram what should the
join be? - ✔✔FROM ORDER_RESULTS
INNER JOIN ORDER_PROC
ON ORDER_RESULTS.ORDER_PROC_ID = ORDER_PROC.ORDER_PROC_ID
✔✔What is the granularity of the PATIENT table? - ✔✔One row per patient
✔✔What is the granularity of the result set returned by joining PATIENT to
PATIENT_DOCS? - ✔✔One row per document per patient
✔✔Describe the result set for the query by joining PATIENT to PATIENT_DOCS -
✔✔For each patient find the list of documents that the patient has provided.
✔✔Would using a left outer join change the result set of PATIENT to PATIENT_DOCS?
- ✔✔Yes it would include patients who have not turned in any documents
✔✔What is the granularity of the result set returned by joining PATIENT_DOCS to
PATIENT? - ✔✔One row per document provided per patient
✔✔Would using a left outer join change the result set of PATIENT_DOCS to PATIENT?
- ✔✔No because PATIENT_DOCS extracts EPT data every row has a match in the
PATIENT table
✔✔What is the result set when HSP_ATND_PROV and PAT_ENC_HSP are joined on
their PAT_ID columns - ✔✔For every attending provider the query would return each of
the hospital encounters the patient had even if they were not attending provider for that
encounter.
✔✔What foreign key column should be used to join from HSP_ATND_PROV to
PAT_ENC_HSP? - ✔✔PAT_ENC_CSN_ID
✔✔What is the primary key of PAT_ADDRESS? - ✔✔PAT_ID and LINE
✔✔What is the primary key of OTHER_COMMUNCTN? - ✔✔PAT_ID and LINE
, ✔✔What is the result when PAT_ADDRESS and OTHER_COMMUNCTN are joined on
their PAT_ID Columns? - ✔✔One row per combination of a line in a patients address
and phone number for a patient.
✔✔What is the result when PAT_ADDRESS and OTHER_COMMUNCTN are joined on
their PAT_ID and LINE columns? - ✔✔The first line of the patients address is matched
to the first listed phone number, the second line of the address is matched to the
second listed phone number etc. IF there are more lines in one table than in another
those will be filtered out by an inner join.
If a report required both patient addresses and patient phone number how would you
handle this data?
Pivot the data, such that each phone number was listed in a separate column. Limit the
results to only the first 2 lines of the address and the first phone number. Clarify the
report request to determine whether all lines were needed or only the first few lines.
✔✔If an items add type is no add and the response type is single what element will be
present in the primary key of the table that extracts the item? - ✔✔Record ID
✔✔What value is stored in a networked item? - ✔✔The .1 of a Chronicles record or the
CSN of a contact on a Chronicles record.
✔✔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 column search to help 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 find the
table that stores provider names. Use the foreign key of the starting table to join the
primary key of the destination table.
✔✔True or False: Would it be possible to have more than one copy of the same table in
one report? Why or why not? - ✔✔True 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 present in the primary key
of the table that extracts the item? - ✔✔LINE
✔✔Why is it good practice to conduct research in Chronicles? - ✔✔Report requestors
may only be able to provide a workflow or field in Hyperspace.
In Chronicles you can open the record viewer and validate the data in the source and
explore links between data points.
The most reliable way to find the Clarity column that reflects the data point is to use the
information stored in Clarity Compass. Understanding how data is structured in
Chronicles will allow you to better understand the structure of Clarity.
