BookmarkSubscribeRSS Feed
metalray
Calcite | Level 5
Hello,

I am looking at some MDX that takes into account the SAS.IdentityGroupName or SAS.IdentityGroups.
If the user that has logged in, is part of the Group="SalesTeamLead" then
this agent should see all descendants of the group he/she is part of.

For example:

Dimension_Sales_Levels
-Region
---SalesTeam
-----SalesAgents

Security_Groups
-SalesAgent
-SalesTeamLead

Does someone

Note: One user will only ever be part of one group.

I tried the following as a filter for the management console but had no luck.

SELECT
IIF( SAS.IdentityGroupName IS 'SalesTeamLead'
,
Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name
= 'SALESAGENT' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.Userid')).Item(0))
,
NULL
)
FROM
[mycube]
7 REPLIES 7
AngelaHall
SAS Employee
Bob,
What is the issue you are having?

Is the user still able to see everything? Ensure that the security_subset option is on in the OLAP Cube. http://sas-bi.blogspot.com/2011/01/implementing-olap-member-level-security.html

Is the edit expression returning an error? Here is documentation: http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212399.htm

Also in 9.2.M3 - you can set up a security batch table. See the bottom of the pg:
http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212372.htm

~ Angela
metalray
Calcite | Level 5
Hi Angela,
Thanks for your help.
We have used the security_subset option and that is very helpfull but to
get to a security_subset, or a valid set of data from the OLAP cube for certain
SalesAgents we need to have MDX filters that restricts access.

Yes, the expression is returning an error, and I dont think my expression so far is anything close to correct nor is there any SAS documentation that comes close to such "advanced MDX".

"Formula error - An invalid dimension name was encountered in the MDX statement - near: "[SAS].[IdentityGroupName]""

The following part is correctly working:
Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name
= 'SALESAGENT' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.Userid')).Item(0))

BUT... I need that this filter only applies when the user is in role "SalesTeamLead"
and wants to see all his/her SalesAgents.
Additionally we would have various other filters for other roles (hence the IFF....).

I have heard of permission tables/security batch tables but we are using SAS 9.2 and there is no plan to go to 9.2 M3 anytime soon, besides that, I think we still would need
MDX filters to get the correct data for a certain user.

Regards,
Bob
AngelaHall
SAS Employee
In your IIF statement - have you tried to replace:
IIF( SAS.IdentityGroupName IS 'SalesTeamLead'

with:
IIF( "SUB::SAS.IdentityGroupName" IS "SalesTeamLead"


~ Angela

(The only documentation I have seen is: http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212399.htm - so seems like we need a SASGF paper for 2012 in Orlando!)
metalray
Calcite | Level 5
Hi Angela,

May thanks for the help! I dont blame SAS for not having such MDX examples in there documentation - it not what most people need, I guess.

IIF( 'SUB::SAS.IdentityGroupName' IS 'SalesTeamLead'
,
Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESAGENT' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.Userid')).Item(0))
,
NULL
)

Error: Formula error - The argument type is incorrect -
in the "IS" function near: "SUB::SAS.IdentityGroupName"

I just wonder how to use the Identity Driven Properties in conjunction with each other.

Bob
metalray
Calcite | Level 5
Since I have not made any progress with finding the right syntax I thought about a
CASE statement. This works in MS Analysis Services but I am not sure if it does with SAS OLAP.

CASE 'SUB::SAS.IdentityGroups' AS var
WHEN var CONTAINS 'SalesTeamLead' THEN
'agent should see all descendants of the group he/she is part of'
WHEN var CONTAINS 'SalesPerson' THEN
Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESAGENT' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.Userid')).Item(0))
END


Original:
CASE [input_expression]
WHEN when_expression THEN when_true_result_expression
[...n]
[ELSE else_result_expression]
END


http://msdn.microsoft.com/en-us/library/ms144841.aspx



SAS.IdentityGroups returns multiple values so we need to check if one of them is x. There is a SAS tutorial but it only shows how to add an identity driven property to an mdx expression not how to use those as part of an expression http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212399.htm
metalray
Calcite | Level 5
Thanks to some guidance I can no reconsider my approach.
Rather than having one giant MDX with conditional logic I use small parts
of MDX different for each SAS metadata user group (OLAP member level security/MDX expression).

That leads me now back to the very first question and problem. Assuming the user is in role
SalesTeamLead then this expression applies, gets individual Seller Information:

Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESAGENT' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.Userid')).Item(0))

how can I tweak it so it gets all the descendats of the group he/she is leading (part of).

Dimension_Sales_Levels
-Region
---SalesTeam
-----SalesAgents
metalray
Calcite | Level 5
solution:

SELECT
Descendants(
Ancestor(
Head(
Filter(
[DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESAGENT_ID' and [DIM_SALES].CurrentMember.Name = 'tedas2')
).Item(0
)
,2)
)
ON COLUMNS
FROM
[mycube]


Ancestor instead of AncestorS

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 1834 views
  • 0 likes
  • 2 in conversation