More thoughts on stored procedures
I know I promised more content on stored procedures a few months ago, but, well, you know… Once I found out that you couldn’t actually run an MDX query from them (unless you used the ADOMD.Net client library and opened a connection from within the sproc, which seems a pretty silly thing to do) and can’t do stuff like dynamically create calculated members or named sets with them, then I realised I couldn’t implement any of my cool ideas.
Anyway, I have been thinking about them again quite a lot recently. For example, I had some contact with Mark Mrachek about this post on his blog about drillthough:
I had already been contacted by someone having the same problem, and thinking some more about we came up with a possible solution using an action which calls an AS sproc similar to the one I posted a while ago to find the currentmember on every dimension (see http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!586.entry), and which in turn passes the keys of each member through to a SQL sproc to do the drillthrough. Mark has promised to blog about the full solution when he’s had time to implement it.
Similarly, today on Charlie Maitland’s blog he talks about how to filter dimension members using wildcards (see http://charliem.wordpress.com/2006/04/26/wild-card-mdx-searching/). It seems to me that this would be a prime candidate for a sproc – there is a lot of string functionality in .NET that could be very useful in MDX; another example would be the way that you couldn’t use the VBA REPLACE function in MDX either.
Finally, there are some things which are very complex in MDX which could be simplified no end if they were put into a sproc. Two examples would be the discussion on this blog last year about tuning YTD-style calculations (see http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry and http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry) and problems such as finding the count of members on an attribute which have the same first letter in their name as the currentmember on that attribute (see http://groups.google.co.uk/group/comp.databases.olap/msg/75fb29730b96f23b for how to do it).
So, to get to the point, I was thinking that solving individual problems and posting code up on this blog was not the best way to go. I’m not the world’s greatest .NET coder by any means, and rather than just being able to see the code it would be much better if there was one dll which people could download to get at all this useful stuff. Surely it would be much better if everyone who was interested could collaborate on producing this dll, perhaps using something like a gotdotnet community (http://www.gotdotnet.com/workspaces/docs/about.aspx), so it would be much easier to add functionality and fix bugs. What does everyone think about this? Is there someone out there with a solid coding background who would be willing to help?