Concepts de développement avancés

Gestion de la qualité et du changement dans les données

1. Data Quality Services (DQS)

a. Considérations sur DQS

Tout comme pour le CDC, ce n’est pas une pure fonctionnalité de SSIS. La mise en place détaillée de DQS est donc en dehors du périmètre de ce livre. Une présentation succincte se trouve tout de même ci-après.

DQS est une fonctionnalité permettant de garantir la cohérence des informations de l’entreprise. On retrouve dans ce premier élément de définition une analogie avec SSIS, mais pour autant les deux produits ne fonctionnent pas de la même façon. DQS va permettre de construire un référentiel (une base de connaissances) qui sera la source permettant de garantir cette cohérence des données, par exemple, dans une entreprise où les adresses des clients proviennent de multiples sources avec de légères différences orthographiques. Il est possible de consolider cette donnée via DQS et sa base de connaissances et l’édition de règles de gestion.

DQS est constitué de deux modules :

DQS Server : il inclut les bases système de DQS (base de connaissances, base de projets). Son installation se fait via un exécutable disponible à l’installation de SQL Server.
DQS Client : c’est une interface qui permet l’édition des règles de gestion (également appelées domaine), des bases de connaissances, l’exécution des processus de nettoyage, de mise en concordance, de profilage, l’administration du serveur DQS. C’est une application utilisateur à part entière qui est présente dans les fichiers d’installation de SQL Server (comme DQS Server).

Ci-dessous se trouve un exemple d’utilisation de DQS client. Une connexion sur le serveur DQS est réalisée, et une base de connaissances sélectionnée. Dans cette base se trouvent les domaines (analogues à des règles de gestion). Parmi ces domaines, Site est celui qui est utilisé dans le flux de données ci-dessous. Ce domaine a pour objectif la correction des noms de site en fonction de la nomenclature différente qui peut être utilisée par les diverses sources d’informations de l’entreprise....

Interactions avec SQL Server et SSAS

En plus des deux tâches d’exécution de requêtes SQL et de CDC, SSIS propose d’autres objets pour la maintenance et la gestion des bases de données relationnelles SQL Server.

1. Insertion en bloc

La tâche d’insertion en bloc permet d’insérer le contenu d’un fichier plat dans une table d’une base de données SQL Server. Elle fonctionne de manière similaire à l’instruction SQL BULK INSERT ou encore au programme bcp.exe. Cette insertion n’autorise pas les transformations de données, elles sont envoyées telles quelles dans la table de destination. De plus, le format du fichier et la structure de la table doivent strictement correspondre, sous peine de voir échouer l’opération d’insertion. Voici un aperçu de l’onglet Connexion de cette tâche :

Les propriétés Connection et DestinationTable permettent de définir le gestionnaire de connexions OLE DB à utiliser ainsi que la table vers laquelle les données seront envoyées. La propriété File utilise un gestionnaire de connexions de fichier ou de fichier plat. C’est la source des données.

Dans la section Format, on retrouve une partie des propriétés qu’il est possible de définir dans un gestionnaire de connexions de fichier plat, à savoir le délimiteur de ligne et de colonne. Dans le cas d’un fichier CSV classique, {CR}{LF} et la virgule sont de mise. Ces informations de formatage peuvent être spécifiées dans un fichier externe .fmt, il faut alors changer la propriété Format pour Utiliser un fichier. La description de la structure de ces fichiers se trouve dans la documentation en ligne du programme bpc.exe.

Le troisième onglet, Options, propose les propriétés suivantes :

Options : elle propose cinq choix :

Vérifier les contraintes : option activée par défaut, elle permet d’imposer la vérification des contraintes de la table (nullité des colonnes, valeur par défaut, etc.).
Conserver les valeurs NULL : permet de considérer les valeurs vides du fichier source en tant que NULL côté table de destination.
Activer l’insertion...

Checkpoints

Un lot en échec redémarre systématiquement à sa première tâche par défaut. Or, il est des cas où ce dernier échoue tout proche de sa complétion, et où il est légitimement souhaitable de pouvoir redémarrer à partir de la tâche en erreur, cela pour éviter de refaire une énième fois des traitements déjà effectués avec succès. La fonctionnalité Checkpoint (point de contrôle) permet cela, en créant lors d’une erreur un fichier XML stockant le contexte d’exécution du lot - les variables essentiellement - et la tâche fautive, afin de pouvoir reprendre précisément sur cette dernière dans les mêmes conditions.

1. Principe général

a. Cas d’usage classique

L’un des cas les plus classiques est celui où le lot a besoin d’une intervention manuelle pour fonctionner correctement, et effectue un certain nombre de traitements lourds avant cette intervention. Par exemple, un fichier FTP volumineux est récupéré via SSIS puis, suite à cela, une intégration des données du fichier est réalisée via une Tâche de flux de données. Si cette dernière tombe en échec, il serait intéressant de ne pas avoir à relancer la tâche FTP qui prend beaucoup de temps et d’alerter par e-mail l’équipe de support de production afin qu’elle corrige le référentiel de données, puis relance l’intégration à partir de la tâche en échec.

Le référentiel est censé être à jour, mais il peut arriver qu’un opérateur oublie une ou plusieurs valeurs, faisant échouer le flux de données. Un e-mail est alors envoyé à ce dernier pour lui demander d’alimenter instamment sa base.

Or, dans ce cas, le téléchargement des fichiers va être refait jusqu’à ce que le flux de données soit en succès. C’est dommage, surtout si les fichiers sont volumineux. Les checkpoints vont permettre de répondre à cette problématique.

b. Mise en place

Les paramètres des checkpoints se gèrent depuis toute tâche du flux de contrôle...

Transactions

Une transaction, au sens moteur de bases de données, est un ensemble de traitements qui n’a de sens que si toutes les parties réussissent. Si une seule des opérations échoue, l’ensemble des travaux effectués par les autres éléments constitutifs de la transaction doit être annulé. Integration Services permet nativement le support des transactions sur les tâches, de manière distribuée en utilisant le coordinateur de transactions distribuées, ou des transactions natives au moteur de base de données cible.

1. Principe des transactions

a. A.C.I.D.

Bien souvent, le principe des transactions se résume à l’acronyme ACID pour Atomicité, Cohérence, Isolation et Durabilité. Atomicité signifie qu’une transaction constitue une unique entité de multiples travaux, qui doit échouer ou réussir globalement. Si une seule partie de la transaction échoue, tout le reste doit être annulé. Cohérence signifie que la transaction génère un état cohérent. Cette notion de cohérence dépend du système cible, mais pour un projet BI cela peut vouloir dire que l’ensemble d’une table est chargé, et pas seulement quelques lignes. Isolation signifie que les transactions travaillent séparément les unes des autres, et que les données qu’elles modifient ne doivent pas les impacter mutuellement si elles sont en concurrence sur une ressource. Enfin, la durabilité d’une transaction désigne juste le fait qu’une fois validée, celle-ci est persistée.

Ces concepts sont propres au monde des bases de données relationnelles. SSIS permet leur implémentation au niveau de tâches du flux de contrôle, mais en raison de la nature multisource et multidestination d’un lot ETL, il en fait un usage quelque peu différent.

b. Transactions distribuées

SSIS fait usage de transactions distribuées pour apporter une touche transactionnelle aux traitements. Par défaut, une transaction est en effet destinée à un seul moteur de base, or dans SSIS, il doit être possible d’appliquer le paradigme ACID à un ensemble de bases de données. Pour permettre cela, SSIS utilise un service...