Non-Extracted Tables and Documentation

Question 1

True or False: Views are results, not stored queries?

Answer 1

False, views are stored query, not results.

Question 2

View:

Answer 2

A view is a stored query that can be investigated in the queries section of its clarity compass entry.

In Clarity, views meet particular reporting needs, pulling in data from several different tables in clarity. As a result, views can greatly decrease the amount of work a clarity report writer needs to do during the query creation process.

Question 3

True or False: Views in clarity typically being with V_?

Answer 3

True, however this naming convention is not strictly enforced.

Question 4

True or False: when using a view in clarity, any logic it contains will execute alongside your code?

Answer 4

True, since views in Clarity can be complex, using a view in clarity report can hide this complexity. When using a view in a clarity query, any logic it contains will execute alongside your code, which can affect performance. If query performance is an issue, consider querying tables directly or coping a portion of the view script for use within your own reports.

Question 5

When using a view in clarity, the logic behind it can be complex, and can affect performance, what can you do to help with performance?

Answer 5

When using a view in a clarity query, any logic it contains will execute alongside your code, which can affect performance. if query performance is an issue, consider querying tables directly or copying a portion of the view script for use within your own reports.

Question 6

(Video) How are views like hologram and derived like teleportation?

Answer 6

A view is a stored query, you do not save the results and there is no data going into the table. A view is like a hologram (help me OB-one kenobi, you are my only hope). This is a projection of a table, it is not stored. You can interact with it but it does not take up space. In contrast a derived table are materialized views, the results are saved at ETL. They do take up space in the data base. NOw derived tables are like teleportation because they are moving data. (bean me up scotty) we are actually transfering data and it physically takes up space in the destination.

Question 7

Derived Tables:

Answer 7

Derived tables or materialized views, in clarity are populated from queries that run against data already contained in the database and can take the place of several tables in one report.

Question 8

True or False: Clarity has more fact and derived tables than caboodle?

Answer 8

False, while caboodle consists primarily of fact and dimension tables, clarity has relatively few derived tables and primarily follows a normalized data model.

Question 9

True or False: the script for a derived table is not a stored procedure?

Answer 9

False, the script for a derived table is actually a stored procedure, which is called during the clarity ETL process to populate the data in the table. This script can be found in the clarity scripts form or in the queries section of the summary.

Question 10

What is the quickest way to scope out the underlying data for a view or derived table.

Answer 10

The quickest way is to check the dependent database objects section of the summary tab. This provides a list of the clarity tables and columns, and even views and derived tables, that form the underlying structure of a given database object.

Question 11

True or False: During the clarity ETL process, the clarity console verifies that all dependent database objects have finished loading?

Answer 11

True, The clarity console verifies that all dependent database objects have finished loading data before running any scripts to populate derived tables.

Question 12

Where can you see what database objects are dependent on views?

Answer 12

You can also research in the dependencies section which database objects are dependent on a view or derived table.

Question 13

True or False: Views and derived tables do not have a queries section?

Answer 13

False, they do. The queries section will display the SQL queries responsible for populating the view or derived table. If you are researching a view or derived table the queries section is the location for troubleshooting.

Question 14

True or False: all derived tables are stored procedures?

Answer 14

False, sorta, all derived tables are based on stored procedures. The stored procedure runs during the ETL to populate it. So derived tables are based on the stored procedures.

Question 15

True or False: Views and derived tables are not constrained by the rules of the Clarity Data Model.

Answer 15

True, views and derived tables take advantage of the fact that the data is in clarity, stored in tables and columns

Question 16

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

Answer 16

True, the views and derived tables use information already stored in the clarity database.

Question 17

What is a data mart?

Answer 17

A data mart is a subset of data designed to meet the needs of a specific reporting area. For example, the F_ED_Encounters table meets many of the needs of ASAP report writers and drives many ASAP metrics and dashboards.

Question 18

Why can the primary key structure of a view or derived table take on any form?

Answer 18

Views and derived tables draw information from multiple places in clarity, allowing them to construct what are essentially mini reports in the database that accomplish any desired logic and can store or retrieve the data at any desired granularity. This results in the primary key structure of these databases taking virtually any form?

Question 19

What do views in clarity begin with?

Answer 19

Views in clarity typically begin with V_, however this naming convention is not strictly enforced.

Question 20

If you see a table with a F_” what type of table is it?

Answer 20

It is a fact table

Question 21

If you see a table with D_ what type of table is it?

Answer 21

It is a dimension table

Question 22

ER Diagram

Answer 22

An ER Diagram is a visual tool that shows relationships between database objects relevant to a particular reporting area.

Question 23

What type of information do ER diagrams contain?

Answer 23

Common tables and their links for a particular reporting area.
relationship cardinality of frequent joins.

Question 24

True or False: you search for the ER diagram in the data dictionary?

Answer 24

False, to find the ER Diagram you search in galaxy.

Question 25

True or False: The ER diagram will show all possible joins?

Answer 25

False, ER diagrams how some joins that are possible between clarity tables. ER diagrams will not document all possible joins between clarity tables. More research is needed to understand the contents of each table and column and its relevance within a report.

Question 26

When searching for the clarity table and column using Cogito Data Dictionary, the results will default what?

Answer 26

The results will by default display the extracted tables, derived tables, and views that can be found in clarity.

Question 27

What is EPIC Standard SQL Functions?

Answer 27

The Epic Standard SQL functions are a library of User Defined Functions (UDF) developed and released by EPIC to accommodate both Microsoft SQL Server and Oracle Syntax.

Question 28

True or False: EPIC Standard SQL functions can not be used in the scripts for views or derived tables?

Answer 28

False, You are able to use the EPIC Standard SQL functions in the scripts for derived tables or views, so a BID may research them to understand the logic used.

Question 29

True or False: Epic SQL functions are unique to Clarity and Caboodle?

Answer 29

False, the EPIC SQL Functions are unique to Clarity and will be found in the Clarity Compass but they are not found in the analytics catalog. You will need to use the database object search to research EPIC SQL functions.

Question 30

Entity

Answer 30

A person, place, thing event, or a concept about which information is kept. An entity is a database object in the clarity data model.

Question 31

Relationship

Answer 31

An association between two entities. A relationship is a foreign key in the clarity data model.

Question 32

Parent

Answer 32

An entity that contributes a foreign key to another entity. The one side of a one to many relationship.

Question 33

(Video) What is the difference between a union and a join?

Answer 33

The difference is that the join makes the table wide and the union makes the table long. A union will add more rows and a join will add more columns.

Question 34

(Video) What is the difference between an extracted table and a non-extracted table?

Answer 34

The difference between an extracted and non-extracted table is that an extracted table gets its data directly from chronicles? In contrast the non extracted table options would include derived table, view, and functions. These tables are populated from queries that run against data already contained in the database. Extracted directly from chronicles and non-extracted is filled by the extracted tables?

Question 35

Child

Answer 35

an entity that inherits a foreign key from another entity. The many side of a one to many relationships.

Question 36

Cardinality

Answer 36

The numeric relationship between rows in the parent table and rows in the child table. This is usually denoted in an M or N statement. For example, one to one or zero to one to one or more.

Question 37

Existence

Answer 37

Also known as optionality, existence refers to whether or not the child entity must have a parent entity. There are two kinds of existence, mandatory and optional.

Question 38

Mandatory Existence

Answer 38

It is 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 a 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.

Question 39

Optional Existence

Answer 39

It is possible for the foreign key in the parent entity to have a null values 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” so such relationships are drawn with a 0 on the parent side.

Question 40

Type

Answer 40

The type of a relationship is determined by how the parent entity’s primary key is used in the child entity. There are three types of relationships:identifying, non-identifying, and non-specific.

Question 41

Identifying relationship

Answer 41

