Exemple d’utilisation d’une procédure stockée SSAS : Création d’une action pour ajouter un named set au cube depuis Excel (1/2 : La procédure stockée)

Objectif

L’objectif est ici de définir depuis un outil client (au hasard Excel) des named sets et de les publier dans le cube pour les rendre utilisables par tous les clients du cube.
Pour la définition du Named set, nous nous appuierons sur le contexte des cellules dans le tableau croisé dynamique défini par l’utilisateur d’Excel.
En d’autres termes, lorsque je sélectionne des membres de dimension en ligne et en colonne et que je dépose une mesure, je souhaite pouvoir figer dans un Named set réutilisable ce contexte (i.e ses coordonnées dans l’hypercube)

Contexte à figer dans un named set

Contexte à figer dans un named set

Le cas d’utilisation pris comme exemple est le besoin de filtrer une population de clients en fonction de plusieurs critères et de marquer cette population pour l’analyser ultérieurement. Il s’agit de conception de cohorte client qui a pour objectif de filtrer la dimension client.

L’environnement de cet article est le cube AdventureWorks téléchargeable ici : http://msftdbprodsamples.codeplex.com/downloads/get/258486
La solution présentée ici propose :
– De créer une procédure stockées SSAS permettant d’ajouter un named set au cube
– De définir dans le cube une action qui rendra possible l’appel de la procédure stockée depuis l’outil client (Excel entre autre)

Mise en oeuvre

Création d’une procédure stockée capable d’ajouter un named set au cube

