Non-Extracted Tables and Documentation

Question 1

Look up the following database objects in the Clarity Compass and fill in the type:

Database Object Type

Clarity_TDL ———————–
OB_HISTORY ———————–
MU_EP_PROV_SUMMARY ———————–

Answer 1

Clarity_TDL –> View
OB_HISTORY –> View
MU_EP_PROV_SUMMARY –> Derived Table

Question 2

Look up the following database objects in the Clarity Compass and fill in the type:

HH_OASIS_INFO
HH_PAT_ENC
PAT_ADDENDUM_INFO
PAT_ENC
SMRTDTA_ELEM_DATA
SMRTDTA_ELEM_VALUE

Answer 2

All Extracted Tables

Question 3

What are “dependent database objects”, listed in the Clarity Compass? How can you use this information?

Answer 3

The list of database objects upon which this object depends.

This list can help determine what extracted tables and columns a view or derived table can be used in place of.

Question 4

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?

Answer 4

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

Question 5

T/F All views in Clarity start begin with ‘V_’

Answer 5

False. Naming conversions are not strictly enforced in Clarity

-May 2022, 5.7

Question 6

How do views and derived tables alleviate creating a tedious query?

Answer 6

By combining data from multiple tables in one place.

-May 2022, 5.4

Question 7

What is a tool that visually shows relationships between database objects relevant to a particular reporting area?

Answer 7

ER Diagrams (Entity Relationship Diagrams)

-May 2022, 5.10

Question 8

What are two things that an ER diagram contains?

Answer 8

• Tables and their links for a particular reporting area
• Relationship cardinality of frequent joins

-May 2022, 5.10

Question 9

Where do you search for Clarity ER diagrams?

Answer 9

In Galaxy:• Search “ ER Diagram htm”• Download ZIP file• Extract• Open “index.htm”(Aug. 2021, 5.5)

Question 10

Do Clarity ER Diagrams document all possible joins between Clarity tables?

Answer 10

No, only some of the joins that are possible between Clarity tables.(Aug. 2021, 5.6)

Question 11

List three types of database objects?

Answer 11

1. View2. Derived Table (Materialized View)3. Extracted Table(Aug. 2021, 5.7)

Question 12

How are Views identified in an ER Diagram?

Answer 12

By a brownish/orangish color.(Aug. 2021, 5.7)

Question 13

How are database object types identified in the Clarity Compass?

Answer 13

By chips in the Overview section of the Summary tab.(Aug. 2021, 5.7)

Question 14

What is a View?

Answer 14

A stored query.

-May 2022, 5.6

• Queries that are stored in the database
• Accessed as if it were a table
• Do not actually hold data. This means that using a view will not necessarily increase the efficiency of a query. However, views can reduce the amount of work that business intelligence developers must do by reducing the complexity of a query.(RPT101i SQL I)

Question 15

What type of database object pulls in data from several different tables in Clarity?

Answer 15

A View

-May 2022, 5.6

Question 16

What are Derived tables also known as?

Answer 16

Materialized Views

-May 2022, 5.7

Question 17

How are Derived tables populated?

Answer 17

Populated from queries that run against data already contained in the database and can take the place of several tables in one report.(Aug. 2021, 5.12)• Materialized views are tables populated from queries that run against other tables.• Materialized views are often populated by stored procedures.• Using materialized views can reduce the amount of work that business intelligence developers must do: one materialized view might take the place of several tables.• Materialized views can increase query efficiency; they are like pre-compiled and saved query results.(RPT101i SQL I)

Question 18

In Clarity, what data model does a derived table follow?

Answer 18

The dimensional data model.• Fact tables begin with F_• Dimension tables begin with D_Fun Fact: Clarity has relatively few derived tables and primarily follows a normalized data model.(Aug. 2021, 5.12)

Question 19

What database objects typically begin with V_ ?

Answer 19

Views. However, this naming convention is not strictly enforced.(Aug. 2021, 5.12)

Question 20

What does a Queries section tell you about a table?

Answer 20

That the table is either a View or a Derived table.Only Views and Derived tables have a Queries section. (Aug. 2021, 5.13)

Question 21

Where can you research Epic SQL Functions?

Answer 21

In the Database Object Search of the Clarity Compass. (Aug. 2021, 5.13)

Question 22

True or False: Views and derived tables are considered SQL-based data marts.

Answer 22

True.(Aug. 2021, 5.14)

Question 23

Why would a Clarity table be replaced with a view that performs the same task?

Answer 23

Occasionally, when a data model change in Clarity is significant enough, a Clarity table may be replaced with a view that performs the same task as the original Clarity table but with significant improvements to its underlying structure.(Aug. 2021, 5.15)