Power BI : Création une table à partir d’une non-équi-jointure (utilisation d’un produit cartésien intermédiaire)

Lors de la création d’un P.O.C. Power BI pour un client j’ai été confronté au besoin de réaliser une non-équi-jointure pour alimenter une table à partir de deux onglets Excel. La solution adoptée n’étant pas immédiate, je vous la soumets.

Cas d’utilisation :

Création d’un calendrier prévisionnel pour une liste de contrats fournis dans un onglet Excel sur la base d’un calendrier fourni dans un autre document Excel.

Fichier Contrat

Nom Date Début contrat Date Fin contrat
Adam 01/04/2015 31/01/2016
Bob 01/06/2015 29/02/2016
Joe 01/01/2016

Fichier Calendrier

Mois Nb Jours ouvrés
01/01/2016 22
01/02/2016 20
01/03/2016 23

Première étape : Créer une table dans le dataset pour le fichier calendrier

Table calendrier

Deuxième étape : Produit cartésien pour obtenir une ligne par mois et par personne

Nous importons désormais le fichier Contrats.
Dans le requête permettant l’alimentation de la table Contrats, nous ajoutons une colonne de clé fictive qui va nous permettre de réaliser le produit cartésien. Cette clé fictive aura la valeur 1 et sera présente dans les deux jeux de données à lier. Dans la table contrats :
Cle Fictive Contrat
Nous faisons de même dans la requête d’import de la table Calendrier
Cle Fictive Calendrier
Nous utilisons ici la jointure interne basée sur la clé fictive (fusion de requêtes) de manière à ce que chaque ligne de la table Calendrier joigne avec chaque ligne de la table Contrat.
Fusion
Nous développons ensuite la table jointe pour en conserver les colonnes pertinentes « Mois » et « Nb Jours ouvrés ».
Développement de la table jointe
Nous avons réalisé un produit cartésien !

Le code M de la requête :

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Contrats_Sheet = Source{[Item="Contrats",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Contrats_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Nom", type text}, {"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "CleFictive", each 1),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée",{"CleFictive"},Calendrier,{"CleFictive"},"NewColumn",JoinKind.Inner),
    #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Période", "NbJours"}, {"NewColumn.Période", "NewColumn.NbJours"})
in
    #"NewColumn développé"

Troisième étape : Filtrer le résultat de la deuxième étape pour limiter à la période comprise dans le contrat

Nous utilisons ici la fonction Table.SelectRows pour ne conserver que les lignes comprises dans l’intervalle du contrat.
La condition de jointure est : debut ≤ periode && (fin ≥ periode or fin is null)
L’utilisation de cette fonction n’est pas associée à ma connaissance à un designer, nous allons donc éditer la requête et introduire une sélection de ligne juste avant le « in » :

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Contrats_Sheet = Source{[Item="Contrats",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Contrats_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Nom", type text}, {"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "CleFictive", each 1),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée",{"CleFictive"},Calendrier,{"CleFictive"},"NewColumn",JoinKind.Inner),
    #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Période", "NbJours"}, {"NewColumn.Période", "NewColumn.NbJours"}),
    #"periodes sélectionnées" = Table.SelectRows(#"NewColumn développé", each ([Début] = [NewColumn.Période] or [Fin] = null)))
in
    #"periodes sélectionnées"


NB : Exploitant un produit cartésien intermédiaire, il faut être sûr d’en maîtriser la volumétrie avant d’appliquer cette méthode au risque d’obtenir des temps de chargement du dataset exponentiels.
Bien entendu cette méthode n’a d’intérêt que si le traitement ne peut être réalisé sur la source comme c’est le cas ici.

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;
  }
}

Variables dans un projet de Base de données Visual studio – Sql Server Data Tools et SQLCmd Variables

Les projets de bases de données permettent entre autres bonnes idées de disposer d’un suivi de version sur les structures de base de données SQL Server.
Ce type de projet a évolué depuis plusieurs versions de Visual Studio. La solution présentée ici concerne Visual Studio 2013 avec SSDT Database pour SQL Server 2012, cependant le principe était valable avec les précédentes versions de SSDT et avec la version 2014 de SQL Server.

Déclaration des variables et utilisation dans les scripts

Dans ce type de projet, il est possible de joindre à différentes occasions des scripts SQL pour

  • Définir une fonction ou procédure stockée
  • Définir un filegroup, une fonction de partitionnement ou un schéma de partitionnement
  • Insérer des données via un postbuild script

