SSAS : Modifier le Dimension Usage d’un cube en AMO (C#)

Dans le cadre d’un projet client, j’ai besoin de reconfigurer les relations entre les dimensions et les groupes de mesure de mon cube de manière dynamique.
Un utilisateur sélectionne un ensemble de dimension à « supprimer » des relations avec un groupe de mesure pour alléger le temps de process et le cube lui-même.

Voici donc une solution pour venir modifier le dimension usage en C# avant le process du cube.
L’exécution de cette routine sera assurée par un package SSIS qui fait déjà parti de la chaine d’alimentation de mon DataMart. Il s’agit ici du dernier package SSIS, celui qui sera également chargé du traitement du cube au travers d’une requête XMLA.

Référencer le namespace « Microsoft.AnalysisServices »

Les opérations disponibles en AMO sont exposées dans le Namespace « Microsoft.AnalysisServices » qui est embarqué dans l’assembly « Microsoft.AnalysisServices.dll ».
Cette assembly est mise à jour avec chaque version de SQL Serveur. Il est donc nécessaire de référencer la version de l’assembly compatible avec le serveur SSAS sur lequel les modifications sont à répercutées.
Traditionnellement, l’assembly se trouve dans le dossier : « C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies ».
Ici le 110 correspondant à la version 2012 de SQL Server.
Une fois l’assembly référencée, le code du script débute donc avec le using du namespace :

using Microsoft.AnalysisServices;

Etablir une connexion au cube

Se connecter au cube à modifier revient à :

  • Etablir une connexion au serveur

    string strStringConnection = "Provider=MSOLAP.4;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=MyDataBase;Impersonation Level=Impersonate";
    Server svr = new Server() ;
    svr.Connect(strStringConnection);
    

  • Etablir une connexion à la base

    String databaseName = "MyDataBase";
    Database db = svr.Databases.FindByName(databaseName);
    

  • Localiser le cube

    Cube cube = db.Cubes.FindByName("MyCube");
    

Exemple 1 : Supprimer la relation entre la dimension et le groupe de mesures

Les étapes suivantes indiquent comment supprimer la relation entre la dimension « Temps » et le groupes de mesures « Ventes »

  • Localiser le groupe de mesure

    MeasureGroup mgVente = cube.MeasureGroups.FindByName("Vente");
    

  • Localiser la relation entre le groupe de mesure « Ventes » et la dimension « Temps »

    RegularMeasureGroupDimension rmgVenteTemps = (RegularMeasureGroupDimension) mgVente.Dimensions["Temps"];
    

  • Supprimer la relation de la collection

    mgVente.Dimensions.Remove(rmgVenteTemps);
    

Exemple 2 : Supprimer la dimension du cube

Les étapes suivantes indiquent comment supprimer la dimension « Geography » du cube:

  • Localiser la dimension au niveau du cube

    CubeDimension dimension = cube.Dimensions.FindByName("Geography");
    

  • Supprimer la dimension de la collection

    cube.Dimensions.Remove(dimension);
    

Sauvegarde des modifications au cube

Pour imputer les modifications réalisées, l’appel à la méthode Update est nécessaire :

cube.Update(UpdateOptions.ExpandFull);


Un traitement du cube sera ensuite nécessaire.

Script complet de l’exemple 2 avec la gestion d’erreurs :

using System;
using System.Data;
using Microsoft.AnalysisServices;
public void Main()
        {
            using (Server svr = new Server())
            {
                string strStringConnection = "Provider=MSOLAP.4;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=MyDataBase;Impersonation Level=Impersonate";

                try
                {
                    svr.Connect(strStringConnection);
                }
                #region ErrorHandling
                catch (AmoException e)
                {
                    Dts.Events.FireError(-1, "Amo operation to manipulate dimension Usage", "Unable to connect the server : " + e.Message, "", -1);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    return;
                }
                #endregion

                String databaseName = "MyDataBase";
                Database db = svr.Databases.FindByName(databaseName);
                if (db == null)
                {
                    Dts.Events.FireError(-1, "Amo operation to manipulate dimension Usage", "Unable to connect the database : " + databaseName, "", -1);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    return;
                }

                string cubeName = "MyCube";
                Cube cube = db.Cubes.FindByName(cubeName);

                if (cube == null)
                {
                    Dts.Events.FireError(-1, "Amo operation to manipulate dimension Usage", "Unable to find the cube : " + cubeName, "", -1);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    return;
                }
                string dimensionName = "Geography";
                CubeDimension dimension = cube.Dimensions.FindByName(dimensionName);
                cube.Dimensions.Remove(dimension);

                cube.Update(UpdateOptions.ExpandFull);
            }
            Dts.TaskResult = (int)ScriptResults.Success;
  }
}

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

Déployer vers SSAS 2008 R2 / 2008 / 2005 une solution développée sous SSDT 2012

Depuis Sql Server Data Tools (SSDT), par défaut, les développements sont évidemment réalisés pour SSAS 2012, néanmoins, il est possible de cibler des versions antérieures de SSAS (2005, 2008 et 2008R2 à première vue).
Bien entendu, seuls des cubes en mode multi-dimensionnel sont concernés par cette compatibilité ascendante.
Pour définir cette compatibilité, il existe au niveau du projet une propriété intitulée « Deployment Server Version ».
Deployment Server Version property
Cette propriété permet de déployer depuis SSDT vers des versions antérieures de SSAS.

Cependant, même en utilisant cette configuration, le fichier .asdatabase résultat de la compilation du projet ne peut être déployé en utilisant le tooling SQL Server 2008 R2.
Si naïvement, je copie les 4 fichiers résultats de cette compilation et que je lance le deployment wizard depuis ce server (donc avec la version 2008 R2 du tool), j’obtiens l’erreur suivante :

Error loading MySSas2012Project.asdatabase: Deserialization failed: The 'AttributeHierarchyProcessingState' element in the 'http://schemas.microsoft.com/analysisservices/2011/engine/300' namespace is unexpected.

En effet, le fichier d’installation permet le déploiement vers une instance 2008 R2 mais à condition d’utiliser le tooling 2012. Il faut donc disposer du deployment wizard en version 2012 sur une machine capable d’atteindre le serveur SSAS pour procéder au déploiement.

Attention néanmoins, certains éléments introduits en version 2012 ne seront pas supprimés du XMLA que le deployment wizard cherchera à déployer et engendreront une erreur.
C’est le cas par exemple du FormatString d’attribut de dimension codé par exemple en xmla :
<ddl300_300:FormatString>dd/MM/yyyy</ddl300_300:FormatString>
Qui, exécuté au sein d’un script xmla sur un SSAS 2008 R2 produira l’erreur suivante :


Executing the query ...
The ddl300_300:FormatString element at line 897, column 41 (namespace http://schemas.microsoft.com/analysisservices/2011/engine/300/300) cannot appear under Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Dimensions/Dimension/Attributes/Attribute.
Execution complete

Dans ce cas, pas d’autre solution que d’éditer le xmla généré par le deployment wizard pour supprimer les formatstring d’attribut de dimension.