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?
I have solid coding background and I am willing to help.
Ho ho, understatement of the year from Mosha. I can\’t think of anyone better to have on board. Any other volunteers?
Great idea, Chris. Keep us posted. I\’ll contribute my only current stored proc idea… retrieving the date the cube was last processed which is useful for putting an "data fresh as of" date on a report:
Imports Microsoft.AnalysisServices.AdomdServerImports Microsoft.AnalysisServices \’reference to AMO
Public Class StoredProcedures Public Shared Function GetLastProcessedDate() As Date Dim oServer As New Server() oServer.Connect("Data Source=(local)") GetLastProcessedDate = oServer.Databases.GetByName(Context.CurrentDatabaseName).Cubes.GetByName(Context.CurrentCube.Name).LastProcessed oServer.Disconnect() End FunctionEnd Class
Note: I first tried "Return Context.CurrentCube.LastProcessed" but it didn\’t work because of a bug:
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=1b671017-0dac-4aaf-af74-39e7dfa25db9
Hi Greg.
I have been playing around with this for quite some time now and I am soo happy to finally see someone with a solution.
Where would I have to store this piece of code and how will I execute and actually see the date the cube was last processed?
Kind regards
Malene
Strangely enough, I demoed a sproc to do exactly this at SQLConnections in Nice only yesterday. It\’s actually much easier than you think because you don\’t need to use AMO, you can get the same information from the Context.CurrentCube object. The following code should do the trick:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdServer;
namespace SPDemo
{
public sealed class MyProc
{
public static string LP()
{
return Context.CurrentCube.LastProcessed.ToString();
}
}
}
Chris, sorry to contradict you, but it doesn\’t work as you would expect. Process the cube. Note the date it processed. Run that stored proc. It returns the right date. Now restart SSAS. Rerun the stored proc. You don\’t get the date it was last processed but the date the service restarted. Grumble. I reported the bug at that link I provided below… feel free to vote for it or mark it as validated if you\’re seeing the same thing as me.
Hi Chris,
Long time no speak! Glad to hear about CrossJoin consulting.
I\’d be glad to work on some MDX sprocs with you. I\’ve been maxed out consulting recently, but should be able to start freeing up some time soon – espcially in June. I\’ve even started blogging again. I blogged some material on MDX sprocs and I intend to continue in the same vein – possibly comparing with SQL CLR.
Christian
Sorry furmangg, that will teach me for not reading your comment properly! And nice to hear from you again too, Christian.
I\’ll wait a few more days for anyone else to reply who\’s interested, then we can take this offline. Furmangg – can you send me your email address please?
Great Idea – I would love to help, count me. 🙂
Hi Chris,
You know this is too tempting for me not to want to chip in … in particular I\’d be interested in resurrecting the stored proc caching ideas you and I were considering some months ago, since initial tests showed there was real potential here. If we\’ve got Mosha on-board, then this is a perfect opportunity to grill him on the details of exactly what guarantees we do have regarding loading/unloading of sproc assemblies, sharing of sproc classes across multiple connections etc etc 😉
Count me in.
Jon