Entrepôts de Données

Introduction

Pour remplir un entrepot de données, il faut :

  * une étape d’extraction (des données pertinentes des les bases de production) ;

  * une étape de transformation (nettoyage, formattage, premières agrégations et reconnaissance des membres) ;

  * une étape de chargement (des données propres dans la base décisionnelle).

En anglais on parle de phase ETL pour Extraction, Transformation and Loading 20.

La fréquence `a laquelle les phases ETL sont opérées doit ˆetre cohérente avec le grain de la dimension temporelle et doit permettre d’historiser les données avant qu’elles ne soient purgées des bases de production. Le remplissage initial des données à la création de l’entrepˆot est généralement facile. C’est historiser périodiquement et automatiquement les données qui pose problème.

En effet, les sources de données sont généralement multiples et gérées par différents systèmes (géographiquement

répartis dans différents sites), ce qui rend la phase ETL bien souvent très problématique.

Chaque situation rencontrée est très spécifique et l’architecture ETL mise en place est souvent dédiée à l’entreprise.

D’ailleurs le marché des outils ETL (qui sont très nombreux), est particulièrement morcelé. Il est

malgré tout dominé par :

* Informatica (PowerMart/Center)

* Ascential (DataStage)

* SAS (Warehouse Administrator).




Suivent les fournisseurs de SGBD :

* Oracle (Warehouse Builder)

* IBM (DataWarehouse Manager)

* Microsoft (DTS),

* Cognos et Hummingbird (parmi tant d’autres).







==== Data Transformation Services (DTS) ====










DTS est un outil fourni avec SQL Server. Il peut se connecter en lecture et en écriture :

* aux logiciels Microsoft, évidemment : SQL Server, Access, Excel et Visual FoxPro ;

* à d’autres SGBD : Corel Paradox, dBase, Oracle et mˆeme IBM DB2 ;

* à des fichiers textes ou des fichiers HTML.







DTS peut donc transférer les données non seulement d’une base SQL Server vers une base SQL Server, mais aussi d’une base DB2 vers une base Oracle, par exemple.

Cet outil permet non seulement de transférer les données, les nettoyer, les transformer, les fusionner et/ou les séparer. On entend par transformation tout calcul numérique ou toute opération sur chaˆines de caractères par exemple. Ces transformations peuvent ˆetre programmées dans des scripts SQL, Visual Basic, Java ou Perl et donc ˆetre extrˆemement complexes.

Une action DTS s’appelle un lot. Un lot DTS peut ˆetre exécuté régulièrement et automatiquement (en collaboration avec un autre service, SQL Server Agent, qui permet de planifier les opérations sur SQL

Server).

Un lot peut comporter plusieurs tˆaches que l’on peut enchaˆiner (sur succès ou sur échec de la tˆache

précédente) et dont on peut détecter et gérer les erreurs.

Pour nous, il s’agit d’utiliser DTS comme outil d’extraction périodique de données depuis les bases de

production vers la base décisionnelle. Parmi les tˆaches disponibles, nous intéressent plus particulièrement :

– les connexions aux données en lecture et en écriture ;

20. l’ETL fait partie des logiciels qui permettent aux applications hétérogènes de communiquer entre elle (middleware) au

mˆeme titre que l’EAI (Enterprise Application Integration) qui permet de maintenir `a jour les données entre les applications

en temps réel




* l’exécution de scripts paramétrés (essentiellement en SQL) ;

* l’exécution de sous-lots (ce qui permet de décomposer le lot ETL très complexe en lots extraction, transformation, chargement et traitement, plus simples) ;

* le traitement des cubes.







DTS offre également la possibilité d’utiliser des variables globales visibles par toutes les tˆaches. Pour

l’ETL, deux variables globales sont indispensables : la date de début et la date de fin qui délimite la

période de temps `a laquelle l’ETL s’applique. Typiquement, la date de début est la date de fin de la

précédente exécution du lot et la date de fin est la date courante.

Pour un approfondissement de cet outil, le lecteur est invité `a consulter [1] et [16].

17.2 Base tampon

étant donné qu’elles ont lieu pendant les plages horaires peu occupées (la nuit), l’ETL des données

OLTP pour le système OLAP entre en concurrence avec les sauvegardes des bases de production. Il faut

donc que cette phase perturbe le moins longtemps possible les systèmes OLTP. Pour cela, il faut que :

– l’extraction prenne le moins de temps possible ;

– les transformations n’aient pas lieu en mˆeme temps que l’extraction et pas sur le mˆeme serveur que

les systèmes OLTP;

Bref, les données extraites doivent atterrir sur une autre base, appelée base tampon (staging area).

Une fois l’étape d’extraction terminée, les transformations nécessaires. peuvent ˆetre effectuées tranquillement

dans la base tampon.

Il ne faut pas non plus que le système OLAP ne soit perturbé par la phase ETL (en particulier, par

l’étape de transformation). Autrement dit, cette base tampon ne doit pas ˆetre la base décisionnelle et

doit ˆetre gérée par un serveur dédié `a l’ETL (cf. figure 20).

Fig. 20 – Les étapes du processus ETL




17.3 étapes ETL

Détaillons maintenant les étapes de la figure 20.




=== Extraction ===

Pour que l’étape d’extraction dure le moins longtemps possible, il faut que :

– la requˆete de sélection ne comporte aucune jointure (il faut donc extraire les tables une par une) ;

– les données soient insérées dans des tables temporaires (elles n’ont aucune contrainte, aucun déclencheur

et aucun index).

Par ailleurs, il est bon que dans les systèmes OLTP, chaque table concernée par l’extraction (clients,

produits, etc.) soit munie d’une colonne pour la date de création et une autre pour la date de dernière

modification. Sans ces colonnes, on serait obligé d’extraire toutes les lignes et il serait compliqué de

déterminer (dans la base tampon) les lignes réellement modifiées depuis la dernière extraction. Avec ces

colonnes, l’extraction peut ˆetre incrémentale 21.

Dans tous les cas, le volume de données `a extraire est important. Il y a toujours un choix `a faire entre

extraire toutes les lignes d’un coup (la méthode la plus rapide, mais comme cette transaction est non

atomique, la moindre erreur est fatale `a tout le processus) ou les extraire une par une (ce qui prend plus

de temps, mais permet de limiter l’effet d’une erreur ponctuelle).

Exemple d’extraction :

1 SELECT *

2 INTO clients_temporaire1

3 FROM SERVEROLTP1.BaseProduction1.dbo.clients AS a

4 WHERE a.derniere_modification BETWEEN @debut AND @fin

5

6 SELECT *

7 INTO clients_temporaire2

8 FROM SERVEROLTP2.BaseProduction2.dbo.clients AS a

9 WHERE a.last_modification_date BETWEEN @debut AND @fin

10

11 SELECT *

12 INTO commandes_temporaire1

13 FROM SERVEROLTP1.BaseProduction1.dbo.commandes AS a

14 WHERE a.date BETWEEN @debut AND @fin







=== Transformation ===




Ce n’est pas parce que les données proviennent de bases de production qui fonctionnent rigoureusement

