topic Re: Role based security as input paramenter, data read from oracle db on demand in Developers

Role based security as input paramenter, data read from oracle db on demand

metalray — Tue, 18 Jan 2011 15:56:18 GMT

Hi guys,

I am thinking about security in the moment.
We have about 3000+ end users that all have access to their data.
Setting all those permissions in the management studio will take forever so
I need to have some kind of general rule that all users (single sign on/LDAP)
have access to their own OLAP cube member "myname" on the dimension "dim_names" and the other dimensions
do not matter. how would you do it?

some of those users are team leads and they actually can access the data of other people.
the whole security model is maintained in an oracle table. my plan was
to populate some prompts with only those member that are actually accessible to the end user in some cases
It will be a very small prompt with just "myname" but some actually show a couple of names
or a whole team.

my idea is that:

web report - > stored process - > sql pass through get user accessible members based on user session role ->
populate prompt

Is that possible? can the user role (login) be passed to a stored process.
How would that work if a thousand people do it at the same time? would the
sql pass through collapse?
I can not put all "mynames" in one group because then they could access each other data and I cant put all 3000+ "mynames" in 3000+ different groups that I all have to assign - hence I think reading it from the oracle db and having a little logic in a STP for the prompts will do it.

Re: Role based security as input paramenter, data read from oracle db on demand

Vince_SAS — Tue, 18 Jan 2011 19:39:02 GMT

Do you already have credentials for your 3,000+ users loaded into SAS metadata, and can they successfully log into the system?

Vince DelGobbo
SAS R&D

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Wed, 19 Jan 2011 09:41:20 GMT

Hi Vince,

yes, its imported from the LDAP. so they can login
but how do I do member level security for 3000+users on 3000+ OLAP dimension members. maintaining
that manually will take forever so I have this plan:

1. dont show them anythink in the selection prompts that they are not allowed to
select (the selection is retrieved with a sql pass through in a stored process that has
somehow taken the user rolee or ID as an input)

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Wed, 19 Jan 2011 11:39:23 GMT

Hi,

Have you considered using an Identity Driven Property in your MDX filter expression so that you have 1 or a few permission conditions rather than 3000+?

