16.10 - Report/Query Analysis Form - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)
The Report/Query Analysis form:
  • Captures a simple category of data demographics for each individual transaction within an application within a system:
    • Lists the tables accessed during a transaction.
    • Lists columns used for valued access (if any).
    • Lists columns used for join access (if any).
  • Provides input to the Table form.

This form supports the fifth and sixth steps in the ATM process (see Goals of the ATM Process).

Terminology

Term Definition
Access column A column name that would be specified in the WHERE clause of a query.

In the following WHERE clause, column_name is the name of the access column:

     WHERE column_name = ‘xyx’

Join column A column name that would be specified in the ON clause of a join query.

In the following ON clause, table_1.column_name and table_2.column_name are the names of join columns:

     ON table_1.column_name = table_2.column_name

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.

Patient Cardinality: 1,000
Patient_ID Bed_Number Nurse_ID Doctor_ID
PK FK, ND FK FK
701 A 201 501
702 B 201 502
703 C 202 502
Bed Cardinality: 1,500
Bed_Number Room_Number
PK, ND FK
A 101
B 102
C 103
Patient Detail by Room Assignment
Room_ Number Patient_ID Bed_Number Nurse_ID
101 701 A 201
716 F 233
723 T 205
102 702 B 201
  725 Q 201
  748 L 222
Frequency or Importance Ranking: __________________________
Report/Query Description:  Patient detail by room assignment __________
Table: Patient                        Number of Output Rows: 1,000
Input Value

Or Source

Bed

Bed Number

   
Access Column(s) Bed Number    
 
Table: Bed                        Number of Output Rows: 1,500
Input Value

Or Source

Patient

Bed Number

   
Access Column(s) Bed Number    
 
Table:                        Number of Output Rows:
Input Value

Or Source

     
Access Column(s)      
 
Table:                        Number of Output Rows:
Input Value

Or Source

     
Access Column(s)      

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.

Service Cardinality: 125
Service_ID Description Cost
PK    
801 Appendectomy                     $   500.00  
802 Tonsillectomy                     $   350.00  
803 Anesthesia                     $1,000.00  
Patient_Service_History Cardinality: 730,000
Patient_ID Service_ID Timestamp
PK
FK FK  
701 801 2007-08-05 11:39
701 803 2007-08-05 23:59
705 801 2007-08-05   8:23
705 802 2007-08-05 13:43
705 803 2007-08-05 18:32
705 814 2007-08-05 23:56
Billing Report for Patient 705, August 2000  
Date Service_ID Description Cost
08/05/2007 801 Appendectomy $    500.00  
802 Tonsillectomy $    350.00  
803 Anesthesia $1,000.00  
Total for 08/05/2007 $1,850.00  
       
08/28/2007 814 Face lift $3,200.00  
  Total for 08/28/2007 $3,200.00  

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.

Billing Report for August 5, 2007
Patient_ID Number_of_Services Total_Cost
701 2 $ 1,500.00
705 4 $ 2,100.00
713 1 $    320.00
783 12 $ 2,400.00
795 2 $    890.00
Frequency or Importance Ranking:
Report/Query Description:  Billing report for a specific date                                            
Table: Patient_Service_History Number of Output Rows:    2,000
Input Value

Or Source

Current_Date Service

Service_ID

 
Access Column(s) Current_Date Service_ID  
 
Table: Service Number of Output Rows:          2
Input Value

Or Source

Patient_Service_History

Service_ID

   
Access Column(s) Service_ID    
 
Table:               Number of Output Rows:
Input Value

Or Source

     
Access Column(s)      
 
Table: Number of Output Rows:
Input Value

Or Source

     
Access Column(s)