REPUBLIQUE DU CAMEROUN

REPUBLIC OF CAMEROON
Paix-Travail-Patrie
Peace-Work-Fatherland
*********
*********
MINISTERE DE L’ENSEIGNEMENT
MINISTERY OF HIGHER EDUCATION
SUPERIEUR
*********
*********
UNIVERSITY OF DSCHANG
UNIVERSITE DE DSCHANG
*********
*********
FACULTY OF SCIENCES
FACULTE DES SCIENCES
*********
*********
DEPARTMENT DE MATHEMATICS
DEPARTEMENT DE MATHEMATIQUES
&
&
COMPUTER SCIENCES
INFORMATIQUE
*********
*********

Code/Intitulé de la Matière : Entrepôts de Données (INF 418)

Option : RSD

Classe LMD : Master 1 (INF4)

THEME : EXTENSIONS DU SQL POUR LE

NOMS & PRENOMS MATRICULES

FOKAM WAMBO G ERNEST CM-UDS-13SCI1264

KOUOKAM SIMO BRICE CM-UDS-16SCI1714

MBOUTCHEKA SILVAIN THIERRY CM-UDS-18SCI3236

MELA ROSINE ANASTASIE CM-UDS-18SCI3182

NGOULA NZEGA AURIOL ESPOIR CM-UDS-16SCI2557

RADJA FADILA CM-UDS-18SCI3001

Supervisé Par
Dr. BOMGNI

i
 TABLE DES MATIERES

I- Définition......................................................................................................................................... 1

I.1- Enjeux du décisionnel ............................................................................................................. 1

1.2- Architecture d'un système décisionnel .................................................................................... 2

I.3- Technologie Utilisée (PostgreSQL) ........................................................................................ 3

1.4- Quelques exemples d'application ............................................................................................ 4

II- Présentation du langage SQL ...................................................................................................... 4

II .1- SQL Server Integration Services (SSIS) ............................................................................. 5

II.3- LE ROLE DU SQL ..................................................................................................................... 6

II.4- SQL Server Analysis Services................................................................................................. 7

III- Différentes extensions du SQL pour le décisionnel .................................................................... 7

a) ROLLUP ..................................................................................................................................... 7

b) CUBE .......................................................................................................................................... 8

c) GROUPING ................................................................................................................................ 9

d) RANKING................................................................................................................................... 9

e) WINDOWING .......................................................................................................................... 10

f) Fonctions statistiques ................................................................................................................ 10

g) CASE ......................................................................................................................................... 12

I- Conclusion ..................................................................................................................................... 14

BIBLIOGRAPHIE ................................................................................................................................ 15

ii
 Table Des Figures

Figure 1: Cycle de décision.....................................................................................................2

Figure 2: Requête avec ROLLUP ...........................................................................................7

Figure 3: Résultats d'un rollup ................................................................................................8

Figure 4: Schema d'un cube ....................................................................................................8

Figure 5: Résultats d'une requête CUBE .................................................................................9

Figure 6: RANK ...................................................................................................................10

Figure 7: AVG......................................................................................................................10

Figure 8: COUNT.................................................................................................................11

Figure 9: MAX .....................................................................................................................11

Figure 10: MIN ....................................................................................................................11

Figure 11: SUM....................................................................................................................11

Figure 12: RANDOM ...........................................................................................................12

Figure 13: ROUND ..............................................................................................................12

Figure 14: CASE ..................................................................................................................13

iii
I- Définition

L’informatique décisionnelle (Business Intelligence) est un ensemble de moyens, méthodes,

outils permettant de collecter, consolider, modéliser, restituer les données matérielles ou

immatérielles d’une entreprise, en vue d’offrir une aide à la décision et de permettre aux

responsables de la stratégie d’entreprise et de son opérationnalisation d’avoir une vue

d’ensemble de l’activité traitée. La finalité d'un système décisionnel est le pilotage et

management de l'entreprise.

Synonymes : informatique décisionnelle, business intelligence, BI