bien, que ces données sont valides pour le système décisionnel. Il faut presque toujours les transformer.

Les transformations se font en deux temps :

– d’abord, pendant le passage des données des tables temporaires aux tables tampon ;

– ensuite, des modifications sont apportées au sein des tables tampon en vue du chargement.

21. notons que l’extraction peut également ˆetre incrémentale si l’on utilise les informations contenues dans le journal des

transactions

17 ALIMENTATION DE L’ENTREP ˆOT 78

Tables tampon

`A

ce stade, la base tampon ne contient que des tables temporaires identiques aux tables source.

L’étape de transformation consiste `a consolider ces données dans les véritables tables de la base tampon

(cf. figure 21).

`A

chaque table de la base décisionnelle correspond une table tampon qui contient :

– les colonnes de la table de dimension ou de faits correspondante ;

– les clés naturelles et les clés de substitution ;

– une colonne exists de type oui ou non qui dira si le membre existe déj`a ou non ;

Ces tables tampon sont dépourvues de contraintes, notamment toutes ces colonnes autorisent la valeur

vide.

Fig. 21 – Tables temporaires et tables tampon

Remarques :

– pour faciliter le passage des tables temporaires aux tables tampon, il convient de supprimer, au

préalable, les index sur les tables tampon ;

– comme les bases de production sont multiples, il peut y avoir plusieurs tables temporaires qui

concerne les produits, par exemple ; donc l’insertion dans la table tampon qui concerne les produits

se fait en plusieurs fois ;

– la base tampon est totalement dépourvue de relation car on ne peut assurer ni l’intégrité des entités

ni l’intégrité référentielle, puisque les données source ne sont pas encore consolidées ;

– notons qu’il y a souvent un travail de jointure `a faire sur les tables temporaires pour insérer les

données dans les tables tampon ;




– notons enfin que si le grain des bases de production est plus fin que celui de la base décisionnelle,

les premières agrégations nécessaires peuvent ˆetre effectuées `a ce moment-l`a.

Réparation, complétion, synchronisation et formattage

Pendant que les données sont insérées dans les tables tampon, on peut les uniformiser, c’est-`a-dire les

réparer, les compléter, les synchroniser et les formatter.

Exemple de réparation des données : les codes postaux invalides peuvent ˆetre corrigés en utilisant un

annuaire des codes postaux.

Exemple de complétion des données : déduire la région o`u est domicilié un propriétaire `a partir du

numéro d’immatriculation de son véhicule.

Rappelons que les bases de production n’utilisent pas forcément la mˆeme horloge. Il faut donc synchroniser

toutes les dates contenues dans les tables temporaires pendant leur transfert dans les tables tampon.

Par ailleurs, quand les données arrivent dans la base tampon, elles ne sont pas toutes au mˆeme format

et ne respectent pas forcément le format de la base décisionnelle (généralement, les contraintes sur les

chaˆines de caractères ne sont pas les mˆemes dans toutes les bases et les codages de date sont hétérogènes).

Il faut donc uniformiser les formats avant le chargement, c’est le formattage.

Exemple d’hétérogénéité : selon leur provenance, les noms de clients peuvent arriver sous la forme

de deux colonnes (nom et prenom) ou sous la forme d’une colonne (nom + prenom, nom + initiale +

prenom). Un autre exemple classique concerne les adresses : il y a quasiment autant de formats que de

bases.

Exemple d’insertion dans une table tampon `a partir d’une table temporaire :

1 INSERT clients_tampon

2 (cle_naturelle, source, NomPrenom, ville, region, pays, date_creation)

3 SELECT a.clt_num, ’SERVEROLTP1.BaseProduction1’, -- pour la substitution

4 a.nom + ’ ’ + a.prenom, -- formattage

5 b.ville, b.region, a.pays, -- completion

6 DATEADD(hour, -3, a.date_creation) -- synchronisation

7 FROM clients_temporaire1 AS a

8 JOIN CodesPostaux AS b ON (a.CodePostal = b.CodePostal)

Notons qu’un client peut ˆetre inséré en double dans sa table tampon, s’il figure dans deux bases de

production. Les doublons sont gérés par le paragraphe suivant. De plus, deux clients différents mais qui

portent la mˆeme clé naturelle, sont distinguables par leur source (et leurs attributs).

Substitution des clés primaires

Une fois que les tables tampons sont remplies, on peut supprimer les tables temporaires et s’occuper

de l’intégrité des données qui vont ˆetre chargées dans la base décisionnelle. Pour rendre la substitution, la

validation et le chargement le plus rapide possible, il convient de re-créer les index sur les tables tampons.

Rappelons que la base décisionnelle n’utilise pas les clés naturelles des bases de production car :

– un produit peut ˆetre identifié dans deux bases de production différentes avec des clés distinctes ;

– un numéro de produit peut correspondre `a deux produits distincts dans deux bases de production

différentes.




Au contraire, pour identifier les membres de manière unique, la base décisionnelle utilise des clés de

substitution (surrogate keys).

Au cours de l’étape de transformation, il faut donc traduire (lookup) les clés naturelles en clés de

substitution et remplir la colonne exists. Si les bases de production ne sont pas pourvues d’une colonne

pour la date de création et une colonne pour la date de dernière modification, alors cette traduction implique

la recherche du membre correspondant (en fonction de leurs attributs dans la base décisionnelle)

pour chaque ligne extraite. Non seulement c’est très coˆuteux mais en plus, cela perturbe le système OLAP.

Exemple de lookup pour les clés primaires de substitution :

1 -- recherche des membres deja presents

2 UPDATE clients_tampon

3 SET exists = 1, cle_substitution = b.cle

4 FROM clients_tampon AS a

5 JOIN SERVEROLAP.BaseDecisionnelle.dbo.clients_dimension AS b ON

6 (

7 ISNULL(a.NomPrenom, ’’) = ISNULL(b.NomPrenom, ’’) AND

8 ISNULL(a.pays, ’’) = ISNULL(b.pays, ’’) AND

9 ISNULL(a.region, ’’) = ISNULL(b.region, ’’) AND

10 ISNULL(a.ville, ’’) = ISNULL(b.ville, ’’)

11 ) -- pays et region sont utiles pour distinguer les villes homonymes

12

13 -- nouvelle cle pour les nouveaux membres

14 UPDATE clients_tampon

15 SET cle_substitution = NEWID()

16 WHERE exists IS NULL

Notons que les clients insérés en double dans la table clients tampon, possèdent désormais une clé

de substitution unique (sauf les nouveaux membres) et que les clients distincts mais avec la mˆeme clé

primaire, possèdent désormais deux clés de substitution distinctes.

Dérive dimensionnelle

Notons aussi, que si un client change de ville, il faut faire un choix entre :

– changer la ville de ce membre (et donc changer les agrégats de cette ville), ce qui n’est pas recommand

é ;

– ou insérer un nouveau membre pour ce client et sa nouvelle ville (c’est le cas dans l’exemple

précédent).

On peut en venir `a introduire un numéro de version `a chaque membre et une date de création pour

chaque version. La clé primaire de la table de dimension est alors composée de deux colonnes : la clé de

