Objectives

After a short introduction on Data Warehousing (DW) and Business Intelligence (BI) topics, we dive into a thorough discussion on the possibilities offered by SQL. More specifically, statistical, analytical and OLAP functions will be treated. The most important ones are individually discussed and demonstrated; realistic labs should allow the participant to get an idea of the applicability of these SQL functions and options.

The SQL syntax covered in this course is platform independent. When relevant, platform specific exceptions will be pointed-out.

At the end of this training, participants will be able to:

• understand how SQL (ie. the SELECT instruction) can be used for DW and BI reporting;
• write effective SQL from a DW and BI perspective;
• write problem-based SQL statements, and evaluate possible alternative SQL constructs for their effectiveness;

Main topics

• Data Warehouse and Business Intelligence - positioning
• Statistics and Analytics - support in SQL
• Purpose and context - multi-platform support.
• AVG, CORRELATION, COUNT/COUNT_BIG, COVARIANCE, MAX, MIN, RAND, STDDEV, SUM, VARIANCE
• Online Analytical Processing in SQL
• Purpose and context - multi-platform support.
• RANK, DENSE_RANK, ROW_NUMBER, PARTITION BY, ORDER BY, ROWS, RANGE, GROUP BY, GROUPING SETS, ROLLUP, CUBE, OVER
• WINDOWING functions: SUM ... OVER ...