There is more info at:
* How to Assign an OLAP Permission Condition (http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/a003139257.htm)
* Identity-Driven Properties (http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/a003250936.htm#a003250949)

Cheers
Paul

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Wed, 19 Jan 2011 12:37:20 GMT

Hi Paul,
that sounds very promising! I give it ago. Thanks a lot.

Cheers,
Bob

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Wed, 19 Jan 2011 15:54:45 GMT

Hello Paul,
I read on the website provided that I can implement
it with SAS OLAP Server using MDX expressions/query. the
link to the document "How to Assign an OLAP Permission Condition" shows me where to click in the management console as well but I dont know how to write the MDX query that effects all bottom level members and only gets those where the salesid = =&SAS.Userid;

SalesPersonID is the bottom level member.

[SalesPersonID].[All Members].where the salesid = =&SAS.Userid;

Do I have to write an MDX query with a SELECT clause or is an MDX "expression" sufficient? I think an expression is without a select.

Re: Role based security as input paramenter, data read from oracle db on demand

Vince_SAS — Wed, 19 Jan 2011 15:59:47 GMT

See also:

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212372.htm#a003212373

The section "Applying Batch Security with Permission Tables" talks about using a "Permissions Table" that contains the cube access controls. If you can create this SAS table programmatically it could save you some typing.

Vince DelGobbo
SAS R&D

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Thu, 20 Jan 2011 11:00:34 GMT

hi Vince, thanks for that. I will keep it as an option. before I move to it I want to see if the MDX idea work but I just noticed that clicking on tools->manage permission tables and providing the information results in OLAP Cube Studio not doing anything when clicking on "next".

back to the query, I can not restrict the query by any selection of the other dimensiosn apart from the sales channel so I hoped that someting like:

[SalesPersonID].[All Members].where the salesid =&SAS.Userid;

would be sufficient but that just gives me an error.

I found this great tutorial but it is not LEVEL specific rather member specific and it does not show how the final query with the member definition and identity driven property looks, it almost seems the author had not clue how the final query would look either 🙂

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212399.htm

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Thu, 20 Jan 2011 15:37:50 GMT

Hi Bob,

I tested this on a cube here and it worked for me. In the MDX expression filter window I used the following:

{[DimSalesPerson].[All Sales People].[SUB::SAS.Userid]}

(Note: it complains about invalid syntax but if you keep clicking through the OK buttons it will be saved and it does work)

... and when I logged in as sasdemo I only saw the subset corresponding to:

{[DimSalesPerson].[All Sales People].[SASDEMO@MYDOMAIN]}

Notice that the userid is normalized to the uppercase format with domain suffix.

Applying this to your question it looks like you could use:

{[SalesPersonID].[All Members].[SUB::SAS.Userid]}

This will only work though if your SalesPersonID values are in the normalized form USER@DOMAIN.

Thanks to Vince for pointing out the permission tables - I haven't tried them yet - I suspect they will be a lot more manageable/flexible.

Cheers
Paul
http://platformadmin.com/

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Fri, 21 Jan 2011 08:57:06 GMT

Hi Paul,
Thanks for that. I actually did not go any further after I got the "invalid syntax" error.
When I try to add a permission table with a library that uses the SAS ACCESS engine and press Next then
nothing seems to happen. Do permisson tables work with a libraray that uses the SAS Access (Oracle) engine?
Creating a new library did not help either. I use sas olap studio 4.2 M2. - I think there is a hotfix for that

Why do you think permission tables make it more flexible? I am worried that the format of the table and providing the data in that format will be a big job.
Thanks,
Bob Message was edited by: metalray

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Tue, 25 Jan 2011 13:34:35 GMT

Hi Bob,

I haven't tried to use a SAS/ACCESS library for a permission table (not as yet anyway). I initially had trouble with a base engine library because it had a relative path. Then I saw Problem Note 40424: Permissions tables for use with member-level security are not supported for UNIX libraries or for libraries that use relative paths (http://support.sas.com/kb/40/424.html). After applying SAS OLAP Cube Studio 4.2 M2 Hotfix D06001 the relative base engine library worked. The hotfix doc doesn't say anything about SAS/ACCESS libraries but if it were me I would give it a try anyway.

I only say that permission tables might be more flexible because they should be easy to manage, can be programmatically generated even including programmatically generated MDX expressions (with the full power of Base SAS behind it). It should be a whole lot easier than point and click when you have more than a handful of permission conditions.

BTW - I will be posting a blog article on the identity driven member level security example if you are interested in taking a look. I am also thinking of doing a post on the format of the permission table. I can't find much documentation for it so have been investigating the table and the generated SAS code that reads/writes the metadata permission conditions to get a better idea of how the table should be populated.

Cheers
Paul
http://platformadmin.com/

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Thu, 27 Jan 2011 16:24:20 GMT

Hello Paul,

I used the example you described but this did not work well: {[SalesPersonID].[All Members].[SUB::SAS.Userid]}
I got the syntax error but additionally the web report could not display anything in the cube (so I think the invalid syntax broke it).

n error occurred in processing the report element. Please refresh the page to try again. Contact your administrator if this problem persists.
[a12962322664760.05642803333271551_0_0] Error Rendering the Report
Unable to access the OLAP server for cube DEV_MLS_CUBE_V2.

Returning to fix my syntax and using EG 4.2 as a inspiration I was unable to make out how to apply a filter to a bottom level or any level that is not the top one (whole dimension).

WHERE ([SALESPERSON_ID].[All Members])
WHERE ([DIM_SALES].[SALESPERSON_ID].[All Members])
Both cause the error:
"Invalid Dimension Name was encountered in the MDX statement"

Back to permission tables:
I tried what you did (also using D06001) but failed creating a permission table with the error 76: LINE and COLUMN cannot be determined.
Assuming that my SAS/ACCESS Library has the permission to create an Oracle table in a
Oracle Schema for developers (which is already very unlikely and I dont even know how to grant such a right to create tables in
other schemas) I could still not imagine how this permission table would be represented on the SAS side. Would it be a SAS VIEW
that SAS created by itself to show me in SAS the Oracle talbe it created seconds before?

Having an empty permission table seems like a lot of work to fill manually.
I dont see that it has a benefit over the approach via member level permission in that respect.
I see howerver, the high potential of reusability. is that correct?

Thanks for your reply and your brilliant blog! What you wrote should go in the SAS documentation.

Bob

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Sun, 30 Jan 2011 08:24:00 GMT

Hi Bob,

Regarding the MDX error:

With the OLAP cube open in SAS Enterprise Guide did you drag the appropriate dimension to the slicer, drill down on it to a specific user and then view the generated MDX? You shouldn't see an Invalid Dimension Name error if that was the case. Can you show us an example of the MDX generated by SAS Enterprise Guide?

Regarding permission tables:

Do you have the log containing that "LINE and COLUMN cannot be determined" error message? I would be interested in seeing it in context.

If it is possible to use a permission table via a SAS/ACCESS Interface to Oracle library (I don't know for sure it is) then you would need appropriate permissions to create the table in the Oracle schema - your Oracle DBA should be able to help you with that. There would be no view within SAS as the Oracle table would be used directly - just like if you assigned a library in a SAS program pointing to the Oracle database. Is there any specific need for the permission table itself to be kept in Oracle vs keeping it in SAS? Once the table is registered its contents could always be dynamically created from the contents of Oracle tables.

The permissions table seems to have a fixed format with specific columns that enables OLAP Cube Studio to apply all of the permission conditions via the generated batch code. Here is an example permissions table which just has 2 rows to apply geographic based security on the DimGeog dimension. Users in the US group can only see US data and users in the AU group can only see Australian data.

fullname, olapschema, cube, dimension, permission, perm_type, mdx_condition
-----------------------------------------------------------------------------------------------------------------------------------------------------
US Users Group, SASApp - OLAP Schema, MyCube, DimGeog, Read, GD, {[DimGeog].[All Countries].[U.S.A.], Descendants([DimGeog].[All Countries].[U.S.A.])}
AU Users Group, SASApp - OLAP Schema, MyCube, GimGeog, Read, GD, {[DimGeog].[All Countries].[Australia], Descendants ([DimGeog].[All Countries].[Australia])}

I have left out the items and remove_ace columns as they are blank in this example.

If you were going to fill the table out manually then the advantages over manually applying the rules with point and click methods are somewhat lessened but there are still benefits. With a table it is easy to re-apply them if the cube was deleted and recreated for example. I see the biggest benefit from a dynamically generated permissions table - imagine a SAS program that queries an LDAP directory (or an Oracle database) to determine what user groups are available and what those groups have access to, then generates a permissions table containing hundreds of rows - much faster than point and click and much easier to reapply when the rules change.

Hope that helps.

Cheers
Paul
http://platformadmin.com/

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Mon, 31 Jan 2011 11:53:02 GMT

Hi Paul,
I agree on your permission table analysis.
Regarding the Member-Level Permission I am still not where I want to be:

If I drill down to the Salesperson_ID, using a filter on the sales person ID, the MDX Query generated looks like this:

SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
Hierarchize(Filter({ [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].Children }, ([DIM_Sales].[DIM_Sales].CurrentMember.Level.Ordinal < [DIM_Sales].[SalesPerson_ID].Ordinal OR Intersect(Ancestors([DIM_Sales].[DIM_Sales].CurrentMember, [DIM_Sales].[SalesPerson_ID]), Filter(Generate({ [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].Children }, Ancestors([DIM_Sales].[DIM_Sales].CurrentMember, [DIM_Sales].[SalesPerson_ID])), kindex(kupcase([DIM_Sales].[DIM_Sales].CurrentMember.Properties("CAPTION")), kupcase("040347788")) <> 0)).Count > 0))) ON ROWS
FROM
[DEV_MLS_CUBE_V2]

If I dont use a filter and do a right click "Isolate 040347788", then the query looks like this:

SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
{ [DIM_Sales.[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].[040347788] } ON ROWS
FROM
[DEV_MLS_CUBE_V2]

In both cases its a valid query but no WHERE clause I can use to specify an Member-Level Permission MDX expression.

--error , invalid member name at SALESPERSON_ID
SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
{ [DIM_SALES].[All DIM_SALES].Children } ON ROWS
FROM
[DEV_MLS_CUBE_V2]
WHERE [DIM_SALES].[SALESTEAM].[SALESPERSON_ID].[All Members].['johnsmith']

--error , invalid dimension name at SALESPERSON_ID
SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
{ [DIM_SALES].[All DIM_SALES].Children } ON ROWS
FROM
[DEV_MLS_CUBE_V2]
WHERE [SALESPERSON_ID].[All Members].['johnsmith']

Thanks for your help,
Bob

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Wed, 02 Feb 2011 05:43:37 GMT

Hi Bob,

It looks like the MDX you posted in the forum thread has been mangled along the way. The forum watch email I received has much more MDX in it than I can see here on this page.

Is the SalesPerson_ID (040347788) the same as the userid? Is your SAS OLAP server on a UNIX or a Windows platform? Watch out for the fact that the user id that will be substituted into the SUB::SAS.Userid placeholder will be normalized - upcased and including a domain suffix on Windows (like 040347788@YOURDOMAIN).

It looks like your userid / SalesPerson_ID level has a number of parent levels above it in the DIM_Sales dimension. One of the difficulties is to express the filter in terms of the lowest level userid member without using/knowing the values of its parent members. You cant use '[DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].[SUB::SAS.Userid] because whilst that might work for 040347788 it probably wont for many other user ids that have different parents members in that dimension - ie. something other than [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ]

One way of expressing the filter - if the userid is a leaf (has no descendants) and it's member name doesn't also appear elsewhere in the tree under different parents is to use something like:

Filter( [DIM_Sales].AllMembers, [DIM_Sales].CurrentMember.Name = '040347788' and [DIM_Sales].CurrentMember.Level.Name = 'SalesPerson_ID' )

.. that results in a set (hopefully of 1). The SAS OLAP server complains about sets in a slicer (WHERE) so you can use the Head() function in an MDX WHERE clause variation to only take the first item in the set (assuming you know there are no duplicates):

WHERE Head( Filter( [DIM_Sales].AllMembers, [DIM_Sales].CurrentMember.Name = '040347788' and [DIM_Sales].CurrentMember.Level.Name = 'SalesPerson_ID') ) )

You can test this out in SAS Enterprise Guide by adding it as a slicer to an existing table that has no slicer - just make sure that table doesn't already include the DIM_Sales dimension in its rows/columns already since you can't use a dimension in the slicer and in rows/columns at the same time. If you want to use the DIM_Sales dimension in the body of the table then you will need to integrate a filter into the rows/columns components instead of using WHERE (just like in the EG generated MDX you posted). I just find working on a filter I can readily cut/paste into a permission condition easier to do from the slicer.

If you get that working then you could try it in a member level security filter expression by replacing 040347788 with SUB::SAS.Userid (assuming 040347788 is actually the normalized form of the user id), so you end up with something like this:

Filter( [DIM_Sales].AllMembers, [DIM_Sales].CurrentMember.Name = 'SUB::SAS.Userid' and [DIM_Sales].CurrentMember.Level.Name = 'SalesPerson_ID' )

Hope this gets you a bit further.

Cheers
Paul
http://platformadmin.com/

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Wed, 02 Feb 2011 10:16:04 GMT

Hello Paul,

You are right, the MDX was altered by the forum engine while posting it.
Yes, SalesPerson_ID (040347788) is the user ID and before going into
identity drive properties I wanted to see if I can set some kind of filter on
a LEVEL basis in the first place. We use SAS OLAP Server on a Windows platform.

Your assumption is correct, userid / SalesPerson_ID level has a number of parent levels above it and its
a ragged hierarchy as well. Specifying a fixes member name in the MDX will be of no use to me.

The userid / SalesPerson_ID appears only onces in a hierarchy and is the bottom level at this tage
but there might be a point later in time when this sales person will have a LEVEL below
it like sales_contracts.

If I add a filter using the EG4.2 add filter function that says only select SALESPERSON_ID where
it matches exactly 040347788 then EG4.2 generates a lot of MDX:

-----------------MDX CODE-------------------
>SELECT
> CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].
> [FACTSUM] }) ON COLUMNS,
> Hierarchize
> (Filter({ [DIM_SALES].[All].AllMembers }, ([DIM_SALES].
> [DIM_SALES].CurrentMember.Level.Ordinal < [DIM_SALES].
> [SALESPERSON_ID].Ordinal OR Intersect(Ancestors([DIM_SALES].
> [DIM_SALES].CurrentMember, [DIM_SALES].[SALESPERSON_ID]),
> Filter(Generate({ [DIM_SALES].[All].AllMembers }, Ancestors([DIM_SALES].
> [DIM_SALES].CurrentMember, [DIM_SALES].[SALESPERSON_ID])), kupcase
> ([DIM_SALES].[DIM_SALES].CurrentMember.Properties("CAPTION")) =
> kupcase("040347788"))).Count > 0))) ON ROWS
> FROM
> [DEV_MLS_CUBE_V2]
--------------------------------------------