substitution et le numéro de version. C¸ a n’est pas la solution que nous avons retenue ici.

Substitution des clés étrangères

Il reste encore `a traiter l’intégrité référentielle des données qui vont ˆetre chargées dans la base

décisionnelle. Pour cela, il faut recalculer les clés étrangères avec les clés de substitution afin que les

relations de la base décisionnelle soient vérifiées lors du chargement (cf. figure 22).




Fig. 22 – ´ Evolution des tables clients et commandes au cours du processus ETL

Malheureusement, certaines clés étrangères peuvent ˆetre introuvables. Dans toutes les tables de dimension,

il faut donc prévoir un membre 0 qui permet de traiter ces relations invalides 22. Ce membre 0

est créé une bonne fois pour toutes dans chaque table de dimension de la base décisionnelle :

1 -- sur le server OLAP et dans la base decisionnelle

2 INSERT clients_dimension (cle, NomPrenom, pays, region, ville)

3 VALUES (0, ’autre’, ’autre’, ’autre’, ’autre’)

22. la numérotation des membres valides doit donc commencer `a 1

17 ALIMENTATION DE L’ENTREP ˆOT 82

Exemple de lookup pour les clés étrangères de substitution (dans la base tampon) :

1 -- traitement des relations valides

2 UPDATE commandes_tampon

3 SET client_cle_substitution = b.cle_substitution

4 FROM commandes_tampon AS a

5 JOIN clients_tampon AS b ON

6 (

7 a.cmd_clt = b.clt_num AND

8 a.source = b.source

9 )

10

11 -- traitement des relations invalides

12 UPDATE commandes_tampon

13 SET client_cle_substitution = 0

14 WHERE client_cle_substitution IS NULL

Remarque : la phase de substitution est plus simple pour un schéma en étoile que pour un schéma en

flocon. Il faut en tenir compte lors de la conception de la base décisionnelle.




=== Chargement ===

Comme les données sont chargées dans la base décisionnelle qui est muni d’un schéma relationnel, il faut charger ses tables dans cet ordre :

- d’abord les tables qui ne contiennent aucune clé étrangère ;

– ensuite les tables qui ne contiennent que des clés étrangères vers des tables déj`a chargées ;

– etc.



Ensuite, pour chaque table, le chargement de décompose en deux requˆetes :

– une pour les nouveaux membres ou faits ;

– et une pour les membres ou faits modifiés.



Exemple de chargement dans une table de dimension :

<code>

1 -- chargement des nouveaux membres

2 INSERT SERVEROLAP.BaseDecisionnelle.dbo.clients_dimension

3 SELECT cle_substitution, NomPrenom, ville, region, pays, age, profession

4 FROM clients_tampon

5 WHERE exists IS NULL

6

7 -- modification des anciens membres

8 UPDATE SERVEROLAP.BaseDecisionnelle.dbo.clients_dimension

9 SET age = a.age,

10 profession = a.profession

11 FROM clients_tampon AS a

12 JOIN SERVEROLAP.BaseDecisionnelle.dbo.clients_dimension AS b

13 ON (a.cle_substitution = b.cle)

14 WHERE a.exists = 1

</code>

Exemple de chargement dans une table des faits :

<code>

1 -- chargement des nouveaux faits

2 INSERT SERVEROLAP.BaseDecisionnelle.dbo.commandes_faits

3 SELECT cle_substitution, client_cle_substitution, date

4 FROM commandes_tampon

5 WHERE exists IS NULL

6

7 -- modification des anciens membres

8 UPDATE SERVEROLAP.BaseDecisionnelle.dbo.commandes_faits

9 SET client_cle_substitution = a.client_cle_substitution

10 date = a.date

11 FROM commandes_tampon AS a

12 JOIN SERVEROLAP.BaseDecisionnelle.dbo.commandes_faits AS b

13 ON (a.cle_substitution = b.cle)

14 WHERE a.exists = 1

</code>

=== Traitement === 

Le traitement des cubes intervient juste derrière la phase ETL proprement dite. Il d’agit d’une opération entièrement réalisée par Analysis Services, mais il s’agit d’une tˆache que l’on peut inclure dans le même lot DTS que l’ETL.

Hormis pour le remplissage initial de l’entrepˆot, le traitement des cubes doit ˆetre incrémental. Pour cela, il est conseillé de partitionner le cube avec notamment une partition séparée pour l’année en cours, afin de ne pas traiter les autres années (qui, normalement, ne sont pas touchées par les modifications).

Structurer les données en cube 

On sent bien que la table est un objet bidimensionnel mal adapté `a l’agrégation de données dès que le

groupement porte sur deux colonnes ou plus. L’état offre une première solution, mais la feuille A4 (mˆeme

orientée en paysage) est rapidement saturée quand le nombre de niveau de groupement augmente et la

navigation dans un état de 200 pages est fastidieuse.

==== Définition d’un cube ====

On préférerait donc un objet :

– qui aurait autant de dimensions qu’il y a de colonnes de groupement (clients et pays dans notre

exemple précédent) ;

– dont chaque dimension détaillerait les valeurs possibles (France et Italie pour la dimension pays) ;

– dans lequel, `a l’intersection d’une valeur dans chaque dimension (France et 0001 par exemple) on

trouverait une unique cellule ;

– une cellule qui contiendrait toutes les valeurs agrégées pour ce sous-groupe (c’est-`a-dire les mesures

tel que le nombre de commandes et le montant total) ;

– et aussi les agrégats des ces valeurs pour les sous-groupes supérieurs.

C’est cet objet que l’on appelle cube.

Par exemple, organisons, en cube, les données de la requˆete suivante :

1 SELECT a.cmd_clt, b.btq_pays, COUNT(cmd_num), SUM(cmd_montant)

2 FROM commandes AS a

3 JOIN boutiques AS b ON a.cmd_btq = b.btq_num

4 GROUP BY a.cmd_clt, b.btq_pays WITH CUBE

Résultat :

Fig. 11 – Cube bidimensionnel

Remarque : le terme de cube ne présupposant ni du nombre de dimensions ni de l’égalité des longueurs

des dimensions (cf. figure 11).

En MDX, pour créer ce cube il suffit d’écrire :

1 CREATE CUBE ClientsPays

2 (

3 DIMENSION clients

4 DIMENSION pays

5 MEASURE NbCommandes FUNCTION COUNT

6 MEASURE [Montant total] FUNCTION SUM

7 )

Remarque : on dispose bien évidemment de DROP CUBE et de ALTER CUBE18.

Mesures

Les mesures sont généralement additives (soit une somme, soit un dénombrement).

Il ne faut stocker dans le cube que les mesures primitives :

– le prix TTC peut ˆetre calculé `a partir du prix hors taxe, ce n’est donc pas la peine de le stocker ;

– il en va de mˆeme pour les prix en dollars `a partir des prix en euros ;

18. on laisse au lecteur le soin de se renseigner auprès de l’aide en ligne pour connaˆitre les possibilités de ALTER CUBE

15 STRUCTURER LES DONNéES EN CUBE 65

