Data Warehousing (DW) – Advanced SQL Queries & Performance

Course:   DWHASP
Duration:   2 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/DWHASP.html
 
Course Summary

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 hands-on exercises, best practices and performance considerations.

Topics Covered In This Course

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)

STATISTICS & ANALYTIC QUERY OPTIONS

  • Rationale
  • AVG
  • CORRELATION
  • COUNT/COUNT_BIG
  • COVARIANCE
  • MAX
  • MIN
  • RAND
  • STDDEV
  • SUM
  • VARIANCE
  • Workshop

ONLINE ANALYTICAL PROCESSING (OLAP) QUERY OPTIONS

  • Rationale
  • RANK
  • DENSE_RANK
  • ROW_NUMBER
  • PARTITION BY
  • ORDER BY
  • ROWS
  • RANGE
  • GROUP BY
  • GROUPING
  • ROLLUP
  • CUBE
  • OVER
  • Workshop

COMMON BUSINESS SCENARIOS

  • Retail
  • Finance
  • Sports
  • Advertising
  • Office supplies
What You Can Expect

Upon completion of this presentation, the participant should be able to code efficient SQL queries to meet both single and multidimensional business requirements.

Who Should Take This Course

Data warehouse and BI developers, data warehouse architects and DBAs.

Recommended Prerequisites

Completion of our 'Data Warehousing Terms, Concepts & Architecture' (DWHTCA) course or equivalent knowledge.

Related Courses
Code Course Title Duration Level
DWHTCA
Data Warehousing Terms, Concepts and Architecture
1 Day
I
Details
DWHWPT
DB2 Data Warehousing Performance And Tuning Workshop
4 Days
II
Details
OWHPSQ
Advanced PL/SQL (and SQL) for Data Warehousing ETL
4 Days
III
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.