Vania B


Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.





Re: mdx script context execution

Darren Gosbell


Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.





Re: mdx script context execution

Darren Gosbell

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.







Re: mdx script context execution

Vania B

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)

It'd be great.

have you any examples

Thank you very very much!!!!






Re: mdx script context execution

jwelch

Check out this link for examples: http://www.codeplex.com/ASStoredProcedures




Re: mdx script context execution

Darren Gosbell

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

  • That value would be calculated by a stored procedure.
  • When the stored procedure is deployed it can be configured to run under a specific user account.
  • In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.
  • This would return a cellset and the stored proc would extract the value of the required cell and return that value

While I believe that functionaly this may do what you want, it is highly likely that the performance will be an issue if this type of calculation needs to be done over a lot of cells. So it really depends on your cube and the type of calculation as to whether this approach will be feasible or not.

If you wanted to do something like "report a given business unit's profit as a percentage of the total companies profit" (without disclosing the figure for the total company profit) then this sort of thing may be at a high enough level that the performance hit is not too bad. Although in this simple example someone could easily reverse calcuate the profit for the total company. And this is something you will need to be careful of in this situation - that you are not providing people with enough information to reverse calculation the figures you are trying to hide.






Re: mdx script context execution

Vania B

Thank you very much again.

You say: ¡°

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

¡°

But I wonder if in this way the ¡°poor¡± user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to¡­ In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way

However, I give you an example of what I need to do. Let¡¯s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the ¡°[DIM Mesi].[DIM Mesi].&[01]¡±, ¡°[DIM Mesi].[DIM Mesi].&[02]¡± and the ¡±existing¡± with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I¡¯d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can¡¯t see that value) so that the total is not twice, in case I don¡¯t use VisualTotals.

If possible, I¡¯d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope