I have the need to restrict users of a pivot table from being able to drill
down past a certail level conditionally. For example, I have a cube that has
salary information for a group of professionals, and the users want to view
this data in a pivot table, and slice it by location, by individual
professions, etc., and view the average salaries, hours worked per week,
bonuses, etc. However, as the users browse the data, I don't want to display
these averages if there are less than 10 underlying data points. So, if a
user is looking for sea captains in North Dakota, and there are only 2 of
them, the cells should be displayed as NA (or something similar).

After reviewing the security model for SSAS 2005, I think cell level
security is the way to implement this, but I haven't found any examples of
how to do it. If anyone could point me in the right direction I would
appretiate it.

Thanks,

--
Steve Loper
Amadeus Consulting

Hi Steve,

A cautionary note on the performance implications of cell security was
raised in this recent post - don't know how that would compare with
other approaches. When you are counting the underlying data points, is
it just the number of non-empty leaf-level cells in the selected slice
of the cube?

http://forums.microsoft.com/MSDN/Sho...01831&SiteID=1
Mosha Pasumansky

Can you please give more details about when AS2005 by default disables
cell security ? Creating cell security, even as trivial as constant 1
may have very bad effect on performance, since there are many important
optimizations which are disabled in the presence of cell security...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for the warning on the performance.

I'm not sure if "leaf-level cells" is the same as what I'm talking about.
The cube will be based on fact tables that are individual responses to a
survey, and what I'm trying to make sure is that I don't provide anyone
access to aggregate data that is an aggregate of less than 10 individual
responses.

--
Steve Loper
Amadeus Consulting


"Deepak Puri" wrote:

Sounds like the criteria is that the number of fact records underlying
the current cell - each measure group can have its own "count" measure
for this. For example, in the Adventure Works cube, I configured cell
security based on the fact count of the Sales Summary measure group,
which blocks access to cells with less than 100 fact rows:

[Measures].[Transaction Count] >= 100


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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