Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

Comme à mon habitude à la fin de chaque mission j’essaie de faire un état des lieux de mes connaissances et prendre un peu de recul sur les sujets traités.
Aujourd’hui ma réflexion prendra dans cet article la forme d’une étude comparative à l’usage pour un développeur ( il ne s’agit pas de faire un test de performance ici).

Ceci afin de vous permettre de faire le parallèle en mettant en évidence les avantages, les similitudes et les inconvénients de deux des références phares du marché de l’intégration des données SAS studio et Integration Services de Microsoft sur laquelle nous sommes spécialisés chez AI3.

Avant toute migration d’un outil à l’autre, et pour l’avoir expérimenté  dans une moindre mesure lors d’une mission précédente ( notamment dans le cadre d’une migration de rapports  de masse développés initialement avec DESKI de  Business Object vers Reporting Services) il est nécessaire de se poser des questions essentielles à la bonne marche du projet :

  • Faire l’inventaire des composants à disposition les plus utilisées.
  • Avoir des spécifications fonctionnelles à jour.
  • Réaliser un modèle conceptuel des données
  • Savoir si le procédé de développement est réutilisable et reproductible dans l’outil cible.
  • Bien comprendre fonctionnellement le sujet à traiter afin de ne pas se focaliser sur un procédé unique de développement.
  • Adopter une démarche globale en se gardant de vouloir reproduire à l’identique l’intégralité des développements ( parfois il est par exemple nécessaire de passer par du code SQL avec Microsoft pour développer certaines phases qui existent via un composant natif sous SAS) et ainsi éviter d’alourdir les traitements avec des composants bloquants ou semi bloquants connus tels que le SORT, UNION ALL ou le MERGE JOIN qui existent sous SAS ( Sort, Append et LOOKUP mais ne produise pas les mêmes effets).
  • Eviter autant que possible l’emploi des colonnes de type nvarchar(max) souvent proposées par SSIS lors de la création de la table de destination dans le cas où vous utilisez du XML PATH pour faire de la concaténation de valeur sur une clef
  • Connaitre la profondeur des données, la fréquence et les méthodes de chargement (Incrémental, Full)
  • Identifier  les traitements gourmands et chronophages dans l’optique de les assainir en proposant une solution plus rapide.
  • Se renseigner sur les sources de données à intégrer( Type de base de données : Oracle , SQL, MySQL, AS400 ou fichiers plats, excel, csv) afin de savoir au préalable si les connecteurs existent.
  • Séparer et distinguer, pour des questions de maintenabilité et de clarté, les phases d’extraction , de transformation et de chargement des données (1 projet pour chaque phase et par sujet fonctionnel).

A la découverte de SAS

Premièrement, j’ai été sacrément surpris de constater que SAS ne dispose pas d’un moteur de base de données ( ce qui est le cas chez Microsoft  qui propose un outil d’administration de base de données Management Studio )

Cet outil, développé en JAVA s’articule autour d’une organisation logique des données réparties en fichier physique pour  chacune des « tables » du modèle portant une extension .sas  et appelé via une library qui référence le chemin physique.

La possibilité est tout de même faite de pouvoir appliquer des indexes avec une utilité bien plus limitée qu’avec SQL Server de l’avis des experts SAS.

Fonctions SAS Vs Fonctions SQL et VB/C#
Maintenant mettons le curseur sur les fonctions disponibles et les points de convergence ou non dans SQL Server.
Retenons que SAS est initialement un outil de statistique appliqué à l’intégration de données et en ce sens, il possède énormément de fonctions mathématiques natives et de manipulation de chaîne de caractère non développées chez Microsoft ou nécessitant plusieurs lignes de codes en SQL.

