Cet article fait suite à l’article "Help for Hierarchies" de Ralph Kimball, publié en septembre 1998, qui traite des structures hiérarchiques de profondeur variable. Ces structures sont le plus souvent représentées dans les bases de données relationnelles comme des relations récursives.
Ci-dessous, la définition d’une dimension Entreprise simple qui contient ce type de relation récursive entre la clé étrangère PARENT_CLE et la clé primaire ENTREPRISE_CLE :
Create table ENTREPRISE(
ENTREPRISE_CLE INTEGER NOT NULL,
ENTREPRISE VARCHAR2(50),
PARENT_CLE INTEGER);
Bien que cela soit efficace pour stocker des informations sur les structures organisationnelles, il n'est pas possible de naviguer ou de cumuler des faits au sein de ces hiérarchies en utilisant le SQL généré par les outils d'interrogation du commerce. Pour résoudre ce problème, l’article original de Ralph décrit une table d'aide semblable à celle ci-dessous qui contient un enregistrement pour chaque chemin et pour chaque entreprise dans l'arbre de l'organisation elle-même et pour chaque filiale en dessous d’elle.
Create table STRUCTURE_ENTREPRISE (
PARENT_CLE INTEGER NOT NULL,
FILIALE_CLE INTEGER NOT NULL,
FILIALE_NIVEAU INTEGER NOT NULL,
NUMERO_SEQUENCE INTEGER NOT NULL,
PREMIER_NIVEAU CHAR(1),
DERNIER_NIVEAU CHAR(1),
ENTREPRISE_MERE VARCHAR2(50),
FILIALE VARCHAR2(50));
Les deux dernières colonnes dans cet exemple - qui dénormalisent les noms de société - ne sont pas strictement nécessaire mais ont été ajoutées pour simplifier la vue.
La procédure stockée PL/SQL qui suit propose une technique possible pour remplir cette table sous Oracle:
/* début de la procédure */
CURSOR Get_Roots is
select
ENTREPRISE_CLE as RACINE_CLE,
decode(PARENT_CLE,
NULL,'Y','N') as PREMIER_NIVEAU,
ENTREPRISE as ENTREPRISE_RACINE
from ENTREPRISE;
BEGIN
For Racine in Get_Roots
LOOP
insert into ENTREPRISE_STRUCTURE
(PARENT_CLE,
FILIALE_CLE,
FILIALE_NIVEAU,
NUMERO_SEQUENCE,
DERNIER_NIVEAU,
PREMIER_NIVEAU,
ENTREPRISE_MERE,
FILIALE)
select
Racine.RACINE_CLE,
ENTREPRISE_CLE,
NIVEAU - 1,
ROWNUM,
'N',
Racine.PREMIER_NIVEAU,
Racine.ENTREPRISE_RACINE,
ENTREPRISE
from ENTREPRISE
Start with ENTREPRISE_CLE = Racine.RACINE_CLE
connect by prior
ENTREPRISE_CLE = PARENT_CLE;
END LOOP;
update
ENTREPRISE_STRUCTURE
set DERNIER_NIVEAU = 'Y'
where not exists
(select * from ENTREPRISE
where PARENT_CLE =
ENTREPRISE_STRUCTURE.FILIALE_CLE);
COMMIT;
END;
/* fin de la procédure */
Cette solution utilise l’extension Oracle CONNECT BY SQL pour construire chaque arbre. Alors que CONNECT BY est très utile dans cette procédure, elle ne peut pas être utilisée par un outil de requête ad hoc. Si l'outil peut générer cette syntaxe pour explorer la relation
récursive il ne peut pas, dans la même instruction, faire une jointure avec une table de faits. Même si Oracle pouvait supprimer cette limitation, les temps d’exécution de la requête ne serait probablement pas très bons.
Les données suivantes d’une entreprises fictive vous aidera à comprendre la table STRUCTURE_ENTREPRISE et la procédure ENTREPRISE_HIERARCHIE_SP:
/* l'ordre des colonnes est ENTREPRISE_CLE, ENTREPRISE, PARENT_CLE */
insert into ENTREPRISE values (100,'Microsoft',NULL);
insert into ENTREPRISE values (101,'Software',100);
insert into ENTREPRISE values (102,'Consulting',101);
insert into ENTREPRISE values (103,'Products',101);
insert into ENTREPRISE values (104,'Office',103);
insert into ENTREPRISE values
(105,'Visio',104);
insert into ENTREPRISE values (106,'Visio Europe',105);
insert into ENTREPRISE values (107,'Back Office',103);
insert into ENTREPRISE values (108,'SQL Server',107);
insert into ENTREPRISE values (109,'OLAP Services',108)
insert into ENTREPRISE values (110,'DTS',108);
insert into ENTREPRISE values (111,'Repository',108);
insert into ENTREPRISE values (112,'Developer Tools',103);
insert into ENTREPRISE values (113,'Windows',103);
insert into ENTREPRISE values (114,'Entertainment',103);
insert into ENTREPRISE values (115,'Games',114);
insert into ENTREPRISE values (116,'Multimedia',114);
insert into ENTREPRISE values (117,'Education',101);
insert into ENTREPRISE values (118,'Online Services',100);
insert into ENTREPRISE values (119,'WebTV',118);
insert into ENTREPRISE values (120,'MSN',118);
insert into ENTREPRISE values (121,'MSN.co.uk',120);
insert into ENTREPRISE values (122,'Hotmail.com',120);
insert into ENTREPRISE values (123,'MSNBC',120);
insert into ENTREPRISE values (124,'MSNBC Online',123);
insert into ENTREPRISE values (125,'Expedia',120);
insert into ENTREPRISE values (126,'Expedia.co.uk',125);
/* fin du jeu de données */
La procédure prendra les 27 lignes et créera 110 enregistrements dans la table STRUCTURE_ENTREPRISE représentant 27 nœuds et 26 arbres plus petits. Pour les grands ensembles de données, vous pouvez constater que la performance peut être améliorée en ajoutant des index sur certaines colonnes concaténées. Dans cet exemple, un index sur ENTREPRISE_CLE, PARENT_CLE, et un autre sur PARENT_CLE, ENTREPRISE_KEY.
Si vous souhaitez voir la structure de l’arborescence textuellement, la requête suivante affiche une liste indentée pour Microsoft :
select LPAD( ' ',
3*(FILIALE_NIVEAU)) || FILIALE
from ENTREPRISE_STRUCTURE
order
by NUMERO_SEQUENCE
where PARENT_CLE = 100
Le champ NUMERO_SEQUENCE à été ajouté depuis l'article original pour numéroter les nœuds de haut en bas et de gauche à droite. Il permet de trier les noeuds afin que ceux de niveau 2 apparaissent en dessous des noeuds de niveau 1 correspondant.
Pour une version graphique de l'arborescence de l'organisation, jetez un oeil au logiciel VISIO 2000 Enterprise Édition qui a un assistant pour créer un organigramme à partir d'une base de données ou d'un fichier texte.
Avec l'aide d'un script VBA, d'une vue sur la table ENTREPRISE_STRUCTURE et d'une table de fait, vous pouvez automatiser la génération des pages HTML que vous voulez.
Source originale: www.kimballgroup.com
Article original "Kimball Design Tip #17: Populating hierarchy helper tables", publié le 14 janvier 2001.
Aucun commentaire:
Enregistrer un commentaire