EncartaLabs

Teradata Application Development

( Duration: 3 Days )

Teradata Application Development Training course is designed to enable attendees to develop complete command of the Application Development Process using Teradata. The course provides comprehensive guidance on application development of CLI and Teradata Utilities.

By attending Teradata Application Development workshop, attendees will learn to:

  • Develop applications for the Teradata environment
  • Leverage Teradata’s available application utilities and parallelism
  • Explain the Extended Logical Data Model
  • Apply join strategies

  • IT Professionals

COURSE AGENDA

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
  • 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

Understanding Views, Macros and Triggers

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

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
10

Collect Statistics

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

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
12

Loading the Data

  • Fastload
  • Multiload
  • Tpump
  • Fastexport
  • Insert/Select
13

MISCELLANEOUS

  • 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

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top