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

Course:  OWHPSQ
Duration:  4 Days
Level:  III
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:

  • Packages
  • Table functions
  • Collections
  • Multi-table INSERTs
  • External tables
  • MERGE statement
  • Cursor variables
  • OWB vs. program coded ETL
  • Transformation options
  • Change Data Capture (CDC)

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.

« Hide The Details
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.

« Hide The Details
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.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com