Hi all,
I try to implement dynamic security and I have a problem with a dependent dimension.
What I want:
Each user is responsible for one or more branches of the company, so each user should see the data the branches, she is responsible for.What I get:
Error: Das Branch-Attribut in der DIM_ZWST-Dimension weist einen ungültigen Sicherheitsausdruck für eine generierte Dimension auf. (sorry, it's german) It means: The Branch-attribute of the DIM_ZWST-dimension has an invalid security expression for a generated dimension.This is my setup:
SSAS 2k5 Dimensions: DIM_USERS with attributes Name, Loginname (this is NT user name and should correspond to the MDX UserName()-function) DIM_ZWST: These are the branches of the company. Each user should be allowed to view one ore more branches. Cube: I have a measure group 'PERMISSIONS' where each combination of a user and a branch has a value of '1' for the measure HAS_PERMISSIONS. Every other combination doesn't exist. I can check this by just browsing that measure. My other measure groups are related m:n with the DIM_USERS dimension via the PERMISSIONS measure group. Browsing the cube I get all the values I would like to see. Roles: After playing around with the STRTOSET and STRTOMEMBER functions, what worked for the DIM_USERS dimension but not for the DIM_ZWST function I tried it with a hardcoded role: DIM_USERS, allowed group = {[DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser]} (this one works!) DIM_ZWST, allowed group = NONEMPTY([DIM_ZWST].[Branches].[Branch].Members, ([DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser], [MEASURES].[HAS_PERMISSION])) (this one doesn't work!)I don't wont to implement an assembly for retrieving my valid set of members.
Does anyone has any ideas?
Michael
So, what exactly do you get if you run the following MDX query
SELECT {} ON 0, NONEMPTY([DIM_ZWST].[Branches].[Branch].Members, ([DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser], [MEASURES].[HAS_PERMISSION])) ON 1 FROM Cube
|||Hi Mosha,
thanks for your reply. I get a list of all branches the user is allowed to see, if I run the query with my admin user. (Running the query with my test user results in the same error as mentioned above)
Michael
|||Hi all,
I just have another strange effect: If I try editing the attribute security of my branch attribute in Management Studio it will show me a dialog listening exactly my allowed branches saying, that the unqiue member names are no longer valid and I can answer yes or no to the question, if I want to delete these ones.
Michael
|||OK, this should work then... One more guess: Is dimension DIM_ZWST used in any other cube ? If the other cube doesn't have same Permission measure group, that could be a problem, but I think this is not very likely...|||Hi Mosha,
yes, it is used in another cube, but I have implemented security on the cube dimension not the database dimension, because the latter didn't work at all. I guess that it didn't work because the database dimension doesn't know anything about measure groups of any cubes.
Second, my other cubes shouldn't be visible to that kind of users at all.
Thanks so far,
Michael
|||Hi all,
I just want to let you know, how I have solved my problem:
I have built a SSIS package that will run every night and that will create, update or delete my roles, depending on what information has changed in the underlying database table. Now I have many roles but in AS2k5 this shouldn't be a problem anymore (I hope 
).
Michael
|||Michael,
Will you give me some hints on what your package looked like? I have the exact same problem but am not sure how to go about creating the roles in SSIS. We had though about using the script component somehow with the api, or something with xml but we are not too sure yet.
Thanks in advance,
Sean
|||Hi Sean,
sorry, I missed your post, because I didn't get an email for this.
It was a solution for our customer, so at the moment I can't send you any code snippets. I decided to do it rather with a script component inside of a data transformation than with some xml because the xml is a little bit clumsy.
I have database view as a datasource where I get the user logins and the branch ids. This will lead into a script component, e.g. as a destination. There you can use AMO scripting for creating and updating the roles. To distinguish between "normal" roles and "automatic" roles I have used a naming schema like "prefix_" + userid.
For deleting unused roles you can have a script component as datasource which returns userid and branch of the existing role. You can use a lookup with an error path to another script component to delete the unused roles.
Michael
|||Thanks Michael!
Sean
).