home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
C
All CoursesbalkjeGeneral » Introduction to HW & SW » Soft skills » TracksOperating systems » MVS - z/OS » UNIX - Linux - AIX » Mac OS X » iPad and iPhone iOSDatabases and middleware » Relational databases & SQL » DB2 for z/OS » DB2 for LUW » Oracle » SQL Server » MySQL & MariaDB » IMS » CICS » IBM MQ » WebSphere » Big data and analyticsApplication development » Methods and techniques » TOGAF » PRINCE2 » Agile development and Scrum » Programming languages » Internet development » Object Oriented systems » Java » Development tools » SAS » XML » SOA & web servicesSystems management » ITIL » SecuritybalkjePractical informationRegistration 
SQL for BI reporting and analysis

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, as well as the use of SQL for ETL. 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 with respect to Oracle, DB2, and SQL Server.

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
  • Extract / Transform / Load (ETL)
  • the dimensional model
  • Statistics and analytics - support in SQL
  • Purpose and context - multi-platform support.
  • AVG, CORRELATION, COUNT, COVARIANCE, MAX, MIN, RAND, STDDEV, SUM, VARIANCE, MEDIAN, ...
  • 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 ..., LEAD; LAG and similar aggregation functions, and their application in a.o. trend analysis
  • Text analytics; regular expressions
 

Intended for

This course targets everyone involved in the development of business reports, by means of SQL statements on relational database systems.

Background

Advanced SQL proficiency is a must (see SQL advanced course), as well as a good understanding of the data warehousing concepts (see e.g. Data warehouse concepts).

Training method

Class training alternated with exercises.

Course leader

Peter Vanroose, Kris Van Thillo.

Duration

2 days.

Schedule

You can enrol by clicking on a date
datedur.lang.  location  price
08 Jun2NWoerden  (NL)950 EUR  (exempt from VAT)  guaranteed
15 Nov2?Leuven  (BE)950 EUR  (excl. VAT) 
18 Dec2EWoerden  (NL)950 EUR  (exempt from VAT) 

Global score

   
4.2/5 (based on 16 evaluations)

Reviews

     
Goede cursus; voldoende hoog niveau, met gelegenheid tot even "ophalen" van kennis. Analyse-onderdeel bleek een beetje onderbelicht, maar dat heeft mogelijk te maken met de geschiktheid van SQL hiervoor. (, )
     
Prima, veel geleerd. (, )
     
Goede uitleg en duidelijk verhaal. Goede nieuwe inzichten gekregen (, )
     
met enthousiasme gegeven (, )
     
Zeer enthousiast. (, )
     
Heel goed (, )
     
Goed, gedreven trainer die veel onderwerpen aankaart (, )
     
Uitstekend, zeer goede voorbeelden (, )
     
Le cours s'est bien passé en general (, )