Teradata Training : Fundamentals of Teradata Application Development

Fundamentals of Teradata Application Development

Teradata Training Description:

This course is designed to enable students to develop complete command of the Application Development Process as it relates to Teradata. The course provides comprehensive guidance on application development including CLI and Teradata Utilities.

This course can be taught using Version 12, 13, or 14.

Teradata Training Course duration:

3 days

Teradata Training Course outline:

1. The Rules of Data Warehousing

Teradata certification

A logical view of the teradata architecture

The Parsing Engine (PE)

The Access Module Processors (AMPs)

The BYNET

A visual for data layout

How Teradata handles data access

Teradata cabinets, nodes, Vprocs, and disks

Three types of parallelism with Teradata

Multi-step parallelism

Multi-statement parallelism

The active data warehouse

OLTP environments

The DSS environment

Teradata maximums

Teradata maximums per release

2. Data Layout: AutoMagically

Data distribution explained
Rows and columns
The primary index
The two types of primary indexes
Unique primary index (UPI)
Non-unique primary index
Data layout summary

3. Partition Primary Indexes

V2R5 partition primary indexes
V2R4 example
V2R5 partitioning
Partitioning doesn't have to be part of the primary index
Partition elimination can avoid full table scans
The Bad NEWS about partitioning on a column that is not part of the Primary Index
Two ways to handle partitioning on a column that is not part of the Primary Index
Partitioning with CASE_N
Partitioning with RANGE_N
NO CASE, NO RANGE, or UNKNOWN

4. The Extended Logical Data Model

The application development life cycle
Asking the right questions
Logical data model
Primary keys
Foreign keys
Normalization
A normalized data warehouse
Dimensional modeling
Extended logical data model
The end goal of the ELDM is to build Table templates
Column ACCESS in the WHERE clause
Data demographics
Extended logical data model template
The physical data model
How to pick an excellent primary index
Denormalization
Derived data
Temporary tables
Derived tables
Volatile temporary tables
Global temporary tables

5. Secondary Indexes

Unique Secondary Index (USI)
USI subtable example
How Teradata retrieves an USI query
NUSI subtable example
How Teradata retrieves a NUSI query
Value-Ordered NUSI
Collecting statistics on NUSI Indexes
NUSI bitmapping
Prototyping indexes with EXPLAIN
Chart for primary and secondary access
Secondary index summary

6. Join Strategies

A join in simple terms
The key things to know about Teradata and joins
Merge join strategies
Joins need the joined rows to be on the same AMP
Another great join picture
Joining tables with matching rows on different AMPs
Redistributing a table for join purposes
Big table small table join strategy
Big table small table duplication
Nested join
Hash join
Exclusion join
Product joins
Cartesian product join
Outer joins
Inner and outer tables with residual conditions

7. Join and Hash Indexes

Description of join indexes
Join index fundamentals
Types of join indexes
Single-table join indexes
Multi-table join indexes
Aggregate join index
Partial-covering global join index
Partial-covering global join index picture
Multi-table join back
Sparse index
Sparse index picture
UPSERT ON indexed tables
Hash indexes
Hash indexes vs. secondary indexes
Hash indexes vs. single-table join indexes
Hash and join indexes vs. base tables

8. Explains

The Teradata Optimizer "knows" how to explain
Row estimate confidence levels
Explain terminology

9. Understanding Views, Macros and Triggers

All about views
View aggregation
All about macros
Creating a MACRO
Macros that use parameters
All about triggers

10. Locks

Teradata has 4 locks for 3 levels of locking
Locks and their compatibility
How Teradata locks objects
Teradata locks - first come first serve
Locking queue example 2
Locking queue example 3
Locking modifier
The NOWAIT option

11. Collect Statistics

How collect statistics works
Sample statistics
What you should collect statistics on

12. Tools

Database Query Log (DBQL)
DBQL collection options
DBQL tables and views
How to begin logging for DBQM
Performance Monitor - PM
Access logging
Statistics wizard
Index wizard
TSET

13. Loading the Data

Fastload
Multiload
Tpump
Fastexport
Insert/Select

14. MISC

Identity columns
Identity column example
Set manipulation
System calendar
Stored procedures
OLAP and analytical functions
ANSI OLAP syntax
Referential integrity
Soft referential integrity
Materialized views
Roles
Profiles
Compression
Implementing compression
How compression works
Teradata and ANSI mode
Teradata multi-statement request examples
PP2 -the Teradata preprocessor
Call Level Interface - CLI