…un centile est chacune des 99 valeurs qui divisent les données triées en 100 parts égales, de sorte que chaque partie représente 1/100 de l’échantillon de population…
http://fr.wikipedia.org/wiki/Centile

Un quantile est la version générique du centile puisque vous remplacez 100 par ce que vous voulez (ex : décile pour 10 et quartile pour 4).
http://fr.wikipedia.org/wiki/Quantile

C’est une méthode utilisée en statistiques pour, par exemple, supprimer de l’échantillon les valeurs limites. On parle d’ailleurs du 1er centile ou du dernier centile (ie. le 99ème) que l’on calcule.

Pour information, vous pouvez souvent trouver le terme Percentile qui est tout simplement centile en anglais.

Les présentations sont faites, rentrons dans le sujet avec son application en MDX.

Nous allons calculer la valeur du 1er centile des Ventes de la base AdventureWorks. Le modèle est simple, des ventes par date (date-mois-année) et par produit.

Nous sommes sur une mesure statistique donc nous travaillons toujours par rapport à un échantillon de valeurs.

La formule générale du centile est de prendre la valeur du

p(n+1)/100 –ème élément

p : centile recherché (ici 1 car nous voulons le 1er centile)
n : nombre d’éléments dans l’échantillon

Pour faire simple, si j’ai 100 valeurs, je prends la première.

Tout cela avec un échantillon ordonné, cela va de soit.

Définir l’échantillon

Nous sommes dans un calcul statistique, l’échantillon doit avoir un nombre de valeurs significatives et doit être idéalement non agrégé.
En effet, si je calcule le 1er centile sur des échantillons à une granularité mensuelle, je n’aurai que 12 valeurs quand je consulterai une année et mon centile sera la somme des ventes d’un de ces mois.

On voit donc un point important, il faut définir sur quoi on fait l’échantillonnage : quelle dimension et quelle granularité.
Le centile sera toujours en rapport à une lecture bien particulière, surtout dans un cube où l’on travaille constamment avec des agrégations.

Pour notre exemple, je vais descendre à la granularité commande (SalesOrderNumber). Je rajoute donc un nouvelle dimension basée sur ma table de fait avec comme attribut le n° de commande.

On voit qu’on a de nombreuses ventes pour chaque mois, ce qui nous donne un nombre intéressant d’échantillons pour faire nos calculs.

Notez que ces échantillons ne sont pas les lignes de la table de faits. On est déjà à un niveau agrégé car la table des ventes descend encore d’un cran, à la ligne de commande (on le voit à partir de juillet 2007).

On a calcule bien le 1er centile des commandes.

Calcul du rang

Maintenant qu’on a notre échantillon, le calcul du rang du premier centile est simple. Il suffit de diviser par 100.

Mais on divise quoi par 100 ? Et bien le nombre d’échantillons que l’on regarde (ie. le nombre de commandes).

On pourrait faire un simple COUNT

WITH MEMBER NbEchantillons AS
Count([Commandes].[Commande].[Commande].Members)

Sauf que l’on perd le contexte et il me ramène l’ensemble des membres de la dimension.

Pour résoudre cela, j’utilise la fonction NONEMPTY() qui va filtrer les commandes ayant des ventes dans le contexte actuel (ie. pour chaque mois).

