Best Practice Microsoft BI : SSIS

Microsoft SQL Server Integration Services (SSIS) est un ETL (Extract, Transform, Load). Les préconisations en matière de développement varient d’une entreprise à l’autre. Cependant certaines bonnes pratiques garantissent une intégration des données et des développement facile à maintenir.

Voici une liste non exhaustive de « Best Practice » permettant d’uniformiser, d’optimiser les développements avec SSIS:

Structuration des packages SSIS

1. Afin de faciliter le développement, la maintenance et la reprise d’activité des différentes tâches SSIS, il est conseillé de les segmenter en différents packages SSIS.
2. Le chargement d’une dimension ou d’une table de faits doit correspondre à un package SSIS.
3. Le pilotage du chargement de l’ensemble des dimensions ou de l’ensemble des tables de fait doit aussi correspondre à un package.
4. Le pilotage global correspondant au chargement des dimensions, des tables de faits et du process du cube doit également faire partie d’un Package SSIS
5. Nommer systématiquement les composants du Control Flow et du DataFlow
6. Si Integration Services et SQL Server sont exécuté sur le même serveur, utiliser la destination SQL Server au lieu de la destination OLE DB pour améliorer les performances.

Utilisation du composant Data Flow

1. Commenter le code.
2. Définir des variables pour le chemin des fichiers, les paramètres de connexion à une base de données.
3. Nommer les colonnes au niveau de la source identiques à ceux de destination pour permettre le mapping automatique.
4. Avec une source de type fichier activer l’option FastParse pour les dates et champ de type numérique.
5. Définir les bons types de données pour éviter de faire des conversions de données.
6. Ne pas sélectionner les colonnes non utilisées dans la source de données.
7. Effectuer les conversions de type au niveau SQL dans la mesure du possible.
8. Ne retourner que les colonnes et les lignes nécessaires :
   • Pas de SELECT *
   • Clause WHERE adaptée, utiliser de préférence un filtrage à la source de données
9. Utilisez le NOLOCK ou TABLOCK dans vos SELECT pour supprimer les temps de verrouillage.
10. Éviter de filtrer les données à l’aide du composant « Conditional Split ».
11. Limiter l’usage des composants « Union All » et « Multicast ».

Traitement des cubes Analysis Services

1. Alimenter les tables de dimension en parallèle en les regroupant dans un container de type « séquence ».
2. Pour des chargements important de lignes :
   1. Partitionner la table de fait,
   2. Charger les données dans une table temporaire sans indexes, ni contraintes,
   3. Indexer et ajouter les contraintes de la table temporaire,
   4. Intégrer la table temporaire dans la table partitionnée.
3. Éviter au maximum de faire des opérations bloquantes en particuliers les tris. Réaliser au maximum les tris au niveau du SGBD et configurer la source de données avec la propriété « source triée »

Paramétrage d’un package SSIS

1. Afin d’être indépendant de l’environnement, définir une variable d’environnement pour spécifier le chemin physique du fichier de configuration dtsconfig.
2. Paramétrer tous les éléments variant d’un environnement à un autre comme le nom d’un serveur, le chemin d’un fichier,…
3. Enregistrer les paramètres de configuration des packages SSIS en base de données afin de faciliter le déploiement, la maintenance, l’exploitation et la reprise des activités des packages SSIS.
4. Définir le lien vers la base de données de configuration à travers un fichier de configuration. Le chemin de configuration doit lui-même être défini à l’aide d’une variable d’environnement pour être indépendant de la plateforme.
5. Créer une variable de type package parent pour assurer le suivi d’exécution des packages SSIS.

Exploitation

1. Prévoir l’exécution des packages en lignes de commandes si ils doivent être pilotés depuis un ordonnanceur, un programme,…,  sinon utiliser l’agent de SQL Server.
2. Afin d’enrichir la traçabilité de l’exécution des packages SSIS, il est nécessaire d’enrichir le schéma de base de données de chargement en rajoutant les tables ExecutionLog, StatisticLog, CommandLog et ProcessLog. Il est pertinent de créer un schéma de base de données « audit ».
3. Tracer dans les tables précédemment mentionnés le début, la fin d’exécution des packages, ainsi les différentes étapes du DataFlow.
4. Implémenter l’évènement au niveau du package OnError et tracer les erreurs éventuelles.
5. Créer des rapports Reporting services pour suivre l’activité des packages.

Conception de Package en équipe

1. Il est préconisé d’utiliser des modèles de package afin de réutiliser des composants.
2. Afin de conserver un historique des développements, de gérer les conflits dans le travail collaboratif entre les développeurs, il est préconisé d’utiliser un outil de gestion de source.

Un package modèle est un package SSIS standard dans lequel les éléments communs auront été implémentés. Ce package doit ensuite être copié dans le répertoire :

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems