EncartaLabs

Teradata Designer

( Duration: 2 Days )

This Teradata Designer training course is designed to provide a highly interactive environment for Teradata users of all skill levels to learn how to design databases.

By attending Teradata Designer workshop, attendees will learn to:

  • Explain the rules of data warehousing
  • Describe the application development life cycle
  • Model data
  • Define denormalization
  • Design Teradata databases

  • Teradata Users
  • IT Professionals

COURSE AGENDA

1

The Rules of Data Warehousing

  • Teradata facts
  • Teradata: brilliant by design
  • The Teradata parallel architecture
  • A logical view of the Teradata architecture
  • The Parsing Engine (PE)
  • The Access Module Processors (AMPs)
  • The BYNET
  • A visual for data layout
  • Teradata is a shared nothing architecture
  • Teradata has linear scalability
  • How Teradata handles data access
  • Teradata cabinets, nodes, Vprocs and disks
2

Data Distribution Explained

  • Rows and columns
  • The primary index
  • The two types of primary indexes
  • Unique Primary Index (UPI)
  • Non-Unique Primary Index
  • Turning the Primary Index Value into the row hash
  • The row hash value determines the rows destination
  • The row is delivered to the proper AMP
  • The AMP will add a uniqueness value
  • Teradata accesses data in three ways
  • Data layout summary
3

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
  • Partitioning and joins
4

Teradata Space

  • How permanent space is calculated
  • How permanent space is given
  • The Teradata hierarchy
  • How spool space is calculated
  • PERM, SPOOL and TEMP space
  • Spool space controls system time
  • Spool reserve
5

Modeling the Data

  • The application development life cycle
  • Asking the right questions
  • Logical data model
  • Primary keys
  • Foreign keys
  • Normalization
  • A normalized data warehouse
  • Dimensional modeling
  • Data marts
  • Update applications
6

Extended Logical Data Model

  • The end goal of the ELDM is to build table templates
  • Column ACCESS in the WHERE clause
  • Data demographics
  • Distinct values
  • Typical rows per value
  • Maximum rows NULL
  • Change rating
  • Extended logical data model template
7

Denormalization

  • Derived data
  • Horizontal partitioning
  • Vertical partitioning
  • Multi-table join indexes
  • Temporary tables
  • Derived tables
  • Volatile temporary tables
  • Global temporary tables
  • Views
8

Secondary Indexes

  • Unique Secondary Index (USI)
  • How Teradata retrieves an USI query
  • How Teradata retrieves a NUSI query
  • Value-ordered NUSI
  • How Teradata retrieves a value-ordered NUSI query
  • NUSI bitmapping
  • Prototyping indexes with EXPLAIN
  • Chart for primary and secondary access
9

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
10

Join Indexes

  • Three basic types of join indexes
  • Join index fundamentals
  • Join indexes versus other objects
  • Multi-table join index
  • Single-table join indexes
  • Aggregate join index
  • Sparse index
  • Sparse index picture
  • Global join index
  • Global join index picture
  • Global join index – multi-table join back
  • Hash indexes
  • Hash indexes vs. single-table join indexes
11

Locks

  • four locks for three levels of locking
  • Locks and their compatibility
  • How Teradata locks objects
  • Teradata locks – first come first serve
  • Locking modifier
  • The NOWAIT option
12

Tools

  • Database Query Log (DBQL)
  • DBQL collection options
  • DBQL tables and views
  • How to begin logging for DBQM
  • Teradata Dynamic Query Manager (TDQM)
  • TDQM works with Queryman (SQL assistant)
  • TDQM workloads and rules
  • Performance Monitor – PM
  • Access logging
  • Statistics wizard
  • Index wizard
  • TSET
13

Loading the Data

  • Fastload
  • Fastload picture
  • Multiload
  • Multiload picture
  • Tpump
  • Tpump picture
  • Fastexport
  • Fastexport picture
  • Warehouse builder
  • Insert/select
14

Various Topics

  • Identity columns
  • Referential integrity
  • Soft referential integrity
  • Roles
  • Profiles
  • Compression
  • Implementing compression
  • How compression works
  • Sync scan
  • Call Level Interface – CLI
  • LAN connection to Teradata for network attached clients
  • Mainframe connection to Teradata
15

Priority Scheduler

  • Priority scheduler partition hierarchy
  • Priority scheduler hierarchy definitions
  • Scheduling policies
  • Performance periods
16

Data Protection

  • Transaction concept and transient journal
  • How the transient journal works
  • FALLBACK protection
  • How fallback works
  • Fallback clusters
  • Down AMP Recovery Journal (DARJ)
  • Redundant Array of Independent Disks (RAID)
  • Cliques
  • Permanent journal
  • Table create with fallback and permanent journaling
  • Archive and recovery
  • Disaster recovery
  • Teradata crash dumps

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