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 [email protected]