Bases de données
multidimensionnelles
OLAP
OLAP
• OLAP (On‐Line Analytical Processing):
Ensemble des outils nécessaires pour
l’analyse multidimensionnelle.
• Les données sont historisées, résumées,
consolidées.
Modèles de données
• Le modèle
Poste1 Poste2 Poste 3 d'intégration unifie
les données
• Le modèle de
Modèle de diffusion modélise
présentation la structure de
l'entrepôt de
Modèle de
données (Serveur
diffusion OLAP).
• Le modèle de
Modèle présentation définit
d'intégration la manière dont les
données seront
présentées à
l'utilisateur.
Base1:Base de données de Base 2:Base de données Base3:Base de données de
production de production production
3
Les outils OLAP
• Les outils relationnels OLAP (ROLAP)
– Les données sont stockées dans une base de données
relationnelle, moteur OLAP permet de simuler le comportement
d’un SGBD multidimensionnel
– .
• Les outils MOLAP
– Utilisent un système multidimensionnel « pur » qui gère les
structures multidimensionnelles natives (les cubes)
– Accès direct aux données dans le cube
• Les outils HOLAP (Hybrid OLAP)
– tables de faits et tables de dimensions stockées dans SGBD
relationnel (données de base)
– données agrégées stockées dans des cubes
4
Le cube
• Modélisation multidimensionnelle des données facilitant
l’analyse d’une quantité selon différentes dimensions:
– Temps
– Localisation géographique
– Produits
– Fournisseurs
– Clients
– …
• Les calculs sont réalisés lors du chargement ou de la mise à
jour du cube
• Un cube permet de visualiser les données selon plusieurs
dimensions
• Un cuboïde est un cube de dimension n
5
Exemple de cube
Total annuel des ventes
Date de TV aux U.S.A.
1Trim 2Trim 3Trim 4Trim sum
TV 100 200 300 100 700
PC U.S.A
Tablette
sum
France
Pays
Allemagne
sum
ALL,ALL,ALL
6
Treillis cuboïdes
7
Opérations typiques de l’OLAP
• Roll up : consolider (résumer) les données :
Passer à un niveau supérieur dans la hiérarchie
d’une dimension
• Drill down : l’inverse du Roll‐up : descendre dans
la hiérarchie d’une dimension
• Slice et Dice (tranche et extraction): Projection et
sélection du modèle relationnel
• Pivot (rotate): Réoriente le cube pour
visualisation
8
Modèles de stockage
– ROLAP: les données d'un groupe de mesures
sont extraites dynamiquement à partir de la
table des faits (le groupe de mesures est
utilisé dans ce cas là uniquement pour définir
les métadonnées).
– MOLAP: les données de la table des faits
ainsi que les agrégats sont chargées dans
dans la base de données.
– HOLAP:Base de données
Multidimensionnelle hybride.
Couche métadonnées (sémantique)
• Le cube peut contenir des méta informations
pour:
– Pour décrire les tables, colonnes et relations dans une
base de données.
– Définir des hiérarchies
– Supporter la localisation
– Mettre en forme les données
Dimensions
• Hiérarchies
– Hiérarchie d’attributs: une hiérarchie d’attribut possède deux
niveaux: la racine qui contient tous les membres (toutes les
valeurs) et les feuilles de la hiérarchie sont composées par les
membres.
– Hiérarchie naturelle: une hiérarchie naturelle est le résultat d’une
relation entre attributs. chaque attribut fils doit avoir un seul
attribut parent (Exemple: CatégorieSous Catégorie Produit )
– Hiérarchie utilisateur:
• Peut être basée sur une hiérarchie naturelle ou non.
• Facilite le parcours d’une dimension.
• N’affecte pas la structure de stockage du cube.
• Exemple d’une hiérarchie utilisateur non basée sur une hiérarchie
naturelle: Couleur d’un article Taille de l’article
Exemples de serveurs OLAP
• EssBase: Oracle
• SSAS: Microsoft
• Cognos TM1: IBM
• Cubes: open source license MIT
• Mondrian: open source
Mondrian
Présentation
• Mondrian est un serveur ROLAP sous licence open
source.
• Couches d’un système Mondrian
– Couche présentation
– Couche Multidimensionnel: valide et exécute les
requêtes MDX, les axes sont d’abord évalués puis les
cellules.
– La couche étoile: responsable de la gestion d’un cache
d’agrégation, si les données demandées par la couche
Multidimensionnelle ne sont pas présentes dans le
cache alors une requête est envoyée à la couche
persistance.
– La couche persistance: une base de données RDBMS.
1
4
Création du cube
• Création de la base de données
– Exécuter les deux fichiers de scripts (adventure
works)
– Le driver mysql doit être copié dans le dossier
drivers
15
<Schema name="schema1">
<Cube name="Ventes indirectes">
<Table name="factresellersales">
</Table>
<Measure name="CA" column="SalesAmount" formatString="#,###.00"
aggregator="sum">
</Measure>
<Measure name="Frais de port" column="Freight"
formatString="#,###.00" aggregator="sum">
</Measure>
</Cube>
</Schema>
1
6
17
Création d’un cube avec PSW
• Cube
– Dimensions:
• [Types de grossiste] avec une hiérarchie unique (non
nommée) et basée sur une table de dimension unique
(dimreseller)
• [Temps] basée sur un schéma en étoile (tous les
niveaux de la hiérarchie étant gérés par une table de
dimension unique)
– Mesures
• deux mesures non calculées ([CA] et [Frais de port])
• une mesure calculée ([Pourcentage frais de port])
18
Création du cube Ventes2
1. Créer et enregistrer
un schéma nommé
« schema2»
2. Créer un cube
nommé « cube2 »
3. Définir la table de faits
associée au cube
19
4. Sélectionnez la table
de faits à partir de la
liste
5. Ajouter la mesure
CA
5. Ajouter la mesure
« Frais de port »
20
6. Ajouter la mesure
calculée «
Pourcentage Frais de
port »
7. Ajouter une dimension
associée au schéma.
21
8. Renseigner le nom de
la dimension « Type de
grossiste »
9. Ajouter une hiérarchie
à la dimension « Type
de grossiste »
22
10. Ajouter un
niveau à la
hiérarchie
11. Définir la table
associée à la
hiérarchie
« Hiérarchie des
types de
grossiste »
23
12. Ajouter la
dimension
« Temps » dans
le schéma
13. Ajouter une
hiérarchie dans
la dimension
« Temps » 14. Ajouter la table
« dimtime » à la
hiérarchie, puis
définir la propriété
« PrimaryKey ».
24
14. Ajouter le
niveau
« Année »
15. Ajouter le
niveau
« Trimestre »
25
16. Ajouter le
niveau
« Mois »
17.
26
18. Associer les
deux
dimensions au
cube
27
Le cube Cube2
28
<Schema name="schema2"> uniqueMembers="true" levelType="TimeMonths"
<Dimension type="StandardDimension" captionColumn="FrenchMonthName">
name="Type de grossiste"> </Level>
<Hierarchy name="Hiérarchie des types </Hierarchy>
de grossite" hasAll="true" allMemberName="Tous </Dimension>
les types de grossiste" <Cube name="Cube2" cache="true">
primaryKey="ResellerKey"> <Table name="factresellersales">
<Table name="dimreseller"></Table> </Table>
<Level name="Type de grossiste" <DimensionUsage source="Type de grossiste"
table="dimreseller" column="BusinessType" name="Type de grossiste"
type="String" uniqueMembers="true" foreignKey="ResellerKey">
levelType="Regular"></Level> </DimensionUsage>
</Hierarchy></Dimension> <DimensionUsage source="Temps" name="Temps"
<Dimension type="TimeDimension" foreignKey="OrderDateKey">
name="Temps"> </DimensionUsage>
<Hierarchy name="Temps" hasAll="true" <Measure name="CA" column="SalesAmount"
primaryKey="TimeKey"> formatString="#,###.00" aggregator="sum" >
<Table name="dimtime" > </Measure>
</Table> <Measure name="Frais de port"
<Level name="Année" table="dimtime" column="Freight" formatString="#,###.00"
column="CalendarYear" type="Numeric" aggregator="sum" >
uniqueMembers="true" levelType="TimeYears"> </Measure>
</Level> <CalculatedMember name="Pourcentage frais
<Level name="Trimestre" table="dimtime" de port" formatString=""
column="CalendarQuarter" type="Numeric" formula="Measures.[Frais de port]/[Link]"
uniqueMembers="true" levelType="TimeQuarters"> dimension="Measures" >
</Level> </CalculatedMember>
<Level name="Mois" table="dimtime" </Cube>
column="MonthNumberOfYear" type="Numeric" </Schema>
29
• Création d’une source de données dans Pentaho
30
Créer une analyse Mondrian
• Créer une nouvelle vue analytique:
Fichier/Nouveau / Vue Analytique
31
Exercices
Ecrire les requêtes MDX suivantes:
Afficher les CA et les frais de port de tous les types de grossiste.
• Req1
• Req2
• Req3
• Req4
32
• Req 1Select
{
[Measures].[CA], [Measures].[Frais de port]
} on columns,
{
[Type de grossiste]
} on rows
from [Cube2]
33
3. Publication du cube à partir de PSW
Fichier/Publish
Dossier créé dans
Pentaho BI Server
Nom de la chaine de
connexion
34
MDX
MultiDimensional Expressions
Présentation
• Langage de requêtes sur les bases de données
OLAP, il comprend des instructions de
manipulation de données et des instructions de
définition de données
• Développé par Microsoft en 1997.
• Adopté par les plus importants éditeurs de
solutions BI
– SAP (Business Objects)
– Oracle
– IBM Cognos
– Mondrian (Pentaho)
– …
Exemples
• Measures est considérée comme une dimension spéciale qui
regroupe l’ensemble des mesures (faits) du cube.
• Il faut préciser les membres à afficher sur les colonnes(ON
COLUMS, ON Axis(0) ou ON 0, et les membres à afficher sur
les lignes(ON ROWS, ON AXIS(1) ou ON 1).
• Exemple 1: afficher toutes les ventes:
SELECT Measures.[sales amount] ON COLUMNS,
Client.[State Province Name] ON ROWS
FROM [Adventure Works DW]
Exemples
Ventes par pays:
SELECT [Measures].[sales amount] ON COLUMNS,
[Client].[State Province Name].MEMBERS ON ROWS
FROM [Adventure Works DW]
Pour obtenir les valeurs non nulles
uniquement il faut précéder [Client].[State
Province Name] par NON EMPTY.
Pour ne pas afficher le membre ALL,
utiliser CHILDREN à la place de
MEMBERS.
Exemples
• Si vous sélectionnez plusieurs colonnes alors ils doivent être séparées
par des virgules et regroupés par des accolades {} (on crée alors un
ensemble.)
SELECT { Measures.[sales amount] , [Measures].[Order Quantity]} ON
COLUMNS,
NON EMPTY Client.[State Province Name].CHILDREN ON ROWS
FROM [Adventure Works DW]
Total des ventes et quantité commandée par ville et par couleur de
produit: les membres de lignes doivent être entre ( ) (on crée alors un
tuple).
Le même résultat peut être obtenu en utilisant le produit des deux
dimenssions:
SELECT [Link]
{ Measures.[sales * [Produit].[Color].CHILDREN
amount] , [Measures].[Order Quantity]} ON
COLUMNS,
NON EMPTY (Client.[State Province Name].CHILDREN ,
[Produit].[Color].CHILDREN )ON ROWS
FROM [Adventure Works DW]
Tri
Pour ordonner les lignes par ordre croissant (décroissant ) des ventes par
pays, il faut utiliser la fonction ordrer (ASC ou DESC)
SELECT Measures.[sales amount] ON COLUMNS,
NON EMPTY order(Client.[State Province Name].CHILDREN , Measures.[sales
amount]
,ASC) ON ROWS
FROM [Adventure Works DW]
Tri
Ventes par pays et par couleur de produit (tri ascendant)
SELECT [Measures].[Sales Amount] ON COLUMNS,
NON EMPTY Order(
[Client].[State Province Name].CHILDREN * [Produit].[Color].CHILDREN,
[Measures].[Sales Amount],ASC)
ON ROWS
FROM [Adventure Works DW]
La fonction order est par défaut hiérarchique, il
réorganise d’abord les lignes en fonction de
leur position dans la hiérarchie, puis elle
ordonne chaque niveau, pour ignorer la
hiérarchie utilisez BASC, ou BDESC
Afficher le total des ventes Internet par classe de produit:
(H=Haute, M=Moyenne, L=Basse)
SELECT Measures.[sales amount] ON COLUMNS,
{[Link].H,[Link].L, [Link].M} ON ROWS
FROM [Adventure Works DW]
Pour obtenir le même résultat, comme dans une feuille de calcul en
utilisant la première et la dernière valeur séparées par « : »:
[Link].H:[Link].M
Filtre
• La clause prend en argument un tuple qui définit la
condition du filtre.
SELECT Measures.[sales amount] ON COLUMNS,
{[Link].H,[Link].L, [Link].M} ON ROWS
FROM [Adventure Works DW]
WHERE [Client].[French Country Region Name].FRANCE
SELECT { [Measures].[Sales Amount], [Measures].[Order Quantity]} ON
COLUMNS,
Filter([Client].[French Country Region Name].CHILDREN,[Measures].[Sales
Amount]>5000000 AND [Measures].[Order Quantity]> 250)ON ROWS
FROM [Adventure Works DW]
Exercices
Ecrire les requêtes MDX suivantes:
Ex1: Nombre de ventes par Gamme et par Pays de client.
Ex2: Nombre de ventes par Canal et par Pays de client
Ex3: Montant des ventes par Gamme et par Pays de
Commercial
Ex4: Afficher le montant (Prix) et le nombre de ventes par
mois (de l’année 2004) et par type de produit.
Exercices
Ecrire les requêtes MDX suivantes:
Ex1: Nombre de ventes par Gamme et par Pays de client.
Ex2: Nombre de ventes par Canal et par Pays de client
Ex3: Montant des ventes par Gamme et par Pays de
Commercial
Ex4: Afficher le montant (Prix) et le nombre de ventes par
mois (de l’année 2004) et par type de produit.