I.1- Enjeux du décisionnel

Toutes les entreprises accumulent de grandes quantités de données au travers de leurs

systèmes d’informations, mais ces données ne deviennent des informations pertinentes pour

les décideurs que lorsqu’elles permettent de répondre à des questions sur l’entreprise et sur

son évolution. Pour être aidés dans leurs choix, les décideurs ont besoin de mesurer l’activité

de l’entreprise à l’aide d’indicateurs de performance (KPI). Il est donc nécessaire de

transformer la masse de données en provenance des multiples sources opérationnelles en

informations pertinentes pour la prise de décision. On développe des analyses de

performances à partir de données transformées à destinations de Chef des Ventes, de manager

sur le terrain.

L’informatique décisionnelle a pour démarche de connaitre, mesurer, prévoir et agir dans le

seul but de définir la stratégie de l’entreprise. La prise de décisions stratégiques dans une

organisation nécessite le recours et le croisement de multiples informations qui concernent

tous les départements : production, RH, DAF, achats, ventes, marketing, service après-vente,

maintenance, R&D... Or ces données sont généralement :

1
 Éparpillées au sein des départements et non connectées entre elles

 Hétérogènes dans leurs formats techniques et leurs organisations structurelles, voire leurs

sémantiques implémentées pour l'action (par construction) et non pour l'analyse

 Volatiles, au sens où leur mise à jour peut conduire à oublier des informations obsolètes

Figure 1: Cycle de décision

1.2- Architecture d'un système décisionnel

Le but du système est globalement d'être capable de présenter des tableaux de données

(fichiers plats) en intrants des applications d'exploitation. Tout système décisionnel est

architecturé globalement de la même façon :

 En amont un accès au système transactionnel en lecture seule

 Un DW fusionnant les données requises

 Un ETL permettant d'alimenter le DW à partir des données existantes

 Des applications d'exploitation de reporting, exploration et/ou prédiction

 D'éventuels DM permettant de simplifier le DW en vue de certaines applications

2
I.3- Technologie Utilisée (PostgreSQL)

PostgreSQL est un système de gestion de base de données relationnelle et objet

(SGBDRO). C'est un outil libre disponible selon les termes d'une licence de type BSD. Ce

système est concurrent d'autres systèmes de gestion de base de données, qu'ils soient libres

(comme MariaDB et Firebird), ou propriétaires (comme Oracle, MySQL, Sybase, DB2,

Informix et Microsoft SQL Server). Comme les projets libres Apache et Linux, PostgreSQL

n'est pas contrôlé par une seule entreprise, mais est fondé sur une communauté mondiale de

développeurs et d'entreprises.

Avantages Inconvénients

Open Source Non disponible par défaut sur tous les hébergeurs

Hautement extensible Documentation extensible en anglais seulement

Largement conforme à la norme SQL Vitesse de lecture relativement faible

Traitement possible de types de données

complexes (par ex : géo données)

Recherche de texte flexible

Possibilité de créer ses propres fonctions,

déclencheurs, types de données, etc.

Bon support du langage (Python, Java, Perl,

PHP, C, C, C++ etc)

Supporte JSON

Cross-plateforme

3
1.4- Quelques exemples d'application

 Analyse du comportement de consommateurs ou de citoyens, en fonction de leurs

caractéristiques (sexe, age...), de critères socio-économiques (profession...),

géographiques

 Analyse de ventes en fonction de l'implantation géographique de magasins (densité,

caractéristiques des régions...), de l'organisation de magasins (rayonnage, marketing,

RH...)

 Analyse des structures de paniers (quel produit est vendu en même temps que quel autre à

quelles conditions ?)

 Prédiction de ventes en fonctions de données conjoncturelles, gestion des stocks, des

approvisionnements

 Contrôle qualité et analyse de défaut des chaînes de production en fonction des centres de

production, des organisations, des fournisseurs...

II- Présentation du langage SQL

Le langage SQL (Structured Query Language) est un langage informatique utilisé pour

