BookmarkSubscribeRSS Feed
Calcite | Level 5

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::SAS.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::SAS.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?

Calcite | Level 5 RSB
Calcite | Level 5

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

Calcite | Level 5

We're using 9.2.

Calcite | Level 5 RSB
Calcite | Level 5

Instead of Using the [SUB::SAS.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.

Calcite | Level 5

Ok, but [SUB::SAS.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::SAS.IdentityGroups] resolves into a list of groups and that [Auth_dim].[All Auth_dim].[SUB::SAS.IdentityGroups] is incorrect syntax when there are more answers than one.

Calcite | Level 5

I've been looking for information and I found something interesting at:

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::SAS.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

Rhodochrosite | Level 12


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:  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.



Calcite | Level 5

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.



Calcite | Level 5

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!


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3 in conversation