[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.

Gestion des Orphelins

La gestion des orphelins est une problématique récurrente de tous les projets décisionnels. On va détailler ici les 3 méthodes permettant de gérer tout ça.

Tout d'abord : qu'est-ce qu'un orphelin ? Il s'agit d'une clé dans notre table de fait qui arrive de notre système source, mais qui n'est pas référencée dans la dimension associée. Comment cela peut-il se produire ? Plusieurs hypothèses sont possibles : une erreur à la saisie lors d'une gestion par saisie manuelle d'un code, des systèmes de gestions indépendants n'ayant pas des mises à jours simultanées, ... Les causes peuvent être multiples.

Dans les exemples qui suivent on va partir d'une table de faits de ventes qui contient un code client qui n'est pas dans la dimension client (l'exemple n'est pas innocent, c'est un cas rencontré chez un client. La création de la carte client remontait dans les systèmes sources avec une latence d'une quinzaine de jours alors que la vente était bien saisie sur la carte non encore créée)

On a donc en tables sources les tables suivantes :

Une table source Client :

Code Client	Nom	Prénom	…
12	Pierre	Jean
15	Dupont	Jacques
25	Martin	Louis

Une table source Ventes :

DateVente	CodeClient	CodeProduit	Quantité	…
12/12/11	12	FR1234	2
12/12/11	12	FR3421	5
13/12/11	25	GR0987	10
15/12/11	40	RF5483	9

qui contient bien un client non référencé (ici le CodeClient 40)


1ère méthode : Le rejet


La première méthode est la plus simple : elle consiste à dire que cette ligne de ventes avec un client inconnu est une anomalie, et à rejeter de ce fait la ligne.

Attention, il faut penser à insérer la ligne rejetée dans une table de rejet pour pouvoir retenter une intégration une fois notre système source client mis à jour.

On a donc au final les 3 tables suivantes :

Une dimension Client :

IdClient	CodeClient	Nom	Prénom	…
1	12	Pierre	Jean
2	15	Dupont	Jacques
3	25	Martin	Louis

Une table de faits Ventes :

IdDate	IdClient	IdProduit	Quantité	…
20111212	1	1	2
20111212	1	2	5
20111213	3	3	10

Et une table de rejet :

DateVente	CodeClient	CodeProduit	Quantité	…
15/12/11	40	RF5483	9

Cette méthode est une des plus simples à mettre en place. Il faut juste penser à rejouer l'intégration des lignes de la table de rejet de manière régulière.


2ème méthode : Création d'un membre inconnu

La méthode vue précédemment a un inconvénient : à un instant t, on peut avoir au niveau global des différences de chiffres. En effet des lignes sont rejetées, ce n'est pas la meilleure manière de gérer l'intégrité des données.
La solution consiste donc à définir un client inconnu sur lequel on rattachera les lignes contenant un code client absent du système source.

On crée donc un client inconnu dans notre nouvelle table de dimension Client (souvent en id -1, ligne que l'on créera une bonne fois pour toutes au moment de la création de la table) :

IdClient	CodeClient	Nom	Prénom	…
-1	N/A	N/A	N/A
1	12	Pierre	Jean
2	15	Dupont	Jacques
3	25	Martin	Louis

Et au moment du chargement de notre table de faits Ventes, on met comme IdClient -1 lorsqu'on trouve un client non présent dans la table de dimension :

IdDate	IdClient	IdProduit	Quantité	…
20111212	1	1	2
20111212	1	2	5
20111213	3	3	10
20111215	-1	4	9

Cette méthode est la plus répandue sur les projets décisionnels. Elle a cependant un inconvenient majeur, c'est qu'on a une perte d'information. Si le client est créé ultérieurement notre ligne sera toujours rattaché au client inconnu dans notre table de faits.

Pour cela, on peut utiliser une variation de cette deuxième méthode (méthode 2bis) qui consiste à rajouter dans notre table de faits le CodeClient afin de pouvoir mettre à jour l'IdClient une fois le client créé dans le système source.

La table de faits Ventes ressemblerait donc à :

IdDate	IdClient	CodeClient	IdProduit	Quantité	…
20111212	1	12	1	2
20111212	1	12	2	5
20111213	3	25	3	10
20111215	-1	40	4	9

Cela nécessite comme la première méthode le passage d'un script de mise à jour que l'on planifierait par exemple toutes les semaines ou tous les mois.
Je n'aime pas trop cette variation car elle crée dans la table de faits des données qui n'ont pas grand chose à y faire.


3ème méthode : la méthode "ultime"

La dernière méthode empêche toute perte d'informations mais peut être coûteuse en performance.
Elle consiste à scanner en amont du chargement de la table de faits les codes client non présents dans la table de dimension, et à les insérer dans la table de dimension, avec des attributs neutres qui seront mis à jour lors de l'arrivée du client dans le système source.

On obtient alors la table de dimension suivante :

IdClient	CodeClient	Nom	Prénom	…
1	12	Pierre	Jean
2	15	Dupont	Jacques
3	25	Martin	Louis
4	40	Inconnu	Inconnu

Le nom inconnu et le prénom inconnu seront mis à jour lors de l'arrivée dans le système source du client ayant le CodeClient 40.

Et notre chargement de la table de faits donne la table suivante :

IdDate	IdClient	IdProduit	Quantité	…
20111212	1	1	2
20111212	1	2	5
20111213	3	3	10
20111215	4	4	9

Cette méthode est la plus robuste (d'où le nom "ultime") dans le sens où vous pouvez alimenter votre table de faits sans lancer l'alimentation de votre table de dimension et ne pas perdre d'informations.


Conclusion :

Le méthode 2bis et 3 sont les plus robustes en terme de conservation de l'information. En revanche, elles n'ont d'intérêt que dans le cas où on a une chance de recevoir ultérieurement le client dans le système source. Dans les faits, ce sont la méthode 2 et dans une moindre mesure la méthode 3 qui sont les plus utilisées sur les projets.
Gardez à l'esprit que la gestion des orphelins doit se faire en fonction du souhait de votre client car il s'agit avant tout d'une problématique fonctionnelle. Ce n'est pas au développeur de décider quelle méthode implémenter, mais c'est à lui d'informer son client quand aux différentes gestions possibles.

Premier article : Modélisation d'une dimension temps

Pour mon premier article, je souhaite parler d'un sujet qui va paraître évident à tout le monde et qui est la première pierre de tout système décisionnel : la dimension Temps


Pourquoi m'attarder dessus ? Tout simplement car je remarque très souvent chez mes clients que les choses les plus évidentes ne le sont pas forcément pour tout le monde.


Je parlerai essentiellement de deux choses :

• La clé primaire de cette dimension
• Le numéros de semaine et les années qui y sont rattachées

Clé primaire d'une dimension Temps :

On nous apprend très tôt que toute dimension d'un datawarehouse doit être un entier auto-incrémenté, et beaucoup l'applique également au temps.

Je préconise plutôt un entier de la forme YYYYMMDD (par exemple 20101119) comme clé primaire.

Ceci pour deux avantages :

• Une requête sur les tables de faits ne nécessitent pas de jointures sur la dimension Temps car la date est stockée en clair.
• Dans le cas d'une forte volumétrie qui vous impose de partitionner votre base de données relationnelle, votre fonction de partitionnement aura une valeur fonctionnelle : c'est-à-dire que vous définirez les plages de votre fonctions entre deux dates.

Numéros de semaine et année de la semaine :

Sauf besoin spécifique de votre client, celui-ci attend comme numéro de semaine, le numéro ISO de la semaine, car il s'agit de celui qui permet de comparer une semaine d'une année à l'autre.

Je vous donne le lien vers la définition de Wikipedia, mais pour faire simple, la première semaine d'une année est celle qui contient le premier jeudi de l'année.

SQL Server 2008 a introduit dans sa fonction datepart le mot-clé isowk, c'est pourquoi vous pouvez définir votre numéro de semaine directement à la création de votre table de dimension en créant une colonne calculée DATEPART(isowk,date).

Cette définition implique qu'une date n'est pas forcément rattachée à son année civile (par exemple le 1er janvier 2006 est rattaché à la semaine 52 de l'année 2005). Il faut donc ajouter une colonne année de la semaine dans notre table.

Celle-ci peut être aussi ajoutée grâce à une colonne calculée :

DATEPART(year,DATEADD(d,-(DATEPART(weekday,date)+5) % 7+3,date))
(plus de détails sur le calcul ici)


to be continued...