exploiter des bases de données. Il permet de façon générale la définition, la manipulation et le

contrôle de sécurité de données.

Dans la pratique, le langage SQL est utilisé pour créer des tables, ajouter des enregistrements

sous forme de lignes, interroger une base de données, la mettre à jour, ou encore gérer les

droits d'utilisateurs de cette base de données. Il est bien supporté par la très grande majorité

des systèmes de gestion de base de données (SGBD). Créé au début des années 1970 par

Donald D. Chamberlin et Raymond F. Boyce, tous deux chez IBM, le langage SQL est

aujourd'hui reconnu comme une norme internationale.

4
De nombreuses bases de données s'appuient sur le langage SQL. C'est le cas de MySQL qui

fait partie de la suite de logiciels libres LAMP, mais aussi des serveurs de base de données

Oracle, D2B, Microsoft SQL Server, etc.

SQL fait partie de la même famille que les langages SEQUEL (dont il est le descendant),

QUEL (intégré à Ingres) ou QBE (Zloof).

SQL Server Management 2014, produit par Microsoft est un système de gestion de bases de

données relationnelles. Le stockage, la manipulation et l’analyse de ces données se font au

sein de son moteur de bases de données. Ce service permet la réalisation de nombreuses

applications, requêtes, et transactions, notamment grâce au langage T-SQL (Transact-SQL).

En plus son moteur, SQL Server contient trois plateformes, indispensables pour réaliser un

projet BI :

 Integration Services, qui permet d’intégrer des données provenant de différentes sources

pour les ranger dans un entrepôt central ;

 Analysis Services, qui permet d’analyser les données, agrégées lors de S.S.I.S., grâce à

des fonctions d’analyse multidimensionnelle ;

 Reporting Services, qui permet de créer, gérer et publier des rapports résultant des

analyses réalisées lors de S.S.A.S. ;

II .1- SQL Server Integration Services (SSIS)

SQL Server Integration Services est donc la première plateforme à aborder. Dans cette partie

seront expliquées les notions propres à I.S. : l’ETL, les packages et les tâches d’intégration.

L’ETL ou notre DataBase CoreStaging, une fois la structure du datawarehouse définie, les

données doivent être insérées. L’outil qui va permettre le remplissage de notre base est l’ETL

(Extract- Transform-Loading) grâce à la techno SSIS. Comme son nom l’indique, il

5
commence par extraire les données provenant de différentes sources (Excel, MySQL...), les

transforme si besoin est, puis les charge dans le datawarehouse qui sera par la suite le

CoreSystem.

Ce processus se déroule donc en trois étapes :

 Extraction des données à partir d’une ou plusieurs sources de données telles que fichier

plat, fichier brut, OLE DB (source relationnelles telles que SQL Server, Access...),

Excel...

 Transformation des données agrégées.

 Chargement des données dans la banque de données de destination (datawarehouse). La

phase d’ETL est ici incontournable car elle conditionne et influence la qualité du projet

par la suite.

II.3- LE ROLE DU SQL

Le rôle du SQL est utile pour administrer les privilèges lorsqu'une base de données compte

plusieurs utilisateurs. Les rôles constituent un moyen plus puissant d'octroi de privilèges aux

sessions d'utilisateurs que d'attribution de privilèges à chaque utilisateur de la base de

données, ce qui devient facilement fastidieux et source d'erreurs lorsque de nombreux

utilisateurs sont impliqués. Les rôles ne confèrent pas en eux-mêmes une meilleure sécurité

des bases de données, mais utilisés correctement, ils facilitent une meilleure sécurité. Seul le

propriétaire de la base de données peut créer, octroyer, révoquer et supprimer des rôles.

Toutefois, les propriétaires d'objets peuvent octroyer et révoquer des privilèges pour ces

objets vers et depuis des rôles, ainsi que vers et depuis des utilisateurs individuels et PUBLIC

(tous les utilisateurs).

