[Modélisation BI] Les Clés Etrangères

Relançons le débat pour un vendredi : faut-il mettre des clés étrangères dans un datawarehouse.
Sujet sensible puisque deux sessions aux dernières journées SQL Server en ont plus ou moins parlé :

• La session de Florian Eiden et Jean-Pierre Riehl sur la modélisation dimensionnelle
• La session de Frédéric Brouard sur les contraintes et performances

Comme Florian le suggère sur son blog, vous devinerez que je suis partisan de ne pas en mettre.

Pourquoi une telle hérésie ?

Je fais de l'informatique décisionnelle : ma base de données se charge via des batchs uniques développés majoritairement avec SSIS (tout du moins pour mon cas). Mes tables n'ont donc qu'une façon unique d'être alimentée et ce qu'on insère doit, de ce fait, être totalement maîtrisé par mon package (voir mon article sur la gestion des orphelins à ce sujet).
C'est un point essentiel de ma démonstration : losrque je fais du développement SQL pour une application .NET par exemple, je mets des clés étrangères car mes tables peuvent dépendre à la fois de batch ou d'écrans de l'application, etc.

L'autre point essentiel est l'optimisation de mes traitements : la fenêtre temporelle pour le chargement des données est par expérience de plus en plus réduite et le volume des données toujours plus grand. Toute optimisation de chargement est donc bonne à prendre.
C'est pour cela également que je ne fais pas un un rejet total de toutes les contraintes : j'implémente des clés primaires et des index uniques bien pensés au niveau de mon datawarehouse

Et l'intégrité des données dans tout ça ?

Garantir l'intégrité des données par des relations de clés étrangères c'est bien, mais ça peut être coûteux en performance. Développer une solution d'alimentation qui ne permet pas de créer des problèmes d'intégrité, c'est mieux.
Il faut garder à l'esprit que vous devrez de toute façon repasser sur votre alimentation et redévelopper vos packages si vous avez des données non intègres. Les clés étrangères ne vous épargneront pas ce travail, donc autant y remédier en amont.

Et concrètement dans la pratique ?

Les conditions de test sont les suivantes :

• Machine Virtuelle en Windows 7
• SQL Server 2008
• 4 CPU
• 3 Go de Ram

J'ai fait un test avec une table de dimension Produit à 50000 lignes et une table de dimension Client à 50000 lignes.
J'ai imaginé une table de faits avec un idTemps, un IdClient, un IdProduit et une quantité.
Un index unique sur les Id est créé.

 J'utilise un package SSIS d'upsert classique développé avec un double lookup.

Le premier test consiste en un chargement de 250000 lignes dans la table de faits lorsque celle-ci est vide.

Résultats :

Sans clé étrangère	Avec clés étrangères
6s078	7s458

Le second test consiste en un chargement de 250000 lignes dont la moitié sont en update et l'autre moitié sont en insert.

Résultats :

Sans clé étrangère	Avec clés étrangères
1min32	1min39

Conclusion :

Je ne prétends pas clore le débat avec cet article, mais juste exposer mes préférences et les argumenter. Il faut garder à l'esprit que les tests effectués ici sont faits sur une faible volumétrie et seulement deux clés étrangères.
Il convient enfin de garantir que votre solution d'alimentation empêche l'insertion de données non intègres pour pouvoir justifier la non-utilisation des clés étrangères.