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.

3 réflexions sur “SSIS – Charger des données dans des tables liées

  1. nlievain dit :

    J’ajouterais : on fait une procédure stockée!
    Dans beaucoup de cas c’est bien plus performant que les taches de lookup, plus souple et surtout par le truchement des jointures on peut mettre à jours plusieurs FK d’un coup.
    Après c’est moins « etl » friendly

  2. La méthode des procédures stockées est de loin la plus mauvaise (Difficulté de maintenance, …), SSIS n’est pas un Ordonnanceur et nous ne sommes plus sous SQL Server 2000. Pour ma part je pense que Thomas à bien fait de ne pas mettre cette méthode qui doit tomber dans l’oubli🙂

  3. Je pense réellement que tout dépend des points forts et des faiblesses des gens. Il est essentiel d’utiliser une méthode dans laquelle on est le plus à l’aise mais également le moyen qui correspond le plus aux équipes qui s’occupent des flux de données.

    Le fameux débat développeur ETL et développeur SQL, est ce qu’on préfère travailler avec SSIS ou compter sur le moteur de base de données.
    Au final on peut arriver au même résultat en étant adepte à l’une ou l’autre école de pensée. Bien évidemment les questions de temps de traitements, optimisations et performances sont à prendre en compte.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s