[SSIS] Lookup paramétré en mode Full Cache

La non équi-jointure est-elle possible en mode ETL pure avec SSIS ?

Nombreux sont les cas d'utilisation où la jointure peut être un peu plus complexe qu'une égalité entre les colonnes de 2 tables. Dans le cas simple et répandu de la récupération d'un ID d'une dimension SCD2 dans le chargement d'une table de fait, il est nécessaire de recourir à une non équi-jointure. Et pourtant cette simple tâche parait complexe à faire de manière performante à l'aide de SSIS.

Rappel: La non équi-jointure consiste en l'utilisation d'opérateur de jointure différent de l'égalité comme par exemple : <, <=, >, >=, LIKE, BETWEEN ... AND ..., etc ...

Comment met-on en place le lookup paramétré dans SSIS

Prenons un exemple simple de la récupération d'un seul ID sur une dimension que l'on appellera Produit.

Prenons 2 tables simples pour illutrer le lookup paramétré

Créons un dataflow simple avec un lookup pour récupérer l'ID d'une dimension

Commençons par choisir le mode nocache pour l'exemple

On écrit une requête avec dans la sélection uniquement les colonnes dont on a besoin, l'ID et les colonnes impliqués dans la paramétrisation. Le but est d'avoir des metadata identique avec la requête custom

On map les colonnes sources qui seront les paramètres de la requête custom

On utilise le ? pour signifier un paramètre

Et on map les paramètres dans l'ordre d'apparition des ?

ATTENTION: Vous l'avez sans doute remarqué. Les performances de cette méthode ne sont pas satisfaisante. Mais pourquoi me direz-vous ? C'est ce que l'on va tenter d'éclaircir.

Je vous propose d'allumer notre SQL Server Profiler et lancer une trace standard pour capturer les classes d'évenement RPC:Completed, SQL:BatchStarting et SQL:BatchCompleted

Voilà le résultat de la trace

L'intégralité des traces seraient trop long et indigeste. Je vais en choisir quelques extraits pour les commenter.

• La trace commence par une phase préparatoire

  exec sp_prepare @p1 output,NULL,N'SELECT [IdProduit]
        ,[CodeProduit]
        ,[Valid_From]
  FROM [dbo].[DimProduit] dp',1
  select @p1
  go
  exec [sys].sp_describe_first_result_set N'SELECT [IdProduit]
        ,[CodeProduit]
        ,[Valid_From]
  FROM [dbo].[DimProduit] dp',NULL,1
  go
  exec sp_unprepare 3
  go
  exec [sys].sp_describe_undeclared_parameters N'SELECT [IdProduit]
        ,[CodeProduit]
        ,[Valid_From]
  FROM [dbo].[DimProduit] dp
  WHERE
   @P1 >=  dp.[Valid_From]
  and @P2 <= ISNULL(dp.[Valid_To],''9999-12-31'')
  and   dp.[CodeProduit]=@P3'
  

• Puis elle termine par un appel de procédure par ligne

  exec sp_executesql N'SELECT [IdProduit]
        ,[CodeProduit]
        ,[Valid_From]
  FROM [dbo].[DimProduit] dp
  WHERE
   @P1 >=  dp.[Valid_From]
  and @P2 <= ISNULL(dp.[Valid_To],''9999-12-31'')
  and   dp.[CodeProduit]=@P3',N'@P1 date,@P2 date,@P3 char(10)','2013-05-02','2013-05-02','FR00000001'
  go
  

Un appel par ligne ? Un traitement séquentiel sur un moteur relationnel ? On est loin de l'état de l'art de l'ETL et même de l'état de l'art du relationnel. Vous me direz : Oui mais ... là nous avons paramétré le lookup en mode NoCache.

Il existe effectivement 2 autres modes :
• le mode Partial Cache
• et le mode Full Cache

Ce que va changer le mode Partial Cache par rapport au mode NoCache, c'est qu'en début d'exécution le cache sera vide et au fur et à mesure des lectures, les lignes seront ajoutés au cache, ce qui permettra aux nouvelles lignes identiques d'être servies par le cache. Ce mode est très légerement plus performant que le mode NoCache, mais reste très peu performant et loin de la philosophie ETL.

Le mode FullCache permet de monter en cache l'intégralité des données et de traiter les jointures en mémoire de manière performante. Cependant, SSIS ne permet faire des non équi-jointure dans le cache.

A savoir: L'interface empêche de paramétrer des requêtes custom en mode FullCache aussi bien par l'interface que par les propriétés du composant ou que par les expressions du dataflow.

Propriété en PartialCache

Propriété en FullCache

On constate que les propriétés de paramétrisation ont disparu.

