Bon suite aux posts de Florian http://fleid.net/2011/12/13/pour-ou-contre-les-clefs-etrangeres-dans-le-datawarehouse et de moi-même http://wp.me/p2yhHc-6T sur le « contre » ou le « pour » des Foreign Keys (FKs) dans un DWH, je vous propose un Pattern pour ceux :
- qui voudraient rester libre (voir anarchiste) et se passer de FKs lors de la phase de l’alimentation (pour faire plein de Truncate et s’affranchir des contraintes, sans jeux de mots)
- mais qui, avec leur âme de papa, pourraient vouloir les réactiver afin d’exposer aux utilisateurs, les métadonnées « complètes » du DWH (BI Self-Service oblige) et de sécuriser les données qui ont été chargées (les équipes BI ne sont pas les seules à intervenir sur un DWH)
Merci à Florian pour cette analogie anarchiste/papa, j’adore.
Le principe est simple :
- designer l’entrepôt avec les FKs
- stocker les définitions des FKs et les supprimer les FKs avant chaque alimentation
- alimenter l’entrepôt
- recréer les FKs
Et pour cela, nous n’avons besoin que d’une table et de deux packages SSIS.
Mise en place de la table permettant de stocker la définition des FKs
Cette dernière est assez simple, il faut pouvoir stocker l’ensemble des informations relatives aux FKs afin de pouvoir les recréer par la suite. Etant donné le lifecycle de votre projet, cette table doit être vidée et rechargée à chaque exécution afin d’avoir dynamiquement la liste actuelle des FKs à gérer
CREATE TABLE [adm].[ForeignKeyManagement]( [IdForeignKey] [int] IDENTITY(1,1) NOT NULL, [FkConstraintName] [nvarchar](130) NOT NULL, [FkTableName] [nvarchar](130) NOT NULL, [FkTableSchema] [nvarchar](130) NOT NULL, [PkTableName] [nvarchar](130) NOT NULL, [PkTableSchema] [nvarchar](130) NOT NULL, [FKeyColumns] [nvarchar](max) NOT NULL, [PKeyColumns] [nvarchar](max) NOT NULL, [IsDisabled] [bit] NULL, [IsNotForReplication] [bit] NULL, [DeleteReferentialAction] [tinyint] NULL, [UpdateReferentielAction] [tinyint] NULL, CONSTRAINT [PK_ForeignKeyManagement] PRIMARY KEY CLUSTERED ([IdForeignKey] ASC), CONSTRAINT [AK_ForeignKeyManagement] UNIQUE NONCLUSTERED ([FkConstraintName] ASC) )
Package de suppression des FKs
Ce package permet de lister toutes le FKs et de stocker leur définition dans la table adm.ForeignKeyManagement puis de les supprimer du DWH.
Pour récupérer la liste des FKs, des tables et des colonnes mises en jeux, il suffit de s’appuyer sur les vues systèmes sys.foreign_keys et sys.foreign_key_columns. Afin de n’avoir qu’une seule ligne par FKs, il faut linéariser les colonnes de clés étrangères et les colonnes de clés primaires :
SELECT
FkConstraintName,
FkTableName,
FkTableSchema,
PkTableName,
PkTableSchema,
STUFF(FKeyColumns,1,1,'') as FKeyColumns,
STUFF(PKeyColumns,1,1,'') as PKeyColumns,
IsDisabled,
IsNotForReplication,
DeleteReferentialAction,
UpdateReferentielAction
from
(
select
'[' + foreign_keys.name + ']' as FkConstraintName,
'[' + OBJECT_NAME(foreign_keys.parent_object_id) + ']' as FkTableName,
'[' + schema_name(foreign_keys.schema_id) + ']' as FkTableSchema,
'[' + tables.name + ']' as PkTableName,
'[' + schema_name(tables.schema_id) + ']' as PkTableSchema,
(
select ',[' + col_name(foreign_keys.parent_object_id, foreign_key_columns.parent_column_id) + ']' as 'data()'
from sys.foreign_key_columns
where foreign_key_columns.constraint_object_id = foreign_keys.object_id
order by foreign_key_columns.constraint_column_id
for xml path('')
) as FKeyColumns,
(
select ',[' + col_name(foreign_keys.referenced_object_id, foreign_key_columns.referenced_column_id) + ']' as 'data()'
from sys.foreign_key_columns
where foreign_key_columns.constraint_object_id = foreign_keys.object_id
order by foreign_key_columns.constraint_column_id
for xml path('')
) as PKeyColumns,
foreign_keys.is_disabled as IsDisabled,
foreign_keys.is_not_for_replication as IsNotForReplication,
foreign_keys.delete_referential_action as DeleteReferentialAction,
foreign_keys.update_referential_action as UpdateReferentielAction
from
sys.foreign_keys
LEFT OUTER JOIN sys.tables ON tables.OBJECT_ID = foreign_keys.referenced_object_id
) ForeignKeys
Je duplique ensuite le flux pour l’insérer d’une part dans la table adm.ForeignKeyManagement et d’autre part dans un recordset afin de boucler par la suite dessus pour supprimer les contraintes une à une. Le recordset est stocké dans la variable @User::foreignKeyList, il se réduit quant à lui aux colonnes FkConstraintName, FkTableName, FkTableSchema.
Il ne reste plus qu’à boucler sur la variable @User::foreignKeyList pour supprimer les FKs une à une. Pour chaque occurrence, on récupère le nom de la contrainte, le nom de la table et le nom du schéma respectivement dans les variables @User::fkConstraintName, @User::FkTableName, @User::FkTableSchema
Cela me permet de construire dynamiquement la requête SQL permettant de supprimer chaque FK. L’ordre est construit par expression dans la variable User::sqlstmtDropFk
"ALTER TABLE " + @[User::fkTableShema] + "." + @[User::fkTableName] + " DROP CONSTRAINT " + @[User::fkConstraintName]
La syntaxe ainsi créée est ensuite exécutée dans un SQL Task.
Au final votre package doit avoir cette allure :
Package de création de FKs
Une fois votre DWH correctement chargé par votre ETL, il ne reste plus qu’à récupérer la définition des FKs et de les recréer.
Et là, rien de plus simple, il suffit de récupérer le contenu de la table adm.ForeignKeyManagement
SELECT [FkConstraintName], [FkTableName], [FkTableSchema], [PkTableName], [PkTableSchema], [FKeyColumns], [PKeyColumns], [IsDisabled], [IsNotForReplication], [DeleteReferentialAction], [UpdateReferentielAction] FROM [adm].[ForeignKeyManagement]
Cette requête est exécutée dans un SQL Task et le ResultSet est stockée dans la variable @User::foreignKeyList
Pour lire le ResultSet, j’utilise un For Each Loop Container; chaque colonne est mappée dans une variable
La mise en place des FKs s’effectue en deux temps :
-
la création de la FK avec la clause WITH NO CHECK, car il est inutile de vérifier la contrainte puisque vous avez correctement implémenté vos packages SSIS
- la modification de la contrainte l’activité (ou pas) en fonction du flag IsDisabled enregistré dans notre table adm.ForeignKeyManagement.
Il me faut donc deux variables, respectivement @User::sqlstmtCreateFk et @User::sqlstmtCreateFk alimentée par expression.
L’expression utilisée pour la création de la contrainte est la suivante :
"ALTER TABLE " + @[User::fkTableShema] + "." + @[User::fkTableName] + " WITH NOCHECK " +
" ADD CONSTRAINT " + @[User::fkConstraintName] + " FOREIGN KEY (" + @[User::fkColumns] + ")" +
" REFERENCES " + @[User::pkTableSchema] + "." + @[User::pkTableName] + "(" + @[User::pkColumns] + ")" +
" ON UPDATE " + ( @[User::updateReferentialAction] == 0 ? " NO ACTION " : (@[User::updateReferentialAction] == 1 ? " CASCADE " : " SET_NULL " ) ) +
" ON DELETE " + ( @[User::deleteReferentialAction] == 0 ? " NO ACTION " : (@[User::deleteReferentialAction] == 1 ? " CASCADE " : " SET_NULL " ) ) +
( @[User::isNotForReplication] ? " NOT FOR REPLICATION " : "")
L’expression utilisée pour l’activation ou pas de la contrainte est la suivante :
"ALTER TABLE " + @[User::fkTableShema] + "." + @[User::fkTableName] + ( @[User::isDisabled] ? " NOCHECK " : " CHECK " ) + "CONSTRAINT " + @[User::fkConstraintName]
Le package de création est donc au final très simple :
« Tchic Tchac, et voilà » comme dirait nous amis d’outre Atlantique. Vous n’avez qu’à intégrer ces deux packages à chaque bout de votre chaîne d’alimentation et le tour est joué.
Malgré tout, je ne saurais que vous conseiller de mettre une « Check Database Integrity Task » à exécuter une fois par semaine (le dimanche par exemple) car comme le dit la devise Shadok :
Les sources de cet article sont disponibles sur SkyDrive http://sdrv.ms/Tc1wv4
MS BI Addict 
Fred, j’aime beaucoup cet épilogue!
D’une part il repositionne bien le débat autour du choix quasi-philosophique à faire par rapport à son projet: rapidité ou sécurité.
D’autre part c’est une brique à ajouter pour l’automatisation des tests, pratique fondamentale pour la BI Agile mais techniquement délicate pour le moment en décisionnel Microsoft.
Merci 🙂
Salut Florian et merci de ce retour.
Mais c’est de la lecture de ton excellent résumé, opposant la liberté et la sécurité, que ce post est né. Il faut un savant et subtile mélange pour que les deux puissent cohabiter voir même se compléter. Un peu comme dans une république démocratique démocratique (en simplifiant à l’extrême)
Merci Fred pour cette idée et son implémentation.
Ce qui serait top ça serait un lien Skydrive/Google drive ou autre avec ton implem (SSIS + script SQL) ce qui permettrait de tester rapidement.
Voir même pusher tout ça sur Github ou Codeplex; Ce qui permettrait à d’autres de raffiner ta solution.
Dans le monde de la BI, les gens n’ont pas tendance à mettre à dispo leur solution techniques; Il me semble qu’on devrait prendre exemple sur les développeurs
En tout cas, bravo pour l’article.
Merci m’sieur.
Je pousserai le code source de cet article (et des précédents) sur skydrive.
Fred,
Tu sais que tu peux juste désactiver la FK et la réactiver après ?
ALTER TABLE NOCHECK CONSTRAINT ;
ALTER TABLE CHECK CONSTRAINT WITH CHECK;
Le WITH CHECK est optionnel car il va scanner toute la table pour s’assurer que la contrainte est « intègre »
Mais si tu ne le mets pas, ta FK devient « non trustée »…
select object_name(parent_object_id) as tablename, name, is_disabled, is_not_trusted
from sys.foreign_keys
order by is_not_trusted, tablename
PS : le DBCC CHECKDB ne vérifie pas la contrainte elle même mais la cohérence des méta-données.
Au début j’étais aussi parti sur cette solution, faire une simple boucle qui désactive toutes les FKs, mais je voulais que la solution ratisse plu large et puisse convenir à des modèles d’alimentation de type Truncate/Insert de façon à laisser le champs libre à l’ETL lors du chargement de l’entrepôt. Et la seule façon de faire des truncate sur des dims et/ou des M2M est de supprimer les contraintes.