I am naturally interested in MDX stored procs so I tried them out. I have often wondered how they compare to SQL CLR. I hereby commit myself to (trying to) do a few blog posts in this space! Here are some findings regarding when to use them.
It is well known that the strengths of SQL CLR for real-life scenarios are as follows.
- Computationally intensive operations
- Iteration through lists
- Complicated / conditional execution logic
- Leveraging the .NET Framework for string handling, mathematical functions, regular expressions, etc.
What about MDX sprocs? Here is a summary of when I think they would be particularly useful.
- Complex calculations that require iteration. Similarly to TSQL vs. SQL CLR, some custom calculations – e.g. financial – can be pretty much unworkable without iterating a dataset and .NET is good at this. Not to mention the plethora of mathematical functions provided out-of-the-box by the .NET Framework.
- Real-time integration with data outside the cube.
- Dynamic sets (sets for which tuples/members are populated at runtime). This could, for example, be used for a custom security implementation.
- We could also use data outside the cube to manipulate the cube data itself (not just which members are displayed). For example, if we have a measure called Market Value and one of its inputs is current stock price(s), even the 5 minute latency (as would probably be the case with proactive caching) would be unacceptable - the numbers could be out by £ millions. Also, if Market Value were the only measure in the cube with a real-time requirement, pro-active caching could be a sledgehammer to crack a nut. Implementing this with an MDX sproc would be a very simple solution.
Chris Webb has blogged about MDX sprocs recently. Here are some other good ideas:
- Overcoming the limitations of drillthrough by accessing the relational data in the sproc. Sounds promising – watch this space.
- Leveraging the .NET Framework for string handling functions etc. For example, this could be used to filter dimension members.
I’ve also seen examples of using MDX sprocs to administer AS through AMO, but I haven’t really seen a compelling reason to not just do it directly through AMO.
Admittedly MDX sprocs are a bit of a niche area, but I think they have huge potential.
Any other ideas for usefulness would be welcome. I’m sure there are other clever ways of leveraging the biggest ever class library (the .NET Framework BCL). I guess we just need to be a little creative!