ORACLE DATA WAREHOUSE DESIGN - ETL
Course: OWHETL
Duration: 3 Days
Level: I
Course Summary
This course focuses on the extract, transform and load (ETL)
phase of the data warehouse (DW) development life cycle.
After discussing ETL as it relates to all DBMSs (DB2,
MS SQL Server, Sybase, etc.) we elaborate on Oracle's ETL
offering:
- Change Data Capture
- SQL*Loader
- Data Pump
- External tables
- Transportable tables
- SQL MERGE
- Table Functions
- Etc.
« Hide The Details
Topics Covered In This Course
REVIEW OF DATA WAREHOUSING (DW) TERMS AND CONCEPTS
- The DW environment
- What is a data warehouse?
- What is a data mart?
- What is Business Intelligence (BI)?
- How do OLTP and OLAP differ?
- What is data mining?
- Operational vs. historical data
- What is a star schema?
- What is a snowflake schema?
- Normalization vs. denormalization
- What are hierarchies?
- What is dimensional modelling?
- What is the Data Warehouse Bus Architecture (DWB)?
- What are surrogate keys?
- What is Extract, Transform, Load (ETL)?
- What are Slowly Changing Dimensions (SCD)?
- What is Metadata?
- What Materialized Views (MV)?
- How does logical design differ from physical design?
EXTRACT, TRANSFORM, LOAD (ETL) TERMS and CONCEPTS
- Options
- Extraction options
- Transformation options
- Loading options
- Change Data Capture and publishing
- Staging areas
EXTRACTING
- Logical-to-physical data mapping
- Disparate (heterogeneous) data sources
- Extracting changes data – delta or other
DATA CLEANING and CONFORMING
- Data quality criteria
- Design methods and alternatives
- Cleaning deliverables
- Conforming dimension tables
- Conforming fact tables
DIMENSION TABLE DELIVERY
- Dimension table structure
- Surrogate key generation
- Dimension table grain
- Flat (denormalized) or snowflake?
- Data and time dimensions
- 'Big' vs. 'small' dimensions
- Dimensional roles
- Dimensions as subdimensions
- Degenerate dimensions
SLOWLY CHANGING DIMENSIONS
- Type 1
- Type 2
- Type 3
- Hybrid
- Late arrivals
MULTIVALUED DIMENSIONS
FACT TABLE DELIVERY
- Fact table structure
- Referential integrity (RI)
- Surrogate key derivation and flow
- Fundamental grain
- Transaction fact tables
- Factless fact tables
- Periodic snapshots
- Accumulating snapshots
FACT TABLE LOAD CONSIDERATIONS
- Index management
- Partition management
- Updates, deletes and inserts
- Recovery
- Summary tables
- Parallelism
ETL TOOLS
ORACLE'S DATA EXTRACTION
- Synchronous Change Data Capture (CDC)
- Asynchronous Change Data Capture (CDC)
ORACLE'S DW LOADING OPTIONS
- SQL*Loader
- Optimising SQL*Loader performance
- SQL*Loader Direct Path Load
- SQL*Loader partitioning considerations
- SQL*Loader and data constraints (e.g., RI)
- SQL*Loader and parallelism
- SQL*Loader transformation options
- SQL*Loader and index optimisation
- Oracle's Data Pump
- External tables
- Transportable tablespaces
- Using SQL MERGE
- Multiple table INSERTs
ORACLE DATA TRANSFORMATIONS and CLEANING
- SQL updates
- Regular expressions
- Data validation
- DW key lookups
- Table functions
- Moving data from staging to fact
- Exchanging partitions
- Direct path inserts
- CREATE TABLE AS ...
DW PERFORMANCE and GOOD PRACTICES
- Query rewrite and MVs
- Star vs. snowflake schema
- SQL aggregates (e.g., ROLLUP)
- Parallelism
- Partitioning options
- Index options
What You Can Expect
Upon completion of this presentation, the participant should
understand the ETL phase of the data warehouse development
life cycle.
Who Should Take This Course
Data Warehouse administrators, DBAs, data architects, DW
PL/SQL developers.
Recommended Prerequisites
Completion of our course Data Warehouse Terms, Concepts
and Architecture or equivalent knowledge.
Training Style
Lecture and hands-on.
« Hide The Details
Related Courses
| Code |
Course Title |
Duration |
Level |
|
DWHTCA |
Data Warehousing Terms, Concepts and Architecture |
1 Day |
I |
Details |
OWHDWD |
ORACLE DATA WAREHOUSE DESIGN |
5 Days |
I |
Details |
DWHFUN |
Data Warehousing Fundamentals |
3 Days |
I |
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.
|