WITH MEMBER NbEchantillons AS
Count(
   NONEMPTY(
      [Commandes].[Commande].[Commande].Members,
      [Measures].[Ventes]
   )

Sinon, vous pouvez aussi créer une mesure directement dans le cube qui vous donne l’information. Ici, j’ai mis un DistinctCount sur SalesOrderNumber.

Et ce rang alors ? Et bien le premier centile sera la commande se trouvant à la position :

MEMBER Rang1erCentile AS Int(([NbEchantillons]+1) * .01)

Il faudra penser à retirer 1 car l’indexation des éléments dans un SET est basée sur 0.

Récupération du 1er centile

Maintenant que nous avons le rang, il suffit de :

1. Ordonner le SET des commandes
2. Récupérer le n-ième membre
3. Récupérer la valeur de notre mesure pour ce membre

Pour ordonner un SET, on peut avoir recours à la fonction ORDER mais étant donné que nous cherchons dans les premiers éléments du SET, on préfèrera BOTTOMCOUNT qu’on limite au rang recherché (rappelez vous, il y a une différence de numérotation).

BOTTOMCOUNT( Commandes, Rang, Mesure )

Récupérer un élément dans un SET peut se faire de différente façon. On pense à TAIL mais j’utilise ITEM

Enfin, pour récupérer la valeur des ventes du premier centile, il suffit de croiser le membre récupéré avec ITEM avec la mesure. Pour cela, on utilise simplement la syntaxe d’un tuple ().

Cela donne au final :

MEMBER [1erCentile] AS
(BOTTOMCOUNT(
   (NONEMPTY(
      [Commandes].[Commande].[Commande].Members,
      [Measures].[Ventes])
   ),
   RangCentile,
   [Measures].[Ventes]
).Item(RangCentile-1), [Measures].[Ventes])

 

Et si je ventile sur l’axe produit ?

Qu’importe ! Nous avons développé ce calcul en testant sur l’axe temps mais nul part cette dimension n’est référencée dans le calcul.

Si je ventile ma mesure 1er centile sur les catégories de produits, j’aurai la valeur du premier centième des commandes de cette catégorie, quelque soit le mois ou l’année.

Evidemment, vous pouvez croiser Produit, Temps, ainsi que toutes les autres dimensions disponibles dans le cube. Ce n’aura juste aucun sens de ventiler sur la dimension Commandes.

 

Interpolation numérique

Et ce calcul, il est précis ?

Eh bien, ça dépend. En effet, si vous avez 336 échantillons, le premier centile est en fait un peu entre la 3ème et le 4ème valeur…

Pour corriger, cela, on a donc une méthode alternative de calcul, l’interpolation. Je vous rassure, elle ne change pas énormément.

On va tout simplement faire une pondération entre la valeur qu’on a au rang de notre centile et du rang suivant (oui, comme un barycentre).
Ainsi, si notre rang était en réalité 2,2, on va prendre le rang 2 + 20% de la différence avec le rang 3.

On a donc besoin de

MEMBER RangCentileReel AS ([NbEchantillons]+1) * .01 

(vous remarquerez qu’il n’y a plus d’arrondi)

MEMBER [1erCentileSuivant] AS
(BOTTOMCOUNT(
   (NONEMPTY(
      [Commandes].[Commande].[Commande].Members,
      [Measures].[Ventes])
   ),
   RangCentile + 1,
   [Measures].[Ventes]
).Item(RangCentile), [Measures].[Ventes])

MEMBER [Centile] AS
 [1erCentile] + ([1erCentileSuivant] - [1erCentile]) * ([RangCentileReel] - [RangCentile])

 

En conclusion

Le calcul est en fait plutôt simple mais retenez surtout qu’il dépend d’un sens de lecture qui a un impact important.

Ensuite, faites attention aux performances car il faut compter et ordonner la dimension utilisée comme référence. L’utilisation si possible d’une mesure permettra de mettre les bonnes agrégations et booster le comptage.

Aussi, notez qu’il y a des calculs de centile inclus ou exclus. Ca change juste le rang pris en compte (bref, je résume).

Enfin, merci à l’article de Richard Mintz qui m’a fournit une aide précieuse pour améliorer ma mesure.

Prochain article, la même chose en DAX pour PowerPivot et vos modèles tabulaires .

Voici le script complet pour AdventureWorks (avec une cube simplifié).

WITH 
MEMBER NbEchantillons as Count(
   NONEMPTY(
      [Commandes].[Commande].[Commande].Members,
      [Measures].[Ventes])
)

MEMBER RangCentile AS Int(([NbEchantillons]+1) * .01)
MEMBER RangCentileReel AS ([NbEchantillons]+1) * .01 

MEMBER [1erCentile] AS
(BOTTOMCOUNT(
   (NONEMPTY([Commandes].[Commande].[Commande].Members , [Measures].[Ventes]),
   RangCentile,
   [Measures].[Ventes]
).Item(RangCentile-1), [Measures].[Ventes])

MEMBER [1erCentileSuivant] AS
(BOTTOMCOUNT(
   (NONEMPTY([Commandes].[Commande].[Commande].Members , [Measures].[Ventes]),
   RangCentile - 1,
   [Measures].[Ventes]
).Item(RangCentile), [Measures].[Ventes])

MEMBER [Centile] AS
  [1erCentile] + ([1erCentileSuivant] - [1erCentile]) * ([RangCentileReel] - [RangCentile])

SELECT
  {NbEchantillons, [Measures].[Ventes], RangCentile, RangCentileReel, [1erCentile], [1erCentileSuivant], [Centile] } ON 0,
  [Temps].[Temps].[Mois].members ON 1
FROM [Ventes]