MDX: Ceinture noire en langage SQL

Une des problématique rencontrée en business intelligence est le calcul de cumuls, le fameux  « year to date » pour les connaisseurs, dans cet article, je présenterai différentes techniques dont l’utilisation du langage MDX.

Introduction

Je ne donnerai pas un cours de MDX dans cet article, je me contenterai de vous donner son historique et sa définition, qui je l’espère sera la plus claire possible, j’expliquerai sa venue dans le monde open source, puis je finirai par un exemple concret de son utilisation en BI, je vous donne également tous les liens pour reproduire ma démo ainsi que la documentation pour bien débuter.

Les origines

Ce langage a été créé en 1998 par Microsoft, vous l’aurez compris, il est propriétaire et toujours utilisé à l’heure actuelle dans les produits tel que Microsoft SQL Server Analysis Services en relation étroite avec la DB SQL Server 2012.

Définition

Le MDX pour « Multidimensional Expressions » est un langage de requête dédié aux données structurées de manière multidimensionnelles. C’est un langage exclusivement réservé à l’analytique avec lequel on ne peut donc faire que de l’interrogation, pas de DML (manipulation) ni de DDL (gestion de la structure), les requêtes sont conçues plus rapidement qu’avec du SQL classique.

En résumé, le MDX est à la base de données OLAP ce que SQL est pour la base de données relationnelle classique.

Quid de l’open source

Pour utiliser le MDX dans le monde open source, il est nécessaire de connaitre un peu l’historique et le vocabulaire qui lui est propre.

Serveur Mondrian

En 2002 sort le projet Mondrian, développé en java ce serveur est en fait un moteur qui utilise un driver OLAP pour piloter une base de données relationnelle classique (ROLAP). Le serveur Mondrian est capable d’exécuter des requêtes MDX et de restituer le résultat au format multidimensionnel. Voir la Documentation Mondrian

Driver OLAP

L’API OLAP4J développé également en java embarque nativement un driver JDBC, ce driver fait le lien entre le serveur Mondrian et le serveur de base de données relationnel  et peu importe la DB pour autant que vous fournissiez le bon jar correspondant (Mysql, Oracle, SQL Server, DB2). Voir la documentation OLAP4J

Schéma OLAP

Un schéma est un fichier XML qui fournit une description d’une base de données multidimensionnelle, un modèle qui décrit cubes, hiérarchies et membres basés sur des données physiques.

Base de données OLAP

Ce type de base de données est l’addition d’une base de données relationnelle et d’un schéma OLAP (mapping XML) qui la décrit sous forme de cubes.

Démonstration

Pour cette démonstration j’utiliserai JasperAnalysis Workbench (cliquer pour télécharger) qui se connectera à une base de données MySql, la documentation complète de cet outil se trouve dans l’archive ZIP.

Jasperanalysis Workbench est un outil un peu tombé en désuétude depuis sa version 4.0, c’est un éditeur de schéma OLAP qui va vous aider à créer un fichier XML de toutes pièces, cerise sur le gâteau, il permet une fois le fichier XML créé, de l’utiliser pour se connecter à une base de données (devenue OLAP) et de lancer des requêtes MDX.

Sachez aussi que cet outil vous est utile si vous désirez faire de l’OLAP avec JasperServer version communautaire, ce principe tombe à l’eau si vous utilisez la version professionnelle.

Vous pouvez télécharger la base de données de test très connue nommée FoodMart et son schéma OLAP qui lui correspond. Je mets ce lien sur mon domaine car j’ai adapté le script SQL de chargement sur une base de données MySQL Version 5.

A télécharger ici

Parce qu’un dessin vaut toujours mieux qu’un long discours, voici ce que donne l’ensemble des notions que j’ai expliqué le long de cet article:

Environnement mondrian

Faire des cumuls

Avant de se lancer dans le MDX, sachez que vous pouvez faire des calculs de cumuls en langage SQL, il existe plusieurs techniques, j’expliquerai celles dont j’ai connaissance (il en existe surement d’autres).

Méthode SQL générique

Cette méthode fonctionne sur toutes les bases de données utilisant le SQL standard, il n’y a pas de fonction spécifique dans cette requête. La technique est d’appeler une seconde fois la requête pour la placer dans un nouveau champ, il faut ensuite utiliser un signe « inférieur ou égal » entre les champs mois qui sont triés.

SELECT pc.product_family, t.the_year, t.quarter, t.month_of_year, SUM(f.store_sales) AS store_sales,
-- Nouveau champ issu de la requête d’origine
(SELECT SUM(f1.store_sales)
FROM foodmart.sales_fact_1997 f1
INNER JOIN product p1 ON p1.product_id = f1.product_id
INNER JOIN product_class pc1 ON pc1.product_class_id = p1.product_class_id
INNER JOIN time_by_day t1 ON t1.time_id = f1.time_id
WHERE pc1.product_family ='Drink'
-- on compare les champs mois afin de cumuler les montants
AND t1.month_of_year <= t.month_of_year
GROUP BY pc1.product_family, t1.the_year
ORDER BY t1.the_year, t1.quarter, t1.month_of_year) as cumul
FROM foodmart.sales_fact_1997 f
INNER JOIN product p ON p.product_id = f.product_id
INNER JOIN product_class pc ON pc.product_class_id = p.product_class_id
INNER JOIN time_by_day t ON t.time_id = f.time_id
WHERE pc.product_family ='Drink'
GROUP BY pc.product_family, t.the_year, t.quarter, t.month_of_year
ORDER BY t.the_year, t.quarter, t.month_of_year

