WriteBack et sécurité dynamique de dimension pour gérer les droits sur son cube depuis Excel

La sécurité dynamique de dimension ou Dynamic Dimension Security est un sujet relativement bien couvert dans la littérature anglophone sur Analysis Services. En revanche, beaucoup de scénarii avancés ne sont pas très abordés, surtout dans la langue de Molière, tant il est vrai que nous sommes relativement peu confrontés à des demandes complexes de sécurisation poussées.

Imaginons le cas d’un cube centralisé sans Active Directory. Les utilisateurs se connectent au cube avec un compte unique, ou un nombre restreint de comptes Windows: cette demande est relativement fréquente et peut heureusement être résolue si le frontal utilisé pour se connecter au cube est capable de modifier la chaîne de connexion pour y ajouter un attribut CustomData. Cet attribut peut être interrogé en MDX via la fonction CustomData(), qui renvoie la chaîne de caractère associée.

NB: Vous pouvez aussi faire ça avec un Excel et des ODC: arrangez vous cependant pour mettre dans le CustomData de l’ODC des données incompréhensibles – GUID? – pour l’utilisateur final à qui vous donnez le fichier: celui ci pourrait sinon bypasser la sécurité trop facilement…!

Cette sécurisation doit être dans notre cas appliquée au niveau fin, sur deux dimensions Produit et Magasin, sur lesquelles nous allons vouloir donner des droits membres par membre. Pour ce faire, nous allons créer une dimension Utilisateur, et deux tables factless de sécurisation qui porteront les associations autorisées.

Le schéma est le suivant, il est classique de la sécurisation à la ligne en utilisation des tables de pont:

image

Nous avons donc trois groupes de mesures: un sur la table de faits des ventes par date, produit et magasin, et un sur chacune des factless de sécurisation.

Pour sécuriser cela, un Rôle doit être créé. Ce rôle porte classiquement dans la partie Dimension Data une expression MDX de la forme:

Exists
(
   [Store].[Store].Members
   ,StrToMember("[User].[Custom Data].&["+CustomData()+"]")
   ,"User Store"
)

Qui dit que sont autorisés les membres de la dimension pour lesquels il existe une association avec l’utilisateur connecté. Bien entendu, il faut que l’utilisateur correspondant au CustomData() de la chaîne de connexion existe.

Arrivé à ce stade, c’est terminé, le cube est sécurisé dynamiquement. Il ne reste plus qu’à cacher les objets de sécurité (tables factless, dimension User) et alimenter les tables de sécurité pour gérer les matrices de droits.

Et c’est justement sur ce dernier point que la chose peut devenir intéressante. On m’a demandé récemment si on ne pouvait pas imaginer remplir ces matrices de droits depuis Excel: et évidemment l’idée d’utiliser du WriteBack m’est venue. Mais comme il faut pouvoir voir les éléments pour écrire dedans dans la What-If Analysis de Pivot Tables, il faut créer un second cube, dédié à l’écriture et n’exposant que les tables de sécurité (User, Store, Product et les deux factless) si on ne veut pas du même coup montrer les mesures des factless à l’utilisateur final.

NB: On aurait aussi pu faire de la Cell Security mais euh… non… En fait. NON. Si jamais un jour une envie de Cell Security vous gratouille, rappellez vous que c’est antiperformant, et relisez Chris Webb🙂

Pour ne pas rendre le tout redondant on peut donc récupérer les deux groupes de mesures via des objets liés. Il est en effet permis dans Analysis Services de récupérer dans un cube les groupes de mesure et partitions d’un autre cube.

WriteBack, Linked MeasureGroups, Dynamic Dimension Security avec CustomData(): que voilà un programme alléchant! Modifions donc la base Analysis Services. Créons deux cubes:

image

Le cube de sécurité ne concerne donc que les tables d’administration, comme le montre son Dimension Usage:

image

