La clause MODEL du SQL d'Oracle vous permet de construire un modèle composé d'une ou plusieurs matrices, avec un nombre variable de dimensions. Le modèle utilise une partie des colonnes disponibles dans la clause FROM. Il doit contenir au moins une dimension et un indicateur (measure), et éventuellement une ou plusieurs partitions. Le modèle peut être représenté comme un classeur de tableur, contenant des feuilles de calcul différentes pour chaque valeur calculée (indicateur). Une feuille de calcul présente un axe horizontal et un axe vertical (deux dimensions) ; vous pouvez séparer votre feuille en plusieurs zones identiques, chacune dédiée à un pays ou un département différent (partition).

La figure ci-dessous présente un modèle tiré de la classique table EMP, avec deptno comme partition, empno comme dimension, et les deux indicateurs sal et comm.

Une fois le modèle mis en place, vous définissez les règles qui modifient la valeur des indicateurs. Ce sont ces règles qui sont au coeur de la clause MODEL. Avec quelques règles, vous pouvez faire des calculs complexes sur vos données, et même créer de nouvelles lignes. Dans le modèle, les colonnes d'indicateurs deviennent des tableaux indexés (hash tables) dont les clés sont les colonnes de dimension. Oracle y applique les règles de calcul à toutes les partitions. Une fois ces calculs effectués, le modèle est re-converti en lignes de données traditionnelles.

Selon mon expérience, le diagramme syntaxique de la clause MODEL présenté par la documentation Oracle est passablement complexe et tend à effrayer tout le monde. Pour éviter d'en arriver là, je vais tenter une autre approche, en utilisant un série de petits exemples sur la table EMP, en commençant par les plus simples, et en introduisant progressivement de nouveaux éléments. A la fin de cet article, vous trouverez un script que vous pouvez télécharger et exécuter sur votre propre base de données.

 SQL> select empno
   2       , ename
   3       , sal
   4  from emp
   5  where deptno = 10
   6  /
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300
 
 3 lignes sélectionnées

Voici le contenu (classique) de la table EMP pour le département 10. L'équivalent de cette requête SQL avec une clause MODEL (qui ne fait rien) est :

 SQL> select empno
   2       , ename
   3       , sal
   4   from emp
   5   where deptno = 10
   6   model
   7       dimension by (empno)
   8       measures (ename, sal)
   9       rules
  10       ()
  11 / 
 
      EMPNO ENAME             SAL
 ---------- ---------- ----------
       7782  CLARK           2450
       7839  KING            5000
       7934  MILLER          1300 
 
 3 lignes sélectionnées

Nous avons ici deux indicateurs, ename et sal, et une dimension, empno. La combinaison des colonnes de partitions et de dimensions doit permettre d'identifier chaque cellule de manière unique. Cette contrainte est vérifiée à l'exécution, et sa violation produira une erreur ORA-32638.

Autrement dit, cette combinaison des dimensions et partitions doit pouvoir servir de clef unique pour les lignes qui vont être traitées par la clause MODEL. Cette limite est la plus importante (elle peut imposer une aggrégation préalable), mais aussi la plus révélatrice du fonctionnement du modèle. Une matrice de calcul est créée, où dimensions et partitions vont servir d'axes de coordonnées.
Les habitués d'Hyperion Essbase peuvent considérer les partitions comme des dimensions sparses, tandis que les dimensions de la clause MODEL seraient les dimensions denses d'Essbase.

Comme il y a deux indicateurs, Oracle crée deux tableaux à une dimension, indicés par empno. Sur la ligne 9, vous voyez le mot-clé RULES, sans contenu pour le moment. RULES est optionnel, mais par souci de clarté je l'utiliserai sytématiquement.

 SQL> select empno
   2       , ename
   3       , sal
   4   from emp
   5   where deptno = 10
   6   model
   7       dimension by (empno)
   8       measures (sal)
   9       rules
  10      ()
  11 /

Une fois le modèle de calcul appliqué, toutes les partitions, dimensions et indicateurs sont re-convertis en colonnes à l'intérieur de lignes classiques, ce qui veut dire que vous ne pouvez avoir dans le SELECT que des colonnes citées dans le modèle. Si, par exemple, je n'avais pas inclus la colonne ename comme indicateur, j'aurais eu ce message d'erreur :

 ERREUR à la ligne 2 :
 ORA-32614: expression MODEL SELECT interdite

Avec l'exemple suivant, je crée une nouvelle ligne :

 SQL> select empno
   2       , ename
   3       , sal
   4  from emp
   5  where deptno = 10
   6  model
   7       dimension by (empno)
   8       measures (ename,sal)
   9       rules
  10       ( ename[7777] = 'VAN WIJK'
  11       )
  12 / 
 
      EMPNO ENAME             SAL
 ---------- ---------- ----------
       7782 CLARK            2450
       7839 KING             5000
       7934 MILLER           1300
       7777 VAN WIJK 
 
 4 lignes sélectionnées

