OLAP On Line Analytical Processing

Le modèle OLAP Online Analytical Processing est une solution technologique pour faciliter la manipulation de grandes quantités de données à des fins décisionnelles. En effet de par sa nature, cette base de données bien spécifique permet de de réorganiser les informations à volonté afin de réaliser des analyses pointues. Voyons le principe.

1) Définition, 2) Principe, 3) Mettre en oeuvre, 4) Erreurs typiques, 5) À lire

Hyper Cube et Analyse Multidimensionnelle

Les bases de données de type relationnel (SGBDR) sont inadaptées aux besoins décisionnels.

Les requêtes décisionnelles, particulièrement complexes par principe, mobilisent abusivement les ressources machines. Lors de leur exécution, elles perturbent les traitements de production (OLTP).

De OLTP à OLAP

L'infocentre, base relationnelle exclusivement réservée aux requêtes décisionnelles, a pu durant un temps assez bref sembler proposer une solution. Mais les bases OLTP structurées en 2 dimensions ne se prêtent guère aux requêtes décisionnelles.
Il était temps de redéfinir une nouvelle structure spécifique aux exigences du décisionnel.

EF CODD récemment décédé, père des bases relationnelles a relevé ce défi en 1993. En fondant son expertise sur le produit ESSBASE, un tableur multidimensionnel, EF CODD a établi 12 règles, complétées par la suite de 6 nouvelles, pour définir le concept du cube OLAP (On Line Analytical Processing), une base de données multi dimensionnelle.

OLAP, comment ça marche ?

Au sein d'une base de données de type OLAP, les données sont rangées selon un principe de dimensions correspondant étroitement aux axes de recherche des utilisateurs. Cette structure en forme de "cube" présente de nombreux avantages.

L'organisation des données dans le cube est orientée "besoins de l'analyste", qui n'est pas nécessairement un expert. Après un rapide apprentissage, l'outil est assez aisé à domestiquer. Encore faut-il qu'il soit conçu au préalable avec méthode.

Voyons un exemple

Un chef de secteur peut ainsi souhaiter visualiser une représentation du chiffre d'affaires réalisé selon les deux axes suivant : par produit et par région et par période.
Puis, après réflexion, il pense qu'il obtiendra une meilleure appréciation en inversant les axes : par région et par produit et par période.
Avec une base multidimensionnelle, il suffit de faire "pivoter" le cube sans pour autant être contraint de générer une nouvelle requête.
Il existe bien sûr en natif d'autres fonctions essentielles pour le décisionnel comme le Slice and Dice, permettant de découper une "tranche" du cube afin de l'analyser plus finement et le Drill Down et Drill Up pour descendre plus avant dans le détail.
Drill Down :
Ainsi notre chef de secteur peut se poser la question suivante :
"Vente des produits frais dans la région Alsace pour le trimestre écoulé"
Puis il voudra affiner :
...Et dans le département du Bas Rhin...
...Et dans la ville de Strasbourg...
...Et dans le quartier....

OLAP réactualise l'ensemble des calculs de synthèse et les agrégats selon la question posée.

Les 12 règles du modèle OLAP (d'origine)

OLAP et ESSBASE

Le concept OLAP (On Line Analytical Processing) a été formalisé en 1993 par Edgar F. CODD, le père des bases de données relationnelles. E. F. CODD a fondé en grande partie son expertise sur le modèle de tableur multidimensionnel EssBase de Arbor System (intégré à Hyperion par la suite). Ces douze règles ne sont pas sans rappeler les prescriptions formulées pour guider la conception des bases relationnelles.

La petite histoire raconte aussi que E.F. Codd était rémunéré par Arbor system. L'éditeur s'assurait ainsi que les résultats de l'étude engagée collaient pile poil avec le produit Essbase déjà présent dans les tuyaux de la commercialisation...

Les 12 règles de conception OLAP

E. F. CODD a ainsi prescrit 12 règles de conception.

1) Multidimensionnalité
Le Modèle OLAP est multidimensionnel par nature.
2) Transparence
L'emplacement physique du serveur OLAP est transparent pour l'utilisateur.
3) Accessibilité
L'utilisateur OLAP dispose de l'accessibilité à toutes les données nécessaires à ses analyses.
4) Stabilité
La performance des reportings restent stables indépendamment du nombre de dimensions.
5) Client-Serveur
Le serveur OLAP s'intègre dans une architecture client serveur.
6) Dimensionnement
Le dimensionnement est générique afin de ne pas fausser les analyses.
7) Gestion complète
Le serveur OLAP assure la gestion des données clairsemées.
8) Multi-Utilisateurs
Le serveur OLAP offre un support multi-utilisateurs (gestion des mises à jour, intégrité, sécurité).
9) Inter Dimension
Le serveur OLAP permet la réalisation d'opérations inter dimensions sans restriction.
10) Intuitif
Le serveur OLAP permet une manipulation intuitive des données.
11) Flexibilité
La flexibilité (ou souplesse) de l'édition des rapports est intrinsèque au modèle.
12) Analyse sans limites
Le nombre de dimensions et de niveaux d'agrégation possibles est suffisant pour autoriser les analyses les plus poussées.