6
II.4- SQL Server Analysis Services

SQL Server Analysis Services est la plateforme qui permet de créer et gérer des structures

multidimensionnelles et des modèles d’exploration de données. Pour cela, Analysis Services

fournit des fonctions OLAP (OnLine Analytical Processing), et des applications d’exploration

de données (data mining). La création des cubes OLAP ou Tabulaires et des modèles

d’exploration se fait avec SQL Server Business Intelligence Development Studio, leur gestion

avec SQL Server Management Studio. Ces analyses comprennent un traitement sur des bases

de données volumineuses et permettent de comprendre les métriques et les éléments qui

influent sur le fonctionnement de l’entreprise.

III- Différentes extensions du SQL pour le décisionnel

a) ROLLUP

Calcule des agrégats (SUM, COUNT, MAX, MIN, AVG) à tous les niveaux de totalisation

sur une hiérarchie de dimensions et calcule le total général :

• Selon l’ordre de gauche à droite dans la clause GROUP BY

• S’il y a n colonnes de regroupements, GROUP BY ROLLUP génère n+1 niveaux de

totalisation

Figure 2: Requête avec ROLLUP

7
Figure 3: Résultats d'un rollup

b) CUBE

Calcule des agrégats (SUM, COUNT, MAX, MIN, AVG) à différents niveaux d’agrégation

comme ROLLUP mais de plus permet de calculer toutes les combinaisons d’agrégations :

 GROUPE BY CUBE créé des sous-totaux pour toutes les combinaisons possibles d’un

ensemble de colonnes de regroupement

 Si la clause CUBE contient n colonnes, CUBE calcule 2n combinaisons de totaux.

 Intéressant pour des colonnes représentant des dimensions appartenant à des hiérarchies

différentes.

NB : GROUP BY CUBE est une alternative plus performante au UNION AL

Figure 4: Schéma d'un cube

c) GROUPING

La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats et utiliser une

fonction de totaux sur un groupe de résultat. Sur une table qui contient toutes les ventes d’un

magasin, il est par exemple possible de lister regrouper les ventes par clients identiques et

d’obtenir le coût total des achats pour chaque client.

d) RANKING

La fonction rank produit un rang numérique dans la partition de l'enregistrement pour

chaque valeur différente de l'ORDER BY, dans l'ordre défini par la clause ORDER BY. rank

n'a pas besoin de paramètre explicite, puisque son comportement est entièrement déterminé

par la clause OVER.

9
Figure 6: RANK

e) WINDOWING

Une fonction de fenêtrage effectue un calcul sur un jeu d'enregistrements liés d'une certaine

façon à l'enregistrement courant. On peut les rapprocher des calculs réalisables par une

fonction d'agrégat. Cependant, les fonctions de fenêtrage n'entraînent pas le regroupement des

enregistrements traités en un seul, comme le ferait l'appel à une fonction d'agrégation

standard. À la place, chaque enregistrement garde son identité propre. En coulisse, la fonction

de fenêtrage est capable d'accéder à d'autres enregistrements que l'enregistrement courant du

résultat de la requête.

f) Fonctions statistiques

1) SQL AVG() : La fonction d’agrégation AVG() dans le langage SQL permet de calculer

une valeur moyenne sur un ensemble d’enregistrement de type numérique et non nul.

Figure 7: AVG

2) SQL COUNT() : En SQL, la fonction d’agrégation COUNT() permet de compter le

nombre d’enregistrement dans une table. Connaître le nombre de lignes dans une table est

très pratique dans de nombreux cas, par exemple pour savoir combien d’utilisateurs sont

présents dans une table ou pour connaître le nombre de commentaires sur un article.

10
Figure 8: COUNT

3) SQL MAX() : Dans le langage SQL, la fonction d’agrégation MAX() permet de retourner

la valeur maximale d’une colonne dans un set d’enregistrement. La fonction peut

s’appliquée à des données numériques ou alphanumériques. Il est par exemple possible de