– d’un montant `a partir d’un prix unitaire et d’une quantité ;

– etc.

Dans Analysis Services, les mesures non primitives peuvent ˆetre calculées avec les membres calculés

(cf. §18.4.1 page 91).

Ceci dit, si les règles de calcul des mesures non primitives sont compliquées ou si elles changent souvent

(c’est le cas des conversions euro/dollar, par exemple), alors il vaut mieux stocker une mesure de plus.

15.2 Hiérarchie

Complexifions progressivement la définition d’un cube.

15.2.1 Niveaux

Avec la structure de cube, on peut détailler chaque dimension en plusieurs niveaux. Par exemple,

une dimension générale geographie peut permettre des groupements selon un pays, une region ou une

ville. On voit alors apparaˆitre une hiérarchie dans les dimensions que l’on dépliera et pliera `a volonté,

selon nos besoins d’agrégation.

On peut prendre comme illustration un cube ventes `a trois dimensions (cf. figure 12) :

– une dimension geographie comprenant plusieurs pays qui se décomposent chacun en plusieurs

régions qui regroupent elles-mˆemes plusieurs villes dans lesquelles se situent les boutiques ;

– une dimension produits dans laquelle les articles sont regroupés en gammes puis en marques ;

– et une dimension temporelle détaillée en années, mois et jours.

Fig. 12 – Cube ventes hiérarchisé

La syntaxe MDX pour déclarer ces dimensions hiérarchisées est

– pour les dimensions standards :

1 DIMENSION geographie

2 LEVEL tout TYPE ALL, -- mettre ce niveau a chaque fois

3 LEVEL pays,

4 LEVEL region,

5 LEVEL ville

6

7 -- on separe les niveaux par des virgules mais pas les dimensions

8

9 DIMENSION produits

10 LEVEL tout TYPE ALL, -- du plus agrege

11 LEVEL marque,

12 LEVEL gamme,

13 LEVEL article -- au plus detaille

– et pour la dimension temporelle :

1 DIMENSION temps

2 LEVEL tout TYPE ALL,

3 LEVEL annee TYPE YEAR,

4 LEVEL mois TYPE MONTH,

5 LEVEL jour TYPE DAY

Remarques :

– le niveau ALL un niveau formel qui regroupe tous les autres ;

– s’il y a ambigu¨ité sur le nom des niveaux, il suffit de préciser la dimension concernée selon la

syntaxe : dimension.niveau (exemple : produits.articles) ;

– dans une dimension hiérarchisée, les données du niveau le plus bas sont issues des bases de production

;

– mais le cube peut aussi stocker des données aux niveaux supérieurs (il s’agit le plus souvent de

données agrégées, mais pas toujours).

Le niveau le plus bas d’une dimension s’appelle le grain de la dimension (le grain de la dimension

geographie c’est la ville). Toutes les dimensions et leur grain doivent ˆetre choisis dès le début et doit

rester inchangés pendant toute la durée de vie du cube.

15.2.2 Membres

Les différentes valeurs d’un niveau sont appelées membres. Par exemple, les membres du niveau pays

sont France, Italie, Allemagne et Espagne. Un membre de niveau supérieur regroupe des membres du

niveau immédiatement inférieur, ce sont ses enfants. Par exemple, les enfants du membre France sont

PACA, Rhones-Alpes et Corse.

Remarque : un membre d’un niveau supérieur (une région) peut posséder des données (son ensoleillement,

par exemple) et pas seulement des agrégats (montant total des ventes dans cette région).

15 STRUCTURER LES DONNéES EN CUBE 67

15.2.3 Hiérarchies multiples

Certaines dimensions peuvent ˆetre organisées selon plusieurs hiérarchies.

C’est classiquement le cas de la dimension temporelle qui peut non seulement ˆetre organisée en :

– jours, mois, trimestres et années ;

– mais aussi en jours et semaines ;

– ou encore en jours et saisons.

Cela peut également se produire pour n’importe quelle dimension (cf. la dimension produits sur la figure

16).

Analysis Services ne permet malheureusement pas `a une dimension de posséder plusieurs hiérarchies.

La technique pour les représenter malgré tout consiste alors simplement :

– soit `a introduire plusieurs dimensions (avec une hiérarchie chacune), ce qui n’est pas recommandé ;

– soit `a utiliser les propriétés de membre (cf. §19.1 page 96) et les dimensions virtuelles.

15.3 Normalisation d’un cube

Le schéma relationnel d’un cube (cf. section suivante) n’a pas besoin de respecter la troisième forme

normale (cf. [?]), ce qui permet d’utiliser des schémas en étoile. Par contre, quelques règles (tirées de [4])

doivent ˆetre respectées lors de la construction de chaque cube :

Règle 1 : dans un cube, deux membres appartenant `a deux dimensions différentes doivent ˆetre

indépendants .

Autrement dit, s’il n’y a qu’un vendeur par produit, il faut fusionner les dimensions produits et

vendeurs.

Règle 2 : dans un cube, tous les faits doivent dépendent de toutes les dimensions.

Autrement dit, les ventes (qui dépendent du produit, du jour, du client et de la ville) et les coˆuts de

développement (qui ne dépendent que du produit) définissent deux types de faits distincts (et conduisent

donc `a deux cubes distincts).

Règle 3 : dans un cube, toutes les mesures doivent respecter le grain du cube.

Si la marge n’est définie que par région et par mois, tandis que le montant des ventes le sont par



ville et par jour, alors il ne faut pas chercher `a les faire cohabiter par une division arithmétique mais les

séparer dans deux cubes distincts.

Règle 4 : la hiérarchie d’une dimension doit ˆetre strictement arborescente.

Ce n’est pas le cas d’une dimension organisation dans laquelle :

– les agences sont regroupées administrativement en divisions ;

– les agences sont regroupées géographiquement en établissements, mˆeme si elles appartiennent `a des

divisions différentes ;

– les divisions sont regroupées en directions régionales ;

– les établissement sont également regroupés en directions régionales.

Il faut alors utiliser deux hiérarchies pour cette dimension :

– une dont les niveaux sont : agences, divisions et directions régionales ;

– et une autre dont les niveaux sont : agences, établissement et directions régionales .

Lorsqu’un cube vérifie ces quatres règles, on dit qu’il est en forme dimensionnelle normale.




===== Interroger un cube =====










Avant de découvrir comment consulter un cube, définissons quelques opération de navigation dans




un cube :




– le dépliage (drilldown) qui consiste `a passer `a un niveau inférieur dans une dimension ;




– le pliage (drillup ou rollup) qui consiste `a passer `a un niveau supérieur dans une dimension ;




– le tranchage ou découpage (slice) qui consiste `a ne garder qu’une tranche du cube (une tranche




étant une coupe du cube selon un membre) ;




– le cubage (dice) qui s’intéresse `a plusieurs tranches `a la fois ;




– et la rotation qui consiste `a choisir les dimensions que l’on veut en colonnes et en lignes.




Avec ces cinq opérations, on peut naviguer dans les données (i.e. pratiquer le data surfing). Il faut




