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

[metalray]

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.

[metalray]

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

[PaulHomes]

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

[metalray]

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

[PaulHomes]

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