Méthode Mysql

Ici j’utilise une variable incrémentale, les mots clés utilisés sont spécifiques à MySql.

-- définition d'une variable temporaire qui va accumuler chaque montant
SET @sum:= 0;
SELECT t1.*, @sum:=@sum + store_sales AS cumul
FROM
(
SELECT pc.product_family, t.the_year, t.quarter, t.month_of_year, sum(f.store_sales) as store_sales
FROM foodmart.sales_fact_1997 f
INNER JOIN product p on p.product_id = f.product_id
INNER JOIN product_class pc on pc.product_class_id = p.product_class_id
INNER JOIN time_by_day t on t.time_id = f.time_id
WHERE pc.product_family ='Drink'
GROUP BY pc.product_family, t.the_year, t.quarter, t.month_of_year
ORDER BY t.the_year, t.quarter, t.month_of_year) t1

Résultat des deux méthodes:

Résultat cumuls foodmart avec mysql

Méthode Oracle

Ici je sors un peu du monde open-source pour vous donner une méthode spécifique à Oracle, en effet cette base de données possède des fonctions analytiques, dont vous trouverez un excellent tutoriel sur le site de developpez.com

Comme il est plus difficile d’utiliser Oracle, vous pouvez utiliser SQL Fiddle un site de test SQL très bien conçu et très utile.

Voici l’exemple que j’ai pris soin de sauvegarder :

CREATE TABLE test (id number, year number, month number, amount number);
INSERT INTO test VALUES (1,2013,1,1000);
INSERT INTO test VALUES (2,2013,2,2000);
INSERT INTO test VALUES (3,2013,3,3000);
INSERT INTO test VALUES (4,2013,4,4000);
INSERT INTO test VALUES (5,2013,5,5000);
INSERT INTO test VALUES (6,2013,6,6000);
INSERT INTO test VALUES (7,2013,7,7000);
INSERT INTO test VALUES (8,2013,8,8000);
INSERT INTO test VALUES (9,2013,9,9000);
INSERT INTO test VALUES (10,2013,10,10000);
INSERT INTO test VALUES (11,2013,11,11000);
INSERT INTO test VALUES (12,2013,12,12000);
SELECT id, year, month, amount,
SUM(amount) OVER (PARTITION BY year ORDER BY month) AS cumul
FROM test;

Le résultat de la fonction analytique d’oracle

Résultat fonction analytique oracle

Résultat fonction analytique oracle

Méthode MDX

C’est ce dernier cas qui nous intéresse tout particulièrement, pour en savoir plus sur le langage MDX, rendez-vous sur la page du site de Microsoft qui donne toutes les références de ce fabuleux langage

Structure du langage MDX

Pour vous donner une idée je vais rapidement décrire ici, comment s’articule la construction d’une requête MDX.

SELECT [Niveau].[Membres] et/ou [MESURES] ON COLUMNS
       [Niveau].[Membres] et/ou [MESURES] ON ROWS
FROM   [Cube]
WHERE  [Condition]

Composé des éléments suivants

Eléments Exemples
Dimension

->Hiérarchies

->Niveaux

->Membres

Temps

-> (Année-Trimestre-Mois), (Année-Semaine)

-> Année OU trimestre OU Mois OU Semaine

-> 2006, Q3, 10

Dimension :

Elément qui compose les faits

Hiérarchie :

Ensemble de plusieurs niveaux

Niveau :

Décomposition de la dimension

Membre :

Valeur que peut prendre un niveau

Pour calculer des cumuls en MDX, il existe une fonction qui se nomme YTD() , elle fonctionne avec la clause WITH MEMBER qui permet de calculer de nouveaux membres, un peu comme la technique d’une variable incrémentale.

WITH MEMBER [Measures].[Cumul]
	as Sum(YTD(), [Measures].[Store Sales])
SELECT
{( [Measures].[Store Sales]) , ([Measures].[Cumul] )}  ON COLUMNS,
[Product].[Drink] * Descendants([Time].[2006],2)
ON ROWS
FROM [Sales 2]

Résultat de la requête MDX

Résultat requête MDX

Conclusion

Avec le langage SQL, il faut considérer le fait qu’il devient très fastidieux de créer des requêtes analytiques, on peut toutefois réussir à obtenir le résultat escompté mais au prix de requêtes à rallonge et souvent difficilement maintenable.

Le MDX intervient donc avec une syntaxe épurée et une meilleure facilité de compréhension, malheureusement peu de gens le connaissent, hormis bien sûr les utilisateurs des solutions BI Microsoft.

Enfin, mon dernier conseil sera de vous rediriger (encore une fois) sur l’excellent outil Saiku cela vous permettra de monter graduellement en puissance sur ce langage en assimilant des requêtes MDX générées automatiquement.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> 

Petit calcul pour valider votre commentaire! merci * Time limit is exhausted. Please reload CAPTCHA.