y ajouter une sixième opération : le drillthrough (traduit maladroitement par extraction dans Analysis




Services, le terme de désagrégration étant préférable) qui permet de retrouver les données qui ont permis




de calculer un agrégat.










18.1 Requˆetes MDX




La syntaxe pour rédiger une requˆete MDX est la suivante :




WITH certaines notations




SELECT les colonnes, les lignes et les autres axes




FROM le cube




WHERE les dimensions tranchées et leur tranche (au singulier)




CELL PROPERTIES les informations contextuelles voulues pour les cellules










Le résultat d’une telle requˆete est un tableau multidimensionnel dont les cellules ne contiennent

qu’une valeur (contrairement aux cubes) et dans lequel on peut naviguer avec les opérations décrites

précédemment.

Parmi les dimensions du cube initial, certaines servent d’axes (un axe contient les membres issus d’un

dépliage et/ou d’un cubage) pour le résultat (clause SELECT) et d’autres de tranchage (clause WHERE)

mais pas les deux `a la fois.

Pour MDX, les mesures constituent une dimension, et peuvent donc faire l’objet d’un axe ou d’un

tranchage.

Affichons par exemple, le montant des ventes, avec en colonne l’année 2002 et en ligne la région PACA :

<code>

1 SELECT {temps.annee.[2002]} ON COLUMNS,

2 {geographie.region.PACA} ON ROWS

3 FROM ventes

4 WHERE (measures.montant)

</code>




Le résultat de cette requˆete est le suivant :




2002




PACA 56986.12




Remarques :




– n’oublier ni les accolades dans la clause SELECT ni les parenthèses dans la clause WHERE ;




– rappel : dès qu’un intitulé contient une espace, un accent ou commence par un chiffre, il faut le




délimiter par des crochets ;




– si l’intitulé utilise un crochet fermant ], employer les doubles crochets fermants :

Bill [William]] Clinton]

– pour des raisons qui deviendront claires au §18.4.2 page 93, si l’intitulé contient une quote ’, alors

il faut la doubler :

[k’’s Choice]

– la syntaxe complète des noms de membres dans un cube est :

dimension . niveau . membre
– s’il n’y a pas de confusion possible on peut omettre la dimension et/ou le niveau ;

– si dans un mˆeme niveau plusieurs membres ont le mˆeme nom, préciser autant de parents que

nécessaire pour les distinguer :

dimension . [ancˆetre le plus ancien] ... [grand-père] . [père] . membre

18.1.1 Clause SELECT
La clause SELECT offre plusieurs possibilités. On peut afficher :
– plusieurs colonnes et plusieurs lignes :
1 SELECT {temps.annee.[1998], temps.annee.[2002]} ON COLUMNS,
2 {geographie.region.PACA, geographie.pays.France} ON ROWS
3 FROM ventes
4 WHERE (measures.montant)
Résultat : 2 colonnes et 2 lignes
1998 2002




PACA 133419.96 56986.12




France 1458311.45 248996.54

– tous les enfants d’un membre :

1 SELECT temps.annee.[1998].CHILDREN ON COLUMNS,

2 {geographie.region.PACA} ON ROWS

3 FROM ventes

4 WHERE (measures.montant)

Résultat : autant de colonnes qu’il y a d’enfants

Janvier Février Mars Avril ... Octobre Novembre Décembre

PACA 19856.45 11458.58 7589.47 8799.15 ... 11589.45 10569.65 38360.35

– tous les membres d’un niveau :

1 SELECT temps.annee.MEMBERS ON COLUMNS,

2 {geographie.region.PACA} ON ROWS

3 FROM ventes

4 WHERE (measures.montant)

Résultat : autant de colonnes qu’il y a de membres

1998 1999 2000 2001 2002

PACA 133419.96 121598.45 104789.56 89634.25 56986.12

– une plage de membres :

1 SELECT {temps.annee.[1998] : temps.annee.[2001]} ON COLUMNS,

2 {geographie.region.PACA} ON ROWS

3 FROM ventes

4 WHERE (measures.montant)

Résultat : autant de colonnes que d’années entre 1998 et 2001 (inclus)

1998 1999 2000 2001

PACA 133419.96 121598.45 104789.56 89634.25

18 INTERROGER UN CUBE 86

Remarques :

– il n’y a pas de confusion possible entre MEMBERS et CHILDREN puisque l’un s’applique `a un niveau,

l’autre `a un membre :

dimension . niveau . MEMBERS

dimension . niveau . membre . CHILDREN

– on peut aussi utiliser :

dimension . MEMBERS (les membres de tous les niveaux de la dimension)

dimension . CHILDREN (les membres du niveau le plus élevé)

– avant d’utiliser l’opérateur :, s’assurer de l’ordre dans lequel sont stockés les membres.

18.1.2 Mesures

On peut afficher plusieurs mesures `a la fois. Mais comme le résultat d’une requˆete n’autorise qu’une

valeur par cellule, il faut aligner les mesures selon un axe :

1 SELECT temps.annee.MEMBERS ON COLUMNS,

2 {measures.montant, measures.NbCommandes} ON ROWS

3 FROM ventes

Résultat : sur tous les articles et dans tous les pays

1998 1999 2000 2001 2002

montant 1133419.96 1121598.45 1104789.56 189634.25 156986.12

nb 2569 2107 1568 1474 978

Les mesures forment donc naturellement une dimension nommée measures dans chaque cube. On

précise quelle mesure afficher dans la clause WHERE quand on en veut qu’une (c’est une tranche du cube).

Et on est obligé d’en préciser au moins une, sans quoi une mesure est choisie par défaut.

18.1.3 Clause WHERE

On peut effectuer plusieurs découpes sur le cube. Reprenons par exemple la requˆete précédente :

1 SELECT temps.annee.MEMBERS ON COLUMNS,

2 {measures.montant, measures.NbCommandes} ON ROWS

3 FROM ventes

4 WHERE (produits.marque.Channel, geographie.pays.Italie)

Résultat : sur tous les articles de la marque Channel et pour l’Italie seulement

1998 1999 2000 2001 2002

montant 419.96 598.45 789.56 634.25 986.12

nb 69 107 68 74 78

Remarques :

– une dimension ne peut apparaˆitre qu’une fois dans la clause WHERE ;

– si on veut plusieurs tranches dans une dimension, il faut en faire un axe ;

– on ne peut utiliser dans la clause WHERE ni MEMBERS ni CHILDREN.

Le grand danger relatif aux découpes de cube, est que certaines applications MDX n’affichent pas les

tranches alors que c’est une information indispensable pour comprendre les valeurs des mesures.

18.1.4 Description des axes

Une requˆete MDX autorise jusqu’`a 127 axes, mais évidemment on ne peut pas dépasser le nombre

de dimensions du cube +1 (avec la dimension measures). Les cinq premiers axes sont : COLUMNS, ROWS,

PAGES, SECTIONS et CHAPTERS. Au-del`a il faut utiliser : AXIS(5), ..., AXIS(126).

