Saturday, 23 October 2010

Analysis Services Stored Procedure Best Practices

Whilst answering questions on the Microsoft Analysis Services forum the other day, I came across a question regarding recursive MDX. At first read I thought that an Analysis Services stored procedure might be quicker than recursive MDX, which was an incorrect assumption.

However, in the discussion Darren Gosbell posted a list of best practices with regards Analysis Services stored procedures that I hadn't seen before, so I figured I would post them here too.

So, the "best practices" for Analysis Services stored procedures (ASSP) are apparently as follows:
  • for administrative functions
  • for static sets, scopes, LHS of assignment and other early-binding things
  • It is usually OK to use sprocs for query axes
  • It is usually OK to use sprocs if they don’t need to get cell data (but only look at metadata and members)
  • inside calculations if there is good NEB that can be defined on the calculation
  • inside calculations without NEB if sproc is executed over small amount of cells, i.e. inside WHERE clause etc.

The thread where you can see all of this is here:


  1. I would have missed this post if it wasn't for your blog... Thanks! :)

  2. Yeah, it seems to be a hidden piece of information which is why I put it on my blog, so I can find it again. :-)

  3. This list originally came from Mosha. It was either on his blog or part of the discussions that we had when we created