[SSIS] Data Transformation : utilisation des composants de la logique floue

Ce billet aborde brièvement les composants dédiés à la logique floue (ou fuzzy logic), sous SSIS.

La logique floue est – pour être concis – un concept qui s’intéresse à l’approximation de valeurs (ou termes) au détriment de leur exactitude. D’une certaine façon, elle permet de travailler sur des problématiques qui dépassent la logique classique (ou booléenne).

SSIS et la logique floue

SSIS propose 2 types de transformations basées sur la logique floue : Fuzzy Lookup Transformation et Fuzzy Grouping Transformation.

La Fuzzy Lookup Transformation est une opération de recherche de floue dont la particularité est de permettre de retourner des enregistrements similaires. Par exemple « Dupont » est similaire à « Dupond ». La Fuzzy Grouping Transformation, quant à elle, sert à regrouper ensemble des enregistrements similaires.

Au niveau de SQL Server, l’algorithme utilisé pour les opérations relatives à la logique floue induit la création de quelques tables temporaires dans le but de déterminer les scores et finaliser les correspondances entre les différents enregistrements traités.

Il existe 2 types de scores :

  • Similarity à permet de mesurer, sur un intervalle de 0 à 1, jusqu’à quel point les enregistrements sont similaires.
  • Confidence àfait référence à un score de confiance, avec intervalle allant de 0 à 1. Un tel score dépend, pour chaque enregistrement traité, de ses correspondances.
Un score de similarité ne signifie pas forcément une confiance élevée. En effet, si par exemple la valeur traitée est Av. et que les correspondances renvoyées sont Avenue et Aventure, la valeur de similarité de ces résultats est élevée, pas la valeur de confiance, dans la mesure où aucun des 2 résultats est plus pertinent que l’autre.

Le composant Fuzzy Lookup Transformation

Les indexes de recherche floue

Comme dans le cas d’un composant de recherche (Lookup Transformation, Merge Transformation,…), 2 types de tables sont utilisées : une table en entrée (ou table source) et une table de référence (ou lookup reference table). Toutefois, dans le cas de la recherche floue via Fuzzy Lookup Transformation, la table de référence est appelée « index » (ce terme n’a rien à voir avec le terme d’ « index » dans le sens de B-Tree). Ces indexes sont utilisés par SSIS pour stocker des fragments d’éléments recherchés.


Dans le cadre de l’utilisation du composant Fuzzy Lookup Transformation, 4 options sont fournies et permettent de travailler avec lesdits indexes :

  • Create index every time à permet de générer un nouvel index en mémoire à partir de la table de référence. Option recommandée si le data flow est lancé occasionnellement.
  • Create a new index and store it à permet de générer un nouvel index et de le stocker comme un objet statique au sein de la base de données. Option recommandée si le data flow est lancé fréquemment, et que la table de référence change peu ou prou.
  • Create a new index with maintenance features à permet de générer un nouvel index, de le stocker et de le maintenir à jour. La mise-à-jour se fait via un trigger créé au sein de la table de référence de façon à assurer la réplication des changements. Option recommandée si le data flow est lancé fréquemment et que la table de référence est susceptible de changer régulièrement.
  • Reuse an existing index à permet de réutiliser un index existant. Option recommandée si le data flow est lancé trop fréquemment.

Contrôle des correspondances

La Fuzzy Lookup Transformation offre différentes options de recherche intéressantes.


L’option Maximum number of matches to output per lookup sert à restreindre le nombre maximum de correspondances que l’opérateur de recherché floue peut retourner. Par exemple, si la valeur de ladite option est spéficiée à 20, l’opérateur ne retournera que jusqu’à 20 correspondances, même si « Dupont » en a plus de 20.

L’option Similarity threshold est un seuil de similarité compris entre 0 et 1. Il s’agit, pour l’opérateur de recherche floue, de déterminer le score de similarité entre les différents termes comparés.

L’option Token Delimiters (ou séparateur de jetons) est intéressante dans le sens où elle permet de découper les termes de recherche en pièces pour la correspondance floue.

Le composant Fuzzy Grouping Transformation

Le composant Fuzzy Grouping Transformation permet d’identifier les termes (ou enregistrements) similaires.

Son algorithme est le même que celui du composant Fuzzy Lookup Transformation, à la différence qu’il crée sa propre table de recherche basée sur les données en entrée qui seront utilisées pour référencer les termes similaires.

Concrètement, le composant charge toutes les données en entrée dans une (ou plusieurs) table(s) temporaire(s) dédiée(s), scanne tout le(ur) contenu, afin de rechercher les termes similaires. Après le scan, il retourne en sortie chaque correspondance dont le score est au-dessus du seuil de similarité (le cas échéant, il ne retourne rien).

