par Lionel Billon - Mis en ligne le 05/04/2006 - Publié en Février 2005
Outre les membres calculés, MS Analysis Services propose plusieurs mécanismes sophistiqués pour que vous puissiez surcharger les agrégations d’un cube. Dans le précédent article, nous avions parlé des Custom Members. A présent, nous allons aborder la notion de “Custom Rollup” et de Cellules calculées.Les notions de Membres calculés, Custom Member, “Custom Rollup” et Cellules calculées ont des points communs : elles permettent de surcharger les agrégations du cube et sont définis en MDX. Elles ont également des différences, dont voici un bref rappel :
- Le Membre Calculé est associé à une dimension, il permet d’ajouter un calcul MDX matérialisé sous forme d’un nouveau membre de cette dimension.
- Le Custom Member est lui aussi associé à une dimension. Il permet cependant d’externaliser le calcul MDX d’un membre calculé classique au sein d’une table relationnelle. L’avantage par rapport au membre calculé est donc de pouvoir bénéficier automatiquement de ce calcul dans l’ensemble des cubes utilisant la dimension hôte du "custom member", sans redéfinir le calcul. (Ce qui facilite grandement les tâches d’administration).
- Le “Custom Rollup” est également associé à une dimension, plus précisément à un niveau d’une dimension. Il s’applique à tous les membres du niveau sur lequel il est appliqué à l’exception des membres calculés. Tout comme le Custom Member, le “Custom Rollup” est réagrégé automatiquement.
- La Cellule Calculée est sans doute l’outil le moins connu et le moins utilisé de MS Analysis Services. Souple et versatile, la Cellule Calculée permet de définir un calcul s’appliquant à un sous ensemble de cube (tranche de cube) sans création de membre supplémentaire. La Cellule Calculée vient tout simplement remplacer la valeur affichée dans les cellules du cube comprises dans la tranche qu’elle couvre. Si les membres calculés peuvent être, dans une certaine mesure, récursifs, les cellules calculées sont les seules à pouvoir être exécutées plusieurs fois tout en pouvant accéder aux valeurs obtenues lors des passes précédentes. Ce qui peut être fort utile pour les calculs itératifs de type « Goal Seeking ». (Par exemple, dans l’utilisation d’un calcul cherchant le pourcentage de rétribution optimal pour les commerciaux. Le calcul pourrait évaluer plusieurs scénarios : Un premier test pourrait être effectué avec une rétribution de 10%, cependant si après rétribution la rentabilité n’est plus conforme aux objectifs, le calcul pourrait tester une rétribution de 5% et si nécessaire baisser la rétribution à 3%.)
Après cette courte introduction, reprenons les notions de “Custom Rollup” et de Cellules calculées en détail. Afin de mieux vous approprier ces concepts, je vous invite vivement à réaliser les exemples au fur et à mesure de votre lecture.
Les “Custom Rollup” se définissent directement dans l’éditeur de cube au niveau des propriétés avancées des niveaux des dimensions. En effet, le calcul MDX du “Custom Rollup” s’applique à tous les membres du niveau de la dimension choisie à l’exception des membres calculés. Si le calcul est appliqué à un niveau inférieur au premier niveau, il sera automatiquement ré-agrégé par Pivot Table Services conformément à la fonction d’agrégation de la mesure rencontrée par le calcul. Attention : les “Custom Rollup” sont incompatibles avec les cubes contenant une mesure dont la fonction d’agrégation est Distinct Count.
Afin de bien comprendre l’intérêt des “Custom Rollup”, nous allons l’utiliser au travers d’un exemple.
Pour cela, j’ai fait une copie du cube Sales de la base exemple Foodmart 2000, en ne gardant que les dimensions Customer, Time et Product ainsi que les mesures Unit Sales et Sales Dollars. J’ai nommé ce cube SalesSimple, il servira de base aux exemples de cet article.
Nous allons mettre la dimension Product en ligne et la dimension mesure en colonne. Nous allons utiliser les “Custom Rollup” pour surcharger les agrégations du cube : pour chaque Famille de produit nous afficherons la moyenne des unités vendues et du chiffre d’affaire des Départements Produits au lieu de les sommer.
Ainsi par exemple, avant l’ajout des “Custom Rollups”, nous avons le résultat suivant (Voir Figure 1).
Associons, un “Custom Rollup” au niveau Product Family (Famille Produit) de la dimension Product. Le “Custom Rollup” est défini dans l’éditeur de Cube depuis l’onglet avancé du niveau Product Family de la dimension Product. Voir Figure 2.
Pour que l’agrégation des membres du niveau Product Family, soit remplacée par la moyenne des Product Département, il nous suffit de définir le calcul suivant :
Avg({Product.currentmember.Children})
pour la propriété “Custom Rollup” Formula comme spécifié en figure 2.
Vous devez obtenir le résultat suivant (Voir Figure 3).
Les chiffres obtenus pour Drink, Food et Non- Consumable sont désormais des moyennes. (Pour les Unit Sales en Drink, 8 199 est bien la moyenne de 6 838, 13 573 et 4 186).
Vous remarquerez également que les Chiffres obtenus pour le membre All Products n’est pas exactement ce à quoi nous nous attendions. Par exemple, pour les Unit Sales les 266 731 de la figure 1 sont devenus 31 042.20 qui n’est autre que la somme des résultats des Customs Rollup pour Drink, Food et Non- Consumable. En fait, les “Custom Rollup” défini au niveau Product Family a été agrégé en fonction de la fonction d’agrégation Sum définie sur la mesure Unit Sales.
En fait, il serait préférable ici d’obtenir la moyenne des « Product Family » (Familles de produits). Pour ce faire, il suffit d’ajouter dans l’éditeur de cube un “Custom Rollup” au niveau All de la dimension Product. Nous lui affectons le calcul Avg({Product.currentmember. Children}) comme spécifié en Figure 4 .
Une fois le cube sauvegardé et calculé vous devez obtenir les résultats de la Figure 5.
Téléchargez cette ressource
Guide inmac wstore pour l’équipement IT de l’entreprise
Découvrez toutes nos actualités à travers des interviews, avis, conseils d'experts, témoignages clients, ainsi que les dernières tendances et solutions IT autour de nos 4 univers produits : Poste de travail, Affichage et Collaboration, Impression et Infrastructure.
