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
Please contact your training representative for more details on having this course delivered onsite or online
