Discussion :

[cana13]

Bonjour,

J'ai un cube qui doit être utilisé par plusieurs utilisateurs.

Mon cube comporte une dimension AGENCE (plusieurs centaines d'agences dans toute la France) et je souhaite limiter les informations du cube en fonction de l'utilisateur connecté : Un utilisateur ne doit pouvoir consulter que les informations de son agence.

Le nombre d'agences est trop important et les utilisateurs peuvent bouger, je ne peux donc pas m'amuser à créer un rôle par agence ni mettre de valeur en dur dans ma requête MDX.

J'imagine faire un rôle avec une requête MDX qui ferait :

1/ En récupérant le compte de la personne connecté (domaine/nom) par la fonction USERNAME() il faudrait que je détermine son périmètre (son agence). Pour cela, faut il que je crée dans mon cube une dimension Hiérarchique AGENCE / UTILISATEUR ? (à noter que j'ai cette information dans ma base de données d'origine SqlServer)

2/ Ensuite il faudrait filtrer la dimension AGENCE sur l'agence ainsi récupérée.

Est il possible de construire une requête MDX dynamique pour réaliser cette opération ?


J'ai lu un ancien post sur le forum concernant cette problématique :

http://www.developpez.net/forums/d68...ces-dimension/

mais cela me semble bien compliqué et j'ai du mal à le transposer à mon cas personnel. (Je précise aussi que mes connaissances en MDX sont très limitées voir quasi nulles !).

Merci d'avance de votre aide.

[LapinClair]

Lien vers un post écrit par F.JEHL qui traite du sujet.

http://fjehl.blogspot.com/2009/07/ss...rity-avec.html

[Jinroh77]

Bonjour,
Pour avoir déjà eu affaire au problème, voici la solution que nous avions utilisée.

1. Avoir en base SQL les autorisations Agence / utilisateur
2. Utiliser un cache distribué (Velocity par exemple) pour stocker cette information (accès plus rapide).
3. Créer une assembly qui prend en paramètre le UserName(), fait appel au cache distribué, crée le set MDX correspondant.
4. On ajoute l'assembly à l'instance SSAS, on fait appel à cette assembly via la gestion des rôles.

C'est un peu fastidieux, mais ça à le mérite d'être très efficace.
SSAS gère ensuite un cache avec ces informations, pas besoin de faire appel à l'assembly en permanence, et lorsque l'on met à jour la table SQL, on fait en sorte de mettre également à jour Velocity et avec un petit script XMLA on vide le cache des rôles de SSAS

[cana13]

Merci à vous deux de vos réponses rapides.

La solution énoncée par François Jehl semble correspondre à ce que j'imaginais.

La solution de Jinroh77 me semble plus délicate car elle fait appel à des notions que je ne maitrise pas (pour l'instant !) : cache distribué, assembly, l'ajouter et faire appel à celle ci, 'set' MDX ...
C'est surement très efficace mais j'aurai besoin de plus d'explication pour la mettre en œuvre.

Je vais donc creuser pour le moment du coté de la première solution et vous tiendrai au courant.

Merci et bonne soirée

[FrancoisJehl]

En fait l'arbitrage est assez facile je pense: si tu as une notion de collaborateur dans le cube (un axe au niveau collaborateur) le vieux post que j'avais fait marche bien. Dans le cas ou tu n'as pas ce niveau de détail il semble un peu maladroit de rajouter juste pour gérer de la sécurité (sauf s'ils sont peu nombreux), et perso je passerais par une solution comme celle de Jinroh (Assembly .NET qui renvoie les règles de sécu, donc les appartenances aux agences dans ton cas).

[Jinroh77]

Le cache Velocity que nous utilisions n'est pas vraiment obligatoire. On était réparti entre plusieurs serveur plus ou moins distant donc l'accès à la BDD n'était pas évident pour l'assembly.
En plus SSAS va faire du cache sur les autorisations des rôles, si 'accès à la BDD (contenant les règles d'autorisations) n'est pas trop lente la première fois, ça passera aussi très bien.

[cana13]

Effectivement je n'ai pas pour l'instant dans mon cube la hiérarchie Utilisateur / Agence mais je l'ai bien dans ma base SQL Server. Un utilisateur peut être rattaché à plusieurs agences.

Je vais avoir une requête sql du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT CODE_AGENCE from dbo.USER_AGENCE WHERE USER = @USER_NAME

Concrètement, dans visual studio est ce que je dois créer une nouvelle classe visual C# ? comment récupérer en paramètre le USER_NAME ?

ensuite pour créer l'expression MDX : ce sera une expression du style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Filter([AGENCE].[AGENCE].Members, [AGENCE].[AGENCE].Members = ( Les CODES_AGENCES récupérés de ma requête).

Cette expression MDX est dans l'assembly ou dans la gestion des données de la dimension dans la gestion des rôles ?

enfin, comment fait on l'appel à une assembly dans la gestion des rôles et comment lui passe t on un paramètre ?

Je ne sais pas du tout comment imbriquer tout ça !

[cana13]

HELP !! Je patauge de plus en plus !

