Here is my problem:

I have a hierarchical dimension called “Property Of Secured”. Every record
in the fact table is linked to one of the nodes in the dimension. The
dimension levels are not fixed, one branch can have more levels then other
branches. For example:
Corporate X
Company 1
Department 1
Department 2
Company 2

Company 3
Department 1

The records in the fact table can be linked to any node, not just the leaves.

I want the security to be configured so that a user can see his node and
subnodes. Totals should be visible for his nodes only.

I want the security model to be dynamic and I’ve tried Mosha’s excellent
example at http://www.mosha.com/msolap/articles...llsecurity.htm. I
configured the cell data security like this:
IIF(Instr([Property of Secured].[Property of
Secured].CurrentMember.Properties('Users' ),’SYNTAX\AXHA’)>0,1,0)
The property “Users” contains user id for allowed users.

The user SYNTAX\AXHA is a member of Company 1 (he should only see Company 1,
Department 1 and Department 2). The measure is correct for this user but the
total for Company 1 is N/A. Why?

Thanks in advance
Axel

Hi Axel,

Based on your problem description, you might be better off configuring
dynamic dimension security on the hierarchical dimension, with Visual
Totals enabled to only reflect that user's data. Cell security doesn't
seem to suit your scenario.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak!

Thank you for your quick answer!

I’ve tried your suggestion of solution in the following way:

I removed the cell security settings and configured the dimension data
security. I made the configuration on the cube dimension (TestCube.Property
Of Secured).To make it easier to test, I only used the Basic tab to select
allowed nodes in the tree (no filter on username as described in my first
question).

The allowed member set in the Advanced tab is as follow:
{[Property of Secured].[Property of Secured].&[110]}

I also checked the ‘Enable Visual Totals’ checkbox.

When I try to browse the cube in Visual Studio (after processing) I get the
following error message:

“Error occured retreiving child nodes: The ‘Property Of Secured’ attribute
in the ‘Property Of Secured‘dimension has a generated dimension security
expression that is not valid.”

When I try to “Reconnect” I get this error message:

“Error HRESULT E_FAIL has been returned from a call to a COM component.
(Microsoft Visual Studio)



------------------------------

Program Location:

at Microsoft.Office.Interop.Owc11.PivotView.get_Field Sets()

at Microsoft.AnalysisServices.Browse.CubeBrowser.Tran slatePivotTable()

at Microsoft.AnalysisServices.Browse.CubeBrowser.Upda tePivotTable(Boolean
translate)

at Microsoft.AnalysisServices.Browse.CubeBrowser.Upda teAll(Boolean
translate)

at Microsoft.AnalysisServices.Browse.CubeBrowser.Reco nnectCanFail()

at
Microsoft.AnalysisServices.Browse.CubeBrowser.Supp ortFunctionWhichCanFail(FunctionWhichCanFail function)”



If I apply the same security settings on the shared dimension
(ASDatabase.Property Of Secured) instead, then I get no errors when browsing
the cube, but there are no restrictions for the role. All nodes are allowed
when I test the role.

What am I doing wrong?

Thanks!

/Axel

Hi Axel,


Not sure what the problem is; but since it sounds like you're using AS
2005, it would be worth checking the version. There has been a relevant
fix recently:

http://support.microsoft.com/kb/918222
...
SQL Bug number Description

410 Dimension security does not support visual totals on a parent
attribute that is in a parent-child dimension.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thank you Deepak, now it works.
/Axel

"Deepak Puri" wrote: