Teradata Application Development
Teradata Training Course duration:
5 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
15. QueryGrid
- Introduction to Teradata QueryGrid: Teradata Database-to-Hadoop
- Syntax for Teradata QueryGrid: Teradata Database-to-Hadoop
- Stored Procedures for Teradata QueryGrid: Teradata Database-to-Hadoop
- Privileges and Security for Teradata QueryGrid: Teradata Database-to-Hadoop
- Administration and Utilities for Teradata QueryGrid: Teradata Database-to-Hadoop
16. Unity
- Unity Overview
- Teradata Unity Server High Availability
- Teradata Unity States
- Read-Override Mode
- Read-Write Mode
- Common Teradata Unity Tasks
- Teradata Unity Management System GUI
17. DSA
- Introduction to Data Stream Architecture
- Teradata BAR Portlets
- Teradata DSA Command Line Interface
18. Aster database management
- Create databases, schemas, and tables
- Know the rules of Aster modeling
- Load data
- Manage tables and connectors
- Create users, assign privileges and roles
- Configure workloads based on predicates of your choice
- Back-up and restore Aster tables
- Use the Data Dictionary, selected Aster scripts, and Ganglia
- Identify bottlenecks and resolution strategies
- Read and interpret Log Files
