Exploring Database Objects in Clarity

Question 1

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

Answer 1

By combining data from multiple tables in one place.

Aug. 2021, 5.4

Question 2

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

Answer 2

ER Diagrams (Entity Relationship Diagrams)

Aug. 2021, 5.5

Question 3

What are two things that an ER diagram contains?

Answer 3

• Tables and their relationships
• Cardinality of those relationships

(Aug. 2021, 5.5)

Question 4

Where do you search for Clarity ER diagrams?

Answer 4

In Galaxy:
• Search " ER Diagram htm"
• Download ZIP file
• Extract
• Open "index.htm"

(Aug. 2021, 5.5)

Question 5

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

Answer 5

No, only some of the joins that are possible between Clarity tables.

(Aug. 2021, 5.6)

Question 6

List three types of database objects?

Answer 6

1. View
2. Derived Table (Materialized View)
3. Extracted Table

(Aug. 2021, 5.7)

Question 7

How are Views identified in an ER Diagram?

Answer 7

By a brownish/orangish color.

Aug. 2021, 5.7

Question 8

How are database object types identified in the Clarity Compass?

Answer 8

By chips in the Overview section of the Summary tab.

Aug. 2021, 5.7

Question 9

What is a View?

Answer 9

A stored query.

(Aug. 2021, 5.12)

• 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 10

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

Answer 10

A View.

Aug. 2021, 5.12

Question 11

What are Derived tables also known as?

Answer 11

Materialized Views

Aug. 2021, 5.12

Question 12

How are Derived tables populated?

Answer 12

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 13

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

Answer 13

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 14

What database objects typically begin with V_ ?

Answer 14

Views. However, this naming convention is not strictly enforced.

(Aug. 2021, 5.12)

Question 15

What does a Queries section tell you about a table?

Answer 15

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 16

Where can you research Epic SQL Functions?

Answer 16

In the Database Object Search of the Clarity Compass.

Aug. 2021, 5.13

Question 17

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

Answer 17

True.

Aug. 2021, 5.14

Question 18

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

Answer 18

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)