Data Warehousing (DW) – Advanced SQL Queries and Performance
Datawarehousing Training Overview
PLATFORM:
Multi-platform: DB2 UDB for z/OS & OS/390, DB2 UDB for
Linux/UNIX/Windows, Oracle and SQL Server.
After briefly reviewing DW and BI terms and concepts, the lecturer will
develop an understanding of how to use the statistics, analytic and
OLAP features in a BI environment.
The main features of this presentation are examples, intensive handson
exercises, best practices and performance considerations.
Datawarehousing Training Audience
Data warehouse and BI developers, data warehouse architects and
DBAs.
Datawarehousing Training Prerequisites
Completion of our ‘Data Warehousing Terms, Concepts &
Architecture’ (DWHTCA) course or equivalent knowledge.
Datawarehousing Training Course duration
2 Days
Datawarehousing Training Objectives
Upon completion of this presentation, the participant should be able to code efficient SQL queries to meet both single and multidimensional business requirements.
Datawarehousing Training Course outline
1. SUMMARY – DATA WAREHOUSE (DW) TERMS & CONCEPTS
- Rationale and beneficiaries
- DW architecture (including BI considerations)
- Data warehouse vs. data mart
- Metadata
- Normalization vs. denormalization
- Integrity constraints
- Schema types
- DW objects (logical vs. physical)
- Hardware considerations
- Partitioning and parallelism options
- Indexes
- Summary tables
- Extract, Transform, Load (ETL)
- Rationale
- AVG
- CORRELATION
- COUNT/COUNT_BIG
- COVARIANCE
- MAX
- MIN
- RAND
- STDDEV
- SUM
- VARIANCE
- Workshop
- Rationale
- RANK
- DENSE_RANK
- ROW_NUMBER
- PARTITION BY
- ORDER BY
- ROWS
- RANGE
- GROUP BY
- GROUPING
- ROLLUP
- CUBE
- OVER
- Workshop
- Retail
- Finance
- Sports
- Advertising
- Office supplies
