Non-Extracted Tables and Documentation Flashcards
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 ———————–
Clarity_TDL –> View
OB_HISTORY –> View
MU_EP_PROV_SUMMARY –> Derived Table
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
All Extracted Tables
What are “dependent database objects”, listed in the Clarity Compass? How can you use this information?
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.
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
T/F All views in Clarity start begin with ‘V_’
False. Naming conversions are not strictly enforced in Clarity
-May 2022, 5.7
How do views and derived tables alleviate creating a tedious query?
By combining data from multiple tables in one place.
-May 2022, 5.4
What is a tool that visually shows relationships between database objects relevant to a particular reporting area?
ER Diagrams (Entity Relationship Diagrams)
-May 2022, 5.10
What are two things that an ER diagram contains?
• Tables and their links for a particular reporting area
• Relationship cardinality of frequent joins
-May 2022, 5.10
Where do you search for Clarity ER diagrams?
In Galaxy:• Search “ ER Diagram htm”• Download ZIP file• Extract• Open “index.htm”(Aug. 2021, 5.5)
Do Clarity ER Diagrams document all possible joins between Clarity tables?
No, only some of the joins that are possible between Clarity tables.(Aug. 2021, 5.6)
List three types of database objects?
- View2. Derived Table (Materialized View)3. Extracted Table(Aug. 2021, 5.7)
How are Views identified in an ER Diagram?
By a brownish/orangish color.(Aug. 2021, 5.7)
How are database object types identified in the Clarity Compass?
By chips in the Overview section of the Summary tab.(Aug. 2021, 5.7)
What is a View?
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)
What type of database object pulls in data from several different tables in Clarity?
A View
-May 2022, 5.6
What are Derived tables also known as?
Materialized Views
-May 2022, 5.7
How are Derived tables populated?
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)
In Clarity, what data model does a derived table follow?
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)
What database objects typically begin with V_ ?
Views. However, this naming convention is not strictly enforced.(Aug. 2021, 5.12)
What does a Queries section tell you about a table?
That the table is either a View or a Derived table.Only Views and Derived tables have a Queries section. (Aug. 2021, 5.13)
Where can you research Epic SQL Functions?
In the Database Object Search of the Clarity Compass. (Aug. 2021, 5.13)
True or False: Views and derived tables are considered SQL-based data marts.
True.(Aug. 2021, 5.14)
Why would a Clarity table be replaced with a view that performs the same task?
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)
