Conseil 12: Compter correctement avec une dimension enrichie

Dans l’article précédent, nous avons abordé le sujet des calculs basés sur une dimension. Nous pouvons ajouter encore plus de valeur à ces résultats lorsque nous introduisons une table séparée, contenant des attributs supplémentaires et qui croise la table de dimension.

Nous avons récemment eu un exemple simple de ce genre de table complémentaire qui lie les codes postaux à une zone commerciale. Les personnes du marketing voulaient connaitre la répartition des clients par zone commerciale par rapport à l’ensemble de la population. En d'autres termes, nous voulions savoir où avions-nous une forte pénétration géographique, et où celle-ci était moins bonne. Si cette information supplémentaire s'avère précieuse pour l'organisation, nous devrions l'ajouter dans la dimension Client sous la forme d’un nouvel attribut. Mais auparavant, nous avons besoin de faire quelques requêtes pour nous assurer que cela en vaille la peine.

Pour exécuter ces requêtes, nous faisons une jointure entre la table complémentaire et notre dimension Client et nous calculons le nombre de clients par zone commerciale. Cependant, nous devons être prudents parce que les deux ensembles ne se superposent pas à 100%. Il y a des codes postaux dans la table Zone_Commerciale qui n’ont pas de clients correspondants, et il y a des clients dont les codes postaux n'ont pas de correspondance dans la table Zone_Commerciale. Une jointure interne (inner join) masquerait une partie des résultat. Utilisons les deux tables suivantes pour illustrer cela:

Zone_Commerciale               Client

CP         Zone                            Clé_Client        CP

94025   SF-Oak-SJ                  27                     94303

94303   SF-Oak-SJ                  33                     94025

97112   Humboldt                     47                     24116

98043   Humboldt                     53                     97112

00142   Gloucester                   55                     94025

Si nous voulions connaitre le nombre de client par zone commerciale, une jointure interne (inner join) nous donnerait le résultat suivant:

Zone commerciale       Count(Clé_Client)

Humboldt                         1

SF-Oak-SJ                      3

La jointure interne est une jointure d’égalité. Comme il n'y a pas de zone commerciale pour le code postal 24116, la requête ne compte que 4 clients alors que nous en avons réellement 5. On perd également des informations de l'autre côté de la jointure car les résultats ne nous disent pas s’il existe des zones commerciales où la pénétration est nulle (par exemple, Gloucester). 
Réécrire la requête avec une jointure externe (full outer join) nous donnera le résultat suivant:

Zone commerciale       Count(Clé_Client)

NULL                               1

Gloucester                      0

Humboldt                        1

SF-Oak-SJ                     3

Maintenant, nous retrouvons nos 5 clients, et nous voyons qu’il n’y en a aucun à Gloucester. Nous pourrions utiliser une fonction IFNULL sur la colonne des zones commerciales pour remplacer les valeurs NULL par un libellé qui ait plus de sens, tels que "Zone commerciale inconnue". Notez que ce que vous comptez fait une grande différence dans vos résultats. Dans notre cas, nous avons compté le nombre de Clé_Client. Si nous avions utilisé COUNT(*), nous aurions obtenu un total de 7 lignes. Si nous avions écrit COUNT(zone_Commerciale.CP), nous aurions obtenu un total de 6, puisque le code 94025 aurait été compté deux fois.

Vous devez être prudent en utilisant des jointures externes, car vous pouvez facilement ruiner ce raisonnement en mettant une contrainte sur l’une des tables. Par exemple, un filtre "Age_Client < 25" donnerait un résultat avec exactement la même structure et les mêmes rubriques, mais avec un total réduit. Si le rapport n'a pas de titre suffisamment explicite pour l’utilisateur, il pourrait le tromper.

Nous avons trouvé que la combinaison de l’instruction CASE avec la fonction SUM est un bon moyen d’obtenir différents sous-totaux à partir de l'ensemble de données rapporté par la jointure externe. En utilisant les données ci-dessus, nous pourrions créer une requête qui nous donne les totaux des trois cas possibles. Dans la clause SELECT, vous pouvez écrire:

Sum(case when Zone_Commerciale.CP IS NULL then 1 else 0 end) AS

Nombre_De_Client_Sans_Zone_Commerciale,

Sum(case when count(Clé_Client) = 0 then 1 else 0 end) as

Nombre_De_Zone_Commerciale_Sans_Client,

Sum(case when not(Zone_Commerciale.CP IS NULL or count(Clé_Client) = 0) then 1 else 0 end) as

Nombre_De_zone_Commerciale_Avec_Client

Cela vous donne trois colonnes: le nombre de clients sans zone commerciale associée, le nombre zones commerciales sans clients, et un total des correspondances. Les contraintes sont à l’intérieur de l'instruction CASE ainsi elles ne limitent pas le résultat de la jointure externe.

Source originale: www.kimballgroup.com

Article original "Kimball Design Tip #12: Accurate counting with a dimensional supplement", publié le 27 août 2000.