dimension security with visual totals but not for the all member

Question

Hi,

I want to use a dynamic security for my organisation dimension.

Company -> Division -> Unit

the visual total must be used at the division & unit levels

but NOT at the company level.

and the company level MUST always display the grand total even when the user filter for only 1 specific division.

the dimension security works, the user see only the authorized members.

the visual total works, the total for the division = totals of the authorized units only.

but the company level always display the visual total of the divisions, and I'm not able to bypass this to always display the grand total "without any security applied".

what can I do??? thanks.

Answer 1

Hi Willgart1,

Thank you for your question. 
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Thanks,
Eilee

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

Hi Willgart1, what you are trying to accomplish cannot be done without compromising security.

When a user is not allowed to see data for a Division through dimension security, the data is restricted in the whole cube. The Role user no longer has the grand total at the Company level.

If you decide not use dimension data security, and use calculation script to overwrite cell values, then there is security risk, the users can easily pass through the calculation script and look at detail fact level data.

You can create custom application and retrieve your grand totals via query from source database and then combine it with the result received using MDX with the security applied.

Best regards, Vlad.

Answer 3

its the solution I identify too.

but I have an issue, if a subcube is used, I'm not able to get the current members of the other dimension.

Answer 4

Did you included that dimension in your subcube?

Answer 5

could be included based on how the user access it.

I want to identify the limits of the security + presentation control we can have for all the type of access:

display in row / column

filtering 1 member in the where clause

filtering using a subcube

filtering and displaying using the visualtotals command

Answer 6

I would suggest to read some articles as a starting point of your research:

http://www.mosha.com/msolap/articles/yukondimensionsecurity.htm

http://blogs.msdn.com/b/azazr/archive/2008/08/15/dynamic-security-in-ssas-cube.aspx

etc.

Regards, Vlad.