Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
BlockChain
Business Analysis
Business Intelligence
Business Objects
Business Skills
C/C++/Go programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Data Science
Datawarehousing
DB2
Desktop Application Software
DevOps
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PostgreSQL
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
Oracle Data Warehouse (DW) Design, Administration and Performance
Datawarehousing Training Overview

CURRENCY: 9i & 10g

After presenting entity relationship (ER) and dimensional modelling (DM) as competing alternatives, the presentation will focus on the use of DM techniques when developing and implementing a very large data warehouse. Using real-world business scenarios, SALES, for example, the instructor will coach the participants from logical through physical design of a data warehouse involving at least five dimensions and one-or-more star schemas.

Thus hands-on exercises include:

  • Design and creation of dimensions
  • Design and creation of star schemas
  • Design and creation of hierarchies
  • Design and creation of indexes
  • Design and creation of integrity constraints
  • Extract, Transform and Load (ETL) options
  • Parallelism
  • Design and create materialized views (summary tables)
Throughout the foregoing, best practices and performance issues are discussed.

Datawarehousing Training Prerequisites

Completion of our course ‘Data Warehouse Terms, Concepts & Architecture’ (DWHTCA) or equivalent knowledge.

Datawarehousing Training Course duration

5 Days

Datawarehousing Training Objectives

Upon completion of this presentation, the participant should be able to monitor and tune large data warehouses in a BI (Business Intelligence) or DS (Decision Support) environment.

Datawarehousing Training Course outline

1. ORACLE ARCHITECTURAL OVERVIEW
  • Processes
  • Memory
  • Directory
  • Dictionary and catalog
  • Connectivity
  • Replication
  • Partitioning
  • Database
  • Real Application Cluster (RAC)
2. LOGICAL DESIGN CONCEPTS
  • Why data modelling
  • Requirements analysis
  • Normalization vs. denormalization
  • Entity relationship modelling
  • Dimensional modelling
  • OLAP vs. OLTP
  • Star vs. snowflake schemas
  • Metadata considerations
  • Data marts
  • Workshop
3. DIMENSIONAL MODELLING DESIGN (1) – INITIAL STEPS
  • How to establish business requirements
  • How to choose a business process (e.g., sales)
  • How to determine the business process grain (i.e., level of
  • detail for fact table)
  • How to choose dimensions (e.g., time)
  • How to identify measurement (numeric facts) to
  • populate the fact table
4. DIMENSIONAL MODELLING DESIGN (2) – FACT TABLE
  • Definition
  • Granularity selection
  • Measurements
  • Additive vs. non-additive measures
  • Foreign keys
  • Joins with dimension tables
  • Staging
  • Workshop, case study
5. DIMENSIONAL MODELLING DESIGN (3) – HIERARCHIES
  • Definition
  • Types
  • Levels
  • Level relationships
  • Workshop, case study
6. DIMENSIONAL MODELLING DESIGN (4) – INTEGRITY
  • CONSTRAINTS
  • Scope and purpose
  • Unique
  • NOT NULL
  • FOREIGN KEY
  • Enforced vs. not-enforced
  • Workshop, case study
7. DIMENSIONAL MODELLING DESIGN (5) – SCHEMA DESIGN
  • Star or snowflake
  • Data warehouse or data mart
  • Naming conventions
  • Maintenance requirements
  • Workshop, case study
8. PHYSICAL DESIGN (1) – LARGE DATA WAREHOUSE
  • CONSIDERATIONS
  • The environment (e.g., machine configuration)
  • Disk layout and placement (e.g., RAID)
  • Table sizes (e.g., maximum size for materialized view)
  • Database partition (e.g., how many?)
  • Partition key considerations
  • Initialisation parameters
  • Buffer pools
  • Data warehouse loads (e.g., parallelism options)
9. PHYSICAL DESIGN (2) – OBJECTS
  • Table spaces
  • Tables (partitioned vs. non-partitioned)
  • Index options
  • Integrity constraints
  • Materialized views (i.e., summary tables)
  • Creation of dimensions
  • Creation of hierarchies
  • I/O design considerations (e.g., striping and redundancy)
  • Best practices
  • Workshop, case study
10. PHYSICAL DESIGN (3) – PARALLELISM
  • Definitions
  • When to consider (e.g., bulk loads, summaries)
  • How to enable parallelism
  • Hardware requirements
  • Query parallelism
  • Partitioned and non-partitioned tables
  • Data manipulation
  • Types of parallelism (e.g., DML, DDL)
  • How parallelism works
  • Restrictions
  • Best practices
  • Workshop, case study
11. PHYSICAL DESIGN (4) – PARTITIONING
  • Definition
  • Types
  • When to consider
  • Table compression
  • Partition pruning
  • Join techniques
  • Range partitioning
  • Index partitioning
  • Best practices
12. PHYSICAL DESIGN (5) – INDEXES
  • Bitmap indexes
  • B-tree indexes
  • Compression
  • Global vs. local indexes
  • Best practices
  • Workshop, case study
13. PHYSICAL DESIGN (6) – INTEGRITY CONSTRAINTS
  • Rationale
  • Constraint states
  • Unique constraints
  • Foreign key constraints
  • Enforced vs. not-enforced constraints
  • Materialized views considerations
  • Query rewrite considerations
  • Best practices
  • Workshop, case study
14. PHYSICAL DESIGN (7) – CREATE DIMENSIONS
  • Dimension hierarchical specification
  • Integrity constraints
  • Dimension validation
  • Dimension maintenance (e.g., ALTER)
  • Best practices
  • Workshop, case study
15. PHYSICAL DESIGN (8) – MATERIALIZED VIEW CREATION & MAINTENANCE
  • Use cases
  • Materialized view types
  • How to create
  • How to refresh
  • How to partition
  • How to tune
  • Logs (e.g., staging options)
  • Security considerations
  • Query rewrite considerations
16. PHYSICAL DESIGN (9) – ETL
  • Options
  • Extraction options
  • Transformation options
  • Loading options
  • Change data capture and publishing
17. INTRODUCTION TO ORACLE DATA WAREHOUSING TOOLS
  • Oracle Warehouse Builder
  • Oracle Discoverer
  • Oracle Reports
  • OLAP and data mining
18. INTRODUCTION TO ORACLE SQL ADVISOR
  • Use
  • Tuning materialized views
19. DW PERFORMANCE CONSIDERATIONS
  • Query rewrite
  • Schema modelling
  • Aggregation
  • SQL modelling
  • EXPLAIN
  • I/O design
  • Parallelism
  • Initialisation parameters

 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
? Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy