5 bonnes raisons d’utiliser les Foreign Keys dans un DWH

Il s’agit là d’un sujet à controverse. Bon nombre de BI Pros estiment qu’il ne faut pas déclarer les Foreign Keys sur un entrepôt car les contraintes d’intégrités sont « sensées » être gérées dans vos lots SSIS et qu’elles ralentissent les performances lors de l’alimentation des tables de faits.

Et là je dis NON !  Quelles sont pour moi les 5 raisons principales pour lesquelles il faut déclarer des Foreign Keys :

 #5 – Les Foreign Key ne dégradent pas de façon significatives les performances d’intégration. Si tel est le cas, c’est peut-être parce que votre intégration elle-même n’est pas bonne (chargement de plusieurs millions de lignes en annule/remplace plutôt qu’en incrémental), architecture non optimisée (avez-vous partitionné vos tables de faits ? Si oui les alimentez-vous en utilisant le partition switching ?) Si toutefois, elles entravent toujours vos durées d’intégration, SSIS vous permet toujours de passer outre, il suffit de cocher la bonne case sur les composants d’insertion.

OLE_DST - Without CheckConstraints

 #4 – L’ordre d’exécution des packages SSIS doit suivre la logique du modèle (on charge d’abord les dimensions avant de s’occuper des tables de faits). Or sans contraintes d’intégrités rien n’empêche de faire une opération de nettoyage sur une dimension alors que les données de faits ont déjà été chargées. Petit rappel au passage; que vous implémentiez du Tabular ou du Multidim, un seul type de modélisation convient : l’étoile !

 #3 – Il n’est pas rare de se plugger directement sur les sources de données SQL des outils opérationnels (ERP et autres applications tierces), seulement le business n’attends pas de voir et d’avoir les impacts que des évolutions et/ou migrations peuvent avoir sur le décisionnel. Cela peut donc changer l’essence même des données et il est préférable de s’en rendre compte immédiatement par une erreur de votre chaine d’intégration plutôt que de s’en apercevoir 3 à 6 mois après et d’avoir ainsi un chantier beaucoup plus vaste (évolution de la chaîne d’intégration, correction des données dans l’entrepôt …)

 #2 – Dans un contexte de compétitivité sans cesse grandissement, la demande BI Self-Service va croitre rapidement. Or cette dernière n’est pas antinomique avec la BI Corporate. Les deux sont bel et bien complémentaires (je vous renvoie pour cela à l’excellent post de Chris Webb http://t.co/dcVphdzI ). Or la BI Self-Service va s’appuyer en grande partie sur l’entrepôt que vous aurez modélisé. Et là rien de tel que les clés étrangères pour faciliter et s’assurer de la bonne construction du modèle d’analyse (Tabular ou Multidim)

 #1 – Un entrepôt reste avant tout une base de données relationnelle, et s’administre de la même façon : plan de maintenance, de sauvegarde, d’indexation, de statistiques …. Et bien entendu des contrôles d’intégrités référentielles. Cette dernière étape est très peu utilisée sur un entrepôt et pourtant les bugs çà existent (règles de gestion omises ou non connues par le métier, cas particuliers non recettés, etc…)

 Bon j’espère vous avoir convaincu.

