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