Hello,

I have been recently been trying to use the Dynamic AS Security method
of using Dimension Security with a Virtual Cube.

I have implemented the MDX statements and created the standard, secure
and virtual cubes.

The main cube we have has 10 dimension and I am attempting to secure
the cube via the Organisation dimension.

The security_fact table is actually a view created from our user table
and the organisation members that they have been assigned to.

However, I am not sure if the cube security is working. Using the
dimension browser I have exposed the Secure measure and viewed it when
selecting other dimension members. What seems to be happening is if
the Dimension does not have an all level and the Org member I select is
one I have rights to then I see the number 1 in the secure column. If
the dimension DOES have an All Level then it only appears at the top
level and does not propogate down.

To (kind of) make this work, I have put the top level dimension members
in my MDX statment to ensure that it is picking up my rights.

Also, it appears that it is not letting me see any members BELOW the
ones I have rights to - only at the current level.

Can anyone provide any guidance in this matter. I would really like to
get this working otherwise I will be having a very long and tiresome
weekend typing in 150 + cube roles and assigning them to members of my
Org dimension

There is sample code and setup instructions here:
http://www.mosha.com/msolap/samples/...20security.zip
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<graeme.ord (AT) gmail (DOT) com> wrote

Dave,

thanks for that - this was the model I based my Security and virtual
cube on. What I am struggling to understand is exactly what should
happen when the security is applied. For example, when I browse data
in the cube and show the "secure" measure, it does display 1 when I
have selected myself as a user (this is using the AS cube browser),
but, if I select different members in other dimensions for an org I
should have rights to then it returns a 0.

Also, I have had to make non-leaf members visible in my org dimension
which does not now necessarily reflect the correct nature of my org
structure but has been a necessity as the cube would not process due to
the fact that users can be assigned to parts of the organisation that
are not leaf members.

I am just wondering whether the amount of dimensions I have in the cube
is causing some issues - especially as the filter MDX only seems to
work if I add all the All level members of dimensions other than org to
make this work.

Thanks,

Graeme

Unfortunately, Dave Wickert's solution only works in a VERY limited, narrow
business application for securing members.

Dimension level security where the user is limited to specific roll-ups is a
different beast.

It involves the concept of Visual Totals, and the AS Server must know which
rollups are to be secured, and the point in the hierarchy where the security
begins.

In order to do this, one must use the DSO, and a custom security tool.

The number of roles should be limited because each Role could have a
particular, even overlapping, subset of the hierarchy allocated for their
perusal.

The security model you are trying to implement should be clear.

For example:

A large number of domain accounts (Between 1 and 2000 accounts),
representing Account Managers.

10 different Overlapping Hierarchies (potentially common levels). There may
be many account managers that belong to the same division, and they may have
common customer accounts that should be visible to their team members and
across teams too.

The only solution is to allocate the specific hierarchy to roles.

Virtual Cubes do not work for this kind of granular security model.

John Smith

<graeme.ord (AT) gmail (DOT) com> wrote

1) As soon as you start to secure non-leaf members, you need to remember
that dimension security works. When you are allowed to see a non-leaf
member, you will automatically get access to all its children. So you must
be careful.

2) Ref: "the cube would not process" -- processing should not come to play
here at all

3) The filter MDX can be a bit complex -- this is one of the reasons why the
virtual cube is my least favorite way to approach this.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<graeme.ord (AT) gmail (DOT) com> wrote

So, I have a situation where the cube sec has to be role based but the
business has a situation where Unit A cannot see Unit B data,
therefore, the security has to be that you can have roles at:

Group
------ Profit Center
---------------- Business Unit
---------------------------Sub Business Unit
-----------------------------------Operational Unit
-------------------------------------------Operational Entity

1 user may be given rights at Business Unit level, whereas others may
only be allowed to see at operational unit level

Given the size of the Org (circa. 3 profit centers, 14 + Business
Units, 43 + sub business units, 100 + Operational Units, 300 +
Operational entities) role based could be a nightmare and cause cube
performance to die!

I thought the dimension security model would be ideal as each user is
assigned a number of roles in the application that controls data entry,
this application access theoretically would tie very nicely to the
dimension security but what seems to be being suggested is that this is
not a viable approach

I am sorry, but I don't see why dimension security wouldn't be a good fit.
As I talk about in the final slides of the material I pointed you to, in
real-life, it isn't uncommon that implement a system with a combination of
approaches. Dynamic security is useful in some circumstances, but using a
combination of fixed roles for low user counts is also an excellent
approach.

Just remember that when a user is in more than one role that the final
allowed set is an UNION of each individual role.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<graeme.ord (AT) gmail (DOT) com> wrote

If the diagram represnts an Organization dimension/hierarchy, and each
user can be mapped to a node in the hierarchy, with access to all its
descendants, then we have implemented similar scenarios using the
virtual cube dynamic dimension security approach. We used materials from
this MS Support Webcast as a guide:

http://support.microsoft.com/default...b;en-us;828343
Analysis Services

Session Summary

Wednesday, September 3, 2003

This Support WebCast session discusses how to implement dynamic
Dimension Security in Microsoft SQL Server 2000 Analysis Services. It
reviews some popular approaches to use during implementation. It also
talks about common issues that may occur and how to resolve them.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***