Conseil 10: Vos données sont-elles correctes?

Un problème courant lié à la maintenance de l’entrepôt de données est de vérifier que les données qu'il contient soient correctes. L'entrepôt est-il une image exacte du système source? Le chargement de ce matin c'est-il correctement terminé? Des identifiants sont-ils erronés?

Il n'existe pas de technique unique pour valider un chargement de données, car les sources des données sont bien trop variées. Si vous chargez une image d'une source de production, tout en préservant sa granularité d'origine, alors vous pouvez probablement créer un rapport simple sur le système de production avec des totaux jusqu’à la minute, et vous pouvez utilisez ce même rapport avec l'entrepôt de données. Dans ce cas, vous connaissez la réponse à l'avance et les deux résultats doivent correspondre jusqu’à la dernière décimale.

Mais il est plus habituel de ne pas avoir de point de comparaison connu. Peut-être recevez-vous le détail des ventes de 600 magasins chaque nuit. Dans ce cas, vous pouvez certainement compter le nombre de magasins qui vont ont envoyé des données, mais comment pouvez-vous obtenir un meilleur avis et dire si les données sont "probablement correctes"?

En utilisant, comme exemple, les 600 magasins, examinons le total des ventes pour chaque rayon dans chaque magasin tous les matins et demandons-nous si les chiffres d'aujourd'hui sont "raisonnables". Nous allons décider que les ventes totales d'aujourd'hui sont raisonnables si elles sont inférieures à 3 fois l’écart-type de la moyenne du total des ventes précédentes de ce rayon dans ce magasin. Arrghh. Des statistiques. Mais accrochez-vous: Ce n'est pas si difficile. Nous avons choisi un écart-type de 3, car dans une distribution "normale", 99% des valeurs se situent à moins de 3 fois l’écart type au-dessus ou en dessous de la moyenne.

Je vais d’abord décrire le processus. Après cela, j'ajouterais un peu de SQL. Vous pouvez uniquement lire la description, si vous souhaitez avoir l’idée principale.

Afin de rendre ce processus plus rapide, vous voulez éviter de parcourir l’historique complet des anciennes données lorsque vous calculez l'écart type. Vous pouvez éviter de parcourir l’historique complet en conservant trois cumuls pour chaque rayon de chaque magasin, dans un tableau spécial utilisé seulement si les données sont valides. Vous devez garder le nombre de jours cumulés, le cumul de la somme des ventes de chaque jour (par rayon et par magasin) et le cumul de la somme des ventes au CARRE de chaque jour (par rayon par magasin). Ces valeurs peuvent être conservées dans une table distincte. Le grain de cette table est le couple magasin-rayon et les trois champs numériques NOMBRE_JOURS, SOMME_VENTES et CARRE_SOMME_VENTES sont tous des attributs de type 1 qui sont remplacés chaque jour. Vous pouvez mettre à jour ces trois champs en ajoutant simplement les valeurs du lendemain à celles déjà présentes. Donc si vous avez 600 magasins et 20 rayons dans chaque magasin, ce tableau aura 12 000 lignes, mais ne grandira pas au fil du temps. La table conserve également le nom du magasin et du rayon pour chaque ligne.

Maintenant, en utilisant cette table de cumul par rayon, vous regardez les totaux des 12 000 rayons chargés ce matin, et retirez les lignes qui sont supérieures à 3 fois l’écart-type de la moyenne. Vous pouvez choisir de les examiner si elles ne sont pas trop nombreuses, ou vous pouvez rejeter l’ensemble du chargement s’il existe un trop grand nombre de lignes suspectes.

Si le chargement de ce matin est validé, alors vous communiquez les données aux utilisateurs finaux, et vous mettez à jour la table de cumul par rayon pour être prêt pour le chargement suivant.

Voici quelques requêtes SQL non testées mais qui devraient fonctionner. Rappelez-vous que l'écart type est la racine carrée de la variance. La variance est la somme des carrés de la différence entre chacun des points de données historiques et la moyenne des points de données, divisée par N-1, où N est le nombre de jours de données. Malheureusement, cette formulation nous oblige à parcourir l’historique des ventes, ce qui, bien que possible, ne rend pas le calcul attrayants. Mais si nous conservons l'historique de SOMME_VENTES et CARRE_SOMME_VENTES alors nous pouvons écrire la variance de la manière suivante: (1 / (N-1)) * (CARRE_SOMME_VENTES - (1 / N) * SOMME_VENTES * SOMME_VENTES).

Donc, si nous synthétisons notre formule de la variance par "VAR" alors notre contrôle pour valider les données ressemblerait à cela :

SELECT s.Magasin, p.Rayon, sum(f.Ventes)

FROM faits f, Magasin s, Produit p, Periode t, TableDesCumuls a 
WHERE

        --  premièrement les jointures entre les tables

        f.Magasin_Clé = s.Magasin_Clé 

        and f.Produit_Clé = p.Produit_Clé

        and f.Periode_Clé = t.Periode_Clé

        and s.Magasin = a.Magasin

        and p.Rayon = a.Rayon

        -- ensuite, filtrer le temps à aujourd’hui pour avoir les données les plus récentes

        and t.Date = 'July 13, 2000'

-- enfin, spécifier la contrainte sur l’écart-type
HAVING abs(sum(f.Ventes) - (1 / a.N) * a.SOMME_VENTES) > 3 * sqrt(a.VAR)

Nous avons supposé que les rayons sont des groupements de produits et que par conséquent ils sont disponibles comme un attribut dans la dimension Produit.

Une amélioration possible de ce système pourrait inclure l'exécution de deux requêtes: l'une pour les ventes supérieures à 3 fois l'écart-type au dessus de la moyenne, et une autre pour les ventes inférieures à 3 fois l'écart-type en dessous de la moyenne. Peut-être qu’il y a une autre explication pour ces deux situations. Cela permettrait également de se débarrasser de la fonction ABS, dans la clause HAVING, si votre SQL n'aime pas cela.

Si vous avez d’importantes fluctuations quotidiennes des ventes et que cela soit normal (par exemple, lundi et mardi ont très peu d’activité par rapport à samedi), alors vous pouvez ajouter la colonne JOUR_DE_LA_SEMAINE à la table de cumul par rayon et filtrer sur le jour approprié. De cette façon, vous ne mélangez pas les fluctuations quotidiennes normales dans notre test de l'écart-type.

Source originale: www.kimballgroup.com

Article original "Kimball Design Tip #10: Is your data correct?", publié le 3 juillet 2000.