Pour faire simple et essayer d'avancer, je reprend le problème à la base en rajoutant dans mon cube une dimension UTILISATEUR qui contient Agence / Utilisateur. Je verrai après pour optimiser.

Ma clé est Utilisateur. Premier problème rencontré, un utilisateur peut être rattaché à plusieurs agence, donc au traitement j'ai une erreur sur un doublon de clé. Je crée un calcul nommé qui concatène Agence + Utilisateur, et j'utilise ce nouveau champ comme KeyColumn de ma clé de dimension.
Je crée ensuite une hiérarchie Agence - Utilisateur. => c'est ok je n'ai plus d'erreur au traitement et la navigation dans la hiérarchie parait bonne.

Jusque la, est ce que j'ai bon ?

Deuxième étape : en MDX, je voudrait récupérer (Filtrer) Les Agences de ma dimension AGENCE qui sont égales aux Agences de ma dimension UTILISATEUR, pour un utilisateur donné. C'est la que ça se complique !

Pour le moment j'essaye de récupérer tout simplement un set d'agence pour un utilisateur que je donne 'en dur' et ça ne marche pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT 
  FILTER([DIM UTILISATEUR].[Hiérarchie].[CODE AGENCE].Members,[DIM UTILISATEUR].[Hiérarchie].[UTILISATEUR].name = 'Administrateur') ON 0
FROM [DECISIONNEL REC]

mais ça me renvoi rien du tout ... J'ai essayer plein d'autres formules sans succès. Merci de votre aide car je commence à désespérer...

[cana13]

J'ai un peu progressé : j'ai réussi à obtenir les Agences de mon utilisateur avec la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT FILTER ([DIM UTILISATEUR].[CODE AGENCE].[CODE AGENCE].Members,
 [DIM UTILISATEUR].[CODE AGENCE].CurrentMember.properties("UTILISATEUR") = 'DOMREC\Administrateur'
 
) on 0
FROM [DECISIONNEL REC]

Il me suffira de remplacer DOMREC\Administrateur par la fonction UserName pour récupérer celles de l'utilisateur connecté.

Maintenant, il me faut filtrer ma dimension principale AGENCE sur les agences récupérée par la requête ci dessus. Comment fait on cela ? Peut on le faire avec une seule requête imbriquée ou faut il le faire en plusieurs temps ?

(Apparemment y ' a pas grand monde sur le forum !)

[FrancoisJehl]

Apparemment y ' a pas grand monde sur le forum !)

On a un métier nous aussi !
Pour ton expression de Set elle constituera l'AllowedSet de la hiérarchie [CODE AGENCE] de ta dimension [DIM UTILISATEUR] dans l'onglet "Dimension Data", sous onglet "Advanced" d'un rôle de sécurité SSAS.

[cana13]

Bonjour François, content de te lire !

Oui mais ce n'est peut être pas aussi simple car ma dimension [DIM UTILISATEUR] n'a aucune relation avec mes tables de Faits. Je l'ai juste rajouté pour obtenir la liste des Agences par utilisateur (car j'ai du mal pour le moment à mettre en place une assembly dotnet qui le fasse à sa place).

Les Faits sont en relation avec la dimension [DIM AGENCE] (dans laquelle les clés sont unique). C'est cette dimension que je voudrait filtrer.

J'ai bien pensé mettre les utilisateurs directement dans la dimension [DIM AGENCE] ou à lier les 2 tables ensemble mais je vais me retrouver avec des doublons de clés et comment gérer la relation avec les tables de faits ?

Donc, ne peut on faire un truc du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
WITH
SET [CodeAgences]
AS FILTER([DIM UTILISATEUR].[CODE AGENCE].[CODE AGENCE].Members,
[DIM UTILISATEUR].[CODE AGENCE].CurrentMember.properties("UTILISATEUR") ='DOMREC\Administrateur')
 
SELECT
 
FILTER(
[DIM AGENCE].[CODE AGENCE].[CODE AGENCE].Members, Exists([DIM AGENCE].[CODE AGENCE].Members, {CodeAgences}))
 
 on 0
FROM [DECISIONNEL REC]

J'ai essayer bien sur cette requête mais elle me renvoi l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

La fonction* attend une expression de chaîne ou numérique pour l'argument*. Une expression d'ensemble de tuples a été utilisée.

[FrancoisJehl]

Oui c'est ce que je te disais dans le côté "si tu n'as pas la notion d'utilisateur dans le cube ça peut être embêtant". On pourrait essayer la solution de mettre en lien les faits avec les utilisateurs à travers l'agence avec une many to many, genre comme ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Faits -> DimAgence <- FactlessAffectation(AgenceID, UtilisateurID) -> DimUtilisateur