Ces projets sont implicitement destinés à maintenir plusieurs environnements (Développement, Intégration, Production). Au sein de ces scripts, nous pouvons avoir besoin de rendre dynamique une section de code (le lecteur pour un FileGroup, le préfixe pour un nom de table…)
L’aspect dynamique de ces scripts SQL peut être piloté grâce à des variables SQLCmd.
Pour utiliser de telle variable dans les scripts sql de notre projet, la syntaxe est $(NomDeVariable)

Il existe 4 variables prédéfinies pour les projets de base de données :

  • DatabaseName
  • DefaultDataPath
  • DefaultFilePrefix
  • DefaultLogPath

Code sql avec une variable système : Script d’un FileGroup dans le projet Database

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [TestFG];

Si j’ai besoin de variables supplémentaires, par exemple pour distribuer sur 2 disques les filegroups de ma base de données, la procédure est la suivante :

1. Déclaration dans les propriétés du projet

Sur l’onglet SQLCMD Variables, déclarez les variables supplémentaires, ici « DataPathEven » et « DataPathOdd »
Fenêtre de propriétés du projet

2. Utilisation depuis les scripts

Ces variables peuvent ensuite être utilisées dans un script SQL avec la syntaxe permettant d’accéder aux variables. J’ajoute ici un nouveau FileGroup dans le répertoire Storage de mon projet de base de données.

Code sql avec une variable utilisateur : Script d’un FileGroup dans le projet Database