Au sein des options avancées du composant, 2 noms de clés peuvent être spécifiés : un pour la colonne de clé en entrée (_key_in) et un autre pour celle en sortie (_key_out). Et à celles-là s’ajoutent la définition d’une autre colonne : celle destinée à contenir le score (_score) ou niveau de similarité.


Comme pour le composant Fuzzy Lookup Transformation, un seuil de similarité peut être défini (de 0 à 1) ainsi que des séparateurs de jetons.

Exemple d’utilisation avec SSIS

    Contexte

Supposons que l’on possède un fichier Excel appelé Employés et que l’on souhaite retourner les informations dont les noms des employés issus dudit fichier sont similaires à ceux issus d’une table SQL Server appelée Emp.

Voici le contenu du fichier Excel :


Quant à la table SQL Server, elle possède les enregistrements suivants :


    Implémentation…

On suppose qu’un data flow a été créé. Et qu’au sein dudit data flow :

  • Le composant Excel Source a déjà été configuré :


  • Les colonnes Excel ont été converties (si nécessaire), via Data Conversion, pour correspondre au type de données de celles de la table Emp de la base SQL Server :


  • Un composant OLE DB Source a été configuré pour pointer vers la table Emp de la base SQL Server :


  • Un composant d’opération de logique floue (Fuzzy Grouping Transformation ou Fuzzy Lookup Transformation, selon les besoins) a été placé sur la zone de design.
  • Et enfin, un composant Union All permettant de réunir les informations Excel et SQL Server a été placé dans la zone de design, Et qu’un flux allant de ce composant vers le composant de logique floue utilisé a été créé.


Le résultat sera le suivant :

  • Pour la recherche floue (Fuzzy Lookup Transformation) :
  • Pour le regroupement (Fuzzy Grouping Transformation) :


        Avec le composant Fuzzy Grouping Transformation

Dans l’onglet Connection Manager, l’instance SQL Server est spécifiée afin d’accueillir la ou les tables temporaires que le composant de transformation créera pour ses traitements :


Au sein de l’onglet Columns du composant, sélectionner les colonnes à traverser (Pass Through) et celles à prendre en compte pour la recherche de similarités (dans notre cas, le nom de l’employé) :


Dans l’onglet Columns il existe un ensemble d’informations utiles qui influenceront l’analyse des similarités des colonnes :

  • Input Column à colonne sélectionnée pour le regroupement.
  • Ouput Alias à nom de la colonne sélectionnée en sortie.
  • Group Output Alias à colonne qui détient la meilleure valeur de correspondance pour le regroupement (dans notre cas, Excel).
  • Match Type à
    Exact ou Fuzzy. En l’occurrence, il s’agit du type de correspondance (exactitude ou similarité).
  • Minimum Similarity à le minimum de similarité pour les correspondances pour la colonne ConvertedNom.
  • Similarity Output Alias à nom en sortie de la colonne qui contiendra le score de similarité (ici, _Similarity_ConvertedNom).
  • Numerals à indique la façon dont les correspondances gèrent les numéros au sein des données.
  • Comparison Flags à indique la façon dont les chaînes de caractères seront gérées (symboles, sensibilité à la casse,…). Voici un petit aperçu :


Dans l’onglet Advanced, on choisit principalement le seuil de similarité (0,70, dans notre cas) :


Les valeurs par défaut des délimiteurs de jetons s’auto-suffisent dans la majorité des cas.

Pour finir, valider la configuration du composant, puis ajoutez un composant Multicast (ou autre) de sorte que le flux partant de Fuzzy Grouping Transformation puisse lancer le Data Viewer (que l’on activera via clic-droit sur ledit flux) :


Le lancement du data flow donnera le résultat suivant :


La colonne suffixée d’un « _clean »  est générée par la transformation floue, en fonction du nom. Il s’agit de remplacer les noms similaires issus de SQL Server par ceux issus d’Excel (voir étape relative à la configuration du Column Group Output Alias dans l’onglet Column, plus haut).

Avec le composant Fuzzy Lookup Transformation

Configurez, au sein de l’onglet Reference Table du composant Fuzzy Lookup Transformation, la table SQL Server cible et l’option d’index de recherche :


Dans Columns, faites la jointure nécessaire. On optera pour une jointure entre noms :


Dans Advanced, spécifiez notamment le nombre de correspondances acceptées par clé de recherche (20, dans notre cas), ainsi que le seuil de similarité (0,70) :


Validez le tout, placez un composant MultiCast (ou autre) suivi d’un flux allant de Fuzzy Lookup vers ledit composant, avec activation du Data Viewer :


Le lancement du data flow donnera le résultat suivant :


Les valeurs NULL correspondent aux champs dont le nom issu d’Excel n’existe pas dans la table SQL Server.

Pour aller plus loin…

Gardez un œil ici pour de plus amples transformations (sic). Vous pouvez également jeter un œil ici et .

Publicités

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