Après tu fais le filtrage sur Agence avec un Exists sur le groupe de mesure intermédiaire (sur FactlessAffectation) par exemple. Et tu masques la notion d'utilisateur à l'utilisateur (ça ne lui sert à rien et c'est technique).
C'est pas méga propre (voir pas propre) mais si la many to many est pas grosse pourquoi pas?

[cana13]

Mais je pensais qu'il était possible de filtrer [DIM AGENCE] sur un set d'agence récupéré en amont (c'est pourtant bien le principe énoncé par Jinroh77) sauf que la, je récupère le SET d'agences d'une requête MDX et non pas d'une assembly Dotnet, non ?

Sinon, je vais essayer ce que tu proposes. J'ai environ 250 Agences avec environ 4 a 5 personnes par agence...On verra si les temps de réponses ne sont pas trop affectés.

Concrètement, je crée donc une table de Fait fictive FactlessAffectation qui contient un code agence, un code utilisateur et une mesure bidon à 0 par exemple ? et que je génère automatiquement pour chaque couple AGENCE - UTILISATEUR ?

[cana13]

OK, j'ai créé ma table intermédiaire et j'ai alimenté mon jeu de test. Maintenant j'ai un petit problème pour la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Exists([DIM AGENCE].[CODE AGENCE].Members,, "FAIT AFFECTATION")

filtre bien les agences utilisées sur la nouvelle table mais comment je combine le filtre sur l'utilisateur ?

[FrancoisJehl]

Ton Exists te donne bien "les Agences qui ont des faits associés dans ton groupe de mesures Affectations" (qui ont au moins un utilisateur affecté). Mais tu n'utilises que deux paramètres sur les trois: il manque dans ton Exists la partie qui dit "...à l'utilisateur 'DOMREC\Administrateur'". Il faut donc mettre cette partie là (je cite ton code précédent):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
FILTER
(
[DIM UTILISATEUR].[CODE AGENCE].[CODE AGENCE].Members,
[DIM UTILISATEUR].[CODE AGENCE].CurrentMember.properties("UTILISATEUR") ='DOMREC\Administrateur'
)

dans ton Exists.

[cana13]

ha mais bien sur ! je vais essayer (désolé, je suis pas très perspicace mais je découvre MDX et pas mal de choses m'échappent encore).

Sinon, juste pour bien comprendre, pourquoi ne peut on pas faire ce que j'ai énoncé plus haut :

je pensais qu'il était possible de filtrer [DIM AGENCE] sur un set d'agence récupéré en amont (c'est pourtant bien le principe énoncé par Jinroh77) sauf que la, je récupère le SET d'agences d'une requête MDX et non pas d'une assembly Dotnet

comment ferait on alors la requête MDX si le jeu de codes Agences provenait d'une assembly ?

[FrancoisJehl]

Bah une idée par exemple ton assembly peut retourner directement un set, ou renvoyer une chaîne de caractètre comprenant la liste des agences (genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"{[DIM AGENCE].[CODE AGENCE].&[1],[DIM AGENCE].[CODE AGENCE].&[2]}"

)

Toi derrière tu peux faire un StrToSet de ce truc. Encore une fois ça n'est qu'une idée d'implémentation. En fait toute l'idée c'est de récupérer un Set à partir d'un ID utilisateur. L'idée que je t'ai jeté en pâture avec la factless me semble être la moins altérante pour ton cube existant mais il y a d'autres moyens.

[cana13]

Oui, on est d'accord, mais on obtient bien le même résultat qu'avec ma requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
WITH
SET [CodeAgences]
AS FILTER([DIM UTILISATEUR].[CODE AGENCE].[CODE AGENCE].Members,
[DIM UTILISATEUR].[CODE AGENCE].CurrentMember.properties("UTILISATEUR") ='DOMREC\Administrateur')

qui renvoi bien directement un set d'agence.

Donc ça ne répond pas à ma question : Peut on (et comment) avec ce set, faire la requête MDX qui va filtrer les Agences sur ce set ?

Et si c'est possible, pourquoi alors ai je besoin de passer par cette table de Fait intermédiaire fictive ??

Est ce que c'est parce que le set d'agence renvoyé provient de [DIM UTILISATEUR] et non pas de [DIM AGENCE] que je ne peux pas le faire dans mon cas ?

[FrancoisJehl]

Peut on (et comment) avec ce set, faire la requête MDX qui va filtrer les Agences sur ce set ?

La différence c'est que là tu as un Set sur [Dim Utilisateur] alors que tu as besoin d'un set sur [Dim Agence]. Pour passer de l'un à l'autre tu dois pouvoir faire un LinkMember par membre dans un Generate pour itérer sur tous les membres. En gros une expression MDX pas ultra performante. Avec un Assembly tu aurais un set directement ou une simple chaîne à passer à un StrToSet. Je te rappelle que l'expression de sécu est un Set de membres de la hiérarchie considérée, c'est tout.

Et si c'est possible, pourquoi alors ai je besoin de passer par cette table de Fait intermédiaire fictive ??

La solution de la ManyToMany est plus élégante je trouve et ressemble plus à la manière de faire de la sécurité à la ligne en SQL. En plus avec le Exists l'expression de sécurité sur une Factless réduite (comprendre très très largement moins d'un million de membres qui est le maximum recommandé pour une M2M) est plus performante que de faire du LinkMember ou de la conversion de chaînes de caractères.

[cana13]

d'accord, je comprend mieux...

Je vais donc essayer de mettre en œuvre et de tester tout ça.

Un grand merci pour toutes tes explications et ta disponibilité.

bonne journée.