Fonctions analytiques Oracle dans des indicateurs BO [Résolu]

[Antoun]

Bonjour,

Je commence à me mettre aux fonctions analytiques, avec pour but de les utiliser pour certains calculer indicateurs d'un univers BO.

Mon premier essai a été assez décevant, tout était OK avec Toad, mais ensuite j'ai construit un indicateur du genre SUM(montant * RATIO_TO_REPORT(...) OVER(...)), et Oracle m'a envoyé un message très frustrant du genre "les fonctions de fenêtrage sont interdites ici".

J'imagine que je devrais sortir mon RATIO_TO_REPORT du SUM et l'utiliser comme fonction d'agrégation, mais je n'ai pas encore testé.

Plus largement, certains d'entre vous ont-ils déjà utilisé les fonctions analytiques dans un univers BO ? avez-vous plus d'expérience sur leur comportement face à un GROUP BY ?

[Michel SALAIS]

Je n'ai pas utilisé dans un univers BO mais comme tu l'as remarqué les fonctions analytiques sont uniquement utilisables dans la liste de la clause SELECT. Donc même avec GROUP BY, il est impossible d'agréger sur une fonction analytique.

Maintenant, la fonction analytique est utilisable avec GROUP BY si toute colonne référencée dans la fonction analytique apparaît également dans la clause GROUP BY. Mais le sens n'est peut-être pas ce que tu cherche! A titre d'exemple la requête suivante

Code :

1
2
3
SELECT sum(c) over()
FROM t
GROUP BY c

doit être comprise comme

Code :

1
2
3
4
5
6
7
SELECT sum(c) over()
FROM
(
  SELECT c
  FROM t
  GROUP BY c
)

Exemple avec l'agrégat COUNT()

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT job, count(*)
FROM emp
GROUP BY job
 
JOB       COUNT(*)               
--------- ---------------------- 
CLERK     4                      
SALESMAN  4                      
PRESIDENT 1                      
MANAGER   3                      
ANALYST   2                      
 
5 rows selected
 
SELECT job, count(*) over()
FROM emp
GROUP BY job
 
JOB       COUNT(*)OVER()         
--------- ---------------------- 
ANALYST   5                      
CLERK     5                      
MANAGER   5                      
PRESIDENT 5                      
SALESMAN  5                      
 
5 rows selected
 
SELECT job, count(*) over()
FROM
(
  SELECT job
  FROM emp
  GROUP BY job
)
 
JOB       COUNT(*)OVER()         
--------- ---------------------- 
ANALYST   5                      
CLERK     5                      
MANAGER   5                      
PRESIDENT 5                      
SALESMAN  5                      
 
5 rows selected

[Marc Musette]

quel est le code d'erreur exact et quel est ton sql complet ?

[Antoun]

Le message d'erreur est

Code :

ORA-30483: window functions are NOT allowed here

Le SQL de mon indicateur est qqch comme ça :

Code :

SUM(montant * RATIO_TO_REPORT(montant) OVER (personne, projet) * taux_de_conversion)

Mais en fait le problème n'est pas là. C'est un indicateur BO, donc il doit pouvoir être utilisé tel quel avec un GROUP BY inconnu à l'avance. D'après les exemples de Michel, la fonction analytique ne respecte pas les limites du GROUP BY, mais seulement son OVER. Or, je n'ai aucun moyen de modifier dynamiquement ce code pour qu'il reflète le GROUP BY.

J'en déduis que les fonctions analytiques sont inutilisables dans BO, à moins de passer par des vues ou des "tables dérivées" qui réduisent leur intérêt...

Je me déplace dans le forum BO, histoire de voir si quelqu'un a une expérience à ajouter...

[Bruno2r]

Bonjour, Antoun, Michel et Marc ...

Suis super content de voir des passionnés de SQL Oracle sur le forum BO, c'est pas si fréquent, et pourtant... une grosse part de la richesse de l'outil se détermine bien là dans Designer.

Au sujet de l'emploi des fonctions analytiques et du Group by permanent de BO j'aurais deux remarques à formuler.

Les fonctions analytiques dans le select de l'objet ne peuvent être que refusées car BO serait bien en peine d'imbriquer (ou plutôt fédérer) des termes de select aussi disparates que ceux que l'utilisateur peut générer par son choix d'objets.

Le group by automatique dans BO peut s'éviter si besoin est en annulant dans la définition des objets la sommation par défaut en cas d'agrégation.

Enfin, je n'ai pas testé les fonctions analytiques dans Designer (mon parti pris étant d'appuyer mes univers sur des tables infocentre précalculées selon des axes d'agrégations précis où l'emploi des fonctions analytiques se situe dans mon PL/SQL de traitement et pas dans l'univers).

Pour autant, cela vaudrait peut être la peine d'employer ces fonctions analytiques non pas dans le select de l'objet mais dans une Table Dérivée (à partir de la V6). Ce qui fait que, pour reprendre l'exemple de Michel,on peut imaginer (je n'ai pas testé) obtenir

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Select TableDérivée.departement, TableDérivée.JOB, TableDérivée.NB
FROM 
(SELECT departement, job, count(*) over() as NB
FROM emp
GROUP BY departement, job) TableDérivée;
 
puis le cas échéant dans la requête BO
 
Select TableDérivée.departement, Sum(TableDérivée.NB)
FROM 
(SELECT departement, job, count(*) over(...) as NB
FROM emp
GROUP BY departement, job) TableDérivée
GROUP BY TableDérivée.departement;

A tester ....