Create parameterised queries and functions on the server - by Christopher Webb

Status : 


Sign in
to vote
ID 473694 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 7/10/2009 5:08:19 AM
Access Restriction Public


It would be really cool if we could declare parameterised MDX Select statements on the server, and then allow client tools to expose them as pre-canned queries, similar to what can be done with stored procedures in the relational world. This would mean that as developers we could write complex queries that most end users would never be able to create themselves (even if their client tools were capable of building them). Similarly, it would be useful if we could declare custom MDX functions in the MDX Script: for example, you could declare a complex set operation as a new function, then use it in your own calculations and also expose it to users so that they could use it in their own client tools.
Sign in to post a comment.
Posted by Microsoft on 1/6/2010 at 10:51 AM
Thank you for your suggestion. We are investigating this for a future release of SQL Server.

The Analysis Services team
Posted by Christopher Webb on 7/30/2009 at 1:45 PM
Apart from the performance overhead of using a .NET sproc, the need to write .NET code is a big barrier for a lot of database/BI people. You need to have the appropriate version of Visual Studio, be comfortable writing code, and it takes a lot longer etc etc. Personally I think it would be a lot easier and more convenient to be able to do all this in BIDS in pure MDX.

Also, I seem to remember you can't return a cellset from a sproc at the moment?

This is one of my most-wanted features so I hope you implement it.
Posted by Microsoft on 7/28/2009 at 6:36 PM
Thanks a lot Chris for your idea.
We will take a look at this.
But would you be able to acheve similar with Analysis Services stored procedures? Is there difference or functionality missing in them so you wouldnt be able to use in your scenario?
Edward Melomed.