Conseil 63: Construire un système qui capte les changements de données

Le flux de données ETL commence par transférer les données les plus récentes de la source dans l'entrepôt de données. Dans presque tous les entrepôts de données, nous devons transférer que les modifications pertinentes de la source de données depuis le dernier transfert. Rafraichir complètement nos tables de faits et nos dimensions est généralement inopportun.

L'activité d'isoler les dernières données de la source s'appelle "la capture de données". L'idée derrière cela semble assez simple: il suffit de transférer les données qui ont été modifiées depuis le dernier chargement. Mais la construction d'un bon système de capture de données n'est pas aussi facile qu'il n'y paraît.

Voici les objectifs pour capturer des données modifiées:

• Isoler la source de données modifiée pour permettre un traitement sélectif plutôt qu'un rafraîchissement complet
• Capturer tous les changements (suppressions, modifications et insertions) apportés à la source de données, y compris les modifications apportées au travers d'interfaces non-standards
• Etiqueter les données changées avec un code pour en indiquer la raison et ainsi distinguer les corrections d'erreurs des véritables mises à jour
• Aider l'analyse de la conformité grâce à des métadonnées supplémentaires
• Effectuer l'étape de capture des données le plus tôt possible, de préférence avant le transfert de données en masse dans l'entrepôt de données

La première étape dans la capture des changements de données est de détecter les changements. Cela peut se faire de quatre manières:

1. Auditer les colonnes. Dans la plupart des cas, le système source contient des colonnes d'audit. Celles-ci sont souvent placées à la fin de chaque table pour stocker la date et l'heure à laquelle un enregistrement a été ajouté ou modifié. Les colonnes d'audit, sont habituellement remplies par des triggers qui sont déclenchés automatiquement une fois l'enregistrement ajouté ou modifié. Parfois, pour des raisons de performances, les colonnes sont remplies par les applications sources à la place des triggers. Lorsque ces champs sont chargés par un moyen autre que les triggers, vous devez faire attention à leur intégrité. Vous devez analyser et tester chaque colonne pour vous assurer qu'elle soit une source fiable pour indiquer si les données ont changées. Si vous trouvez une valeur nulle, vous devez chercher une autre approche pour détecter les changements. La situation la plus courante, et dont il faut se méfier lorsqu'on utilise des colonnes d'audit, est lorsque les champs sont remplis par les applications sources et que l'administrateur de données exécute ensuite des scripts qui modifient les données. Si vous êtes dans cette situation, vous êtes face à un risque important qui peut vous amener à oublier des données lors de votre chargement incrémental.
2. Analyser le journal (log) de la base de données. Cela consiste à prendre un instantané du journal de la base de données à un moment régulier dans le temps (généralement minuit), et de le fouiller pour trouver les transactions qui affectent les tables dont vous vous souciez pour votre chargement ETL. Cela implique un "sondage" du journal pour capturer les transactions à la volée. Cette technique est probablement la plus sale de toutes. Il n'est pas rare que les journaux de transactions "dégueulent", c'est à dire qu'ils sont pleins et empêchent de nouvelles transactions de s'exécuter. Lorsque cela arrive dans un environnement de production, le réflexe du DBA est de vider le contenu du journal afin que les transactions puissent reprendre, mais quand un journal est vidé, toutes les transactions sont perdues. Si vous avez épuisé toutes les autres techniques et que celle-ci est votre dernier recours pour trouver les nouveaux enregistrements ou ceux modifiés, essayez de convaincre le DBA de créer un journal spécial pour répondre à vos besoins spécifiques.
3. Extraction planifiée. Avec cette technique vous sélectionnez toutes les lignes ayant le champ "Date de Création" ou "Date de Modification" égal à SYSDATE-1, ce qui signifie que vous avez tous les enregistrements de la veille. Cela semble parfait, non? Faux. Charger des enregistrements basés uniquement sur le temps est une erreur commune faite par la plupart des développeurs ETL débutants. Ce processus est horriblement peu fiables. Sélectionner des données en fonction du temps provoque des doublons lorsque le traitement est redémarré après qu'une erreur ai eu lieu au cours de l'exécution du processus. Cela signifie qu'une intervention manuelle pour nettoyer les données est nécessaire si le processus échoue pour une raison quelconque. En attendant, si le processus de chargement nocturne échoue et oublie un jour, il existe un risque pour que les données manquantes ne soient jamais dans l'entrepôt de données.
4. Comparer de base de données. Cette technique conserve un extrait complet de la base de données d'hier et la compare, enregistrement par enregistrement, à la base de données d'aujourd'hui pour trouver ce qui a changé. La bonne nouvelle est que cette technique est générale: vous êtes assuré de trouver tous les changements. La mauvaise nouvelle est que dans de nombreux cas, cette technique est très coûteuse en ressources. Si vous devez appliquer cette technique, alors essayez de faire la comparaison sur la machine source afin de ne pas avoir à transférer l'ensemble de la base de données dans l'environnement ETL. En outre, enquêtez en utilisant les algorithmes CRC (cyclic redundancy checksum) pour dire rapidement si un enregistrement complexe a changé.

Source originale: www.kimballgroup.com

Article original "Kimball Design Tip #63: Building a change data capture system", publié le 11 janvier 2005.