Data Warehousing Terms, Concepts & Architecture

Request info

Data Warehousing Training

Course Title:Data Warehousing Terms, Concepts & Architecture
Summary:This DW terminologies and concepts course addresses the following topics:

  • What is data warehousing?
  • What is a data mart?
  • What are the data modelling options?
  • What are the terms and concepts specific to data warehousing and OLAP design?
  • What are common statistics, analytic and OLAP SQL queries?
Objectives:Upon completion of this course, the participant should be conversant with terms and concepts as these relate to 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.
Audience:IT managers, would-be data warehouse architects, IT developers, database administrators, DW project managers or anyone responsible for a data warehouse or related discipline.
Prerequisites:No technical experience necessary. Some relational database experience useful.
Format:Lecture and discussion

Core Topics

1. DATA WAREHOUSE OVERVIEW
  • Overview
  • Typical uses
  • Architecture
2. 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
  • Logical design vs. physical design
  • Normalization vs. denormalization
  • Referential constraints
3. DATA MODELLING OPTIONS & OVERVIEW
  • Entity model
  • Star schema
  • Snowflake schema
4. DIMENSIONAL MODELLING DEVELOPMENT LIFE CYCLE
  • Four steps of dimensional modelling
  • 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
  • Dimensions
  • Hierarchies
  • Joins
  • Summary tables and aggregation (i.e., materialized views/indexed views)
6. IMPLEMENTATION OPTIONS
  • Overview
  • Top down
  • Bottom up
  • Sizing
  • Cleaning
  • Populating the data warehouse
7. EXTRACT, TRANSFORM, LOAD (ETL) CONCEPTS
  • ETL vs. ELT: pros and cons
  • ETL planning and monitoring
  • Transformation options
  • Loading options
  • Change Data Capture and publishing
  • Data Staging
  • Restart recovery
8. DATA WAREHOUSE PERFORMANCE DESIGN
  • Large concurrent reports
  • Short running queries
  • Long running queries
  • On-line utilities
  • Partitioning and parallelism (e.g., LOADs)
  • Table spaces and buffer pools
9. INTRODUCTION TO DW GUI TOOLS
Duration: 1 Day
Cost: £550 + VAT
Data Warehousing Training Scotland, Inverness, Aberdeen, Glasgow, Edinburgh, Dunfermline and other sites throughout the UK including onsite closed company courses are available.
SELECT wp_posts.*, wp_p2p.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_p2p WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'start_date' AND CAST(wp_postmeta.meta_value AS DATE) >= '2025-01-13' ) ) AND ((wp_posts.post_type = 'schedule' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'))) AND (wp_p2p.p2p_type = 'schedule_to_courses' AND wp_posts.ID = wp_p2p.p2p_from AND wp_p2p.p2p_to IN (SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (8896) AND ((wp_posts.post_type = 'courses' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'))) ORDER BY wp_posts.post_date DESC )) GROUP BY wp_posts.ID ORDER BY CAST(wp_postmeta.meta_value AS DATE) ASC