The primary key of the parent entity is not contained in the primary key of the child entity. Such relationships are drawn as dash lines.

Question 42

Non‐Identifying Relationship

Answer 42

The primary key of the parent entity is not contained in the primary key
of the child entity. Such relationships are drawn as dashed lines

Question 43

Non‐Specific Relationship

Answer 43

Many‐to‐many relationships are referred to as non‐specific 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

Question 44

ER Diagram

Answer 44

An ER diagram is a visual tool that shows relationships between database objects relevant to a particular reporting area.

Question 45

View

Answer 45

A view is a stored query that can be investigated in the queries section of its clarity compass entry. Views are a stored query, no results. A view is like a hologram, it is a projection, of a table. It is not stored there and cannot touch it; it does not take up space.

Question 46

Derived table

Answer 46

Derived tables, or materialized views, in clarity are populated from queries that run against data already contained in the database and can table the place of several tables in one report. We can think of these tables are teleportation because they are moving data from one place to another. This is beam me up scotty.

Question 47

Scripts

Answer 47

SQL scripts contain SQL statements. When looking at derived tables, the script from the derived table gets populated from a stored procedure. This script can be found in the clarity scripts form or in the queries section of the summary.

Question 48

Dependent database objects

Answer 48

The Dependent database objects section provides a list of the clarity tables and columns, and even views and derived tables that form the underlying structure of a given database object. You can find this in the dependencies section of when looking at a table.

Question 49

Identify the location of Clarity ER diagrams.

Answer 49

In order to find the clarity ER diagrams, you have to search galaxy.
1. You go to galaxy
2. You put in your search item. Application ER diagram htm
3. You download the file
4. You open the index file.
5. You click on title, asap core physical data model, main model,

Question 50

Describe the differences between different types of database objects.

Answer 50

VIEWS: views are queries that are stored in the database. A view can be accessed as if it were a table. Unlike tables, however, views do not actually hold data. This means that using a view will not necessarily increase the efficiency off a query. However, views can reduce the amount of work that business intelligence developers must do by reducing the complexity of a query.
Examples V_sched_appt or V_access_log

STORED PROCEDURES: A SQL statement stored in your SQL server so that it can be run as needed. These are often used to populate tables off of other information already stored in the database.
Example: ESP_F_SCHED_APPT (the script that populates F_SCHED_APPT)
Materialized Views: 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-complied and saved query results.
Derived tables are materialized views.
Clarity doesn’t REALLY have Facts or Dimensions at all, because we don’t enforce a dimensional model in Clarity. That said… some clever developers definitely used the letters F_ and D_ in derived table names to say “hey, this one acts a lot like a Fact table, this one acts a lot like a Dimension…” but the convention is totally imperfect, and you would never want to say “All derived tables are fact tables” that’s 100% false on many levels.
Examples: F_SCHED_APPT, D_MU_MEASURES

FUNCTIONS: Functions can take in parameters and return results. In general, they do not make changes to the database. Functions can be used directly by SQL queries.
Example: CURRENT_TIMESTAMP

Question 51

Identify the dependent database objects for a view or derived table in Clarity.

Answer 51

One can identify the dependent database objects in the dependencies section when looking up a table in chronicles.

Question 52

Investigate the logic underlying a view or derived table using the Clarity Compass

Answer 52

You are able to find the logic of a table in the scripts area or in the queries section. To access the script in the overview section

Question 53

How to incorporate a view or derived table into the context of a larger report

Answer 53

You can incorporate a view or a derived table like you would an extracted table. You can do joins and unions. The positive aspect of a view or derived table is that usually it can pull everything you need from various tables, which in the end you do less joins.

Question 54

Why the structure of a view or derived table does not necessarily follow the Clarity data model.

Answer 54

Views and derived tables take advantage of the fact that data is in clarity, stored in tables and columns. Views and derived tables are not constrained by the rules of clarity data model because they are considered data marts. A data mart is a subset of data designed to meet the needs of a specific reporting area.