rechercher le produit le plus cher dans une table d’une boutique en ligne.

Figure 9: MAX

4) SQL MIN() : La fonction d’agrégation MIN() de SQL permet de retourner la plus petite

valeur d’une colonne sélectionnée. Cette fonction s’applique aussi bien à des données

numériques qu’à des données alphanumériques.

Figure 10: MIN

5) SQL SUM() : Dans le langage SQL, la fonction d’agrégation SUM() permet de calculer la

somme totale d’une colonne contenant des valeurs numériques. Cette fonction ne fonction

que sur des colonnes de types numériques (INT, FLOAT …) et n’additionne pas les

valeurs NULL.

Figure 11: SUM

11
6) SQL RANDOM() : En SQL la fonction RANDOM() permet de sélectionner un nombre

aléatoire à virgule, compris entre 0 et 1. Le résultat de cette fonction sera différent à

chaque fois que la fonction est exécutée dans une requête SQL.

Figure 12: RANDOM

Cette fonction se révèle pratique avec un ORDER BY pour classer des résultats

aléatoirement. Toutefois, même si c’est possible il faut essayer d’éviter cette méthode qui

n’est pas particulièrement efficace en terme de performances.

La fonction peut aussi être utilisée dans une clause WHERE. Cela peut être pratique

pour sélectionner un résultat aléatoirement.

7) SQL ROUND() : Dans le langage SQL la fonction ROUND() permet d’arrondir un

résultat numérique. Cette fonction permet soit d’arrondir sans utiliser de décimal pour

retourner un nombre entier (c’est-à-dire : aucun chiffre après la virgule), ou de choisir le

nombre de chiffre après la virgule.

Figure 13: ROUND

g) CASE

Dans le langage SQL, la commande “CASE … WHEN …” permet d’utiliser des

conditions de type “si / sinon” (cf. if / else) similaire à un langage de programmation pour

retourner un résultat disponible entre plusieurs possibilités. Le CASE peut être utilisé dans

n’importe quelle instruction ou clause, telle que SELECT, UPDATE, DELETE, WHERE,

ORDER BY ou HAVING.

12
Figure 14: CASE

13
 I- Conclusion

Arrive au terme de notre travail ou il était question pour nous de parler des extensions

du SQL pour le décisionnel pour ce fait, nous avons présenté les enjeux du décisionnel,

l’architecture d’un système décisionnel, les technologies utilisées (POSTGRESQL), quelques

exemples d’application et enfin nous avons énuméré quelques extensions du SQL parmi

lesquelles : ROLLUP et CUBE qui sont utilisés avec GROUP BY, RANKING,

WINDOWING et les fonctions statistiques. Nous avons donné les différentes syntaxes des

requêtes POSTGRESQL utilisées pour ces différentes extensions et des exemples

d’application de requêtes sur notre base de données.

14
 BIBLIOGRAPHIE

 [Link]

informatique-decisionnelle

 [Link]

 [Link]

 [Link]

 Benitez-Guerrero E., C. Collet, M. Adiba, « Entrepôts de données : Synthèse et analyse »,

Rapport de recherche IMAG N°IMAG-RR - 99-1017-I, 1999.

 Gardarin G., « Internet/intranet et bases de données », Ed. Eyrolles, 1999, ISBN 2-212-

09069-2.

 Han J., Kamber M., « Data Mining: Concepts and Techniques », Morgan Kaufmann

Publishers, 2004.

 Kimball R., M. Ross, « Entrepôts de données : guide pratique de modélisation

dimensionnelle », 2°édition, Ed. Vuibert, 2003, ISBN : 2-7117-4811-1.

 Gray J. et al., Data Cube : A telationnal Aggregation Operator Generalizing Group-By,

Cross-Tab, and Sub-Totals, Data Mining and Knowledge Discovery 1, 29-53, 1997.

 Zemke F. et al., Introduction to OLAP functions, ISO/IEC JTC1/SC32 WG3 :YGJ-068 –

ANSI

15