Mais alors quelle solution choisir dans le cas d'une non équi-jointure ?

En SSIS pure il reste une méthode à base de Merge Join. Mais dont l'inconvénient serait la nécessité de trier les datasets amont ce qui serait parfaitement contre-performant et vraiment non recommandable à partir du moment ou il y a plus d'une jointure de ce type à faire dans un même dataflow.

On pourrait imaginer essayer de bluffer SSIS par une expression pour lui passer dans sa propriété SqlCommand une variable contenant une requête dynamique. Mais cette feinte ne suffirait pas. Il faudrait aller plus loin à coup de requêtes paramétrés, de jointure avec une date et de boucle, pour essayer de se retrouver en situation d'équi-jointure. Une machine à gaz indigeste, contre-performante et de surcroît inmaintenable.

Force est de constater que d'utiliser SSIS en tant qu'ETL (plutôt qu'en ELT ou en séquenceur de procédures stockées) pour traiter ce genre de problématique n'est pas une solution sérieusement envisageable si l'on a beaucoup de volumétrie. Dans ce cas, il faut de manière délibérer utiliser le moteur SQL, qui est de toute façon utilisé contre nature par SSIS. Donc tant qu'à utiliser le moteur sans le savoir et de manière non maitrisé autant le faire à fond avec toute la puissance du moteur (requêtes ensemblistes, utilisation d'indexes et de statistiques) afin d'obtenir des résultats performants.

A bientôt!

[SSIS] Legacy mode vs modèle de déploiement de projet.

Vous ne le savez peut-être pas, mais la dernière version de SSIS a connu de nombreuses modifications. L’une d’entre elle est l’unité de déploiement. Cette modification va chambouler nos habitudes de développeurs en amenant la notion de modèle de déploiement projet.

Pour les nouveaux projets démarrant sur 2012, le choix du type de projet se pose dès la phase de conception (voir même avant). Et pour ceux qui aimeraient migrer leur projets SSIS en 2012, la question de migration dans le nouveau mode se pose, bien qu’elle n’ait rien d’obligatoire.

Ce mode de déploiement est le mode par défaut. Cependant, à tout moment, il est possible de convertir son projet SSIS de mode projet vers mode package et réciproquement. Bien que la conversion ne sera sans doute pas sans douleur en fonction des spécifités d’organisation et devariabilisation inhérente à votre projet.

Le projet SSIS à la mode 2005 qui aura tenu 3 versions a été relégué au rang de mode Legacy. Il n’est cependant pas supprimé (bonne nouvelle). Les 2 types de projets existent et peuvent coexister. Cependant avec un nom comme Legacy, sa pérennité n’est vraisemblablement pas assurée.

Voici un petit listing des + et des – du nouveau mode. Cela n’engage que moi. N’hésitez pas partager votre retour d’expérience sur vos implémentations du nouveau mode en commentant mon article.

Avantages du nouveau mode :
1. Le logging est natif et un jeu de rapports standards est fourni.
2. Les déploiements sont versionnés il est possible de revenir à une version précédente sans effort en cas d’anomalie.
3. La paramétrisation des propriétés offre plus de stabilité que la version précédente, dans laquelle les propriétés étaient identifiées par leur nom.

Points faibles et manquement :
1. L’impossibilité de partager des paramètres au-delà du projet empêche de découper le projet ETL en unité plus fine. Avec une paramétrisation projet, il faut définir et maintenir tous les paramètres partagés entre tous les différents projets de l’instance.
2. Le travail en équipe est rendu moins facile. Il devient difficile de faire travailler des développeurs sur des sujets séparés au sein de la même solution. Ou sinon de bien faire attention à ne jamais récupérer du gestionnaire de code source (Source Safe, TFS, etc …) une autre version de package que la dernière valide afin de pouvoir builder son projet et ne pas causer de régression. Et si jamais un développeur doit faire des modifications au niveau du projet (paramètre, ajout d’un package, etc ….), cela peut s’avérer compliqué.
3. L’impossibilité d’appeler un même package avec plusieurs environnements d’exécution. On peut comprendre qu’il ne soit possible de n’exécuter qu’un seul environnement par exécution du fait de son nom. Mais dans les versions précédente il était possible d’affecter plusieurs configurations pour traiter le spécifique et réutiliser des configurations communes. Ceci aurait pu donner plus de flexibilité pour le développeur.
4. L’impossibilité de classer les environnements dans des répertoires. Dans des projets de grande ampleur, cela peut devenir vite indigeste.

Le nouveau mode offre de nouvelles possibilités, mais impose aussi beaucoup de contraintes. A mon sens, il a plutôt vocation à faciliter les échanges entre une équipe de développement et l’exploitant (ou la personne qui réceptionne le livrable). Néanmoins, dans des projets d’envergures, pour des projets d’alimentation sur des domaines fonctionnelles indépendant mais mutualisable, ou pour la maintenance évolutive, ce mode dans son état actuelle est moins adapté que le mode Legacy.

Pour les nouveaux projets la question est légitime : partir en mode projet ou legacy. Pour les projets de migration vers 2012, pour ceux qui ont investi beaucoup de temps et d’argent dans une structuration de leur projet, je leur conseillerais de migrer leur solution ISO sans passer au mode projet. Rien ne presse. Microsoft dévoilera bien assez tôt ses intentions sur le mode Legacy. Et là ce sera le moment d’agir.

[SSIS] Créer un fichier XML à partir d’une table SQL

A premier abord, cela parait anodin pour un ETL et pourtant nativement il n’existe pas de composant pour envoyer un flux de donnée vers un fichier XML. Confronté à cette problématique chez un client, j’ai dû creuser pour trouver des solutions de contournement à cette carence de SSIS.

Bien heureusement SSIS a l’avantage d’offrir la possibilité de recourir à .NET via des scripts La solution que j’ai trouvée, il y en a d’autre, notamment celle de François Jehl que vous pourrez trouver ici est d’utiliser l’objet DataSet ADO.NET.

L’utilisation de XML dans un DataSet est très bien documentée ici.

L’avantage de cette méthode est de pouvoir générer facilement du XML aussi bien introduire des types complexes, que de définir des colonnes comme attribut d’autres éléments.

Pour cette méthode, il suffit d'implémenter le code suivant dans un script task dans le control flow. Tout le travail (connexion à la base, lecture des tables, transformation XML et écriture dans le fichier) se feront grâce au script ci-dessous.

Voici le script :

//Ne pas oublier la reference.
using System.Data.SqlClient;


public void Main()
{
SqlConnection sqlConn;
// définition des 2 tables liées par un ID
string CurrencyQuery = "SELECT [CurrencyCode],[Name] FROM [Sales].[Currency]";
string CurrencyRateQuery = "SELECT [FromCurrencyCode],[ToCurrencyCode],[AverageRate],[EndOfDayRate] FROM [Sales].[CurrencyRate]";

try
{
    // Connexion à la base AdventureWorks (qui doit être une connexion ADO.NET)
    sqlConn = (SqlConnection)(Dts.Connections["AdventureWorks"]).AcquireConnection(Dts.Transaction);
    sqlConn = new SqlConnection(sqlConn.ConnectionString);
    sqlConn.Open();


    SqlCommand cmdCurrency = new SqlCommand(CurrencyQuery, sqlConn);
    SqlCommand cmdCurrencyRate = new SqlCommand(CurrencyRateQuery, sqlConn);
   
    //permet de remplir les dataset - c'est un lien entre la source et le dataset
    SqlDataAdapter CurrencyAdapter = new SqlDataAdapter(cmdCurrency);
    SqlDataAdapter CurrencyRateAdapter = new SqlDataAdapter(cmdCurrencyRate);

    // création de notre DataSet dont le nom sera la racine de notre fichier XML
    DataSet ds = new DataSet("CurrencyRate");

    //mise en mémoire des 2 tables
    CurrencyAdapter.Fill(ds, "Currency");
    CurrencyRateAdapter.Fill(ds, "CurrencyRate");

    // cette commande attribut est très intéressant il peut prendre plusieurs valeurs dont celle d'attribut.
    ds.Tables["Currency"].Columns["name"].ColumnMapping = MappingType.Attribute;

    // c'est ici que l'on va définir notre type complexe XML en établissant une relation entre les 2 datasets.
    DataRelation CurrencyRateDR = ds.Relations.Add("CurrencyRateRelation", ds.Tables["Currency"].Columns["CurrencyCode"], ds.Tables["CurrencyRate"].Columns["FromCurrencyCode"]);
    // propriété très importante puisqu'elle permet d'imbriquer les éléments enfants dans les éléments parents. Ils sont considéré par défaut comme éléments frère
    CurrencyRateDR.Nested = true;

    ds.WriteXml(new System.IO.StreamWriter("D:\\TEST\\test.xml"));
    sqlConn.Close();
}
catch
{
    Dts.TaskResult = (int)ScriptResults.Failure;
    throw;
}

Dts.TaskResult = (int)ScriptResults.Success;
}

Bien entendu, ici la source est une table XML, mais il est possible d’attaquer d’autres sources directement comme Excel par exemple. J’espère que cela pourra vous aider dans vos projets. A bientôt.