There is not a single WHERE clause generated (I actually never managed that the EG4.2 MDX contains a WHERE clause).
What kind of EG do you have that does that for you?

Anyway, I have done it manually and the suggested WHERE clause worked as
a MDX expression in the management console for the dimension DIM_SALES

Filter( [DIM_SALES].AllMembers, [DIM_Vertrieb].CurrentMember.Name = '040347788' and [DIM_SALES].CurrentMember.Level.Name = '040347788' )

Then I added the cube to a info map without applying any filters and opened that info map
in web report studio to see if it actually works. I logged in as user 040347788 and it worked.
unfortunately it seems to be too restrictive. Do I need to specify that all other Dimensions and members (such
as the period) are allowed to be seen by the user?

WRS Error:
Formula error - Access to member is not allowed - in the "NONEMPTYCROSSJOIN" function

or

Formula error - Access to member is not allowed
(depending which dimension I include in the report)

No Query example given and the user does have access to all other dimension (management console)

Many thanks for your help. Its a real contribution. What you have written should get into the offical SAS documentation because I think it is an usufull way to write a filter expression rather than the simple ones one finds in the standard documentation provided.

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Thu, 03 Feb 2011 09:28:54 GMT

The following MDX eleminated the errors but does not result in anything being shown in Web Report Studio.

