Datawarehousing Training Overview
This data modelling techniques course is designed to answer
questions, such as the following:
- What is data warehousing?
- What is a data mart?
- What are the data modelling options?
- What is Extract, Transform and Load (ETL)?
- What are the terms and concepts specific to data
warehousing and OLAP design?
- How to plan and implement a data warehouse with high
availability, simplified manageability and optimal
performance
- What are common statistics, analytic and OLAP SQL queries?
Datawarehousing Training Audience
Would-be data warehouse architects, IT developers, database
administrators or anyone responsible for a data warehouse or related
discipline.
Datawarehousing Training Prerequisites
At least six (6) months in an IT environment or its equivalent.
Datawarehousing Training Course duration
3 Days
Datawarehousing Training Objectives
Upon completion of this course, the participant should be able to
design a data warehouse using both star and snowflake schemas.
And the delegate should understand the implication of such terms as
cubes, dimensions, attributes, joins, hierarchies, measures, etc.
Datawarehousing Training Course outline
1. DATA WAREHOUSE OVERVIEW2. DEFINITION, ARCHITECTURE AND CONCEPTS
- Enterprise Data Model
- Operational vs. historical data
- Extract Transform Load (ETL)
- Metadata
- Data warehouse vs. data mart
- Data mining
- OLAP vs. OLTP
- Massive size implementation
- Logical design vs. physical design
- Normalization vs. denormalization
- Referential constraints
3. DATA MODELLING OPTIONS
- Entity model
- Star schema
- Snowflake schema
4. DATA MODELLING DEVELOPMENT LIFE CYCLE
- Requirements analysis
- Requirements gathering
- Requirements validation
- Requirements modelling
- Schema design
- Project definition
- Warehouse design
- Implementation
- Follow-up and review
5. DIMENSIONAL MODELLING DESIGN
- Overview
- Metadata properties
- Star schema
- Snowflake schema
- Cubes
- Measures and facts
- Attributes and relationships
- Dimension
- Hierarchies
- Joins
- Summary tables and aggregation
- Exercises
6. IMPLEMENTATION OPTIONS
- Overview
- Top down
- Bottom up
- Sizing
- Cleaning
- Populating the data warehouse
7. EXTRACT, TRANSFORM & LAOD (ETL) CONSIDERATIONS
- Definition and scope
- Extract options
- Transform options
-
- Load options
- Surrogate key concepts
- Slowly Changing Dimensions (SCD)
8. DATA WAREHOUSE PERFORMANCE DESIGN
- Automatic Summary Tables (AST)
- Large concurrent reports
- Short running queries
- Long running queries
- Random queries
- Occasional updates
- On-line utilities
- Index options
- Partitioning and parallelism (e.g., LOADs)
9. INTRODUCTION TO STATISTICS, ANALYTIC AND OLAP SQL
QUERIES via workshop example
- AVG
- CORRELATION
- COUNT
- COUNT_BIG
-
- CONVARIANCE
- MAX
- MIN
- RAND
- STDDEV
- SUM
- VARIANCE
- Regression function
- GROUPING, ROLLUP & CUBE
- Hands-on exercise
10. PHYSICAL DESIGN CONSIDERATIONS
- Denormalization
- Index choices
- Data placement
- Free space
- Summary tables
- Data compression
|
