Call : (+91) 99 8080 3767
Mail : info@EncartaLabs.com
EncartaLabs

Teradata Physical Implementation

( Duration: 2 Days )

Teradata Physical Implementation training course is designed to provide attendees with in-depth knowledge of Teradata Implementation.

  • A mix of intermediate and advanced Teradata users.

COURSE AGENDA

1

The Teradata Architecture

  • Teradata Spreads the Rows across AMPs
  • The Teradata Architecture
  • The Parsing Engine
  • The PE comes up with a PLAN the AMPs follow
  • The AMPs
  • Born to be parallel
  • The BYNET
  • A Scalable Architecture
  • Single-AMP Retrieve
  • Primary Index
2

The Primary Index

  • Two Types of Primary Indexes (UPI or NUPI)
  • Unique Primary Index (UPI)
  • Non-Unique Primary Index (NUPI)
  • Multi-Column Primary Indexes
  • When do you define the Primary Index?
  • Defining a Non-Unique Primary Index (NUPI)
  • Defining a Multi-Column Primary Index
3

Laying out and Retrieving Data

  • Hashing the Primary Index Value
  • The Hash Map
  • Laying a Row onto the Proper AMP
  • Retrieving a Row by way of the Primary Index
  • Hashing Non-Unique Primary Indexes (NUPI)
  • Placing Non-Unique Primary Indexes (NUPI) Rows
  • Non-Unique Primary Indexes Continued
  • Placing (NUPI) Rows Continued
  • Retrieving (NUPI) Rows
  • Placing Multi-Column Primary Index Rows
  • Retrieving Multi-Column Primary Index Rows
  • Even Distribution with an UPI
  • Uneven Distribution with a NUPI
  • Unacceptable Distribution with a NUPI
  • Review – Parsing Engines Plan with an UPI
  • Review – Parsing Engines Plan with a NUPI
  • Review – Big Trouble – The Full Table Scan
  • Big Trouble – A Picture of a Full Table Scan
  • Test your Teradata Primary Index Knowledge
4

The Row-ID

  • The Uniqueness Value
  • The Row ID
  • Duplicates and the Uniqueness Value
  • AMPs Sort Their Rows by the Row ID
  • Search the Data like a Phone Book
  • A Visual for Data Layout
  • Test Your Teradata Access Query Knowledge
  • UPI Row-ID Test
  • NUPI Row-ID Test
5

Secondary Indexes

  • The Base Table
  • Creating a Unique Secondary Index (USI)
  • The Secondary Index Subtable
  • Inside the Secondary Index Subtable
  • How Teradata builds the Secondary Index Subtable
  • Lab: Place the USI Rows on the Proper AMP
  • Lab: Place the USI Rows on the Proper AMP
  • Building the Secondary Index Subtable Results
  • USI – Always a Two-AMP Operation
  • USI Pictorial using the Hash Maps
  • The Base Table
  • Creating a Non-Unique Secondary Index (NUSI)
  • Columns inside a NUSI Secondary Index Subtable
  • NUSI Subtable is AMP-Local
  • A Query using the NUSI Column
  • A Query using the NUSI Column
  • Value Ordered NUSI
  • Test Your Teradata Access Query Knowledge
6

Partitioned Tables

  • Range Queries
  • Why we had to perform a Full Table Scan
  • A Partitioned Table
  • One Year of Orders Partitioned
  • Fundamentals of Partitioning
  • Add the Partition to the Row-ID for the Row Key
  • You Partition a Table when you CREATE the Table
  • RANGE_N Partitioning by Week
  • RANGE_N Partitioning Older and Newer Data
  • Case_N Partitioning
  • Multi-Level Partitioning
  • TIMESTAMP partitioning that is Deterministic
  • Disadvantages of PPI Tables
  • PPI Tricks for the Primary Index
  • Using the Keyword Partition
  • Partitions for No Case, No Range and Unknown
  • SQL for RANGE_N
  • SQL for CASE_N
  • Adding and Deleting Partitions
  • Adding and Deleting Partitions with INSERT
  • Partitioning Rules
  • See the data
7

Columnar

  • Columnar Tables are NOPI tables
  • A No Primary Index (NOPI) table
  • How to CREATE a NOPI table
  • NOPI tables spread data evenly
  • NOPI tables are often used for ETL Staging Tables
  • NOPI Options
  • NOPI Restrictions
  • NOPI Row-ID Architecture
  • Columnar Tables still have AMPs process entire row
  • How does a Columnar Table differ from a traditional Teradata Table
  • The fundamentals of Columnar
  • Columnar splits rows into column Containers
  • All Column Containers contain rows in perfect order
  • Columnar Relative Row Numbers among Containers
  • File System Generating (FSG) Cache
  • How Teradata processes using FSG Cache
  • How Columnar provides performance benefits
  • How to create a Columnar Table
  • Comparing Normal Tables to Columnar Tables
  • How normal tables are processed
  • How columnar tables are processed
  • Indexes can be used with Columnar
  • Visualize a Columnar Table
  • Single-Column and Multi-Column Containers
  • Columnar Row Hybrid Creation
  • Advanced Rules of Columnar Tables
  • Row Based Partitioned (PPI) Tables
  • CREATE Statement for both row and column partitioning
  • Column and Row partitioning combined
  • How to load a Columnar Table
  • Columnar Table Access through Secondary Indexes
  • Columnar Compression Capabilities
  • Auto Compress in Columnar
  • When and When NOT to use Columnar Tables
  • Restrictions of Columnar Tables
8

Joins

  • Joins and the Primary Index
  • Redistributing Rows in Spool
  • Redistributing Rows of Both Tables
  • Duplicating the Smaller Table
  • How Duplication Appears on Every AMP
  • How Many Rows in Spool with Redistribution?
9

The Logical and Extended Logical Model (ELDM)

  • Primary and Foreign Keys
  • A Normalized Data Warehouse
  • Normalization
  • Dimensional Modeling also called Star Schema
  • Dimensional Modeling also called Star Schema
  • Fact Table Vs Dimensional Table
  • The Extended Logical Data Model (ELDM)
  • The End Goal of the ELDM
  • Column ACCESS in the WHERE Clause
  • The Purpose of Examining Access
  • The Purpose of Examining Access for Joins
  • Data Demographics
  • Distinct Values
  • Distinct Values Danger
  • Maximum Rows per Value
  • Typical Rows per Value
  • Maximum Rows NULL
  • Change Rating
  • Extended Logical Data Model Template
  • The Application Development Life Cycle
10

The Physical Data Model

  • An Overview of picking the right Primary Index
  • Step 1 – Look at Distribution
  • Step 1 – Test – Find the Potential Index Candidates through good Distribution
  • Step 1 – Answers- Find the Potential Index Candidates through good Distribution
  • Step 2 – Eliminate based on Change Rating
  • Step 3 – NUSI Elimination via Value Access Frequency
  • Step 4 – Pick the Primary Index
  • Why Join Access Frequency is Top Priority?
  • Why Value Access Frequency is Second Priority?
  • What have we learned about picking the Primary Index?
  • You pick the final Primary and Secondary Index
  • What have we learned about picking the Primary Index?
  • Check your answer to the Primary Index pick
  • Step 5 – Pick Secondary Indexes
  • USI Considerations
  • USI to eliminate Duplicate Row Checking
  • NUSI considerations
  • Four Techniques for using a NUSI
11

Denormalization

  • Derived Data
  • Storing Aggregates
  • Derived Data and Temporary Tables
  • Derived Tables
  • Volatile Temporary Tables
  • Global Temporary Tables
  • Pre–Joining Tables
  • Repeating Groups
  • Horizontal Partitioning 1 – Splitting Rows into Separate Tables
  • Horizontal Partitioning 2 – Separating Rows through a Partitioned Primary Index (PPI) Table
  • Vertical Partitioning
  • Covered Query
  • Value Ordered NUSI
  • Identity Columns
12

Join Indexes

  • Join Index Fundamentals
  • Many types of Join Indexes
  • Multi-Table Join Index Picture
  • Compressed Multi-Table Join Index Picture
  • Single-Table Join Index
  • Explain showing use of the Join Index
  • Compressed Single-Table Join Index Example
  • Aggregate Join Index
  • Sparse Join Index
  • Global Multi-Table Join Index
  • Hash Index
  • Join Index Details to Remember
13

Compression

  • How Compression Works
  • Using the Nexus Query Chameleon to Compress
  • An Example of SmartCompress of Nexus
  • Compression Reports with Nexus
  • Compression in V13
14

COLLECT STATISTICS

  • Parsing Engine uses Statistics for the Plan
  • Columns and Indexes to Collect Statistics On
  • Syntax to Collect Statistics
  • Recollecting Statistics
  • Random Sample instead of Collected Statistics
  • V12 Statistics Enhancement – Stale Statistics
  • Where Statistics are Stored in DBC
  • What Statistics Really Collects?
  • Loner Values and High Bias Intervals
  • Teradata Limits
15

The Load Utility BTEQ

  • Teradata Load Utilities Continued
  • BTEQ
  • The Four Types of BTEQ Exports
  • FastLoad
  • FastLoad has Two Phases
  • Acquisition Phase 1
  • FastLoad Application Phase 2
  • A Sample FastLoad Script
  • MultiLoad
  • The Five Phases of MultiLoad
  • A Sample MultiLoad Script
  • TPump
  • Limitations of TPump
  • A Sample TPump Script
  • FastExport
  • FastExport Enhancement – No Spool
  • Teradata Parallel Transport
  • The TPT Data Stream Flow
  • TPT Operators
  • Read from Multiple Source Files Simultaneously
  • TPT Operators and their Functions
  • TPT Operator Types
  • TPT Operators and their Equivalent Load Utility
  • How to Run a TPT Script

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