Review of Data Warehousing Terms and Concepts

• The data warehouse 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. historic data
• What is a star schema?
• What is a snowflake schema?
• Normalization vs. denormalization
• What are h ierarchies?
• What is dimensional modeling?
• What is the Data Warehouse Bus Architecture (DWB)?
• What are surrogate keys?
• What is extract, transform, and load (ETL)?
• What are slowly changing dimensions (SCD)?
• What is metadata?
• What are materialized views (MV's)?
• How does logical design differ from physical design?

Oracle Database Architecture – A Summary

• Memory structures
• Logical storage structures
• Processes
• SQL tools and extensions
• Oracle RAC clusters

Logical/Physical Design Process

• Data warehouse database objects
• Setting up schemas and privileges
• Hardware and I/O considerations

Index Design Options

• Why use indexes?
• Composite indexes
• Types of indexes
• B-tree index
• Bitmap indexes
• Determining bitmap index candidates
• Bitmap join index
• Tuning star queries
• Using star transformation
• Star transformation restrictions
• Function based indexes
• Reverse key indexes
• Descending indexes
• Invisible indexes (11g)
• Index monitoring
• Foreign key indexing
• Why indexes may not be used

DW Partitions, parallel operation, and compression

• Compression options
• Partition options
• Dropping partitions
• Truncating partitioned tables
• Rebuilding index partitions
• Splitting partitions
• Parallelism options
• Parallel query
• Parallel DML
• Parallel DDL
• Parallel data loading
• Recovery
• Partition pruning
• Merging partitions
• Partition-wise joins
• Local vs global partitioned indexes
• Local partitioned indexes
• Global range partitioned indexes
• Global hash partitioned indexes
• Global non-partitioned indexes
• Guidelines for using partitioned indexes in OLTP applications
• Guidelines for using partitioned indexes in data warehousing and DSS applications
• Partitioned indexes on composite partitions

Joins

• Join types
• Nested loop join
• Hash join
• Sort merge join
• Driving tables
• Caching tables

Constraints, referential integrity, sequences, and synonyms

• Maintaining the integrity of the database
• The Check constraint
• The Default option
• The Not Null constraint
• The Unique Constraint
• The Primary Key constraint
• The Foreign Key constraint
• Modifying constraints
• When to use constraints
• Synonyms
• Sequences

Materialized views

• When to user materialized views
• Types of materialized views
• Materialized views with aggregates
• Materialized views containing only joins
• Nested materialized views
• Build methods
• Enable query rewrite
• Query rewrite restrictions
• General query rewrite restrictions
• Refresh modes and options
• General restrictions on fast refresh
• Restrictions on fast refresh on materialized views with joins only
• Restrictions on fast refresh on materialized views with aggregates
• Restrictions on fast refresh on materialized views with the Union All operator
• Timing the refresh
• The Dbms_mview package
• The Dbms_refresh package

Dimensions, levels and hierarchies

• Multidimensional structures
• Cubes
• Measures
• Joins
• Dimensions
• Levels
• Hierarchies
• Value based hierarchy
• Skip level hierarchy
• Ragged hierarchy
• Working with hierarchies – parents and children
• Dimensional query conditions
• Oracle dimensions
• Creating dimensions and cubes
• Oracle OLAP option

Extraction, transformation, and loading

• The ETL process
• Extraction types
• Data integration techniques
• Data cleansing
• Data cleansing methodology
• Surrogate keys
• The Insert-All statement
• The Merge statement

Oracle Text

• Oracle Text
• Oracle Text indexes
• Context index
• Ctxcat index

Analytic functions, cube, rollup, grouping sets, and Oracle data warehouse tools

• Rank() and over
• Dense_rank()
• Handling Nulls
• Top N and Bottom N queries
• The partition option
• Percentage ranks
• The Percent_rank() function
• The Cume_dis() function
• The Ntile() function
• The Row_number() function
• Windowing
• The Cumulative Aggregate function
• Moving averages
• Moving averages with a partitioned outer join
• Centered moving averages
• The Ratio_to_report function
• The Lag and Lead functions
• Statistical functions
• Linear regression functions
• Predicting the future with linear regression
• Grouping sets
• The Rollup option
• The Cube option

Oracle data warehouse performance considerations

• DML error logging
• Pipeline functions, external tables, and ETL

Oracle Discoverer

• Product overview
• Creating a business area using the Administrative edition
• Join tables
• Creating hierarchies
• Creating lists
• Creating a work sheet with the Desktop edition
• Analyzing a star schema