For instructions on creating and registering Analysis Services stored procedures, see the Books Online topic Creating Stored Procedures
For information on using stored procedures in conjunction with association rules data mining to perform social network analysis, see the whitepaper
and download the code
by John C. Hancock.
For several other data mining related stored procs, see Jamie MacLennan's blog
and the sqlserverdatamining.com
- Here is one that's useful for finding the shortest or longest path to a leaf node in a decision tree.
- Here is one that builds the DMX CREATE MINING MODEL statement for an existing model.
- Here is one that calculates the goodness of fit of a regression model.
- Here is one that calculates covariance/correlation matrixes.
- Here is one that creates a data source object.
- Here is one that extracts information from a logistic regression model.
For an example of how to use a config file to store settings for Analysis Services stored procs, see a blog entry
by Teo Lachev.
For some performance tips and other sproc best practices, see a blog entry
by Mosha Pasumansky.
For information about debugging sprocs, see a blog entry
by Darren Gosbell.
For some examples of returning images from a sproc, see Hilmar Buchta's blog here
, and here
. He also has a post about quantiles
and sproc performance.
For an example of using C# to deploy an assembly to Analysis Services using the management API, see this forum thread
. That thread also shows how to deploy a third-party DLL in addition to your assembly.
For an example of a stored procedure which allows you to retrieve members with identical names, see this blog post
by Hilmar Buchta.
If your sproc opens a connection to Analysis Services, be sure to review the Don't Deadlock Yourself
For an example of a stored procedure which merges two datasets into one dataset, see this blog post by François Jehl in French
and translated into English
For a list of best practices on when (and when not) to use stored procedures, see this
blog post by Philip Stephenson based upon Darren Gosbell's list.Inclusion in ASSP: If you would like to see any of the above included in ASSP, please add a new feature request to the Issue Tracker tab or vote for an existing feature request.Commercial SSAS Sprocs
IT-Workplace has a product called Intelligencia Extensions for SSAS
which contains a sproc that draws a sparkline image.