Vous avez déjà un compte client ORSYS
Se connecter
Vous êtes nouveau chez ORSYS
Créer un compte
Vous ne souhaitez pas créer de compte
Poursuivre sans me connecter

Course : Oracle SQL for Business Intelligence

Practical course - 3d - 21h00 - Ref. ROD
Price : 1740 € E.T.

Oracle SQL for Business Intelligence




Oracle's SQL language has been endowed with specific features for designing Business Intelligence environments. Before you can use it, you need to understand star and flake models, constellations, the difference between fact tables and dimension tables, and the proper use of keys and joins. Once the Data Warehouse has been designed, mastery of SQL, groupings, aggregations and possible functionalities will enable the warehouse to be fed for analysis purposes. We invite you to discover and master Oracle SQL for Business Intelligence.


INTER
IN-HOUSE
CUSTOM

Practical course in person or remote class
Disponible en anglais, à la demande

Ref. ROD
  3d - 21h00
1740 € E.T.

Download in PDF format

Share this course by email




Oracle's SQL language has been endowed with specific features for designing Business Intelligence environments. Before you can use it, you need to understand star and flake models, constellations, the difference between fact tables and dimension tables, and the proper use of keys and joins. Once the Data Warehouse has been designed, mastery of SQL, groupings, aggregations and possible functionalities will enable the warehouse to be fed for analysis purposes. We invite you to discover and master Oracle SQL for Business Intelligence.


Teaching objectives
At the end of the training, the participant will be able to:
Understand the fundamentals of business intelligence architecture and modeling
Master data grouping and analytical functions
Know how to use objects and updates (ETL, external tables, views, etc.)
Perform data analysis with the MODEL clause

Intended audience
This course is aimed at developers of Oracle BI applications who want to make the most of Oracle's SQL potential.

Prerequisites
Good knowledge of SQL.

Course schedule

1
Introduction

  • Business intelligence. The state of the SQL standard. The SQL3 standard and business intelligence.
  • Reminders of datawarehouse concepts. CUBE concepts. OLAP concept. Multidimensional analysis.
  • Star and constellation modeling. Notions of dimension. Table of facts.
Case study
Presentation: the model, the issues.

2
Data grouping

  • Horizontal, vertical and aggregate functions.
  • The group. Reminders: how GROUP BY works, how HAVING works.
  • GROUPING functions, syntax, examples, handling NULL values.
  • The two-level group.
Hands-on work
Manipulate grouping syntax and functions.

3
Data aggregation and analysis

  • ROLLUP and CUBE extensions. GROUPING SET expression.
  • Group functions. Analytic functions. Partitioning clause.
  • The order clause. The windowing clause. Sorting functions.
  • Scheduling: sorting. Filing and window functions.
  • Tabular representation with PIVOT and UNPIVOT.
Hands-on work
Manipulation of syntax and aggregation functions.

4
The MODEL clause

  • Implementation. The CV function.
  • The ITERATION_NUMBER function. The PRESENTNNV function.
  • The PRESENTV function. The PREVIOUS function.
Hands-on work
Manipulating MODEL clause syntax.

5
Data update

  • Multi-table insertion. Unconditional insertion.
  • INSERT_ALL, INSERT_FIRST. Conditional updates.
  • INSERT and UPDATE a table from other tables: use MERGE.
Hands-on work
Star model power supply.

6
Objects

  • ETL (Extraction, Transformation, Loading).
  • Querying external tables. Data dictionary views.
  • Materialized Views (VM): interest, creation syntax and options.
  • Materialized views and optimization. Query rewriting. Dimensions and hierarchies.
Hands-on work
Use external tables to move data. Create materialized views to optimize queries.
PARTICIPANTS
This course is aimed at developers of Oracle BI applications who want to make the most of Oracle's SQL potential.

PREREQUISITES
Good knowledge of SQL.

TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.

ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.

TEACHING AIDS AND TECHNICAL RESOURCES
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.

TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.

ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.