Ses deux partitions sont rendues accessibles en écriture dans l’onglet Partitions. Attention, comme pour pouvoir affecter des droits sur des croisements qui n’existent pas encore dans Excel il faut pouvoir afficher le croisement, on crée une mesure bidon.

CREATE MEMBER CURRENTCUBE.[Measures].[Display All]
 AS 1,
VISIBLE = 1;

OK il y a mieux… Mais ça passera. Dans Excel on constate que l’écriture fonctionne:

image

Quant au second cube, il récupère ces deux groupes de mesures via l’assistant d’importation d’objets liés.

image

Maintenant il faut modifier les expressions de sécurité. En effet, Exists() n’est plus utilisable: lorsque l’on utilise le WriteBack on ne peut pas supprimer la ligne, mais juste lui affecter 0 si on veut enlever un droit. Elle “existe” donc encore au sens MDX. On est donc obligés d’utiliser un Filter() (Oui je sais ça désactive la Block Computation mais pour le coup comme l’a dit Mosha il y a bien longtemps les rôles de sécurité sont appliqués avant le script MDX, donc pas moyen de créer un membre bidon basé sur un IIF).

On a donc un truc moche dans ce genre:

Filter
(
   [Product].[Product].Members
   ,(
      StrToMember("[User].[Custom Data].&["+CustomData()+"]")
      , [Measures].[Rights On Product]
   )>0
)

Une fois ceci fait tout marche comme souhaité: la modification du cube de sécurité
dans Excel implique la mise à jour des règles dans le cube de consultation.

Alors cependant tout n’est pas complètement parfait:

  • Utiliser Filter() ne me plait pas des masses, s’il n’y a pas beaucoup d’utilisateurs c’est cependant peu grave
  • Même remarque pour la mesure Dummy qui affiche 1
  • En revanche plus gênants sont les problèmes récurrents de mise à jour du cache lorsqu’un cube implique des groupes de mesures liés activés en écriture, problème censé être résolu depuis 2008 (http://support.microsoft.com/kb/959768) mais toujours présent en 2012 au vu de mes tests.

D’où en conclusion le fait que c’est limité mais assez intéressant, surtout si vous voulez éviter le dev d’une application qui va mettre à jour la table de sécurité (que l’on aura pris soin de mettre en correspondance avec une partition ROLAP) qui est la seule option équivalente.

5 réflexions sur “WriteBack et sécurité dynamique de dimension pour gérer les droits sur son cube depuis Excel

  1. Super article, comme d’hab’.
    Je changerais bien le titre en : « Faites d’Excel L’OUTIL qui sécurise votre cube. »

    Sinon j’comprends que le dev, puisse rebuter mais la pour le coup une pauvre Winform d’administration d’une table SQL, avec un petit Entity Framework de derrière les fagots (faut faire plaisir à ton collègue Matthieu) c’est torché en moins d’une heure!

  2. Je t’ai écouté poulet pour le titre, et merci pour l’appréciation de la moulinette à sa juste valeur! En plus je suis assez d’accord avec toi. J’avais fait un truc en VSTO sur une table moisie avec partoche ROLAP dessus et effectivement c’était rapide. Après j’ai trouvé le truc marrant comme usage du WriteBack!

  3. Bonsoir, l’article est ancien donc j’espère que qq va répondre

    Nous n’utilisons pas le write back mais la sécurité avec une table de fait comme votre modèle.

    le petit soucis est que la sécurité ne fonctionne que sur l’attribut et pas à l’ensemble de la dimension lorsque l’on fait un exists sur la table de fait.
    Il faut donc sécurisé tout les attributs de la dimension store avec une expression exists

    J’ai essayé de faire une phrase de sécurité par rapport à l’attribut qui est déjà sécurisé mais pour le moment je n’y arrive pas? je n’arrive pas à comprendre pourquoi il faut obligatoirement passer par le exist?
    avez vous une explication???

    Christophe Lenoble

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s