Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

Advanced PL/SQL (and SQL) for Data Warehousing ETL

Course:   OWHPSQ
Duration:   4 Days
Level:   III
On our website at:   http://www.verhoef-training.com/courses/OWHPSQ.html
 
Course Summary

After reviewing data warehousing and extract, transform and load (ETL) basics, the instructor presents the full range of advanced PL/SQL and SQL features which may be incorporated as part of the DW ETL application solution. Skills related to these features are developed in the delegates through real-world examples, hands-on labs, exercises, etc.Among the ETL 9i and 10g topics explored are:

Finally, ETL PL/SQL (and SQL) applications performance and best practices are discussed in detail, using Oracle's performance measurement tools.

The course is applicable to both Oracle 9i and Oracle 10g.

Topics Covered In This Course

REVIEW

  • Terms, concepts and architecture
  • DW development life cycle
  • Dimensional modelling
  • ETL terms and concepts

ORACLE'S ETL CONSIDERATIONS

  • Scope and limits
  • Data structures
  • Extraction options
  • Cleaning and conforming
  • Dimension table delivery
  • Development
  • Operations
  • Metadata
  • Real-time ETL
  • Available ETL tools

PL/SQL ADVANCED TOPICS + LAB

  • Table function advantages
  • Table functions and cursor expressions
  • Function with REF CURSOR in WHERE clause
  • Cursor variables
  • Functions that return a collection
  • Pipelined table functions
  • CASTing and table functions
  • How to parallelize table functions
  • Multi-level collections
  • CASE statements/CASE expressions
  • Bulk Binding of dynamic SQL
  • PL/SQL record types

ORACLE WAREHOUSE BUILDER (OWB) VS. PURE PL/SQL

  • Introduction to OWB
  • Metadata
  • Graphical data mapping
  • Creation of hierarchies and dimensions
  • OLAP options
  • Data integrity
  • Auditing controls
  • Maintenance
  • ETL aggregate and materialized views

ETL: EXTRACTION OPTIONS + LAB

  • Full extraction
  • Incremental extraction
  • Change Data Capture (CDC)
  • Flat files via SQL*Plus
  • Flat files via export utility
  • Flat files via external tables
  • Flat files via OCI or PRO*C programs

ETL: TRANSFORMATION OPTIONS + LAB

  • Transformation: during extraction, in staging area, during load, etc.
  • Multi-stage transformation
  • Pipelined transformation
  • Transformation via SQL*Loader
  • Transformation via SQL UPDATE
  • Transformation via Regular Expressions
  • Transformation via table functions
  • Transformation via SQL MERGE
  • Transformation via multi-row insert
  • Transformation via PL/SQL
  • Key validation: SQL options
  • Staging table --> fact table (DML & SQL)
  • Partition table issues
  • Surrogate key options
  • Logging and audit considerations

ETL: DW LOAD OPTIONS + LAB

  • SQL
  • Loader: SQL (DML)
  • Data pump
  • External tables
  • Transportable tablespaces
  • Loading via SQL MERGE
  • Multi-table inserts

DW INDEX DESIGN OPTIONS + LAB

  • B*tree index – how they work
  • Bitmapped index – how they work
  • Bitmapped join indexes – how they work
  • NULL value considerations
  • Partitioning indexes
  • Local vs. global indexes
  • Index-organised tables

JOIN OPTIONS

  • Hash
  • Lossless
  • Nested loop
  • Partition-wise
  • Sort merge
  • Star transformation

PL/SQL & SQL TUNING AND BEST PRACTICES + LAB

  • When to tune
  • Native compilation of PL/SQL vs. interpreted
  • ROWCOUNTS for auditors, etc.
  • When to choose packages
  • How to code PL/SQL package for ROWCOUNTs
  • When to use BULK COLLECT and FORALL
  • PL/SQL and BULK COLLECT
  • PL/SQL and FORALL ...INDECES OF/VALUE OF
  • How to optimise FUNCTION calls
  • How to optimise loops
  • How to choose the optimal data type
  • How to decide the correct collection type
  • How to optimise VARCHAR2 usage
  • When to consider dynamic SQL
  • How to tune PL/SQL procedures with NOCOPY
  • How to optimise table functions
  • Parallel SQL recommendations
  • Parallel LOAD recommendations
  • Hints and query optimisation

PL/SQL & SQL TUNING TOOLS + LAB

  • EXPLAIN PLAN
  • Oracle Trace
  • SQL Trace via TKPROF
  • DBMS_UTILITY
  • SQL Access Advisor
  • SQL Tuning Advisor
What You Can Expect

Upon completion of this presentation, the delegates should be able to exploit the advanced features of PL/SQL and SQL when carrying out their data warehousing ETL requirements. And the delegate should be qualified to choose optimal PL/SQL and SQL when faced with competing alternatives.

Who Should Take This Course

ETL developers, PL/SQL programmers and DW architects

Recommended Prerequisites

Completion of our course 'Data Warehouse Terms, Concepts & Architecture' (DWHTCA) or equivalent knowledge.

Related Courses
Code Course Title Duration Level
DWHTCA
Data Warehousing Terms, Concepts and Architecture
1 Day
I
Details
DWHFUN
Data Warehousing Fundamentals
3 Days
I
Details
ORPTDW
Oracle 11g SQL Performance Tuning for Data Warehouses
4 Days
II
Details
DWHWPT
DB2 Data Warehousing Performance And Tuning Workshop
4 Days
II
Details
ORDWA
Oracle Database 11g: Administer a Data Warehouse
5 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.