SSIS – Charger des données dans des tables liées

Un titre très vaste pour un sujet très commun.

En effet il arrive souvent (très très souvent) qu’on doit alimenter des tables.
Il arrive de temps en temps (voir tout le temps) que ces tables soient reliées à une ou plusieurs tables.

Les relations entre ces tables s’appuient sur des contraintes de type clé étrangère. Donc si on alimente une table qui possède une clé étrangère on peut rencontrer des erreurs à cause de cette contrainte.

Une erreur du genre :

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_XXX_YYY"

Comment fait on pour pallier à ce problème de contraintes dans SSIS ?

Se poser des questions

Avant de se lancer dans le développement il faut se poser des questions:

  • Quelle la nature des données ?  Données critiques ou non critiques ?
  • Quelle est le type de ma destination ? Dimensions ? Table de faits ? Tables d’une base de données classique ? Tables externes/références ? Tables de Staging ? Tables Temporaires ?
  • Quel est l’importance de l’intégrité de ces données ?
  • Il y a t’il une logique de chargement ? L’ordre d’exécution de mes flux de données est il important ?
  • Est ce que cela doit prendre du temps ?
  • Est ce que je peux toujours changer ce qui a été fait pour m’adapter aux changements ?
  • Quelles sont les politiques et les pratiques actuelles concernant la gestion des données ? Et ma méthode sera t’elle adoptée ou rentre elle en conflit avec ce qui est déjà établi ?

Que de questions …

Les méthodes

Plusieurs techniques existent. Cet article n’a pas pour but de dévoiler la méthode à privilégiée, mais il vous présente les différents école de pensées.

A vous de faire un choix dépendant de la situation rencontrée.

Méthode 0

 « Selon la théorie, si j’ordonne mes flux tout ira bien »

Dans un monde parfait, les données issues de vos systèmes sources sont entièrement intègres. Par conséquent si l’on charge en premier la table de référence puis la table reliée à cette table de référence,
logiquement, vous n’aurez pas de problème.
Exemple : Une table Sous Catégorie reliée à une table Catégorie.
Si on charge toutes les Catégories dans un premier temps puis on charge les Sous Catégorie.

Sauf que dans le monde de la BI, les données ne sont pas si parfaites que ça  surtout quand vous intégrez plusieurs sources de données. Il se peut qu’une sous catégorie ne fasse pas du tout référence à une catégrorie.

Avantages :

  • Exécution logique voir naturelle
  • Principe d’ordonnancement des flux : l’ordre d’exécution des flux  est une notion fondamentale

Inconvénients :

Cette méthode est purement théorique, la pratique est une triste réalité. Dans un environnement où on intègre des données diverses et variées,  il est très propable de rencontrer des exceptions.

Méthode 1
« La contrainte nous dérange… Bah on l’enlève ! Et on la remet après »


Avec des composants Execute SQL de chaque côté de votre composant Data Flow Task.
Le premier Execute SQL va forcer le fait qu’on ne vérifie pas les contraintes via un ALTER TABLE  :

ALTER TABLE  SubCategory NOCHECK CONSTRAINT FK_SubCategory_Category

Le second Execute SQL va quant à lui  ré appliquer la contrainte après le chargement des  données.

ALTER TABLE SubCategory WITH CHECK CHECK CONSTRAINT FK_SubCategory_Category

Avantages:

  • Fonctionne bien et on contrôle ce que l’on fait.
  • Pas besoin d’ordonnancer vos flux.

Inconvénients:

  • Il faut connaitre les noms des contraintes
  • Il faut avoir les droits nécessaires sur les tables et pas sûre que l’idée emballe tout le monde.
  • Petite porte ouverte aux erreurs d’intégrités ?
  • Maintenance : Si on a plusieurs FK et que d’autre FK s’ajoute avec le temps

Méthode 2
« La contrainte nous dérange ? Heu non pas tout à fait, on peut passer à travers … »


Lorsque l’on utilise un composant OLE DB Destination, on a la possiblilté de décocher le Check Contraints.

Avantages:

  • Rapide et simple
  •  Pas besoin d’ordonnancer vos flux.

Inconvénients:

  • Grandes Portes ouvertes aux erreurs d’intégrités ? Désactive toutes les contraintes.
  • Fonctionne uniquement avec les destinations OLE DB en mode FAST LOAD (certes le mode le plus utilisé)

Méthode 3
 » On vérifie qu’il y a une correspondance dans la table de référence puis on charge si c’est vérifié »

C’est ce que l’on fait typiquement pour charger une table de faits. La vérification se fait grâce à un composant Lookup. En temps normal on a autant de LOOKUP que de FK.


Avantages:

  • Sagesse et Sécurité
  • Détections des erreurs

Inconvénients:

  • Le chargement doit être ordonné.
  •  Dépendance au composant Lookup qu’il faut savoir gérer (performance, optimisation…)

Conclusion

En fonction des cas et des situations que vous rencontrez, l’intégration de données dans des tables liées peut varier. Plusieurs critères sont à prendre en compte afin d’adopter une méthode adéquate.

Bien évidemment il n’est pas possible de prévoir tout les scénarios possibles, n’hésitez pas à partager vos points de vue.