Hi,

1. We are using Microsoft Analysis Services 2000 for BI solutions to one of
our customer
2. They have extensive security requirements for about 700 cube roles. They
also need 200 concurrent users.
3. We have used OLAP cube based security and created cube roles and assigned
the dimension security using analysis services.
4. We have tested the solution for 20 cube roles and three concurrent users.
It worked fine for that.
5. We have to apply the dimension security for 3 dimensions, and each
dimension having 3 levels of hierarchy.
6. We also need to enable Visual Totals. The subtotal should indicate the
total of only the visible rows.
7. For each cube role, we have created one ADSI user. We can’t escape from
700 ADSI users, since we have one to one mapping from 700 users to 700 roles.
We are connecting with the ADSI user with HTTP URL to Analysis services
database.


Questions

1. Whether Microsoft analysis services can handle 700 cube roles, security
and 200 concurrent users?
2. What kind of license issues will arise for creating 700 cube roles ? (
Considering Point 7 )


--
Thanks and Regards,

Shanmugavel

1) At 700 roles you will see some impact when running Analysis Manager. It
will slowdown considerably. The issue is that it is written in VB and VB
objects must be fully instantiated before DSO can manipulate them -- DSO is
the API which Analysis Manager uses under the covers to update your server.
How much of an impact varies from customer to customer; situation to
situation. My recommendation would be for you to prototype it. You don't
need 700 ADSI users to do this.. just 700 roles. The actual number of NT
accounts doesn't matter at all. You might look at using DSO/XML as a way to
create a database/cube with 700 roles in it. Just generate the XML, edit it
appropriately to create your roles, change the database name and create it
from the revised XML. DSO/XML can be found here:
http://www.microsoft.com/downloads/d...DisplayLang=en

If you find that you can't live with the manability of Analysis Manager at
this level, drop me a line and I can send you a "packet" I have on how to
configure dynamic security. Dynamic security has a single role, and maps the
USERNAME to a set of allow members. Its pros are that it only takes a single
role; its cons are that it requires considerable management structures and
administrative steps over and beyond what Analysis Services provides. If you
are a member of SQL PASS, (http://www.sqlpass.org) look at their archive of
last year's summit in Seattle. I did a presentation on dynamic security
there.

2) http access is a SQL Server 2000 Enterprise Edition feature.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Shanmugavel" <shanmugavel (AT) rsi (DOT) ramco.com> wrote

I would also point out that it that for every role , you could end up with a
copy of the dimension you are trying to secure in memory.

If your dimension is big you could run into performance issues.

Mark


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

This is technically true, but in practice *not* a common situation at all.
There are many circumstances which have to come together for replicas to
have a major impact on memory. See the discussion of "replica dimensions" in
the Analysis Service Performance and Operations Guides at:
http://www.microsoft.com/sql/evaluat...bianalysis.asp
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"mark hill" <mark (AT) mark-hill (DOT) com> wrote