Friday, February 15, 2013 5:40 AM
We have a cube developed in SSAS 2012 sp1 (Multi dimensional). Access to specific dimension is restricted using a .Net Assembly. It has extended stored procedure which calls the backend stored procedure to list all the values the users can access for the dimension.
Using profiler I can see the stored procedure is called when the user is logged in. But it seems like it is calling the stored procedure 10 - 15 times. I tired my self and unwanted calls are happening. We only want the backend SP to be called once.
How to debug and analyze such issues? Can anyone help?
Friday, February 15, 2013 2:08 PMModerator
Stored procedures can be called on the server or from client application. In either case, stored procedures always run on the server, either the context of the server or of a database. There are no special permissions required to execute a stored procedure. Once a stored procedure is added by an assembly to the server or database context, any user can execute the stored procedure as long as the role for the user permits the actions performed by the stored procedure. Please refer to details about it: http://msdn.microsoft.com/en-us/library/ms176113(v=sql.110).aspx
If you have any feedback on our support, please click here
TechNet Community Support
Sunday, February 17, 2013 10:28 PM
Thanks for your reply. I understand the mechanism of the SP call. I wanted to understand why the SP is called multiple times. If we establish dimension security using the SP, I would expect SSAS to call it one time to get the list of allowed members and restrict the data. How to debug why the multiple calls are happening?