Traditional web-based reporting with SAS BI tools

MDX member authorization using IdentityGroups

Reply
Occasional Contributor
Posts: 6

MDX member authorization using IdentityGroups

We're trying to implement identity driven member authorization, and we've run into some problems.

Let's say we have a cube with a simple dimension called Auth_dim (one  hierarchy). If we put user names in that dimension we can use the following MDX filter:

[Auth_dim].[All Auth_dim].[SUB:Smiley FrustratedAS.PersonName]

The above example works fine, except that a not so very nice error is given in WRS when the user isn't authorised for any members. But when we want to use groups we run into problem. We tried putting  groupnames in the dimension and using the following MDX:

[Auth_dim].[All Auth_dim].[SUB:Smiley FrustratedAS.IdentityGroups]

It not only doesn't work, it also causes both Metadata server and OLAP server to crash. Now I have to admit I don't know anything about MDX syntax, so the above may simply be wrong. Does anyone have a suggestion?

Contributor RSB
Contributor
Posts: 72

MDX member authorization using IdentityGroups

Which Version of SAS(9.2 or 9.3) are you using?

Occasional Contributor
Posts: 6

MDX member authorization using IdentityGroups


We're using 9.2.

Contributor RSB
Contributor
Posts: 72

MDX member authorization using IdentityGroups

Instead of Using the [SUB:Smiley FrustratedAS.IdentityGroups], you may try this .

In the Authorization window, select the Group and click on Build the formula. For example the name of Group is XYZ and you want to enable the users in the Group to see only the data of USA then your MDX will be similar to:

{  [Organization Unit].[Organization Unit].[All Organization Unit],

   [Organization Unit].[Organization Unit].[All Organization Unit].[GROUP],

   [Organization Unit].[Organization Unit].[All Organization Unit].[GROUP].[USA],

   descendants([Organization Unit].[Organization Unit].[All Organization Unit].[GROUP].[USA]

)}

This support document shows information when there are more than hierarchies, but this even works when there is only one hierarchy in the dimension.

http://support.sas.com/kb/37/136.html

Occasional Contributor
Posts: 6

MDX member authorization using IdentityGroups

Ok, but [SUB:Smiley FrustratedAS.IdentityGroups] is exactly the part we need. That's how authorization will be defined in our metadata. Each user is part of one or more groups, and each group has access to a certain part of the data.

By the way, we have no descendants in our authorization dimension. Example:

Hospital A

Hospital B

Hospital C

Person 1 may have access to the data of one hospital, Hospital B for instance. But a second person may have access to 2 hospitals, B and C. The first person only gets group B. The second person gets groups B and C.

In the above example we could add each group to the authorization of the cube, and then assign a member to each group. But that becomes very time consuming when there are 50 or even 100 groups.

In that case it would be much easier if you could set the authorization on SASUSER for instance, using the identity groups.

The only thing we could come up with is that [SUB:Smiley FrustratedAS.IdentityGroups] resolves into a list of groups and that [Auth_dim].[All Auth_dim].[SUB:Smiley FrustratedAS.IdentityGroups] is incorrect syntax when there are more answers than one.

Occasional Contributor
Posts: 6

MDX member authorization using IdentityGroups

I've been looking for information and I found something interesting at: http://www.iccube.com/support/documentation/mdx_tutorial/members.html

It seems that the syntax is indeed not correct. Let's say we have two groups: Group-A and Group-B.

The correct syntax would be:

{ [Auth_dim].[All Auth_dim].[Group-A] , [Auth_dim].[All Auth_dim].[Group-B] }

But [Auth_dim].[All Auth_dim].[SUB:Smiley FrustratedAS.IdentityGroups] will probably generate something like:

[Auth_dim].[All Auth_dim].[ Group-A , Group-B ]

Would it be possible to us IN like in SQL?

[Auth_dim].[All Auth_dim] in Group-A , Group-B

Super Contributor
Posts: 364

MDX member authorization using IdentityGroups

Hi,

Coincidentally I have just replied to a similar question/comment posted today on an old blog post I wrote a few months back about OLAP identity driven member level security: http://platformadmin.com/blogs/paul/2011/01/sas92-olap-cube-identity-driven-member-level-security/  There are a few links to resources in the comments that you might find useful.  An alternative to trying to perform the filter in a single MDX expression is to apply multiple simpler permission conditions for each of the appropriate groups.  If you have SAS 9.2 M3 you can also apply the permission conditions from a batch security permissions table.

Cheers

Paul

Occasional Contributor
Posts: 6

MDX member authorization using IdentityGroups

Hi Paul,

It's not a similar question, it's the same question. Frank is sitting less than 5 meters away from me :smileylaugh:

Thank you for your advice, we're going to take a look at the batch security permissions table.

Regards,

Sander

Occasional Contributor
Posts: 6

MDX member authorization using IdentityGroups

Once you know how it works, those permission tables are a breeze! :smileylove:

This is definitely the right way for us. It would enable us to set the authorization for a large number of cubes and a large number of authorization groups.

Paul, thanks for the advice!

Post a Question
Discussion Stats
  • 8 replies
  • 427 views
  • 0 likes
  • 3 in conversation