Exemple `a quatre dimensions :

1 SELECT temps.annee.MEMBERS ON COLUMNS,

2 geographie.pays.MEMBERS ON ROWS,

3 produits.article.MEMBERS ON PAGES,

4 {measures.montant, measures.NbCommandes} ON SECTIONS

5 FROM ventes

On peut aussi créer un résultat ayant un seul axe :

1 SELECT temps.annee.MEMBERS ON COLUMNS

2 FROM ventes

3 WHERE (measures.montant)

Résultat : les intitulés de colonne et une seule ligne de résultats (pas d’intitulé de ligne)

1998 1999 2000 2001 2002

1133419.96 1121598.45 1104789.56 189634.25 156986.12

Ou mˆeme n’ayant aucun axe :

1 SELECT

2 FROM ventes

3 WHERE (measures.montant)

Résultat : une cellule contenant le montant total des ventes de toutes les années pour tous les produits

et partout (c’est un mauvais exemple)

3706428.34

18.1.5 MDX vs. SQL

Comme on vient de le voir, les requˆetes MDX ressemblent beaucoup aux requˆetes SQL de sélection.

Ceci dit, notons quand mˆeme des différences fondamentales :

– la syntaxe des clauses SELECT et WHERE n’a rien `a voir ;

– la clause FROM n’admet qu’un seul cube, en MDX;

– les clauses SQL GROUP BY, HAVING et ORDER BY n’existent pas en MDX;

– et il existe d’autres clauses spécifiques `a MDX (WITH et CELL PROPERTIES).

18 INTERROGER UN CUBE 88

18.2 Filtrage des données

Jusqu’`a maintenant, la clause SELECT n’a servi qu’`a sélectionner les membres que l’on peut désigner.

Si on veut faire intervenir un critère plus complexe, comme : ne garder que les membres dont une mesure

est dans une certaine plage, alors il faut utiliser la fonction MDX :

FILTER(les membres `a filter, (le critère))

Par exemple, pour n’afficher que les années pour lesquelles on a vendu pour plus de 100000 :

1 SELECT FILTER(temps.annee.MEMBERS, (measures.montant > 113000)) ON COLUMNS

2 FROM ventes

3 WHERE (measures.montant)

Résultat : il ne reste que les années concernées par le critère

1998 2001 2002

1133419.96 189634.25 156986.12

Remarques :

– la mesure dans la clause WHERE n’est pas obligatoirement la mˆeme que celle utilisée dans le filtre ;

– le critère peut faire appel aux fonctions MDX (cf. aide en ligne).

Exemple : pour ne garder que les pays qui ont vendu plus que la France, l’Italie et l’Allemagne

1 SELECT FILTER(geographie.pays.MEMBERS, (measures.montant >

2 MAX({France, Italie, Allemagne}, measures.montant) -- ici

3 )) ON COLUMNS

4 FROM ventes

5 WHERE (measures.montant)

Remarque : MDX offre toutes les fonctions d’agrégat (mais avec deux arguments) ainsi que MEDIAN.

Si le critère ne porte pas directement sur les agrégats des membres `a filtrer mais sur des valeurs plus

fines, alors il faut faire appel `a la notion de tuple pour décrire ces valeurs. Le tuple est la généralisation

de la notion de couple et de triplet, il correspond aux coordonnées des valeurs voulues dans le cube.

Exemple de tuple : le montant des ventes pour le mois de janvier 2002 se note par le couple :

([2002].janvier, measures.montant)

Autre exemple : le nombre de commandes du client Razibus en France et en 1999 se note par le

quadruplet :

(clients.Razibus, geographie.France, temps.[1999], measures.NbCommandes)

Remarques :

– l’ordre a parfois de l’importance dans un tuple ;

– les composantes d’un tuple doivent ˆetre issues de dimensions différentes.

Avec cette notation, on peut par exemple filtrer les articles pour lesquels les ventes ont augmenté

entre janvier 2001 et janvier 2002 :

1 SELECT FILTER(produits.article.MEMBERS,

2 (([2001].janvier, measures.montant) < ([2002].janvier, measures.montant))

3 ) ON COLUMNS

4 FROM ventes

5 WHERE (measures.NbCommandes) -- on n’est pas oblige d’afficher le montant

Remarque : les membres `a filtrer peuvent ˆetre définis en plusieurs fois. Par exemple, pour filtrer les

mois de 2001 et de 2002 pendant lesquels il a été vendu pour un montant supérieur `a 10 000 :

1 SELECT FILTER({[2001].CHILDREN, [2002].CHILDREN},

2 (measures.montant > 10000)) ON COLUMNS

3 ...

Remarque : on peut afficher les deux montants utilisés dans le filtre

1 SELECT FILTER(produits.article.MEMBERS,

2 (([2001].janvier, measures.montant) < ([2002].janvier, measures.montant))

3 ) ON COLUMNS,

4 {([2001].janvier, measures.montant), ([2002].janvier, measures.montant)}

5 ON ROWS

6 FROM ventes

Compléments

Sans utiliser la fonction FILTER, on peut éliminer simplement les membres qui ne contiennent pas de

données :

SELECT NON EMPTY {...} ON COLUMNS

Par ailleurs, on peut ne garder que les membres extremaux avec la fonction :

TOPCOUNT(les membres `a filtrer, le nombre voulu, (la mesure pour établir le classement))

Filtrons par exemple les 10 articles les plus vendus :

1 SELECT TOPCOUNT(article.MEMBERS, 10, (measures.NbCommandes))

2 ON COLUMNS

3 ...

Remarques :

– l`a aussi, on peut décrire les membres `a filtrer en plusieurs fois (en utilisant des accolades) ;

– l`a aussi, on peut employer un tuple en troisième argument ; exemple : les 10 articles les plus vendus

en 1998 :

1 SELECT TOPCOUNT(article.MEMBERS, 10, (measures.NbCommandes, [1998]))

2 ON COLUMNS

3 ...

– on a évidemment la fonction BOTTOMCOUNT (mˆeme syntaxe) ;

– il existe aussi TOPSUM et TOPPERCENT (cf. l’aide en ligne).

18 INTERROGER UN CUBE 90

18.3 Disposition des résultats

On peut encore rendre les requˆetesMDX plus complexe lorsque l’on chercher `a réorganiser les résultats.

18.3.1 Ordonner les axes

Pour ordonner les membres dans un axe et selon une mesure (et non pas par ordre alphabétique), on

utilise dans la clause SELECT la fonction :

ORDER(les membres `a trier, (la mesure selon laquelle trier), ASC ou DESC)

Exemple, pour ordonner les années de la plus lucrative `a la moins lucrative :

1 SELECT ORDER(annee.MEMBERS, (measures.montant), DESC) ON COLUMNS

2 ...

Remarques :

– on peut faire appel aux tuples pour désigner le critère de tri ; exemple, les années de la plus lucrative

`a la moins lucrative en France :

1 SELECT ORDER(annee.MEMBERS, (measures.montant, pays.France), DESC) ON COLUMNS

2 ...

– `a nouveau, on peut décrire les membres `a trier en plusieurs fois (en utilisant des accolades). Par

exemple, trions les mois de 2000 et de 2001 selon le montant des ventes de pinceaux :