>{[DIM_PERIOD].defaultmember}
>{[DIM_KPI].defaultmember}
>{[DIM_SALES].defaultmember}
>Filter( [DIM_SALES].AllMembers, [DIM_SALES].CurrentMember.Name >= '040347788' and [DIM_SALES].CurrentMember.Level.Name = '040347788' )

Removing [DIM_SALES].defaultmember} leads again to Formula error - Access to member is not allowed - in the "NONEMPTYCROSSJOIN" function

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Thu, 03 Feb 2011 14:17:55 GMT

Hi Bob,

It's great to hear that you are starting to get something working now - albeit not yet complete. The MDX filter that I provided as an example before was quite simple. As I mentioned in my blog post, in practice, filters usually need to be more complex to handle the security requirements. I like to start with something simple first to get it working and then add layers of complexity on top testing as I go until I get the results I need. In your case you need to provide access to the levels/members above the level that the user id member is contained in. Down the track if the userid is no longer a leaf you will need to provide access to the descendants of that member too. There are MDX functions to help you with this and you can find out more by reading the SAS 9.2 OLAP Server MDX Guide (http://support.sas.com/documentation/cdl/en/mdxag/59575/HTML/default/titlepage.htm) - if you are not already reading it. You may end up with something that looks like the MDX generated for you by Enterprise Guide. One way to get more examples of the functions in action is to use the point and click facilities in SAS Enterprise Guide and then view the MDX that it generates.

