Report/Query Analysis Form | Database Design | Teradata Vantage - Report/Query Analysis Form - Advanced SQL Engine - Teradata Database

Terminology

Procedure

Perform the following procedure to complete the Report/Query Analysis form for each transaction:

1. Begin the analysis with standard reports, load requirements, and similar data.
2. Identify the tables involved and enter the names of each on a Report/Query Analysis form.
3. Identify which columns are printed and enter the names of each on the same Report/Query Analysis form that documents their tables.
4. Identify whether rows are accessed by specific column values.

   If so, enter the names of each column for which access values must be obtained and mark them with a V.

   Note that these must specify equijoin conditions matching on a single value.

5. Identify whether tables must be joined.

   If so, enter the names of the join columns and mark them with a J.

   Note that these must specify equijoin conditions.

6. Identify the number of rows that qualify for processing. This is not the number of rows in the answer set, but the number of rows that must be processed in order to obtain the answer set.

   Use these guidelines to determine what values to use.

   1. Identify the number of rows, if any, that qualify for value access.
   2. Identify the number of rows, if any, that qualify for join access.
   3. Select the smaller of 1 and 2 and use it as the value for the number of rows processed.
   4. If there are no value access rows and no join access rows, then use the number of rows in the table.

Example: Hospital Database

There are over 1,000 patients in the hospital database used for these examples. Each patient is hospitalized long-term (where long-term means ≥ one year).

Example: Patient Detail by Room Assignment

The Patient Detail by Room Assignment report derives from the patient and bed tables. Values and selected demographics for those tables are modeled as follows.

Example: Billing Report for Patient

The Billing Report for Patient 705 is more complex than the report analyzed in the previous example. The cardinality of the PatientServiceHistory table is based on the assumption that there is one year of service data kept in the history file, each patient receives an average of two services per day, and there are roughly 1,000 patients in the hospital at any time.

The calculation is done as follows.

     1,000 patients * 365 days * 2 services = 730,000 rows.

Example: Summary Report for All Patients on a Particular Date

This report is based on the same tables as that of the previous example. It reports summary information from those tables for all patients on a particular date. The value for the date column is obtained using the EXTRACT function on the Timestamp value for a performed service.