Base OLAP, les 18 règles du nouveau modèle

Le modèle OLAP de "l'Olap Report"

Après une première définition du concept OLAP, Edgar F. CODD a ensuite complété les 12 premières règles de 6 nouvelles. Le modèle FASMI proposé par Nigel Pendse, l'initiateur de l'Olap Report, propose une nouvelle organisation des règles d'origine. Il s'avère nettement plus complet et plus optimal pour concevoir et qualifier les serveurs multidimensionnel de type OLAP. Le modèle FASMI. FASMI : Fast Analysis of Shared Multidimensional Information,

Les 18 règles du modèle OLAP

Les règles OLAP "Basic"

1. Multidimensional Conceptual View (règle 1)
2. Intuitive Data Manipulation (règle 10)
3. Accessibility (règle 3)
4. Batch Extraction vs Interpretive (nouvelle règle)
5. OLAP Analysis Models (nouvelle règle)
6. Client Server Architecture (anciennement règle 5)
7. Transparency (anciennement règle 2)
8. Multi-User Support (anciennement règle 8)

Les règles OLAP "Special"

9. Treatment of Non-Normalized Data (nouvelle règle)
10. Storing OLAP Results: Keeping Them Separate from Source Data (nouvelle règle)
11. Extraction of Missing Values (nouvelle règle)
12. Treatment of Missing Values (nouvelle règle)

Les règles OLAP "Reporting"

13. Flexible Reporting (anciennement règle 11)
14. Uniform Reporting Performance (anciennement règle 4)
15. Automatic Adjustment of Physical Level (remplace la règle 7)

Les règles OLAP "Dimension Control"

16. Generic Dimensionality (anciennement règle 6)
17. Unlimited Dimensions & Aggregation Levels (anciennement règle 12)
18. Unrestricted Cross-dimensional Operations (anciennement règle 9)

Langage MDX

Le langage MDX, né au sein des labos Microsoft (SQL Server OLAP), est un langage d'interrogation des bases multidimensionnelles plus adapté que le classique SQL pour le traitement des requêtes de type OLAP.
MDX signifie "Multi-Dimensional Expressions".
Microsoft a proposé le langage MDX comme standard des interrogations multi dimensionnelles. Pour en savoir un peu plus, voir le tutoriel de Database Journal.

Les déclinaisons du concept OLAP

MOLAP ROLAP HOLAP et DOLAP

Techniquement, il existe deux modèles de stockage physique des données. Soit la base est structurellement multi-dimensionnelle comme le propose le modèle MOLAP soit la base est de type relationnelle mais utilisée comme une base multi-dimensionnelle comme le propose le modèle ROLAP. Rapidement, d'autres modèles spécifiques sont ensuite venus s'ajouter à ces deux concepts de base.

MOLAP

La base MOLAP (Multidimensional) est l'application physique du concept OLAP. Il s'agit réellement d'une structure multidimensionnelle. Les bases MOLAP sont rapides et performantes. Elles proposent des fonctionnalités particulièrement évoluées. Les bases de type MOLAP restent limitées au gigaoctet.

ROLAP

La base ROLAP (Relational) est en fait une classique base relationnelle organisée pour fonctionner comme une base OLAP. Les bases ROLAP sont bien plus lentes et nettement moins performantes que les bases MOLAP. Mais, immense avantage, elles sont sans limite de taille.

HOLAP

Un troisième modèle, le modèle HOLAP avec un H pour hybride, propose de cumuler les avantages des deux modèles précédents. Les données agrégées sont stockées sous formes multi-dimensionnelles, alors que les données détaillées sont stockées dans des structures relationnelles.