I modified my demo cube so that my DimSalesPerson dimension has levels above and below the user id level, Here is an example permission condition filter I used to enable me to drill down the entire range of the dimension but only those paths that include the member that matches the user's userid (with an assumption that the member will only occur once in the tree):

Ancestors(Head(Filter([DimSalesPerson].AllMembers,[DimSalesPerson].CurrentMember.Level.Name = 'SalesPersonId' and [DimSalesPerson].CurrentMember.Name = 'SUB::SAS.UserId')).Item(0))

So in your case I expect it would be something like this:

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

Please let me know how you go with that variation.

I noticed in the latest MDX filter that you posted that you specified both the member name and level name as 040347788 - this seems a bit odd to me - is the level name not SALESPERSON_ID?

BTW To see the MDX where clause in action in SAS Enterprise Guide, instead of adding a filter, drag and drop a dimension onto the slicer item just above the table and drill down on that slicer to the member that you want.

I am guessing at some point you are also going to want to have filters whereby management level users will be able to see wider subsets of the tree encompassing groups of other users?

Cheers
Paul

Re: Role based security as input paramenter, data read from oracle db on demand

metalray — Thu, 03 Feb 2011 17:01:36 GMT

Hi Paul,

Many thanks for your detailed post again.
Well spotted, my level naming was wrong - I just tried things out because it was not working how I wanted it.
I used the following now (including the part you suggested but extended with the CONDITION statement)

