Lors d’une récente mission chez un client nous avons été confronté au besoin d’agréger des mesures de cube sous forme de médiane.
Intérêt d’agréger une mesure en médiane
Concrètement, ce besoin revient à retourner la valeur médiane d’une mesure pour les dimensions en cours, plutôt que classiquement la somme ou la moyenne. Sur un exemple basique, pour une mesure Chiffre d’Affaire, autour d’un axe d’analyse Région, le besoin client est de retourner la valeur médiane du CA de chacune des régions :
EX : [Bretagne=10M€, Sud Ouest=17M€, PACA=23M€, Rhone Alpes=31M€, IDF=51M€ ]
Le CA au niveau national sera la valeur médiane de la liste des valeurs régions, soit 23M€.
Fonctionnellement prendre la valeur médiane (plutôt que la valeur moyenne) a pour effet de minimiser l’impact des valeurs extrêmes, et donc de minimiser le « bruit » que pourrait provoquer des erreurs de saisie ou de chargement.
EX : Si l’on rajoute [Alsace=300M€] à la liste précédente (ce qui pourrait correspondre à une erreur de saisie – avec un zéro de trop)
La valeur médiane au niveau national est 27M€
=> L’impact de l’erreur de saisie de l’opérateur Alsacien sur le CA national est minimisé. Une agrégation en moyenne aurait « tiré vers le haut » le CA national (moyenne = 72M€)
Sur l’exemple donné – qui est volontairement simpliste – le bénéfice de l’agrégation en médiane est plutôt léger, mais sur une liste de valeurs plus importante, il y a de nombreux cas fonctionnels où la médiane d’une liste de valeurs est bien plus représentative (et intéressante pour l’utilisateur) que la moyenne .
Problématique
Le problème vient du fait que les agrégation de type médiane n’est pas supporté nativement par Analysis Services.
Types d'agrégation supportés par SSAS
Solution
La solution que nous avons proposée est d’utiliser la fonction MDX MEDIAN(). C’est une fonction mathématique de base qui retourne la valeur médiane d’une mesure pour un « set » donné.
Elle doit être appelée avec 2 paramètres :
- L’ensemble de valeurs sur lequel « ventiler » la valeur médiane, le « set »
- et la mesure à agréger
Dans le cas du CA par région cela donnerait une mesure calculée du style :
CA_National = MEDIAN( [Geography].[Region].Members, (= liste de valeurs)
[Measures].[CA]) (= mesure à agréger)
=> Cette solution fonctionne et retourne effectivement la valeur médiane du CA de chaque région
En allant plus loin
On remarquera qu’en réalité la solution proposée n’agrège pas – à proprement parler – en médiane : elle calcule la valeur médiane d’une mesure existante. Et ce n’est pas tout à fait la même chose ! Car cette mesure existante a son propre type d’agrégation…
C’est à dire, qu’elle calcule la valeur médiane, d’une liste de valeurs qui restent à leur type d’agrégation par défaut. Dans l’exemple, la formule d’agrégation ne fonctionne qu’au niveau national : la valeur retournée est la médiane des valeurs CA au niveau région, valeurs qui sont elle même agrégées… dans leur type d’agrégation par défaut définit dans SSAS (c’est à dire Somme, Moyenne etc…)
Le besoin de notre client était plus complexe que cela : il souhaitait une valeur médiane d’une mesure quelque soit l’axe d’analyse et quelque soit le niveau de navigation dans la dimension.
Par exemple sur une mesure DélaisRésolutionIncident, le souhait est de voir retournée la valeur médiane (de toute les valeurs disponibles) quelque soit l’axe d’analyse : que ce soit au niveau d’un mois, d’une année, d’un service, d’une région, d’une sous région etc.
Nous avons proposé une solution calée sur la précédente : utiliser la fonction MEDIAN en passant cette fois comme « set » l’ensemble des lignes de faits disponibles. Cela donne quelque chose comme :
MEDIAN_DelaisResIncident = MEDIAN( [DimFait].[DimFait hierarchy].[DimFait fact line].Members, (= ensemble des lignes de fait)
[Measures].[ValDelaisResolutionIncident]) (= mesure à agréger)
Pour cette formule nous avons du créer dans le cube – via le DSV – une dimension de fait dont le nombre de lignes au niveau le plus fin est exactement le même que celui de la table de fait. Cette dimension « virtuelle » ne sert qu’à définir le « set » de valeurs.
=> Cette solution fonctionne et permet une réelle agrégation de mesure sous forme de médiane.
PS : Ce second exemple illustre bien l’intérêt de la médiane évoqué en introduction : si par exemple un incident est resté non clôturé plusieurs mois, alors que généralement ils le sont en quelques minutes, cette anomalie « pourrira » l’indicateur de moyenne. L’indicateur de médiane en revanche sera bien plus pertinent pour l’utilisateur.
Temps de réponse
Si la solution proposée fonctionne sur le papier et avec nos jeux de tests, elle s’est révélée un peu décevante dans la pratique. Avec l’augmentation du nombre de lignes de faits, le temps de réponse du cube explose lorsque l’on fait appel à la mesure utilisant la médiane.
En effet, avec un nombre de ligne de l’ordre de plusieurs dizaines de milliers d’enregistrements, le temps de réponse dépasse les 2 minutes (le « time out » de notre outil de restitution). A titre de comparaison, l’agrégation classique en moyenne, sur les mêmes données met moins d’une seconde…
=> Cette solution est donc à utiliser avec précaution.
Analyse
Mais pourquoi cela prend-t-il autant de temps ?
Cette fonction MDX MEDIAN est à l’origine une fonction mathématique, pas une fonction d’agrégation. Les valeurs ne sont donc pas pré-calculées avec cette médiane comme elles le seraient avec les agrégations en Moyenne ou en Somme. La solution proposée est en réalité une utilisation détournée de la fonction MEDIAN
Dans tous les exemples d’utilisation que l’on trouve de cette fonction, le premier paramètre (le « set ») est de cardinalité très faible. Alors que dans le cas exposé ci-dessus l’ensemble de « ventilation » est l’ensemble des lignes de faits, donc un ensemble à cardinalité plutôt élevée.
La fonction Median ne permet pas de pré-aggrégation dans la mesure ou son algorithme de résolution nécessite 2 « passages » sur la liste de valeur (là où la Moyenne n’en nécessite qu’un) : un passage pour trier toutes les valeurs, un autre pour prendre la moyenne des deux valeurs du milieu de la liste triée.
Dans le premier exemple avec le CA par région, en réalité le cube se base sur les pré-agrégations sous forme de Somme du CA stockées pour chaque région, et calcule ensuite la médiane de ces pré-agrégations. L’algorithme des 2 « passages » n’est en fait déroulé que sur une liste de 22 valeurs. Tandis que dans notre cas il est déroulé sur le nombre de lignes de fait.
=> La fonction MDX MEDIAN n’est donc à utiliser pour agréger une mesure que sur des tables de faits de faible population.
Commentaires récents