MDX all descendants of anchestor where SAS.IdentityGroupName = "lead"

[metalray]

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]

[AngelaHall]

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]

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]

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]

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]

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]

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]

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