[SSAS] Afficher une Parent Child sur un seul niveau avec MDX

Curieuse demande en apparence, mais finalement assez compréhensible. Lorsque l’on utilise une hiérarchie – ce qui est très pratique pour certains états – il est fréquent de vouloir aussi disposer des informations sous une forme non-hiérarchisée, une sorte de liste, et que cette information soit disponible sous Excel 2007.

C’est particulièrement le cas fonctionnellement dans le domaine du reporting financier et plus techniquement dans tous les domaines où tous les niveaux hiérarchiques peuvent porter des données, donc des dimensions généralement en Parent-Child.

Pour générer une liste de tous les attributs d’une Parent-Child à plat, rien de plus simple: la hiérarchie d’attributs sur laquelle s’appuie la Parent-Child le fait nativement.
Par exemple la hiérarchie Employee (sans S) d’AdventureWorks liste bien tous les Employés… mais lorsqu’on y associe une mesure elle n’est capable d’afficher que les DataMember, autrement dit les mesures associés à ce membre précis et non la valeur aggrégée que la hiérachie rapporte.


Par exemple l’on regarde les valeurs de la Parent-Child (Employees) on constate:
1) Que Ken Sanchez et Brian Welcker n’apparaissaient pas dans la hiérarchie d’attributs: ils n’ont pas de faits associés.
2) Qu’Amy Alberts elle apparaît bien à plat mais avec une valeur fausse: la valeur affichée est en effet celle de son DataMember et pas l’aggrégation de celui ci et de ses enfants.


L’idée est donc de recopier dans la hiérarchie d’attributs la valeur de la Parent-Child.
L’instruction LinkMember permet de récupérer un membre d’une hiérarchie à partir d’un membre d’une autre du moment qu’ils partagent la même clé (ce qui est le cas ici). Certes ce n’est pas le top niveau performance (même si dans la même dimension c’est légèrement mieux), mais de toute manière cela ne concerne pas des dimensions de cardinalité trop élevée vues les applications métier.

On peut réaliser cette affectation via un SCOPE dans le script MDX, de cette manière:

SCOPE
(
[Employee].[Employee].[Employee].Members
);
This = LinkMember([Employee].[Employee].CurrentMember,[Employee].[Employees]);
END SCOPE;

Le résultat est tout bonnement enthousiasmant et l’effet immédiat:


Les noeuds sans données apparaissent (Welcker, Sanchez…) ceux composites sont corrigés (Amy Alberts). Bref tout semble parfait. Sauf qu’il y a un hic.
Si l’on re-regarde la Parent-Child, qui s’appuie sur cette hiérarchie d’attributs, on constate que les chiffres sont complétement faux. En effet, la Parent-Child tire ses données… de la hiérarchie d’attributs… qui elle même tire ses données de la Parent-Child.

Par exemple Amy Alberts est égale à son Data Member plus la somme de ses enfants. Son DataMember vaut 732.078,44 – voir premier screenshot – et la somme de ses enfants 14.803.867,81 – merci calc.exe -, le tout faisant 15.935.546,26, la valeur affichée dans la hiérarchie initialement.
Or ici on a:
15.935.546,26 (la valeur réaffectée dans la hiérarchie d’attributs, que SSAS considère à tort comme le DataMember)
+ 14.803.867,81 (la somme des enfants)
=30.339.814,07


Il faut donc verrouiller la ParentChild avant de faire la modification de la hiérarchie d’attributs, ce que l’on peut faire avec l’instruction Freeze.
Le script précédent devient alors:

FREEZE([Employee].[Employees].Members);
SCOPE
(
[Employee].[Employee].[Employee].Members
);
This = LinkMember([Employee].[Employee].CurrentMember,[Employee].[Employees]);
END SCOPE;

Du coup le résultat devient beaucoup plus acceptable:

La hiérarchie peut donc maintenant faire un rapport Excel à mettre à disposition via Excel Services par exemple, permettant d’analyser des membres de niveaux différents dans la même colonne. C’est l’idée générale qui permet de faire des rapports sur des statistiques, des comptes d’exploitation choisis et mis à plat, pour ne prendre que des exemple communs utilisant les Parent-Child…

En espérant vous avoir donné des idées, mais attention cependant, il s’agit clairement d’une manip (les parent-child et leur hiérarchie d’attributs sont très liées) dont je n’ai pas eu le temps de tester extensivement toutes les implications. Si vous constatez un comportement étrange n’hésitez pas à le mettre en commentaire.

A bientôt!

PS: avec Excel 2010 ceci serait obsolète: les PivotTables disposent d’un Set Designer, il suffit alors de se créer un jeu contenant les employés souhaités. Mais 2007 est encore là pour quelques années…

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s