I have dimensional security set up for some of my users - when they access
the data via a pivot table for the dimension I have secured the pivot table
surpresses the members they don't have access to, however for other
dimensions the members that they do not have access to due to a relationship
built in the cube show up as #NA.

How do I get these members to surpress as if they contained no data -
because after all to that user there is no data they can access. This is
particularly a problem for our sales team that have access by region to the
data, the customer list tries to list every single customer, not just those
in their region.

This system is going live in a week so any assistance you can offer soon
would be great - pivots are one of the main ways they will access the data.
amk

Assuming that you're using AS 2005, can you represent the relation
between region and customer as strictly hierarchical? In that case, you
should be able to add region as an attribute of the customer dimension,
with the appropriate attribute relationship to customer, and secure it
using the same rule that you're currently using. This should cause the
correct subset of customers to be secured:

http://sqljunkies.com/WebLog/mosha/a.../10/10599.aspx
For example, if we secure specific member on attribute Year -
automatically all months of that year, all weeks of that year, all
quarters of that year etc - get also secured. This is similar to
Analysis Services 2000 - when you secure the parent - all of its
children get secured, but it only worked in single hierarchy. In
Analysis Services 2005 - all hierarchies: Year-Quarter-Month, Year-Week,
Year-Month etc will get secured accordingly.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Unfortunately there are other slices of the data that are not directly
relatable in a hierarchy to the detail the users want to see - for example
Business group which is a rollup of items, they are related to the customer
through the orderline details. So if I secure a business group - the customer
list still shows every single customer an order was made by, but only
populates numbers in those with an order related to an item in that users
assigned business group.

So I am still challenged I think to find a way to remove the unwanted members.
amk

"Deepak Puri" wrote:

This sounds more like an application data than dimension security issue
- ie. there happens to be no data in the fact table for certain
combinations of dimension selections. If this is an OWC pivot table,
won't clearing the "Always Display - Empty Rows/Columns" option work?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I am not sure that we are using web components - we aren't doing a seperate
installation for this - they simply connect to the warehouse using the pivot
table services (query) component. I am personally not very familiar with how
web compenents would work. Should we do an install in the client? I could not
find options like the one you mentioned in our excel pivot tables.

thank you for your help so far - if you think web components is something we
missed your insight would help.
amk

"Deepak Puri" wrote:

Well, with Excel pivot tables, empty rows and columns should be
suppressed by default - one issue that could arise is with calculated
measures that aren't empty even when the underlying base measures are.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I see what you are saying - my measure is not calculated. Where is the
Analysis Services Dimensional security evaluated? I assume that every refresh
would have to validate the login against the MDX call used for the Pivot
table and restrict the data presented. If that is the case then could it be
that excel does not suppress the value rather masks it? Because these fields
that are "blocked" to the user actually show up with #NA in them and when you
checkthe pivot table setting "For Error values, show:" the #NA's go away and
replaced with blank - so the pivot isn't seeing a zero - rather an error. Any
thoughts on why?

amk

"Deepak Puri" wrote:

One thing you didn't mention is whether you enabled Visual Totals on the
attribute(s) that you're securing - assuming that they're not
parent-child (where Visual Totals may not work as expected). Maybe this
could help your issue?

http://msdn2.microsoft.com/en-us/lib...6(SQL.90).aspx
VisualTotals

The VisualTotals permission for dimension data defines how data is
aggregated for attributes. This is an MDX expression returning True or
False. If VisualTotals is False, data is aggregated on all members of
attributes of the dimension regardless of whether they are visible to
members of the role. If VisualTotals is True, data is aggregated only
for those members of the granularity attribute of the dimension to which
the role has read access. For example, if Customer Name is the
granularity attribute and VisualTotals is set to True for the City
attribute, each city will be the aggregation of data for the customers
to which the role has read access.

The default setting is False.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I enabled visual totals on all my dimensional security - it was the only way
to ensure that the totals didn't include data the user didn't have access to.
These dimensions are not parent - child either

"Deepak Puri" wrote:

If the pivot is getting errors back in the result-set, could you set up
SQL Profiler on the AS 2005 server, to capture the MDX query and test it
in Management Studio?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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