Un named set est un bout de MDX inscrit dans le script MDX de calcul du cube. Il nous faut donc injecter dans le script MDX du cube le code associé à ce named set (qui sera plus tard défini par croisement dans Excel).
Avant de voir comment récupérer la définition du named set dans Excel, nous allons mettre en place une solution pour insérer du MDX dans le cube.
Marco Russo propose un projet MDXScriptUpdater écrit en .Net permettant de se connecter à un cube pour modifier le MDX de calcul. Code disponible ici (http://www.sqlbi.com/tools/mdxscriptupdater/)
Nous allons nous appuyer sur ce code pour l’embarquer dans une procédure stockée SSAS. Il existera alors sur notre serveur une procédure stockée capable de modifier le MDX d’un cube afin d’ajouter un Named Set.

  1. Utilisons le projet MDXSciptUpdater et ajoutons la référence à l’assembly Microsoft.AnalysisServices.dll dans la version du serveur sur lequel doit être inscrite la procédure stockée.
    Mise en route du projet MdxScriptUpdater

    Mise en route du projet MdxScriptUpdater

  2. Transformons le projet en un projet de type « Class library » au lieu du type « Application console » actuel dans le propriétés du projet. Une procédure stockée SSAS doit en effet être embarquée dans une dll et non dans un .exe
  3. Créons dans ce projet une nouvelle classe qui sera l’implémentation de notre procédure stockée. Le code de cette classe peut-être :
    
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using System.Threading;
    
    
    namespace SqlBi.Tools
    {
        public struct NamedSetCarac
        {
            public string CnString;
            public string DbName;
            public string CubeName;
            public string NamedSetName;
            public string NameSetDefinition;
    
        }
    
        public class NamedSetCreator
        {
            public static DataTable CreateCohorte(string namedSetName, string nameSetDefinition, string cnString, string dbName, string cubeName)
            {
                string setName = namedSetName + DateTime.Now.Ticks.ToString();
    
                Thread t = new Thread(new ParameterizedThreadStart(namedSet => ThreadProc((NamedSetCarac)namedSet)));
                NamedSetCarac pNamedSet = new NamedSetCarac()
                {
                    CnString = cnString,
                    DbName = dbName,
                    CubeName = cubeName,
                    NamedSetName = setName,
                    NameSetDefinition = nameSetDefinition
                };
    
                t.Start(pNamedSet);
    
                DataTable resTable = new DataTable("Cohorte");
                resTable.Columns.Add("Name");
    
                DataRow resRow = resTable.NewRow();
                resRow["Name"] = setName;
    
                resTable.Rows.Add(resRow);
    
                return resTable;
            }
    
            public static void ThreadProc(NamedSetCarac namedSet)
            {
                AddNamedSet(namedSet.CnString, namedSet.DbName, namedSet.CubeName, namedSet.NamedSetName, namedSet.NameSetDefinition);
            }
    
            public static void AddNamedSet(string cnString, string dbName, string cubeName, string namedSetName, string nameSetDefinition)
            {
                using (MdxScriptUpdater updater = new MdxScriptUpdater(cnString))
                {
                    string mdxQuery = string.Format("CREATE DYNAMIC SET CURRENTCUBE.[{0}] AS {1};", namedSetName, nameSetDefinition);
    
                    updater.MdxCommands.Add(mdxQuery);
                    updater.Update(dbName, cubeName, MdxScriptUpdater.UpdateType.InsertOnly);
                }
            }
        }
    }
    

    La méthode CreateCohorte est la procédure stockée à appeler, elle prend en paramètres les informations nécessaires à la création d’un Named Set et les informations nécessaires à la connexion au cube cible.
    Le MdxScriptUpdater de Marco Russo ne permettant pas tel quel de mettre à jour 2 fois de suite le script Mdx sans risque, nous générons ici un nom unique pour le Named Set.
    La procédure stockée retourne une datatable qui pourra être interprétée par l’outil client. Cette DataTable ne contient ici que le nom du Set inscrit dans le cube (puisqu’il est en partie dynamique).
    A noter que nous utilisons ici un Thread à part pour exécuter la mise à jour du script MDX. En effet, lorsque la procédure stockée sera appelée depuis une action, il s’agira de modifier un cube sur lequel une requête est en cours. Il est nécessaire de rendre la main à l’appelant pour éviter le deadlock. C’est le rôle du Thread.Start, de la méthode ThreadProc et de la structure NamedSetCarac dans le code ci-dessus.
    Le MdxScriptUpdater est lui appelé dans la méthode AddNamedSet pour concrètement modifier le Mdx du cube.
    Dans cette méthode nous créons à la fois un named set sur la base des informations transmises en paramètres en invoquant la modification du script MDX du cube.

  4. Compilons le projet (le résultat de la compilation doit être une dll dans le sous-répertoire bin/debug ou bin/release du projet MdxScriptUpdater) puis enregistrons la procédure stockée sur notre serveur Analysis Services pour test. L’assembly doit être signée pour être inscrite dans SSAS :
    Inscription de la procédure stockée sur le serveur SSAS

    Inscription de la procédure stockée sur le serveur SSAS

  5. Testons notre procédure stockée dans une nouvelle requête MDX depuis le management studio :
    CALL [MdxScriptUpdater].CreateCohorte("cohorte", "{[Customer].[Customer Geography].[Country].&[Australia], [Customer].[Customer Geography].[Country].&[Canada]}"
    					,
    					"localhost",
    					"AdventureWorksDW2012Multidimensional-EE",
    					"Adventure Works")
    

    Cet appel a pour effet, après reconnexion pour rafraîchir les métadonnées du cube de faire apparaître un nouveau set, visible dans la dimension Customer du cube Adventure works.

A cette étape, nous disposons d’une procédure stockée capable de créer un named set dans le cube à condition de lui fournir sa définition.
Le nom de la cohorte est dynamique pour éviter les doublons en cas de rejeu de la procédure stockée.
Reste à lier cette fonctionnalité à une action pour la rendre disponible dans Excel.

L’article suivant montre comment créer une action qui extrait du tableau croisé dynamique excel la définition du set : ici

SSIS intégré à une architecture .net (1) : Exploiter une assembly dans un data-flow

Cet article est le premier d’une série d’articles traitant de la manière d’exploiter les possibilités de SQL Server Integration Services (SSIS) dans une architecture applicative .Net plus globale.

Petit rappel des faits :

SQL Server Integration Services 2012 (SSIS) permet de réaliser efficacement des imports / exports de données avec transformation, dédoublonnage, nettoyage de la donnée. Ces imports de données peuvent être rapidement développés et sont aisément industrialisables par configuration (Utilisation de paramètres d’exécution, mise en place de logs, gestion des erreurs graphiquement).

Traditionnellement, pour les imports de fichiers ou les traitements de base à base, SSIS est utilisé de bout en bout :

  • Lecture depuis la source de données
  • transformations en mémoire
  • alimentation de tables cibles (destination de données)

La logique des transformations et de l’alimentation des tables cibles se trouvent implémentée dans SSIS.

Ces caractéristiques sont pleinement satisfaisantes lors d’un développement centré sur SQL Server, par exemple l’alimentation d’un DataWarehouse ou la synchronisation de 2 bases de données. Les paramètres d’exécution et les traces sont stockés directement dans une instance SQL Server (pour la version 2012 de SSIS, on peut les trouver sur d’autres supports avec les versions précédentes du produit, ce qui ne change rien à l’article), les rapports d’exécution également.

L’architecture de la solution est homogène.

Le problème :

Dans le cadre d’un développement complet d’un système d’information, il arrive que l’on soit amené à développer des fonctionnalités d’insertion de données dans la couche applicative redondants avec des packages SSIS d’import de données.

La logique de validation des données et les éventuels traitements métier sont alors codés 2 fois ce qui engendre des risques de divergence fonctionnelle d’une part et de divergence technique d’autre part (formats de trace différents, gestions des erreurs incompatibles…)

L’objectif de cet article est de proposer une solution d’import de données en utilisant SSIS pour l’aspect lecture des sources et chargement des données, et la couche métier pour l’aspect validation de règle business. L’objectif étant de tendre vers un développement réellement piloté par un domaine (DDD), même si SSIS est utilisé.

Le contexte

Mon application de démo permet de gérer un référentiel de parution de presse. Chaque jour, je reçois un fichier contenant les parutions prévues à 1 semaine qu’il faut intégrer dans mon référentiel. Des parutions peuvent également être créées manuellement depuis une application Web. Les mêmes règles de validation doivent être respectées qu’il s’agisse de l’import de fichier ou de la saisie manuelle.

La partie technique

Je dispose d’une assembly implémentant mes structures de données et les règles business conduisant à leur validation. Cette assembly constitue mon domaine et contient uniquement la classe suivante :

namespace ParutionDomain
 {
  public class Parution
  {
    public int Numero { get; set; }
    public string Libelle { get; set; }
    public string Codification { get; set; }
    public string Codebarre { get; set; }

 #region BusinessRules
    public bool IsValid()
    {
       return TestBarCodeValid();
    }
    private bool TestBarCodeValid()
    {
       string barCodeRule =string.Format("{0}{1}", Codebarre.Substring(0,5), Codification);
       return Codebarre.StartsWith(barCodeRule);
    }
 #endregion
 }
}

Seule une méthode de validation est implémentée ici, mais on peut sans difficulté imaginer qu’il ne s’agit pas de ma seule règle de validation de parutions entrantes. Cette assembly est utilisée dans mon application Web pour la validation des saisies utilisateur.

Je saurais re-créer les mêmes règles avec les composants SSIS, à base de derived column et conditional split. Pour mon exemple, dans l’objectif de ne pas recoder la logique de mes règles différemment et de centraliser leur mise en place, je choisis d’utiliser l’assembly définie ci-dessus dans un composant de script dans le data-flow qui lit le fichier.

Je vais donc créer un Package SSIS chargé de l’import quotidien de mes fichiers de parutions.

Ce fichier a pour structure :

La codification sur 4 caractères, le libellé de la parution sur 20 caractères, le numéro de parution sur 6 caractères puis le codebarre sur 14 caractères.

Ce qui nous donne par exemple ce contenu :

0051SUP. FIGARO TV VE   00000093789005100003
0745SUP. FRANCE FOOT    00000093789074500001
0041SUP.FIGARO MAGAZI.VE00000093789004100004
0044SUP.FIGARO MADAME VE00000093789004400005
0106EQUIPE              13011803780010601004

Ce package contient un data-flow chargé de la lecture du fichier à largeur de colonne fixe. Il débute par une source de type fichier plat.

Elle est connectée à un composant de script. Ce composant de script marque « invalide » les lignes ne respectant pas les règles business définies dans le domaine. Je souhaite disposer en sortie de ce composant des colonnes d’entrée et d’une colonne additionnelle pour stocker cette information.

Ajout de colonne dans le composant Script

Ajout de colonne dans le composant Script

Pour pouvoir utiliser l’assembly, celle-ci doit être référencée par le composant de script. Pour que cette référence soit permise, il faut que mon assembly soit signée et hébergée dans le GAC. J’ai signé mon assembly, compilée pour le framework 4.0 et je l’ai chargée dans le GAC.

C:> gacutil – i ParutionDomain.dll

Attention pour le framework .net 4 et 4.5, la gestion du GAC a changé comme le résume cet article :

http://rajmittal.blogspot.fr/2013/04/multiple-gac-net-framework-40.html

Attention néanmoins, si je dois modifier ma dll, il faudra penser à mettre à jour la version hébergée dans le GAC, car c’est bien cette assembly qui est chargée par SSIS à l’exécution, même si ma référence semble porter sur l’instance de l’assembly dans le système de fichier.

Je peux ainsi la référencer dans la transformation du data flow :

Ajout de référence dans le composant Script

Ajout de référence dans le composant Script

J’override ensuite la méthode Input0_ProcessInputRow, de manière à affecter la colonne Valide que j’ai créée.

Je construis donc un objet du domaine à partir des colonnes de mon flux SSIS et j’affecte à la colonne Valide la valeur retournée par la méthode IsValide du Domaine.

#region Namespaces
using System;
using ParutionDomain;
#endregion
/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
/// <param name="row">The row that is currently passing through the component</param>
   public override void Input0_ProcessInputRow(Input0Buffer row)
   {
      Parution parution = new Parution();
      try
      {
         parution = new Parution()
         {
            Codebarre = row.Codebarre,
            Codification = row.Codifitre,
            Libelle = row.Libelle,
            Numero = int.Parse(row.Numero)
         };
      }
      catch (Exception)
      {
         row.Valide = false;
      }
      row.Valide = parution.IsValid();
   }
}

J’ai ensuite dans mon data-flow un conditionnal split pour n’insérer dans la table que les objets valides.

Finalement, le dataflow contient une destination vers la table Parution de ma base de données dans laquelle ne sont intégrées que les lignes valides au regard du domaine.

Exécution du package

Exécution du package

Conclusion

En utilisant les composants script et en embarquant mes règles de validation du domaine dans une assembly, je peux faire converger mon architecture de manière à ne pas développer 2 fois dans des technologies différentes les mêmes comportements.

Néanmoins, je dis régulièrement lors des formations que j’anime de n’utiliser les composants de scripts que pour des traitements complexes et non réalisables avec les composants SSIS existants pour atteindre les meilleures performances possibles avec le produit. Le composant de script traite en effet, dans ce cas, en ligne à ligne, alors que je pourrais n’utiliser que des composants synchrones et ensemblistes pour réaliser le même travail.

Suivant, les traitements réalisés, on pourra constater ou non un écart de performance. Il s’agit simplement d’arbitrer entre performance et meilleure maintenabilité de nos développements mêlant à la fois applicatifs .Net et intégration de données.