1 SELECT ORDER({[2000].CHILDREN, [2001].CHILDREN},

2 (produits.pinceau, measures.montant), ASC) ON COLUMNS

3 ...

Mais le problème avec ASC et DESC est que la hiérarchie est respectée (dans notre exemple, les mois

de 2000 seront triés séparemment des mois de 2001). Pour ne pas tenir compte de la hiérarchie, il suffit

d’utiliser BASC et BDESC.

18.3.2 Axes pluridimensionnels

On a parfois besoin que le résultat ne comporte que deux axes (ne serait-ce que pour l’imprimer),

sans pour autant perdre la possibilité d’utiliser 3 dimensions ou plus dans la clause SELECT. La solution

consiste `a représenter plusieurs dimensions par axe. Pour cela, on utilise les tuples.

Exemple, présentons `a la fois les années et les pays en colonne :

1 SELECT {(France,[2000]), (France,[2001]),

2 (Italie,[2000]), (Italie,[2001])} ON COLUMNS

3 ...

Résultat : certains intitulés sont multicolonnes

France Italie

2000 2001 2000 2001

18 INTERROGER UN CUBE 91

Remarques :

– dans ce cas, l’ordre `a l’intérieur de chaque tuple a de l’importance ;

– les tuples doivent ˆetre homogènes (c’est-`a-dire présenter les mˆemes dimensions et dans le mˆeme

ordre).

Si on veut ensuite détaillé chaque année selon les produits A, B et C, on voit tout de suite que la clause

SELECT serait longue `a écrire (12 triplets). Heureusement, on peut générer les tuples par produit cartésien :

1 SELECT CROSSJOIN({France, Italie}, {[2000], [2001]}) ON COLUMNS

2 ...

3 -- donne la meme chose que precedemment

4

5 SELECT CROSSJOIN(CROSSJOIN({France, Italie}, {[2000], [2001]}),

6 {A, B, C}) ON COLUMNS

7 ...

Résultat : trois dimensions sur l’axe des colonnes

France Italie

2000 2001 2000 2001

A B C A B C A B C A B C

18.4 Clause WITH

Comme on l’a déj`a vu, les requˆetes MDX présentent une dernière clause, la clause WITH qui offre la

possibilité de définir certains objets avant le début du SELECT.

Les objets définis dans une clause WITH ne sont visibles que dans la clause SELECT qui suit. C’est

pourquoi Analysis Services propose également de les définir dans l’éditeur de cube afin qu’ils soient utilisables

par toutes les requˆetes.

Comme la syntaxe est la mˆeme, nous nous contentons ici d’utiliser la clause WITH.

18.4.1 Membres calculés

Un membre calculé est un membre supplémentaire dont la définition repose sur les membres déj`a

présents dans le cube. Il s’agit d’un calcul entre membres dont le résultat peut ˆetre utilisé comme un

membre `a part entière.

Exemple : `a partir des membres juillet 2001 et juillet 2002, on peut définir un membre calculé

qui représente la progression entre juillet 2001 et juillet 2002 ainsi :

1 WITH MEMBER temps.[de juillet 2001 a juillet 2002] -- le nom complet

2 AS ’temps.[2002].juillet - temps.[2001].juillet’ -- l’expression du calcul

3 SELECT {temps.[2001].juillet, temps.[2002].juillet

4 temps.[de juillet 2001 a juillet 2002]} ON COLUMNS

5 FROM ventes

6 WHERE (measures.montant)

18 INTERROGER UN CUBE 92

Résultat : la troisième colonne présente la progression du chiffre d’affaire entre juillet 2001 et juillet

2002

juillet 2001 juillet 2002 de juillet 2001 a juillet 2002

10186.12 9486.78 -699.35

Jusqu’ici on s’est contenté d’utiliser les mesures brutes (tel qu’elles sont stockées dans le cube). Si

on veut afficher des mesures plus complexes, il suffit de définir un membre calculé en fonction d’autres

mesures.

Si, par exemple, on dispose des mesures montant et quantite alors on peut définir le membre calculé

prix unitaire :

1 WITH MEMBER measures.prix_unitaire -- le nom complet

2 AS ’measures.montant / measures.quantite’ -- l’expression du calcul

3 SELECT article.MEMBERS ON COLUMNS

4 FROM ventes

5 WHERE (measures.prix_unitaire)

Les membres calculés ne sont pas retournés par défaut par la fonction MEMBERS, il faut utiliser la fonction

ADDCALCULATEDMEMBERS. Si on veut voir apparaˆitre le nouvelle mesure prix unitaire par exemple :

1 WITH MEMBER measures.prix_unitaire

2 AS ’measures.montant / measures.nb’

3 SELECT ADDCALCULATEDMEMBERS(measures.MEMBERS) ON COLUMNS

4 FROM ventes

5 WHERE (temps.[2001])

Remarques :

– les valeurs des membres calculés ne sont pas stockées dans le cube, mais calculées `a la volée (ce qui

ralentit raisonnablement les requˆetes) ;

– le membres calculés peuvent ˆetre utilisés dans la clause WHERE (c’est d’ailleurs la seule fa¸con d’effectuer

une tranche qui concerne plusieurs membres de base du cube).

Ordre de résolution

Il est parfois nécessaire de préciser dans quel ordre les membres calculés doivent ˆetre calculés. C’est

le cas notamment lorsque l’on combine pourcentage et différence.

Considérons l’exemple suivant :

1 WITH MEMBER temps.[de juillet 2001 a juillet 2002]

2 AS ’temps.[2002].juillet - temps.[2001].juillet’

3 MEMBER measures.[profit (en pourcentage)]

4 AS ’100 * (measures.montant - measures.cout) / measures.cout’

5 SELECT {measures.montant, measures.cout, measures[profit (\%)]} ON COLUMNS,

6 {temps.[2001].juillet, temps.[2002].juillet

7 temps.[de juillet 2001 a juillet 2002]} ON ROWS

8 FROM ventes

18 INTERROGER UN CUBE 93

Cette requˆete produit le résultat suivant : la dernière cellule ne contient pas forcément ce que l’on veut

montant cout profit (%)

juillet 2001 10186.12 8451.00 20

juillet 2002 9486.78 7569.50 25

de juillet 2001 a juillet 2002 -699.35 -881.5 -20

Pour obtenir la progression du profit en pourcentage il suffit de préciser que le membre calculé [de

juillet 2001 a juillet 2002] doit ˆetre calculé après le membre calculé [profit (%)] :

1 WITH MEMBER temps.[de juillet 2001 a juillet 2002]

2 AS ’temps.[2002].juillet - temps.[2001].juillet’,

3 SOLVE_ORDER = 2

4 MEMBER measures.[profit (en pourcentage)]

5 AS ’100 * (measures.montant - measures.cout) / measures.cout’,

6 SOLVE_ORDER = 1

7 SELECT {measures.montant, measures.cout, measures[profit (\%)]} ON COLUMNS,

8 {temps.[2001].juillet, temps.[2002].juillet

9 temps.[de juillet 2001 a juillet 2002]} ON ROWS

10 FROM ventes