DOLAP

La base DOLAP (Desktop) est une base OLAP très limitée en taille, hébergée sur le poste client. Elle est bien entendu très rapide.

Quelques produits types

Tous les principaux éditeurs d'outils décisionnels intègrent un serveur Olap dans leur gamme : Microsoft Analysis Services, Oracle Express, Hyperion Essbase. Les offres évoluent régulièrement, changent de nom, se recompose, il vaut mieux se référer régulièrement auprès des éditeurs.

Essayer OLAP sans attendre

La meilleure façon de se familiariser avec le concept OLAP est encore de l'essayer "pour de vrai". C'est aujourd'hui possible, c'est très simple et peu coûteux. Excel ® et les tableaux dynamiques, les pivots tables, sont bien pratiques pour une formation à la carte.

5 étapes pour découvrir Olap

Suivez simplement les 5 étapes de la démarche suivante .

-1- Assurez-vous de disposer de suffisamment de place sur votre poste de travail (Vérifiez la configuration requise).
-2- Si ce n'est déjà fait, installez une version de Microsoft Excel relativement récente (en tout cas supérieure à 2007)..
-3- Rendez-vous sur le site :Microsoft ® SQL Server ® 2008 Express with Advanced Services et téléchargez la version gratuite de SQL Server correspondant à votre système d'exploitation. .
-4- Achetez le livre :
L'essentiel du tableau de bord
Un guide simple, pratique et concret pour concevoir et réaliser les tableaux de bord de l'entreprise
Alain Fernandez
Éditions Eyrolles
280 pages, 22 €
Best seller francophone 5^ème édition 2018

Pour acheter ce livre :

Ce livre décrit toutes les étapes pour connecter et utiliser Excel avec le serveur OLAP de SQL server précédemment chargé. Vous apprendrez aussi à utiliser les tableaux dynamiques (pivots tables).
Téléchargez les exemples
-5- Trouvez-vous un endroit au calme et c'est tout !
Bon travail...

Mettre en oeuvre le cube OLAP

Une base OLAP ne se conçoit pas sans une réflexion de fond préalable fondée sur les habitudes et besoins réels des utilisateurs.

En effet, il ne s'agit pas de chercher à sécuriser les "besoins futurs" en stockant un maximum de données et en multipliant le nombre de dimensions en partant du principe.

Actuellement je ne sais pas pourquoi , mais peut-être que quelqu'un, un jour, en aura besoin.

Ce principe, à l'origine de bon nombre d'usines à gaz en informatique, est la meilleure garantie d'une explosion de la base.

3 erreurs de conception

Nigel Pendse, co-fondateur de "l'Olap Report", un site qui fut une référence incontournable de la Business Intelligence au cours de la première décennie de ce siècle et qui a disparu depuis, a bien analysé ces travers de conception particulièrement courants. Dans un texte fondateur de la technologie Olap, une référence pour tous les concepteurs, il décrit et illustre ces erreurs de conception selon 3 axes principaux de dérives.

Stocker un maximum de données
Définir un grand nombre de dimensions
Multiplier les agrégats

Et pourtant, Pareto's still alive...

À l'usage, on constate que la majorité des requêtes ne portent généralement que sur un ensemble réduit de données. De surcroît, les analyses exotiques, si elles sont lancées lors des premiers jours d'utilisation pour tester la machine, restent plutôt exceptionnelles en usage courant.

C'est par cette identification que doit commencer l'étude de conception d'un cube OLAP.
Ce texte, écrit avant l'essor du Big Data (voir ici le dossier dédié), ne perd pas pour autant sa pertinence. La construction de modèle utilisable dans l'aide à la décision est d'une autre ampleur.

Les dérives du cube OLAP

L’expansion non maîtrisée constitue le principal problème des systèmes OLAP. La tendance naturelle conduit à 3 erreurs de conception comme : essayer de stocker le maximum de données, de définir de trop nombreuses dimensions et de rechercher le meilleur temps de réponse.

Stocker un maximum de données

Les utilisateurs auront besoin de rapprocher de nombreuses données et de conserver un maximum d’historiques. Actuellement, avec un rythme de renouvellement accéléré des gammes de produits des entreprises, les données évoluent très rapidement et demandent des bases de plus en plus volumineuses.

Définir de nombreuses dimensions

