Interesting set of posts from Dejan Sarka on writing stored procedures for AS2005:
The focus is on data mining but the code also includes some useful AMO examples too. I’ve seen plenty of stored proc examples for data mining but very few for the olap side of AS2005 for some reason. My colleague Jon Axon has been promising me a guest post for this blog on the subject but won’t finish his writeup until this ng question is answered; in the meantime and in the absence of any useful information in BOL (no surprise there), there are very few other AS2005 stored proc examples out there. Mosha did a brief blog entry and several books on my book list deal with it too, such as ‘Data Mining with SQL Server 2005’, ‘MDX Solutions’ 2E and ‘Applied Microsoft Analysis Services 2005’.
I like how Dejan diffirentiated between functions and procedures – this is really right way to look at it. And it explains why you see much more examples of stored procedures for data mining rather then for OLAP. The main scenario for OLAP is to use function extensions in MDX (either in calculations or in dimension security or in actions etc) – which is not very widely common, since MDX is a complete language over cube – you usually need external functions to either access data which external to the cube, and with UDM the idea is that you will have this rarely, as all the data should be inside UDM, or when you need some special string/date/etc manipulations or some special statistical computations etc. With data mining, on the other hand – you would use procedures, not functions in order to retrive data (return rowset) in one format or another – and for them this is much more common scenario.