{[DIM_PERIOD].defaultmember}
{[DIM_KPI].defaultmember}
{[DIM_SALES].defaultmember}
Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESPERSON_ID' and [DIM_SALES].CurrentMember.Name = '040347788')).Item(0))

Without the CONDITIONS for the other dimension I get the usual error: Formula error - Access to member is not allowed
I dont see how it can work for you without those.

When I use this MDX filter in WRS (via an Information Map) and I only have the Period and KPI dimension selected as data to display it works
fine but as soon as I add the DIM_SALES dimension the page refreshes and I can see a message tellingm me that for this table no values have been returned.

I have not spotted a slicer item on my OLAP View in EG 4.2 the only way to create a slice is doing a right-click on the cube and selecting "Create Slice".
The following window does not allow a drill down by clicking on a table row.

Your guess is right, at some point I am also going to want to have filters for management level users.

You said that the EG4.2 generated MDX queries might be of use later.
In EG4.2 I have opened the cube, added SALESPERSON_ID to the rows out of the memebr/level selection panel between the left hand process flow
view and the right hand OLAP Query result. This resulted in all the bottom level SALESPERSON_ID's being displayed (around 3500).
When I then click on one and select add SALESPERSON_ID to filter, then specify that the member caption should ="040347788" and confirm it then takes
a long time for EG4.2 to process this step. I leave it running during the night and see tomorrow in hope
that this generated MDX query will be usefull.

Here is th result, its seems the "Generate" was taking the longest, generating a set of all bottom level members:

Filter(Generate({ [DIM_SALES].[SALESPERSON_ID].AllMembers }, Ancestors([DIM_SALES].[DIM_SALES].CurrentMember, [DIM_SALES].[SALESPERSON_ID])), kupcase([DIM_SALES].[DIM_SALES].CurrentMember.Properties("CAPTION")) = kupcase("040347788"))).Count > 0)))

Finally, Paul, I tried it with your query again and I must have made a mistake last time because this time it wors perfectly:

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

Thanks a lot!

Regards,
Bob

Message was edited by: metalray

Re: Role based security as input paramenter, data read from oracle db on demand

PaulHomes — Fri, 04 Feb 2011 10:29:21 GMT

Hi Bob,

Glad to hear you have it working.

I learnt something new today - I hadn't seen before (it looked like an XML comment to me) so I went looking for an explanation. I couldn't find it in any of the SAS docs but eventually tracked it down to a couple of SAS notes.

* SAS Sample 37136: Applying member-level security to a cube dimension that has more than one hierarchy (http://support.sas.com/kb/37/136.html)
* SAS Problem Note 13557: MDX restriction of default member may cause error when viewing cube (http://support.sas.com/kb/13/557.html)

Is this where you found out about it too, or do you have any other documentation references you could share?

The condition thing seems to be required when you are applying member level security to a dimension that has more than 1 hierarchy. My test cube only had 1 hierarchy in the DimSalesPerson dimension so I didn't need to use it. I am guessing that your cube has 2 or more hierarchies in your DIM_SALES dimension - is this right? The 37136 note does make me wonder if you will need to provide filters for each of the hierarchies - if it were me I would include testing to make sure my users are limited no matter which hierarchy is used in the query.

Best of luck with the rest of your member level security MDX work.

Cheers
Paul