Les dimensions doivent être prévues à l’origine du chargement de la base Olap. Pour éviter des impasses et faciliter les analyses, les utilisateurs ont tendance à définir de nombreuses dimensions pour un même ensemble de données. Souvent ces dimensions restent inexploitées et occupent une place inutile.

À l’analyse des bases existantes, on constate qu’elles contiennent de nombreuses cases vides et les données sont très clairsemées.
(La modélisation Big Data (voir ici le dossier dédié), est un tout autre sujet...)

Rechercher le meilleur temps de réponse

Pour être acceptable, un maximum de requêtes doit être traité en moins de 5 secondes. Quelques unes uniquement seront traitées en un temps maximal de 30 secondes. Au delà, c’est le domaine de l’inacceptable.

Pour un maximum de performance, de nombreux agrégats sont pré-calculés et stockés. Et, paradoxe, quelquefois, le nombre d’agrégats stockés dépasse le nombre de données initiales !
En résultat, les bases sont rapidement saturées et peu utilisables.
En complément pour des requêtes accélérées voir le in-memory, la fiche pratique est ici

À la base, une erreur de raisonnement

L’approche initiale des bases OLAP est dans le même esprit que les premiers data warehouse (voir ici le dossier complet) et part du principe que pour une plus grande efficacité, il suffit de mettre à la disposition des décideurs le maximum de données. Ces derniers auront ainsi, selon ce principe, toute liberté pour effectuer tous les croisements qui leur sembleraient opportuns.

Ce raisonnement n’est plus possible et les systèmes conçus selon ce principe sont un échec. Les données doivent être structurées « intelligemment » dans les bases, en connaissance et avec compréhension des besoins réels des utilisateurs.

Une conception réfléchie

Nous ne bâtirons pas une architecture technologique extravagante en supposant qu’un décideur potentiel pourrait éprouver le désir de rapprocher des données totalement antinomiques.

Un jour, je posais la question à un prosélyte de ce type d’architecture. Je lui ai demandé quel pouvait être l’intérêt pour un décideur de terrain, de comparer les caractéristiques intrinsèques de la production d’une référence X, avec le résultat des ventes du mois dernier dans la zone Europe du Sud. Il m’avait répondu : « personnellement je ne le sais pas, mais je ne peux pas empêcher un décideur d’effectuer ce rapprochement s’il le souhaite. Il en tirera peut-être un enseignement et prendra LA décision pertinente ».

À première vue, l’argument semble de poids. Pourtant, c’est en cultivant le mythe de l’information « miracle » et cachée que l’on bâtit les « usines à gaz » peu performantes et peu adaptées pour l’usage courant du décideur en situation.
Avant de faire des recoupements tous azimuts, le décideur à besoin de réponses à des problèmes précis. Il est vrai qu’il aura tout intérêt à ne pas rester cantonné dans sa sphère et rien ne l’empêche de souhaiter rapprocher des informations qui, a priori ne sont en rien concomitantes.

Nous remplaçons à escient le terme données par information. Comme nous l’avons vu à l’étape 7 de la méthode Gimsi, l’enseignement sera beaucoup plus profitable en échangeant avec le professionnel concerné, une information construite et structurée, plutôt que de chercher à analyser des données externes à notre périmètre d’activité et donc peu porteuses de sens.
Ce texte a été écrit il y a déjà quelques années. Il est toutefois bon de noter que le Big Data, non pas en théorie mais en pratique, n'a rien changé à ce raisonnement : voir notamment les limites du Big Data.

Solution Olap Open Source

Mondrian, projet Pentaho
Voir le projet Data Warehouse Open Source.

Quelques fournisseurs

Oracle Essbase Olap
IBM Olap Tools (Cognos)
SAP BusinessObjects Voyager
Voir aussi les "pivots tables" ou tableaux
croisés dynamiques de Microsoft Excel

Solution Olap Open Source

Mondrian, projet Pentaho
Voir le projet Data Warehouse Open Source

Livres recommandés

› Les principes de Olap expliqués. Exemples pratiques avec Oracle Essbase. Comment conduire le projet, bâtir la base multidimensionnelle. Techniques, conseils et recommandations pour configurer et optimiser la base.

Oracle Essbase & Oracle OLAP: The Guide to Oracle's Multidimensional Solution Oracle Essbase & Oracle OLAP
The Guide to Oracle's Multidimensional Solution
Michael Schrader
McGraw-Hill Osborne Media
524 pages (anglais)
Dispo :
› www.amazon.fr & Format Kindle