ALTER DATABASE [$(DatabaseName)]
ADD FilE ( NAME = N'TestFG1', FILENAME = N'$(DataPathOdd)TestFG1.ndf' , SIZE = 0KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO
ALTER DATABASE [$(DatabaseName)]
ADD FilE ( NAME = N'TestFG2', FILENAME = N'$(DataPathEven)TestFG2.ndf' , SIZE = 0KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

Déploiement depuis SSDT

Depuis SSDT, la publication du projet permet d’affecter, via un designer des valeurs aux variables SQLCmd utilisateur.
L’interface ci-dessous est alors disponible.
Affectation de valeurs aux variables dans la publication depuis SSDT
Ici, les valeurs « D:\Data\Odd\ » et « D:\Data\Even » viennent surcharger les valeurs configurées à la déclaration des variables pour la publication en cours.
Cette publication peut être sauvegardée sous forme de profile de publication dans le projet de manière à être réutilisée plus tard.
Les valeurs des variables SQLCmd sont alors embarquées dans le profil de publication.
Le xml ainsi généré contient dans mon cas la section suivante

<ItemGroup>
<SqlCmdVariable Include="DataPathEven">
<Value>D:\Data\Odd\</Value>
</SqlCmdVariable>
<SqlCmdVariable Include="DataPathOdd">
<Value>D:\Data\Even\</Value>
</SqlCmdVariable>
</ItemGroup>

Déploiement du projet en dehors de SSDT

Déploiement depuis le SQL Server Management Studio

Depuis le Management Studio de SQL Server, l’assistant de mise à jour de data-tiers application ne permettra plus le déploiement. En effet, cet assistant n’est prévu ni pour utiliser un profil de publication, ni pour configurer les SQLCmd Variables du projet. Or, la configuration de ces variables étant nécessaire au déploiement, celui-ci échoue avec le message :
Erreur lors du déploiement depuis SSMS
On abandonnera donc cette solution pour se tourner vers un déploiement scripté.
Plusieurs possibilités sont envisageables pour atteindre cet objectif dont:
• Utiliser l’utilitaire en ligne de commande SQLPackage et configurer au déploiement les valeurs à affecter aux variables ou réutiliser un profil de publication
• Utiliser des tâches de déploiement MSBuild et les profils de publication.
La seconde solution fera l’objet d’un article dans quelques temps, je ne la développe pas ici.

Déploiement avec SQLPackage et affectation de valeur aux SQLCmd Variables

Pour la solution reposant sur SQLPackage, comme nous l’indique MSDN, nous pouvons affecter via la ligne de commandes des valeurs aux SQLCmd variable lors de notre déploiement.
Sur mon installation, l’exécutable SQLPackage peut-être trouvé à l’emplacement (à adapter suivant votre version de SQL Server) :
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120
Dans mon cas, le déploiement pourrait être réalisé en utilisant une commande de la forme suivante (exécutée dans le répertoire bin\debug de mon projet de base de données) :

SQLPackage /Action:Publish /SourceFile:DbProject.dacpac /TargetServerName:MyHost / TargetDatabaseName:DbProject

Sous cette forme, le déploiement échoue m’indiquant que les valeurs des SQLCmd variables ne sont pas fournies. Le paramètre « Variables » nous permettra d’affecter des valeurs à nos variables.

SQLPackage /Action:Publish /SourceFile:DbProject.dacpac /TargetServerName:MyHost / TargetDatabaseName:DbProject /Variables:DataPathOdd=D:\Data\Odd /Variables:DataPathEven=D:\Data\Even

Déploiement avec SQLPackage et utilisation d’un profil de publication

Nous pouvons également réutiliser un profil de publication en indiquant son chemin avec la syntaxe :

SQLPackage /Action:Publish /SourceFile:DbProject.dacpac /TargetServerName:MyHost / TargetDatabaseName:DbProject /Profile:{ProjectPath}DbProject.publish.xml

Rétablir Online un fichier d’un FileGroup OffLine (par erreur) – Utilisation d’une connexion DAC

Petit mémo de survie si un jour un des fichiers composant un FileGroup de votre base de données la plus critique se retrouve Offline par erreur.

CECI N’EST PAS UN BEST PRACTICE MAIS PLUTOT UN SURVIVAL TRICK

Petit rappel important, théoriquement un fichier est passé manuellement au statut OffLine parce qu’il est inutilisable. Le fichier est alors écarté du FileGroup de manière à permettre de restaurer la base de données Online privée du fichier fautif. La seule opération possible sur le fichier est alors la suppression.
Mettons-nous dans le cas en déclarant explicitement Offline le fichier TestFG d’une base de données Demo, ce qu’il est possible de faire avec un simple ALTER TABLE :

ALTER DATABASE Demo
MODIFY File (NAME= TestFG, OFFLINE)


Evidemment, il n’existe pas d’opération me permettant de faire marche arrière, « ONLINE » n’étant pas un argument valide pour l’opération MODIFY File. Mon Filegroup est désormais considéré comme Offline et accessoirement ma base comme suspecte. L’opération attendue ici est en fait la restauration d’un backup de ce fichier pour permettre son retour Online.
Maintenant, si l’état de ce fichier est erroné (fausse manipulation par exemple) et que vous n’avez pas de backup voici une solution qui permet de tomber en marche.
4 Etapes pour vous sortir de l’embarras :

Etape 1 : Passer l’instance en single user
Arrêter l’instance SQL Server hébergeant la base de données
Redémarrer l’instance avec la commande suivant (exécutée dans le sous-répertoire BInn de l’installation de SQL Server) :

SQLServer –s InstanceName -m

Etape 2 : Etablir une connexion D.A.C. (Dedicated Administrator Connection)
Dans SSMS (ou via SQLCMD), créer une nouvelle requête en établissant une connexion au serveur dont vous précéder le nom par « ADMIN: »
Connexion DAC - localhost

Etape 3 : Modifier le statut du fichier dans les tables système
Les tables systèmes sont désormais accessibles.
Pour connaitre les tables contenant les informations concrètes liées à nos FileGroup, une astuce est d’activer le plan d’exécution et d’interroger les vues catalog.
En l’occurrence, pour les fichiers participant aux FileGroup, il s’agit de la table sys.sysbrickFiles

select * from sys.sysbrickfiles

update sys.sysbrickfiles
set internalstatus = 0
where lname = 'TestFG'

La première requête permet de vérifier le nom et l’état logique du fichier, la seconde de modifier son état pour rétablir un état ONLINE.

Etape 4 : Vérifier l’état de la base de données
Relancer l’instance en mode classique via le SQL Server Configuration Manager et contrôler le retour du FileGroup perdu.
Un DBCC CheckDb peut être nécessaire pour repasser la base en mode normal.

SSIS : Point sur le mode Chargement rapide des destinations OLE DB (Ole Db Destination – Fast Load)

Petit rappel sur le fast load

La destination OLEDB des data flow task présente l’intérêt d’insérer par block de lignes en base de données plutôt que ligne à ligne.

Techniquement, les lignes sont donc insérées par lot, ce qui permet des performances nettement meilleures et une bonne utilisation des ressources physiques du serveur cible. Petit effet de bord, les commit étant réalisés sur des lots, lorsqu’une erreur est rencontrée tout le lot de ligne contenant celle en erreur est rejeté.

Il est donc nécessaire de comprendre comment ces lots sont délimités.

La propriété MaximumInsertCommitSize

Cette propriété indique la taille (en nombre de lignes) des lots appartenant à la même transaction lors des insertions réalisées par la destination. Conséquence directe, au moment du commit, si une des lignes du lot enfreint une contrainte, c’est tout le lot qui est rejeté.

Destination Ole Db

Il est notamment utile d’adapter cette propriété pour la gestion des erreurs.

La suite de l’article utilise un exemple basé sur adventureWorks2012.

Ma source est une source OLE Db extrayant toutes les lignes de la table Production.Product (504 lignes)

Ma destination est un nouvelle table nommée dbo.ProductBuffer qui reprend les mêmes colonnes que la table Prodution.Product et sur laquelle j’ai déclaré une clé primaire sur la colonne ProductId.

Avant de jouer mon test, j’insère dans la table dbo.ProductBuffer une ligne de ProductId : 1 pour provoquer une erreur lors de l’insertion d’un et d’un seul produit.

Voici le résultat :Flux de données avec 1 buffer en erreur

Je constate bien ici que le lot de 50 lignes contenant la seule ligne en erreur est intégralement rejeté.

Est-ce le seul paramètre qui influence la taille des transactions ?

Propriétés RowsPerBatch

Une destination OleDb configurée en Fast Load traduit cette opération par des instructions SQL Insert Bulk, ce que nous pouvons constater en posant un SQL Server Profiler par exemple lors de l’exécution de notre data flow.

La propriété RowsPerBatch de la destination OleDb permet d’associer une valeur au paramètre RowsPerBatch de l’instruction Bulk transmise au serveur destination.

Msdn (http://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx) nous informe que ce paramètre permet au moteur SQL Server d’allouer au mieux les ressources physiques pour le volume de données à insérer. Il s’agit d’une heuristique d’allocation de ressource.

Cette valeur devrait donc être affectée au nombre de lignes qui seront réellement transmises par l’instruction bulk.

Pour la configurer finement, il faut donc connaitre effectivement le nombre d’instructions bulk requises par notre data flow.

Cette propriété n’est cependant pas censée modifier la taille des transactions.

Nombre de requêtes jouées (Taille des buffers , DefaultBufferMaxRows, DefaultBufferMaxSize)

Pour améliorer la gestion des I/O, nous pouvons être tentés d’augmenter ou diminuer suivant les cas la taille des envois vers la base. La question est alors de savoir combien de requêtes sont jouées par l’OLE DB Destination pour le flux.

Pour jouer sur la taille des lots transmis à la base (et donc l’utilisation des ressources I/O du serveur destination), il va falloir s’intéresser à la gestion des buffers dans les data flow tasks.

Il s’agit alors de comprendre la détermination de la taille des lots en fonction des propriétés MaximumInsertCommitSize de la destination et DefaultBufferMaxRows  (ou DefaultBufferMaxSize) du data flow lui-même.

Pour vérifier l’usage de ces propriétés, je reproduis mon test précédent en jouant sur les 2 propriétés. J’ai également inclus dans ce test la propriété RowsPerBatch de la destination pour valider qu’elle n’influence pas la taille des transactions.

Pour constater ce nombre de requête, j’ai utilisé un SQL Server profiler connecté à ma destination et intercepté les évènements SQL :BatchCompleted. Les commandes qui m’intéressent ont pour TextData quelque chose de la forme « Insert bulk… »

Propriété RowsPerBatch de la destination Propriété MaximumInsertCommitSize de la destination Propriété  DefaultBufferMaxRows du Data flow Nb requêtes (i.e. nb lot de ligne) Nb lignes en erreur (taille des transactions rejetées)
5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 1 504
100 5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 1 504
100 200 5000 (> nb lignes dans la source) 3 200
100 50 5000 (> nb lignes dans la source) 11 50
100 200 350 3 200
5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 100 4 132
100 200 100 4 132
100 50 100 11 50

A noter qu’en ne jouant que sur la taille du buffer, je ne parviens pas à obtenir une transaction de moins de 132 lignes. Ceci est dû à une propriété non configurable dans SSIS : MinBufferSize dont la valeur est de 64Kb qui dicte la taille minimum pour les buffers et est prioritaire sur la propriété DefaultMaxBufferSize.

Conclusion

Le nombre de lignes présentes dans une transaction pour une destination OleDb (et donc le nombre de requêtes jouées sur la base) est déterminé en premier lieu par la taille des buffers utilisés (Taille définie par DefaultBufferMaxRows (ou DefautlBufferMaxSize)). Par défaut, il y aura 1 transaction réalisé par buffer. Si la taille du buffer est supérieure à la propriété MaximumInsertCommitSize de la destination OLE Db c’est alors cette propriété qui détermine la taille de la transaction.

Pour du tuning de performance exploitant la taille de buffer, n’hésitez pas à consulter ce très bon article sur lequel je suis tombé en préparant le mien : http://www.mssqltips.com/sqlservertip/3217/improve-ssis-data-flow-buffer-performance/

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

Objectif

Cet article fait suite à celui-ci. L’objectif de cette série d’article est de présenter une solution reposant sur une procédure stockée SSAS pour inscrire depuis Excel des Named sets dans un cube. Pour plus d’information, consulter le premier article de la série.

Mise en oeuvre

Création d’une action de type rowset

Nous allons donc créer une action permettant de modifier le MDX du cube.
Pour rappel, les actions sont de petits scripts associés à des parties du cube qui peuvent être exécutés notamment à la demande depuis les outils clients.
Les actions sont typées suivant la nature du travail à réaliser. Les outils clients ne sont pas tous capable d’initier tous les types d’action disponible sur SSAS.
Dans notre cas, nous souhaitons associer une action qui modifiera le script du cube. Il n’existe pas à proprement parlé d’action de type « exécution de script MDX », cependant, lest actions de type Rowset permettent d’exécuter une requête MDX.
Nous allons donc définir une action de type rowset avec caractéristiques suivantes :

  • Name : Add Named Set
  • Target type : cells
  • Target Object : All cells
  • Condition : [Measures].CurrentMember is [Measures].[Customer Count]
  • Type : Rowset

Nous indiquons ici que l’action pourra être déclenchée depuis les éléments « cellule ». Nous avons également ajouté une condition pour faire en sorte que l’action ne soit disponible que sur la mesure Customer count.
Reste à établir le code de l’action. Il doit faire appel à la procédure stockée en lui indiquant la définition du set correspondant à la cellule depuis laquelle l’utilisateur a invoqué l’action.
Pour connaitre les membres retenus en ligne et en colonne sur le client, nous allons nous appuyer sur l’assembly ASSP téléchargeable à cet emplacement : http://asstoredprocedures.codeplex.com/wikipage?title=Installation%20Instructions
Cette assembly nous permet, entre autre, de disposer d’une méthode FindCurrentTuple qui retourne le tuple d’une cellule (et donc, l’essentiel de la définition du set qui nous intéresse).
Pour rappel, les sets de mon cas d’utilisation, sont des sets de clients filtrés par leur existence sur un groupe de mesure. J’utiliserai donc comme définition du Set le résultat de l’expression suivante :

EXISTS([Customer].[Customer].[Customer].members, 
                    {'+ASSP.FindCurrentTuple()+'}, 
                    "Internet Customer"
                    )

Cette définition peut-être simplifiée pour d’autre cas d’utilisation.
L’expression correspondant à l’action sera au global :

'CALL [MdxScriptUpdater].CreateCohorte("Cohorte", 
                    "EXISTS([Customer].[Customer].[Customer].members, 
                    {'+ASSP.FindCurrentTuple()+'}, 
                    ""Internet Customers""
                    )",
			"localhost",
			"'+[Measures].DefaultMember.Properties("CATALOG_NAME")+'",
			"'+[Measures].DefaultMember.Properties("CUBE_NAME")+'")'

A noter ici l’utilisation des propriétés Catalog_name et Cube_name pour éviter de spécifier en dur le nom du cube et de la base. Ce qui permet de dupliquer l’action plus aisément.

Pour que cet expression fonctionne il aura fallu inscrire l’assembly ASSP sur le serveur SSAS cible de la même manière que nous avions inscrit l’assembly contenant notre procédure stockée.

Attention à ne pas surestimer les capacité de la méthode FinCurrentTuple. Dès lors qu’excel passera par un sous-cube pour traduire le tableau croisé dynamique en MDX, la méthode ne connaîtra plus la totalité du contexte. Pour l’exercice, nous allons donc nous contenter de déposer des membres en ligne et en colonne, ou bien de faire des sélection simple dans la zone filtre. En effet, les sélections multiple en filtre sont traduites en sous-cube et la définition de notre set sera erroné.

A ce stade, nous disposons d’une action présente uniquement sur la mesure Nombre de clients permettant d’ajouter à la dimension client, un set dont la définition est régi par le croisement donnant lieu à l’agrégat sur lequel nous avons cliqué :

Action "add a cohorte "utilisée dans Excel

Action « add a cohorte « utilisée dans Excel

Le résultat ouvre un nouvel onglet pour nous donner le nom de la cohorte (du set) créé. En rafraîchissant la connexion au cube, nous pouvons utiliser ce set :

Resultat de l'action

Resultat de l’action

Le résultat n’est pas encore parfait. Etant donné la méthode utilisée pour construire ces Set, nous ne pouvons pas procéder de manière itérative (il n’est pas correct d’utiliser un set ainsi généré comme filtre pour un définir un autre set plus restreint).

Cette conception itérative de nos sets fera l’objet d’un prochain article.

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