Je vous ai mis en pièce jointe un peu plus bas dans l’article, un tableau des principales fonctions et de leurs équivalences chez Microsoft.
Attardons nous maintenant sur les fonctions les plus usitées chez les SASiens:

  • Majuscule/Minuscule
    • PROPCASE() , permet de mettre en majuscule la première lettre de chaque mot d’une chaine de caractère et le reste en minuscule, exempale :
      • La chaîne de caractère « mIcrOsofT sSis » après transformation apparaîtra ainsi « Microsoft SSIS ». Ce qui n’existe pas nativement parmi la liste des fonctions proposées par SSIS. Nous avons aussi la possibilité de faire appel au code SQL (en mettant en UPPER() la première lettre de chaque mot via la fonction LEFT() ou SUBSTRING() et LOWER() les autres lettres du mot. Il faudra au préalable déterminer via un algorithme le nombre de mots contenu dans la chaîne de caractère , ce qui n’est pas forcément aisé en soi).
      • La solution de contournement est de passer par du code VBA dans une table de script SSIS car il existe une fonction VBA équivalente à la fonction PROPCASE() de SAS -> VbStrConv.Propercase
  • Retrait des espaces blancs
    • COMPBL() permet de garder un seul espace entre les mots -> faisable avec du code SQL mais largement plus « tricky » en faisant appel à la fonction REPLACE() et en prenant en pré calculant le nombre d’espaces blancs entre les mots.
    • COMPRESS() supprime l’ensemble des espaces blancs.
    • STRIP() supprime les espaces blancs à gauche et à droite à l’image de la fonction TRIM() SQL ou de la concaténation des deux fonctions  LTRIM(RTRIM())  dans un composant Derived Column de SSIS.
  • Fonction de concatenation
    •  CATX enlève les espaces blancs au début et  à la fin d’une chaîne de caractère et permet de concaténer deux colonnes en y ajoutant un séparateur par exemple « , « . Facilement reproductible avec l’opérateur « + » sur une chaine de caractère avec SQL
    • le composant User Written permet de faire des agrégations de données en concaténant certaines valeurs à l’image du XML PATH ou des fonctions SQL récursives
  • Fonctions  de sequençage

