Pour ou contre les clefs étrangères dans le datawarehouse?

Mise à jour 19/12/2012 – Un épilogue intéressant par Fred Brossard!

Durant notre session sur la Modélisation Dimensionnelle, avec Jean-Pierre nous n’avons pas pu nous empêcher de provoquer la foule (en délire :)) en lâchant l’air de rien un « …de toutes façons les foreign keys, en décisionnel, on ne les implémente pas... ». C’était évidemment une petite provocation, qui a bien fonctionné, mais il est temps de faire place aux arguments!

Le pour:

  • Implémentation au niveau du moteur SQL de la contrainte d’existence de la clef étrangère

Le contre:

  • Perte de performance à l’insertion (visible à partir de volumétries « importantes », naturellement dû à la vérification effective de la contrainte)
  • Perte de la commande TRUNCATE sur les tables concernées
  • Implémentation au niveau du moteur SQL de la contrainte d’existence de la clef étrangère

Hum… Il me semble voir un élément en double dans ma liste…

Pourquoi mettre la contrainte d’existence dans les contres? Parce qu’en mode projet, lors de la fabrication du datawarehouse, vous allez vider et remplir des centaines de fois vos tables de faits et vos tables de dimension. Et même très souvent, vous allez recharger vos dimensions sans vous soucier de vos faits. Implémenter à ce moment là vos clefs étrangères, c’est vous obliger à vider dans l’ordre les bonnes tables, sans utiliser un TRUNCATE, ou alors supprimer et recréer vos clefs à chaque fois… Encore du boulot alors qu’on en a déjà assez. C’est pourquoi à mon sens pendant le projet, les clefs étrangères ne sont qu’un frein à l’itération rapide sur le design.

La question devient alors: comment garantir la contrainte d’existence d’un membre de dimension pour un ID présent en table de fait? Pour moi cela doit être fait par design, au niveau de l’ETL:

  1. On dispose initialement d’un « Membre Inconnu« , sur une ID technique comme le -1, dans les dimensions
  2. On alimente ses dimensions à partir des fichiers sources, afin de capturer à la volée les nouveaux membres qui peuvent arriver
  3. Lors du chargement des tables de faits à partir de ces mêmes fichiers, on effectue un LOOKUP sur la table de dimension pour aller chercher l’ID du membre correspondant
    • Soit le membre existe : pas de problème
    • Soit le membre est inconnu : on indique en dur l’ID technique (-1), et on vérifie que la valeur a bien été rejeté lors du chargement de la dimension (sinon c’est qu’on a un vrai problème: pourquoi le membre n’apparaît pas dans la dimension?)

Avec ce petit algorithme, on ne peut pas insérer un NULL dans la table de faits. D’ailleurs, rien ne vous empêche de rendre les colonnes de clefs de dimension NOT NULL dans vos tables de faits. Évidemment, avec cette technique, les DELETE sont interdits dans la table de dimension, sinon rien ne vous protège d’avoir un ID en table de faits qui ne remonte pas sur la table de dimension.

Maintenant que j’ai dit tout ça, ne croyez pas que je sois anti clefs étrangères, bien au contraire. Si vous êtes dans un datawarehouse qui doit effectuer des DELETE sur ses dimensions (chargements exotiques, purge de la profondeur d’historique…), il est plus que conseillé d’utiliser les foreign keys. Et d’une manière plus générale si vous n’avez pas confiance dans votre ETL, appelez les consultants… euh non pardon, utilisez les foreign keys (et appeler aussi les consultants! :)).

Dans tous les cas, le débat est ouvert dans les commentaires, n’hésitez pas à donner votre avis et vos arguments sur le sujet, je suis à l’écoute!

PS : Il faut rendre à César ce qui appartient à César, et celui qui a fait mon éducation sur ce sujet c’est évidemment David Joubert, expert technique de génie 🙂