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: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9feb1ec0-d5f4-4b8c-9d7c-0195d25d7adc