16 réflexions sur “5 bonnes raisons d’utiliser les Foreign Keys dans un DWH

  1. Toi, tu vas avoir des problèmes🙂

    Pour info, des avis (pour et contre) à rajouter au débat : http://fleid.net/2011/12/13/pour-ou-contre-les-clefs-etrangeres-dans-le-datawarehouse/

    Et pour te répondre point par point:
    5 – Un argument pour les FK c’est de les disable? Je ne comprends pas la logique 😉
    4 – Il dit qu’il voit pas le rapport?
    3 – Il ne faut pas confondre: « je n’utilise pas les FK », avec: « je ne vérifie pas mes contraintes ». Si je fais un Lookup en fail sur échec dans SSIS, je vérifie autant mes contraintes que toi non? Je n’utilise juste pas le même outil. Et vis à vis du contexte changeant, je préfère avoir mon intelligence dans le flux, dans SSIS, là ou je peux la faire évoluer facilement, la monter sur mon SVN, plutôt que dans les metadata de la base, où je les oublie, et qu’elles alourdissent les évolutions de ma base (pas de truncate, drop de clefs à prévoir, etc…).
    2 – Je ne vois pas le rapport entre la présence de FK et la bonne construction du modèle d’analyse. Je te garantis que j’ai vu des modèles magnifiques sans FK, et des modèles vraiment foireux avec des FK dans tous les sens. A mon sens y’a même pas corrélation. Par contre si tu parles de faciliter l’exposition des métadonnées aux clients (pour que les relations soient détectées automatiquement dans les outils), là tu marques un point. Mais on peut compenser avec une bonne convention de nommage (et là je te conseille un excellent article par un certain… Fred Brossard ;)).
    1 – Encore une fois, ce n’est pas parce que je n’utilise pas les FK que je ne vérifie pas mon intégrité référentielle. Les FK sont intéressantes en OLTP parce que tu fais des transactions une par une, plus ou moins en permanence, de potentiellement n’importe où dans ton application. Recoder la logique d’intégrité dans toutes les insertions c’est une perte de temps sans nom, d’où la mise en place d’un mécanisme directement dans la base : les FK. En DWHing on maîtrise notre logique d’insertion, elle se fait en batchs planifiés, développés par une seule équipe, dans un seul outil, dans un seul flux. Le débat n’est à mon sens pas vraiment le même.

    Évidemment mon avis c’est que chacun fait ce qui lui plait. Moi je suis branché Agilité, et je veux juste que mon modèle soit le plus évolutif possible. Donc je dégage tous les freins qui pourraient contribuer à la résistance au changement, les FK en font partie. Après une fois le modèle stabilisé, pourquoi pas ajouter des FK, si ça peut rassurer les dba😉

    • J’aime bien, je n’avais pas lu ton excellent post. Bon je vais m’empresser d’apporter de l’eau au moulin.

      #5 : ce n’est pas parce pas parce que tu as positionné des contraintes d’intégrité sur ta base que dans tes lots SSIS tu ne peux pas passer outre. Si vraiment elles posent des problèmes, tu peux toujours décider de les ignorer. Quant aux problèmes de performance ???? J’ai actuellement une table de ventes un peu moins de 300 millions avec des FK dans laquelle on insère hebdomadairement 4 millions d’enregistrements sans problème de performance. Bien sur la table est partitionnée et bien évidemment on utilise du partition switching. Et là j’espère bien ne pas pouvoir faire un truncate sur un des référentiels adjacents. Bon reste l’argument du truncate, mais le FK n’empêche pas les truncate sur les tables de faits (sauf s’il y a des M2M) juste sur les dimensions. Or je ne vide mes tables de dimensions que très rarement.

      #4 : sans contrainte d’intégrité rien ne t’empêche de supprimer des enregistrements sur une dimension alors que tu as déjà chargé les données de faits. Et oui j’ai déjà vu des packages SSIS arrivant après la bataille pour faire le ménage dans les données référentielles. Et là dommage pour les données chargées en incrémentale car on vient de supprimer des références qui pouvait être utilisées. Et çà peut même être bien vicieux si sous SSAS ton groupe de mesures est partitionné et que seule la partition de données fraiches est processée. Le jour où vient le besoin de faire un process full sur la dimension et donc sur le(s) groupe(s) de mesures liés, tu risques d’avoir des surprises.

      #3 : je suis bien d’accord avec toi, le lookup permet de vérifier l’intégrité de tes données mais uniquement sur la fenêtre de données que tu charges. Tant que ton alimentation est en Truncate/Insert c’est ton lot SSIS qui assure l’intégrité des données sur l’ensemble de ton historique. Mais comment peux-tu t’en assurer lorsque tu as mis en place un chargement incrémental. Sans FK, qui protège tes données d’un mauvais script de maintenance SQL passé en prod ? SSIS, euh non ? Plus personne. Les équipes projets ne sont pas les seules à pouvoir intervenir sur un entrepôt.

      N.B : il y en a même qui pense que dans un DWH, on ne doit jamais faire de Truncate ou de Delete mais utiliser des flags pour invalider les enregistrements.

      #2 : tu as lu en moi comme dans un livre ouvert, il s’agit juste d’exposer les métadonnées aux clients, et malheureusement l’expérience prouve que tu as beau mettre en place les plus belles conventions de nommage ; lorsqu’un utilisateur conçoit un modèle d’analyse Self-Service mieux vaut l’assister au maximum. Mais là encore ce n’est pas parce que tu as mis en place tes FK que notre très cher utilisateur « avancé » ne peut pas faire n’importe quoi. Seulement, en mettant tes FK, il est le seul responsable de sa bêtise.

      #1 : là encore les lookup ne permet de vérifier les données que sur la fenêtre des données chargées, mais une fois les données chargées qu’est-ce qui en assurent l’intégrité. J’ai déjà vu des applications OLTP directement branchées sur le DWH. Et oui on voit de tout😦

      N.B : pour avoir fait pas mal d’OLTP avant de faire de la BI, je t’assure avoir entendu les mêmes arguments pour se passer des FK en transactionnel, avec en lieu et place de l’ETL la couche métier.

      Après chacun voit midi à sa porte, il y a des contextes projets ou l’on peut s’en passer, d’autres où il vaut mieux les utiliser. Maintenant, tant qu’on fait du truncate/insert il est vrai qu’elles ne servent à rien, mais personnellement, j’ai eu très peu de projets de ce type.

  2. Je vais pas rebondir sur cet article vu que tout le monde connait mon avis sur le sujet😉

    Par contre je suis intéressé par un comparatif des performances sur le chargement de ta table de faits entre partitionnée et non partitionnée chez ton client (il me semble que la reco MS est à 200 millions de lignes en SQL donc avec un peu moins de 300 millions, je suis pas sûr qu’il y ait un impact évident ; connaissant les clients, j’ai en plus dans l’idée que les fichiers se trouvent sur les mêmes disques mais j’espère me tromper).
    Je voudrais voir si le gain est significatif.

  3. Je suis plutôt du genre à ne pas en mettre (par réflexe pavlovien de vieux con qui a toujours fait comme ça).
    Mais j’avoue que la Self-Service m’encourage à mettre le plus de méta-données partout. Dans ce contexte, les FK apportent beaucoup, (même si on peut désactiver la contrainte référentielle sous-jacente).

    Petite remarque également, même si tu n’as pas de LK pour vérifier la liaison au chargement, ça claquera au process (ou ça te mettra un joli unknown member).

    • Je suis d’accord, en toute logique çà devrait claquer au process, mais il y a des petits malins qui font des « ignore error » sur les process de cube.
      Ils méritent, certes, de se faire tirer les oreilles et se prendre un 48 fillette dans le derrière, mais bon çà existe. On voit de tout en BI😉

      • Il faut mettre des policies sur ta plate-forme pour interdire le Ignore Error. (tiens je vais me tenter ça en Policy Fx…)

  4. Merci pour cet article auquel j’adhère à 100%.

    L’argument des perfs ne tient pas vraiment selon moi…

    Un exemple concret : sur le projet sur lequel je travaille actuellement nous avons une table de faits qui avoisine les 6 milliards de lignes pour moins de 4 ans d’histo. (notre ami commun David pourra attester hein….), on a bencher le process d’alimentation avec ou sans les contraintes; et voici ce que l’on a mesuré…
    – Le chargement d’un mois sans contrainte dure env. 40 minutes
    – Avec les contraintes on passe à 44 -45 minutes.

    les plus obstinés diront que que c’est toujours 4/5 minutes de trop….

    Seulement du coup grâce aux contraintes on a aussi noté que chaque mois, une grosse dizaine de lignes étaient rejetées car ne respectant pas justement l’intégrité référentielle… or tout le monde chez mon client pensait que la source de données était hyper propre avec des phrases du type :

    « Ça fait des années qu’on l’utilise as is et ça fonctionne nickel »…

    Avec des raisonnements comme ceux-ci, ça se finit un jour avec un cube qu’on process en « Ignore errors »!!!

    Moi ça m’a conforté dans l’idée que je préfère me payer le luxe d’un processus d’alim un poil plus long mais avoir l’esprit libre.

    N’oublions pas que nos packages SSIS évoluent dans le temps, les structures de tables également, et avec le temps qui passe il est facile d’oublier un contrôle.
    SQL Server lui n’oublie pas de vérifier les contraintes.

    Selon moi, ne pas mettre de contrainte c’est hypothéquer l’avenir !!

    Dernier point, la prochaine fois que vous arrivez chez un nouveau client sur un projet qui a déjà vécu; juste pour le fun essayez de matérialiser les FK dans le datawarehouse et vous m’en direz des nouvelles!!

    La dernière fois que je l’ai fait, évidement ça m’a explosé a la figure… j’en ai parlé a mon client, et il m’a répondu… : « Haaa on savait pas d’ou ça venait, mais quand le cube ne processait plus on supprime toutes les données…. et ça repart… c’est notre solution de contournement… »

    Mais au secours!!!!!

    Alors j’suis peut-être un vieux con qui a fait un peu trop de SQL, mais si vous aimez vos cubes, il faut aimer leurs géniteurs… non de dieux aimez vos bases de données.

    Fab— en mode coup de gueule :p

  5. Tel que je vois le débat: contrainte et sécurité d’un côté, liberté et risque de l’autre. David et moi sommes des anarchistes, Fabrice et Fred sont des papas. Ouais ça correspond bien 😀

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s