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.
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
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!