La règle en ligne 10 indique que l'indicateur ename est croisé avec la dimension empno et prend la valeur « VAN WIJK ». Si la table EMP avait déjà un empno 7777, la règle aurait écrasé l'ename correspondant. Mais comme 7777 n'existe pas dans EMP, une nouvelle cellule a été créée, qui se traduit par une nouvelle ligne dans le résultat du SELECT. Notez bien que cette ligne n'est pas insérée dans une table, mais seulement affichée dans le résultat.

La clause MODEL ne s'utilise que dans une requête SELECT. Cela inclut le INSERT... SELECT et le MERGE INTO... USING (SELECT...).

Avec une seconde règle, vous pourriez également renseigner la colonne sal :

 SQL> select empno
   2 	, ename
   3 	, sal
   4 from emp
   5 where deptno = 10
   6 model
   7 	dimension by (empno)
   8 	measures (ename,sal)
   9 	rules
  10 	( ename[7777] = 'VAN WIJK'
  11 	, sal[7777] = 2500
  12 	)
  13 / 
 
      EMPNO ENAME                  SAL
 ---------- -------------- -----------
	   7782 CLARK            2450
	   7839 KING             5000
	   7934 MILLER           1300
	   7777 VAN WIJK         2500
 
  4 lignes sélectionnée

La requête renvoie à la fois les lignes existantes et les nouvelles lignes. L'expression-clé RETURN UPDATED ROWS vous permet de ne renvoyer que les lignes modifiées ou créées :

 SQL> select empno
   2       , ename
   3       , sal
   4   from emp
   5   where deptno = 10
   6   model
   7       return updated rows
   8       dimension by (empno)
   9       measures (ename,sal)
  10       rules
  11       ( ename[7777] = 'VAN WIJK'
  12       , sal[7777] = 2500
  13   	  )
  14 / 
 
      EMPNO ENAME                  SAL
 ---------- -------------- -----------
	   7777 VAN WIJK         2500 
 
 1 ligne sélectionnée

Tous les calculs sont exécutés sur chaque partition. Pour le constater, enlevons le filtre « deptno = 10 » et affichons le deptno. Comme partition ou comme indicateur ? Pour commencer, testons ce qui se passe si nous définissons deptno comme indicateur :

 SQL> select empno
   2       , ename
   3       , sal
   4       , deptno
   5   from emp
   6   model
   7       return updated rows
   8       dimension by (empno)
   9       measures (ename,sal,deptno)
  10       rules
  11       ( ename[7777] = 'VAN WIJK'
  12       )
  13 / 
 
      EMPNO ENAME             SAL     DEPTNO
 ---------- ---------- ---------- ----------
	   7777 VAN WIJK 
 
 1 ligne sélectionnée

Une ligne est créée, comme attendu, avec un deptno NULL. Testons maintenant avec deptno comme partition :

 SQL> select empno
  2       , ename
  3       , sal
  4       , deptno
  5   from emp
  6   model
  7       return updated rows
  8       partition by (deptno)
  9       dimension by (empno)
 10       measures (ename,sal)
 11       rules
 12       ( ename[7777] = 'VAN WIJK'
 13       )
 14 / 
 
      EMPNO ENAME             SAL     DEPTNO
 ---------- ---------- ---------- ----------
       7777 VAN WIJK                      30
       7777 VAN WIJK                      20
       7777 VAN WIJK                      10 
 
 3 lignes sélectionnées

Une nette difference ! Dans la table EMP, il n'y a que des deptno 10, 20 et 30, ce qui fait donc trois partitions dans le modèle. La règle est appliquée aux trois, ce qui donne donc trois nouvelles lignes.

Pourrait-on inclure deptno comme dimension ? Oui, mais cela forcerait à indiquer dans la règle le numéro de département comme caractéristique de l'indicateur : par exemple,
ename[7777, 10] = 'VAN WIJK',
ename[7777, 20] = 'DINIMANT',
ename[7777, 30] = 'SCHNEIDER'.
Plus largement, les dimensions permettent de définir les axes qui interviendront dans le calcul des indicateurs. Au contraire, les calculs sont exécutés à l'identique d'une partition à l'autre ; on peut donc dire que les partitions sont précisément les éléments qui ne jouent pas dans le calcul.

Pour l'instant, ces exemples ne vous ont sans doute pas convaincu d'utiliser la clause MODEL. Nous avons ajouté des tas de lignes de code SQL pour un résultat très simple : une utilisation créative de l'opérateur ensembliste UNION ALL et de la table DUAL nous aurait permis de faire la même chose. L'objectif de cette première partie était simplement de vous montrer les bases de l'utilisation de la clause MODEL. Dans la seconde partie, je vous montrerai les références multi-cellules, les modèles de références et les itérations. Et c'est là que les choses deviendront autrement plus intéressantes.

Script SQL contenant toutes les requêtes utilisées