Help me in providing dynamic security for cube

Question

Hi Guys,

 I am trying to provide dynamic security for a cube.

This is my MDX expression I am using in the dimension data tab.This is the MDX expression given in the expert cube development cube.

nonempty(
                         [Dim Activity].[Activity].members,
                (
                  StrToMember("[Security RK User].[User Name].["+USERNAME+"]")) ,
[Measures].[Security  RK ACCESS Count]

)

 

The error I am getting is "Query (5,1) Too many arguments were paseed to the NONEMPTY MDX function.No more than two arguments are allowed.

 

When I am using below expression,it says [security RK ACCESS Count] was not found when [Measures].[Security  RK ACCESS Count]  was parsed.

 

 

The other MDX function I am using is

Exists(
                         [Dim Activity].[Activity].members,
                {
                  StrToMember("[Security RK User].[User Name].["+USERNAME+"]")} ,
[Measures].[Security  RK ACCESS Count]

)

This time I am not getting any errors but this expression giving only empty results.

 

Please help resolving this issue.Thank you

Answer 1

Have you tried:

nonempty(
         [Dim Activity].[Activity].[Activity].members,
         (StrToMember("[Security RK User].[User Name].["+USERNAME+"]") ,
          [Measures].[Security  RK ACCESS Count])
)

 

Here [Measures].[Security  RK ACCESS Count] should be a cube measure in the "factless" fact table relating Users and Activities.

---

- Deepak

Answer 2

Thanks for your reply.I have tried the expression given by you but getting the same error  as '[security RK ACCESS Count] was not found when [Measures].[Security  RK ACCESS Count]  was parsed'.But I can see [Security  RK ACCESS Count] in the factless facttable measure group.

Answer 3

So what is: [Measures].[Security  RK ACCESS Count]  - is it a cube measure?

Also, have you tested this expression in an MDX query, like:

select

nonempty(
         [Dim Activity].[Activity].[Activity].members,
         (StrToMember("[Security RK User].[User Name].["+USERNAME+"]") ,
          [Measures].[Security  RK ACCESS Count])
) on 0

from [Cube]

---

- Deepak

Answer 4

Hi Mr Deepak,

    I could resolve the error now.I was getting the error previously because of the extra space between Security and RK in [Measures].[Security  RK ACCESS Count].Now the syntax is ok for the expression but I am not getting any values.do i have to define any hierarchies?

I have dim activity a key and activity attribute in the [dim activity] table.Do I have to define any hierarchies between dim activity key and activity attribute as I am using the activity in the expression ?Do the same thing applies to the user table also?

once again thanks for your reply.

 

Answer 5

".. Now the syntax is ok for the expression but I am not getting any values .." - some things to check:

- Is the fact table / measure group containing [Measures].[Security  RK ACCESS Count] related to both [Dim Activity] and [Security RK User] dimensions?

- What does the [Security RK User].[User Name] attribute look like? If it doesn't include the domain name, then you need to strip that from USERNAME, like:

StrToMember("[Security RK User].[User Name].["

+ Right(

UserName,

Len(

UserName) -

Instr(

UserName, "\"))

+ "]")

---

- Deepak

Answer 6

There is little bit improvement in the results.I have defined the relationship between my user account name and activity for the testing purpose.While browsing the cube using the roles option now I am getting the corresponding activity for my username.But,I can see all the user name in the USER dimension and also I am getting the measure values as #N\A.

factless fact table has the many to many relationship between users and activities.

the usernames in the user table are like this ADMIN\RKC..so I believe I dont hav to strip.

 

Answer 7

".. But,I can see all the user name in the USER dimension .." - you can configure security for that dimension directly:

{StrToMember("[Security RK User].[User Name].["+USERNAME+"]")}

".. also I am getting the measure values as #N\A .." - this typically happens when you change Cell Data Security from it default settings.

---

- Deepak

Answer 8

Thank you very much.It's really working out for me.

Yes,I checked enable read permissions box in cell data tab.I corrected that and now I can see the measure values.

Now ,the problem is that I can see all the user names associated with the corresponding activity and I can see all the user name in my USER dimension.But,what I want to see is that who ever the user logs in I need  to display his corresponding data only and I need to see only his user name?If I achieve this themn i will be doneIs there any prob with my MDX expression? 

 

Thank you very much for your continuous support....

 

Answer 9

Did you try configuring Dmension Data security for the [Security RK User].[User Name] attribute as:

{StrToMember("[Security RK User].[User Name].["+USERNAME+"]")}

 

---

- Deepak

Answer 10

I am already using the below expression in dimension data advanced tab Allowed Member Set.

nonempty(
         [Dim Activity].[Activity].[Activity].members,
         (StrToMember("[Security RK User].[User Name].["+USERNAME+"]") ,
          [Measures].[Security  RK ACCESS Count])
)

 

 

Do I have to include {StrToMember("[Security RK User].[User Name].["+USERNAME+"]")} also seperately?If so,where can I do that?

Thank you

Answer 11

".. Do I have to include {StrToMember("[Security RK User].[User Name].["+USERNAME+"]")} also seperately?If so,where can I do that? .." - at the same "dimension data advanced tab Allowed Member Set", but select the [Security RK User] dimension and [User Name] attribute.

---

- Deepak