I. Quelques exemples pour débuter▲
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 cœur 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 reconverti 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 une série de petits exemples sur la table EMP, en commençant par les plus simples, et en introduisant progressivement de nouveaux éléments. À 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éesVoici 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éesNous 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 clé unique pour les lignes qui vont être traitées par la clause MODEL. Cette limite est la plus importante (elle peut imposer une agré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 systé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 tous les indicateurs sont reconvertis 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 interditeAvec 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éesLa 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
quatre lignes sélectionnéesLa 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éeTous 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éeUne 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éesUne nette différence ! 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 multicellules, les modèles de références et les itérations. Et c'est là que les choses deviendront autrement plus intéressantes.