En bref, certaines fonctions SAS nécessitent une approche différente avec SSIS ( soit basé sur du Code SQL ou via une fonction C# / VB ou encore grace à la toolbox SSIS). Pour plus d’informations sur le sujet, cliquer sur le lien ci-dessous recensant les fonctions SAS transposable sur SSIS :
Fonctions SAS vs SSIS


 

Analyse croisée des deux outils (SAS et SSIS)

Folder SAS                                                                                                          Solution Explorer SSIS

blog ai3 C15 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS                         blog ai3 C14 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

On constate que la disposition des éléments du projet sont similaires des deux côtés. Dans l’environnement SAS vous retrouverz une liste de jobs dans lesquels les développements sont réalisés (Dossier 02 – Jobs figure ci-dessus).

Une particularité SAS, on retrouve un autre dossier appelé « Data » qui permet d’y ajouter l’ensemble des tables nécessaires au job. En comparaison, à partir de la version de Visual Studio 2013, il est possible d’intégrer un projet de base de données afin d’avoir une vue sur la base de données et pouvoir directement  créer les objets de base de données (tables, vues, indexes, utilisateurs, schémas etc) depuis Visual Studio.
Enfin retenez que côté SSIS on ne parle pas de Job mais de package SSIS pour désigner le lieu de développement des transformations du projet organisées par lot.

Boite à outil SAS                                                                                                        Boite à outil SSIS

blog ai3 C10 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS                                            blog ai3 C11 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

On notera que la disposition des éléments est similaire entre les deux outils avec la possibilité de faire un glisser/déposer dans la zone de développement.

Autre point commun, le Control Flow :

Figure 1-1 (Control Flow SAS)

blog ai3 C1 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS
Par exemple l’encadré rouge  de la Figure 1-1 fait apparaître la connexion aux tables sources qui vont faire l’objet d’une requête en base durant le traitement.
Une fonctionnalité simple mais ô combien utile, vous avez la possibilité de suivre et connaitre d’un simple coup d’œil l’enchaînement d’exécution des différentes boites car elles sont numérotées (surligné en jaune Figure 1). Un simple double clic sur une des boites telles que celles entourées en vert sur la Figure 1-1 permet d’aller à un niveau inférieur.
Ce qui n’est pas sans rappeler le Data Flow Task SSIS et fait apparaître les éléments suivants :

Figure 1-2 (Exemple de data flow SAS )

blog ai3 C3 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

A ce niveau, il faut retenir une chose, les étapes sont toutes, en règle général, codées en un pseudo code SQL ( figure 1-3) avec des vues temporaires en entrée et en sortie de chaque étape (à contrario, si vous regardez le contenu d’un package SSIS, il est codé en XML).
Lors d’une migration SAS vers SSIS nous serions tentées de récupérer ce code SQL et en faire une seule procédure stockée  avec des tables temporaires imbriquées les unes aux autres en SQL en respectant l’ordre d’exécution.
Mais et il y a toujours un mais !! Vous en conviendrez, cette solution ne pourra en aucun cas être viable et maintenable par une autre personne que le développeur initial du package SSIS.
C’est pourquoi, il faut prendre les précautions d’usage et éviter d’une, de vouloir reproduire à l’identique les étapes stricto sensu, et aussi de vouloir tout bonnement remodeler le code SQL SAS vers une tache Execute Script SQL.

(Plutôt privilégier la compréhension fonctionnelle du besoin et garder en tête les bonnes pratiques SSIS édictées au début de l’article)

Figure 1-3 (Code SAS SQL)

blog ai3 C9-2 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

 

Petite différence notable sans  réelle conséquence, le développement du Job SAS s’organise de façon horizontal alors qu’avec SSIS il est souvent vertical comme le montre la figure ci-dessous :

Figure 2-1 (Exemple de Control Flow SSIS)

blog ai3 SSIS1 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

 

Plusieurs connecteurs de base de données sont disponibles (AS400, Oracle, Excel, Mysql et même vers SAS (non natif)) pour établir une connexion avec la base de données source/destinations :

Figure 3 : Exemple de connection Managers SSIS

blog ai3 C12 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

Dans l’onglet Connection Managers faire un clic droit > New Connection > et choisir le connecteur approprié.
Une fois validé, une icone  symbolisant la connexion _Integration (flèche noire) sur laquelle vous pourrez cliquer et renseigner les propriétés suivantes Provider, Server name ,  Connect to a database sur la figure 3 ci -dessus.

Autre point d’attention, l’élément en bout de chaîne de la figure  1-2 ci-dessus écrit dans une table temporaire appelé « Work ».
Toutes les tables works sont stockées dans un espace dédié sur le serveur hébergeant les données SAS.

Afin de pouvoir ouvrir une « work », faire un clic droit au niveau du control flow sur l’icone en forme de cellule et appuyer sur open.

blog ai3 C6 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS
A noter que l’approche est légèrement différente dans SSIS car bien souvent, nous faisons appel aux tables temporaires SQL ou tables physiques ou encore aux fichiers RAW (avec les lenteurs de traitements peut entraîner)

Le gros avantage de ces tables works est de pouvoir lire directement  le contenu de la table (après exécution) pour chacune des étapes du développement à l’image des Data Viewer SSIS (Figure 4) que l’on peut intercaler entre deux éléments à ceci près que les données seront consultables uniquement lors de l’exécution du package et non après exécution comme c’est le cas pour SAS.

Figure 4 (Exemple de Data Flow SSIS avec activation du Data viewer):

blog ai3 SSIS2 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

 

Revenons maintenant sur les transformations pouvant être opérées lors du traitement:
Nous avons vu plus haut que chacune des boîtes possédaient un code qui en l’occurrence est  proche du langage SQL .
L’utilisation des fonctions SAS peut être implémentés dans des composants tels que le SQL JOIN , SORT , EXTRACT, JOIN avec la possibilité de voir le mapping et les relations entre les colonnes de l’étape précédente et les colonnes où sont effectuées les transformations.

Figure 5 (Mapping entre deux éléments reliés sur SAS)

blog ai3 C2 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

Vous constaterez que la « target table » dans l’exemple ci-dessous permet de faire des transformations et d’utiliser toutes les fonctions disponibles sur SAS.
Si je devais trouver un équivalent SSIS, plusieurs éléments dans la boite à outil sont à notre disposition pour réaliser la même action:

  • La première, est de pouvoir ou non incorporer ces fonctions dans une source de données OLE_DB avec du code SQL.
  • La seconde, serait d’utiliser des éléments SSIS de transformation des données tels que le Derived Column, Copy column, Conditional Split ou encore le composant Map columns.

Voir figure ci dessous, l’exemple d’un composant Derived Column, l’encadré de droite permet de choisir parmi un panel de fonction SSIS pour réaliser la transformation souhaitée :

blog ai3 C13 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

 

Exemples de composants avec un comportement similaire entre SAS et SSIS

  • Split SAS                                                                                               Multicast SSIS

blog ai3 C8 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS                                                                       blog ai3 C19 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

  • Sort SAS  <-> Sort SSIS
  • SQL Join SAS <-> Merge Join SSIS
  • Append SAS <-> Union ALL SSIS
  • TRANSPOSE SAS <-> PIVOT/UNPIVOT SSIS
  • USER WRITTEN SAS <-> SCRIPT CODE SSIS

Débuggage  SAS VS SSIS

Avec SAS, lorsque vous voulez tester vos développements il est possible de relancer tout ou une partie du Job:

  • Run From Selected Transformations -> (Lancement du Job à partir de l’élément sélectionné)
  • Run To Selected Transformations -> (Lancement du Job jusqu’à l’élément sélectionné)
  • Run Selected Transformations -> (Lancement des éléments sélectionnés)
  • Step-> (Lancement intégrale du Job)

blog ai3 C16 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

SSIS permet au niveau du Control flow de relancer soit l’intégralité des data flow associés au package, soit d’en sélectionner un parmi la liste. Le seul bémol est de ne pas être en mesure, à l’intérieur d’un Data Flow task, d’exécuter qu’une  partie des éléments. Evidemment  ceci n’est pas possible car SSIS ne créé pas de tables physiques temporaires en entrée et en sortie de chaque composant mais par définition fait plutôt appel à la RAM de votre serveur pour stocker les différentes étapes.

blog ai3 C17 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

 

Conclusion

Afin de clore ce petit article dense en informations en étant optimiste pour les prochaines versions de Visual Studio (Data Tools), je conclurai en disant qu’en tant que développeur SSIS, plusieurs axes d’amélioration sont encore à explorer pour garantir au développeur un confort intégral de développement notamment en s’inspirant de quelques fonctionnalité SAS (cahier de doléances d’un petit développeur SSIS 🙂 .

  • En premier lieu, je vais citer une fonction d’analyse qui permettrait dans le cas ou vous faites des modifications sur un package de connaitre les dommages collatéraux et les packages impactés.
    SAS possède une fonction de ce type avec deux options disponibles reverse/impact analysis:

blog ai3 C18 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

  • Deuxio, la numérotation des boites dans un Data Flow task me parait utile pour la compréhension dans le cas où un autre développeur serait amené à reprendre le travail effectué
  • Augmenter l’éventail de fonctions natives utilisables avec SSIS
  • Revoir la possibilité de lancer intégralement le package SSIS
  • La nécessité de pouvoir ajouter de la documentations du Code et des colonnes. Par exemple sur SAS, il est possible de mettre une description fonctionnelle des colonnes ( dans le cas où c’est souvent le cas votre entreprise serait pauvre en spécifications  🙂 )  ou dans le cas d’utilisation de tables  importées d’un AS400 du style Table.AAGGDF dont le champ est nommé DTFSQ.
    SAS autorise par exemple, la possibilité de renseigner la propriété description « Column Description » et aussi de changer la propriété Informat permettant de formatter à la volée d’une colonne lors d’un traitement  :blog ai3 C20 Analyse croisée des ETL SAS Data Integration Studio et Microsoft SSIS

 

  • Parmi les avantages d’une solution comme SQL Server et SSIS pour l’intégration des données je noterai que la possibilité de positionner des indexes tels que les columnstore index  (disponible à partir de la version 2012 de SQL Server) et autres indexes classiques améliorent grandement le chargement et l’exploitation des données.
  • Autre avantage, le nombre de connecteurs aux bases de données semblent plus étoffés côté SSIS, il est même possible d’aller lire les fichiers des tables SAS par exemple.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.