topic Re: MDX all descendants of anchestor where SAS.IdentityGroupName = "lead" in SAS Web Report Studio

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

metalray — Tue, 29 Mar 2011 14:13:46 GMT

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]

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

AngelaHall — Tue, 29 Mar 2011 19:09:44 GMT

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

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

metalray — Wed, 30 Mar 2011 08:10:05 GMT

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

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

AngelaHall — Wed, 30 Mar 2011 13:52:07 GMT

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!)

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

metalray — Thu, 31 Mar 2011 07:47:49 GMT

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

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

metalray — Wed, 27 Apr 2011 09:50:16 GMT

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

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

metalray — Fri, 29 Apr 2011 08:35:57 GMT

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

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

metalray — Tue, 03 May 2011 16:01:02 GMT

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