Auquel cas nous obtenons bien le pourcentage recherché :

montant cout profit (%)

juillet 2001 10186.12 8451.00 20

juillet 2002 9486.78 7569.50 25

de juillet 2001 a juillet 2002 -699.35 -881.5 5

Mise en forme des membres calculés

D’autres options sont disponibles dans la clause MEMBER (cf. l’aide en ligne). Comme par exemple, une

description du format numérique `a employer :

1 WITH MEMBER measures.prix_unitaire

2 AS ’measures.montant / measures.quantite’,

3 FORMAT_STRING = ’#.## euros’

4 SELECT ...

18.4.2 Jeux nommés

Si un ensemble de membres est souvent utilisés dans des requˆetes MDX, il est intéressant de le définir

une bonne fois pour toutes et de lui donner un nom. C’est ce que l’on appelle un jeu (sous-entendu de

membres) nommé.

Si, par exemple, les 10 articles les plus vendus reviennent souvent alors on peut définir un jeu nommé

ainsi :

1 WITH SET MeilleursArticles -- le nom

2 AS ’TOPCOUNT(article.MEMBERS, 10, measures.quantite)’ -- l’expression

3 SELECT MeilleursArticles ON COLUMNS

4 FROM ventes

5 WHERE (measures.nb)

18 INTERROGER UN CUBE 94

Dans la définition d’un jeu nommé, on peut utiliser les fonctions ensemblistes UNION, EXCEPT et

INTERSECT. Exemple :

1 WITH SET MeilleursArticles

2 AS ’TOPCOUNT(article.MEMBERS, 10, (measures.quantite))’

3 SET ArticlesLesPlusCher

4 AS ’TOPCOUNT(article.MEMBERS, 10, (measures.prix_unitaire))’

5 SET MeilleursArticlesEtArticlesLesPlusCher

6 AS ’UNION(MeilleursArticles, ArticlesLesPlusCher)’

7 SET MeilleursArticlesSaufLesPlusCher

8 AS ’EXCEPT(MeilleursArticles, ArticlesLesPlusCher)’

9 SET MeilleursArticlesQuiSoientParmisLesPlusCher

10 AS ’INTERSECT(MeilleursArticles, ArticlesLesPlusCher)’

11 SELECT ...

18.4.3 Cellules calculées

Il existe un troisième objet que l’on peut définir dans la clause WITH, il s’agit des cellules calculées

(CELL CALCULATION). Mais cet objet est trop complexe pour entrer dans le cadre de ce document. Le

lecteur est donc dirigé vers l’aide en ligne et [12] pour découvrir cette notion.

18.4.4 Précisions

Si un intitulé comporte une quote ’, alors elle doit ˆetre doublée afin de ne par interférer avec la

délimitation de l’expression :

1 WITH MEMBER article.[Tous les albums de k’’s Choice]

2 AS ’SUM({[k’’s Choice - Cocoon Crash], [k’’s Choice - Paradise in me]})’

Si on désire introduire plusieurs notations dans la clause WITH, il suffit de les juxtaposer (les virgules

sont réservées aux propriétés des membres calculés) :

1 WITH MEMBER ... AS ’...’

2 MEMBER ... AS ’...’

3 SET ... AS ’...’

4 SELECT ...

18.5 Clause CELL PROPERTIES

Par ailleurs, on peut contrˆoler les propriétés de cellule que l’on veut afficher dans la fenˆetre contextuelle

(qui apparaˆit au clic droit sur une cellule) `a l’aide la dernière clause des requˆetes MDX. Par exemple,

pour n’afficher que l’ordinal et la valeur formattée :

1 SELECT ...

2 FROM ...

3 WHERE ...

4 CELL PROPERTIES CELL_ORDINAL, FORMATTED_VALUE

18 INTERROGER UN CUBE 95

18.6 Fonctions MDX

Dans ces requˆetes on peut `a tout moment utiliser une multitude d’autres fonctions offertes par MDX

(cf. l’aide en ligne et [12]). `A commencer par la fonction :

IIF(condition, si vrai, si faux )

Exemple : affichons oui ou non en deuxième colonne selon que les articles se sont vendus moins de

200 fois ou non :

1 WITH MEMBER measures.MauvaisArticle

2 AS ’IIF(measures.quantite < 200, "oui", "non")’

3 SELECT {measures.quantite, measures.MauvaisArticle} ON COLUMNS,

4 article.MEMBERS ON ROWS

5 FROM ventes

6 WHERE (temps.[2001])

MDX offre aussi la fonction ISEMPTY et qui permet de remplacer la valeur NULL par 0 (par exemple) :

1 WITH MEMBER measures.[quantite corrigee]

2 AS ’IIF(ISEMPTY(measures.quantite), 0, measures.quantite)

3 SELECT temps.[2003].CHILDREN ON COLUMNS, article.MEMBERS ON ROWS

4 FROM ventes

5 WHERE (measure.[quantite corrigee])

Remarque : dans la condition de la fonction IIF, on peut utiliser le mot-clé NOT.

Autre exemple, pour retrouver un ancˆetre : sélectionner la région dans laquelle se trouve la ville de Nice

1 SELECT {ANCESTOR(Nice,region)} ON COLUMNS

2 ...

Remarques :

– le premier argument doit ˆetre un membre unique ;

– le deuxième argument est soit le niveau auquel on monte, soit le nombre de niveaux `a monter.

Exemple pour retrouver les descendants : sélectionner les articles de la marque Channel

1 SELECT DESCENDANTS(Channel, article) ON COLUMNS

2 ...

Remarques :

– le premier argument doit ˆetre un membre unique ;

– le deuxième argument est soit le niveau auquel on descend, soit le nombre de niveaux `a descendre.

Dernier exemple : pour définir une colonne d’agrégat qui comprend la France et l’Italie par exemple

1 WITH MEMBER geographie.[France et Italie]

2 AS ’AGGREGATE({France, Italie})’

3 SELECT {France, Italie, [France et Italie]} ON COLUMNS,

4 Measures.MEMBERS ON ROWS

5 FROM ventes

La fonction AGGREGATE utilise alors la fonction d’agrégation appropriée `a chaque mesure.

18.7 Conclusion

On aboutit `a la stratégie suivante pour l’élaboration d’une requˆete MDX :

1. remplir la clause FROM avec le cube sur lequel on travaille ;

2. définir dans la clause WITH les membres calculés, les jeux nommés et les cellules calculées locaux ;

3. déterminer les tranches voulues pour remplir le tuple de la clause WHERE ;

4. pour chaque axe de la clause SELECT (et dans l’ordre) :

(a) déterminer les dimensions et les membres concernés ;

(b) filtrer éventuellement ces membres avec FILTER, NON EMPTY et/ou TOPCOUNT ;

(c) ordonner éventuellement les membres restants avec ORDER :

(d) lister après le mot-clé PROPERTIES les propriétés de membres (cf. §19.1 page 96) que l’on veut

ajouter aux propriétés de cellule.

5. lister dans la clause CELL PROPERTIES les propriétés de cellule (cf. §18.5 page 94) que l’on souhaite

avoir `a disposition.