ORACLE DATA WAREHOUSE DESIGN - ETL
Duration: 3 Days
On our website at:
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
- Change Data Capture
- Data Pump
- External tables
- Transportable tables
- SQL MERGE
- Table Functions
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
- Extraction options
- Transformation options
- Loading options
- Change Data Capture and publishing
- Staging areas
- 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
- Late arrivals
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
- Summary tables
ORACLE'S DATA EXTRACTION
- Synchronous Change Data Capture (CDC)
- Asynchronous Change Data Capture (CDC)
ORACLE'S DW LOADING OPTIONS
- 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)
- 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
Who Should Take This Course
Data Warehouse administrators, DBAs, data architects, DW
Completion of our course Data Warehouse Terms, Concepts
and Architecture or equivalent knowledge.
Lecture and hands-on.
Data Warehousing Terms, Concepts and Architecture
ORACLE DATA WAREHOUSE DESIGN
Data Warehousing Fundamentals
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.