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:
- On dispose initialement d’un « Membre Inconnu« , sur une ID technique comme le -1, dans les dimensions
- On alimente ses dimensions à partir des fichiers sources, afin de capturer à la volée les nouveaux membres qui peuvent arriver
- 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 🙂
La BI ça vous gagne! 
Toi, tu veux me fâcher avec tous les DBA de la terre 😉
Petite idée, non vérifiée, qui me vient à la lecture du billet…
Si on définit un membre technique -1 dans chacune de nos dimensions, que l’on se retrouve avec un chargement qui fonctionne principalement à base de procédures stockées, est-ce qu’il ne serait pas bon de définir une valeur par défaut -1 sur nos colonnes d’Id de dimensions ?
Bonjour,
Merci pour ce retour.
Concernant le Staging Area et l’ODS je suis d’accord qu’il n’est pas recommandé d’utiliser de Foreign Keys, en effet ces tables sont amener à être vidées avant chaque chargement Utilisation du « TRUNCATE ».
Cependant dans mon DataWarehouse :
• La nécessité d’utiliser la commande « TRUNCATE » est très rare
• La nécessité d’avoir une intégrité totale de mes données est très importante
Je persiste à croire que les bénéfices d’utilisation des Foreign Key est plus important que ces potentielles « ralentissements » qu’ils peuvent apporter.
Cordialement,
Romain Casteres
> David : Clairement!
> Alexandre : Désolé mais je frissonne devant « chargement à base de procédures stockées », ça me pique les yeux :p
Mais pour répondre, je dois dire que je ne suis pas un fan des valeurs par défaut des colonnes. Je pense que ça me vient du fait que j’utilise exclusivement SSIS comme ETL, et que ce genre de problème est fixé à base de Derived Column. C’est donc un outil que je n’utilise quasiment pas.
Mais si on se retrouve coincé avec une alimentation en SQL, vous avez raison c’est un bon outil pour compléter des attributs de dimension inconnus pour lesquels on veut une valeur par défaut (« Couleur Inconnue », « Gamme Inconnue »…). Par contre j’ai plus de mal avec la gestion des ID inconnues avec cette technique. C’est en effet un process suffisamment important pour nécessiter du code explicite. Pas pour des raisons de performance ou d’élégance, mais plutôt pour la maintenabilité et la visibilité du code.
> Romain : Je comprends complètement votre position et la respecte. C’est un débat ouvert pour lequel les 2 avis se valent tout autant, comme le flocon vs l’étoile.
Tant que je vous tiens je rebondis : vous pensez quoi des PK identity(1,1) sur les tables de fait ? 🙂
@alexandre L’idée de mettre une valeur par défaut n’est pas dénuée de sens, mais contrairement à ce que vous dites, connaissant monsieur Eiden, il utilise plutôt SSIS que des procédures stockées pour le chargement de ses données. Et les valeurs par défaut ne s’accordent pas toujours bien avec un ETL, les mappings étant définis, l’ETL a tendance à forcer l’insertion de la valeur NULL
@Romain Il y a là deux écoles, j’ai tendance à historiser mon ODS (je crois qu’ici aussi deux philosophies s’opposent). De ce fait, l’alimentation de mon datawarehouse est basée sur du truncate/insert.
>David :
« il utilise plutôt SSIS » : et toi non peut-être? 🙂
« Il y a là deux écoles, j’ai tendance à historiser mon ODS (je crois qu’ici aussi deux philosophies s’opposent) » : je m’oppose! 😉
Je tiens à préciser que je ne tiens pas particulièrement aux chargements à base de PS, mais j’ai quelques clients qui ont fait ça il y a quelques temps, où SSIS ne sert que d’ordonnanceur, martelant les performances sans comparaison de leur système. Système qui est relativement bien rodé depuis quelques années.
Un passage à SQL 2012 changera peut-être les choses.
Quoiqu’il en soit, je suis tout a fait pour gérer ces questions à travers la confiance (et le soin) que l’on porte à nos packages.
Mais dans le cas de ces chargements SQL, il faut bien trouver des parades 😉
Pour ce qui est des PK(1,1) sur les tables de faits, tout dépend de l’utilisation que l’on en a 😉
Bonjour,
Pour rentrer dans le débat :
@Alexandre: Tout comme Florian, j’ai du mal avec les chargements à base de Procédure Stockées quant à définir une valeur par défaut dans le design de la table, pourquoi pas, le majeur problème que je vois c’est la lisibilité et la maintenance, parce que finalement, il est possible de gérer le membre inconnu, en table, en SSIS, en SSAS ou encore dans les rapports SSRS … ça devient compliqué tous ça 😉
@Romain: Je suis aussi de cet avis, mais je pense que c’est d’avoir connu des DW alimentés n’importe comment (Et là les FK pouvaient nous sauver la vie …) par contre dans le cas d’un ETL bien formé, les Contraintes n’ont en effet plus vraiment d’intérêt.
En conclusion, je ne suis ni pour ni contre, bien au contraire !
@Fleid : Tu enchaines les sujets Épineux, pour moi c’est identity(1,1) sur les tables de fait quand table de bridge (ça aide) sinon … non 😉
Concernant les PK Identity(1,1) sur les tables de fait de mon DataWarehouse : pour moi « Ca ne mange pas de pain » ! J’ai tendance à les utiliser.
> Alexandre : J’ai eu à subir aussi. Si le débat se résume à la performance brute unitaire de chaque composant entre SSIS et du SQL, le mieux c’est encore de changer de mission…
> Pour le sujet des PK, mouahahahah c’est un piège ! Romain merci d’être tombé dedans 😀
Il ne faut pas poser de PK sur une colonne identity(1,1) sur ses tables de fait (encore une leçon du DJ über). La PK doit être posée sur l’ensemble des colonnes qui détiennent les clefs de dimension. D’une part cela garantit l’unicité des faits à cet intersection des dimensions, ensuite cela garantit un gain de performance notable puisqu’on maximisera l’utilisation de l’index clustered dans la plupart des requêtes, enfin cela permet de partitionner ses tables de manière optimale en utilisant un découpage fonctionnel.
Notez que dans les systèmes pour lesquels plusieurs faits peuvent exister au même croisement des dimensions (des ventes générées à la seconde mais stockées à l’heure à cause d’une source incomplète), on peut rajouter un ROW_NUMBER OVER PARTITION à la clef primaire pour garantir son unicité.
Dans tous les cas, si vous avez absolument besoin d’un identity(1,1), mettez le, mais n’en faîtes pas votre clef primaire. Utilisez bien la combinaison des clefs de dimension comme primary key.
> Charles-Henri, même pour une table de bridge / factless table, je ne pense pas que ce soit nécessaire. Tu peux m’en dire plus ?
Héhé mais de rien, c’était fait exprès :-p
Non, merci je vois le potentiel gain de la solution
Ce qui est bien avec la BI c’est qu’on en apprend tous les jours !
En posant la PK sur l’ensemble des clef des dimensions, on pousse encore, avec un intérêt supplémentaire à bien avoir les membres inconnues dans les dimensions et une valeur (et non NULL qui ne passerait alors pas dans une PK) sur les Id de dimension.
Bon, je rentre après la bataille, je vais essayer d’apporter mon eau au moulin sans réchauffer le débat :
FK : l’ETL garantit l’intégrité référentiel donc je n’en mets pas sauf cas exceptionnels (mauvaise maîtrise de la base ou des sources, héritage d’une solution bancale, etc.)
PK : m’en fout, c’est une contrainte gérée au même titre que les FK par contre :
IX Clustered : utilisation de la clé « naturelle » et IDENTITY s’il n’y en a pas ou si elle est trop complexe. Objectif ==> partitionnement
PROCEDURE : quoi ? je m’insurge 🙂
DEFAULT : idem que les FK, pas de nécessité (sauf en cas d’héritage ou à la création d’une nouvelle colonne – mais je pète la contrainte dès que possible)
TRUNCATE : ça dépend du projet
Tu montes des index clustered non PK?
C’est parce que tu aimes te faire remarquer ou il y a de réelles différences par rapport à SQL Server? 🙂
PK IX CLUSTERED
PK = unique non null
Index Clustered = ordonnancement physique des données sur le disque (je raccourcis, j’espère que des gars comme Christophe Laporte ou David Barbarin n’écoutent pas :)).
Tu choisis le meilleur candidat à l’index Clustered en fonction du requêtage naturelle, pas en fonction de tes contraintes d’intégrité.
Par exemple, une bonne pratique est de mettre le Clustered sur la BusinessKey des dimensions quand tu fais du SCD, comme cela, tu divises par 2 tes lectures.
PS : en OLTP, 90% des PK = IX Clustered 🙂
Tu réponds pas vraiment à la question Jean-Pierre!
Savoir qu’il faut poser l’index clustered sur les business keys on en parlait déjà plus haut 😉
Ma question c’est quel est l’intérêt de dissocier l’index clustered de la PK? Par défaut dans SQL Server la PK contient l’index clustered on est d’accord? Pourquoi toi tu fais l’effort de définir un index clustered sans passer par la PK? Habitude à la peau dure ou avantage réel?
On dissocie parce que ce n’est pas la même chose mais je te rassure, il est intéressant de dissocier seulement dans quelques cas.
Une PK n’est pas un index, c’est une contrainte qui indique au moteur l’unicité et la non-nullité. C’est SSMS (pardon SSDT) qui crée un index Clustered dans la foulée.
Un exemple :
Tu as un IDENTITY dans OrderDetails (ça parait louche comme ça mais admettons). Tu as également le champ OrderId pour pointer vers le parent et tout un tas d’autres champs.
99,9% de ton requêtage va se faire sur le OrderId donc tu as tout intérêt à ordonner physiquement la table. Un index NON-CLUSTERED entrainera des KeyLookup dans le plan d’exécution pour récupérer les autres champs donc plus de lecture (et plus d’IO).
On peut se dire qu’on a qu’à mettre la PK sur OrderId/ProductId mais admettons que les doublons soit autorisés (pour s’abstraire des UPDATE) ?
Evidemment, cela n’a de sens que pour l’ETL ou du reporting SQL sur le DWH. Au chargement du cube, il fera un scan de toute la table donc qu’importe qui est le CLUSTERED puisqu’il doit le lire en entier (sauf si tu fais du partitionnement).
PS : le 0,1% est le DELETE.UPDATE qui pointe sur l’IDENTITY
Re-PS : on peut se dire qu’une partie du requêtage puisse être sur le ProductId, dans ce cas, en fonction du ratio, on choisira précautionneusement son CLUSTERED.
En tout cas MERCI Jean-Pierre car pour moi, ce n’était pas naturelle de distinguer la PK et le Clustered Index en fonction du requêtage réel des tables, maintenant, je me poserais la question !
Perso je suis pour l’implémentation des FK, notamment pour faciliter la lisibilité du modèle de données (humaine, reverse et par les outils divers et variés).
Pour des soucis de perfs en revanche il faut réfléchir à les activer ou non. Il est aussi possible de les désactiver en début de chargement (puis les réactiver après), ce qui évite de planter une chaîne sur une « simple » violation de fk…
Dans tous les cas, le contrôle d’intégrité des données doit être effectué par l’ETL en amont !
Certes, l’ETL effectue les contrôles d’intégrités, néanmoins, les équipes projets ne sont pas les seules à intervenir sur un DWH en production. Et rien n’empêche nos camarades de faire des maintenances à chaud de données et d’exécuter des scripts SQL. Et là qui garantit l’intégrité des données ? Tant que votre entrepôt est alimenté en annule et remplace, cela ne pose pas de soucis, maintenant sur des VLDB j’espère bien que le mode d’alimentation est incrémental…
Bonjour,
lors de chargement de la table de fait à partir des tables de dimensions, j’ai des valeurs null dans la source de la table de fait, dû coup dans la composant de recherche la ligne est rejetée pour résoudre ce problème j’ai ajouté la condition suivante dans la source ISNULL(<<nom colonnes,-1) et j'ai ajouté -1 dans la table de dimension. Le problème est le suivant je ne peux pas insérer -1 dans la table de la dimension, car la colonne PK de la dimension incrémente automatiquement. Si quelqu'un à une solution je serais reconnaissant
Il faut utiliser la commande « SET IDENTITY_INSERT [SCHEMA].[TABLE] ON » pour autoriser l’insertion de valeurs explicites dans la PK : http://msdn.microsoft.com/fr-fr/library/ms188059.aspx
Merci pour la reponse. une autre question qui peut être loin de ce sujet est-ce que on peut avoir des null dans la source de la table de fait si non comment je peux construit la source de table de fait car dans cette source j’utilise des left join à partir des table de staging