SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

A few weeks ago I did a post introduced the ExecuteSQL .net stored procedure for SSAS. Chris Webb asked if this function can be called from Excel 2007 when it is set this up as a rowset action and I figured that this would make a good topic for a blog post. So the following screen shots show how you would go about setting up such an action. As a quick example I cheated a bit and set up an Rowset action that calls the sp_who2 system stored procedure. This way I did not have a depedancy on any particular database. You don't have to use a stored procedure, you can use any sort of SQL command that returns a set of rows into a rowset action.

Below is how I setup the action:

image 

When you right click on a cell in a pivot table you will see the following:

image

And clicking on the "Rowset Action" creates another sheet that looks like this:

image

Since I posted about the ExecuteSQL function I have made a slight change to the code I checked into the ASStoredProcedure project on codeplex. I have now moved it into a separate "SQLQuery" project so that it compiles to it's own .dll file and does not have to be deployed with the same impersonation mode as the main ASSP assembly.

 

Print | posted on Thursday, July 2, 2009 7:42 AM

Comments on this post

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
this is cool. Any info on parameters? like in excel, what your current member is and passing that in as a param to your sql or proc?
Left by Steve on Sep 18, 2009 11:17 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Hi Steve, there is nothing really built-in to actions to support this. You may be able to extend my proc and combine it with the technique from the FindCurrentMember() or FindCurrentTuple() functions in ASSP, but I'm not sure if the context of the action is visible from a proc. Or you could always concatenate expressions in the form of <dimension>.<hierarchy>.CurrentMember.UniqueName or something similar in the action - it's not as clean, but it should work.

Darren
Left by Darren Gosbell on Sep 18, 2009 9:26 PM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
I tried the FindCurrentMember and ran into this issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249956&wa=wsignin1.0

:(

I will keep plugging away at it
Left by Steve on Sep 22, 2009 1:34 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
I think i might just create a custom .net assembly with both Query and FindCurrentMember, and avoid the issue I commented about above. Will let you know what I find..
Left by Steve on Sep 22, 2009 2:02 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
it seems that Context.CurrentCube doesnt work (returns null) when you are calling from an MDX expression (even MSDN says so). So from the rowset, calling the query stored proc works, but trying to get current member fails because we dont have a current cube context.
Left by Steve on Sep 22, 2009 4:18 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Yes, you cannot call from one assembly to another, they are isolated from each other.

You may find that if you don't wrap the entire action expression in a string that you may be able to reference the current context.

There are some new drillthrough functions in ASSP (see http://asstoredprocedures.codeplex.com/Wiki/View.aspx?title=Drillthrough) they are not part of the current 1.2 release, but you can get them by grabbing the latest source code. I would have thought that you could maybe extend the ExecuteDrillthroughAndFixColumns() function to execute your own SQL rather than a drillthrough query.

Left by Darren Gosbell on Sep 22, 2009 8:40 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Hi,
I am not able to see the rowset action in the analytical Grid of Performancepoint server. I however can see it in the Cube Browser of SSAS. Please help!!!
Left by Saugat on Oct 27, 2009 5:42 PM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
@Saugat: According to this blog post by the PerformancePoint team, they only support Url, Drillthrough and Report actions in Analytical Grids.
http://blogs.msdn.com/performancepoint/archive/2008/01/17/analysis-services-actions-and-drillthrough-capabilities-supported-in-performancepoint.aspx

Darren
Left by Darren Gosbell on Oct 27, 2009 5:53 PM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Hi Steve,

Did you manage to progess your call outs using the current cube context?

I want to callout to a relational datasource and pass the current cube context for filtering purposes.

But not making much progess...
Left by sqlconsumer.net on Feb 08, 2010 9:49 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
@sqlconsumer, Steve may not see this as it has been a while since he posted this comment.

Have you looked at the Drillthrough related functions in ASSP? see: http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

The CurrentCellAttributes() function might be of use in your situation.
Left by Darren Gosbell on Feb 09, 2010 7:37 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Cool! Thanks a lot for the example. It will help me a lot. I often use Excel.
Left by Shared FilesSE on Nov 09, 2010 1:01 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Here are this and some other articles on SSAS Actions: http://ssas-wiki.com/w/Articles#Actions
Left by Sam Kane on Feb 25, 2011 1:57 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
This discussion is very near to the solution to what I am looking for.

I am able to call ExecuteSQL from the Rowset Action Expression. But when I break the string to introduce [dimension].[Hierarchy].CurrentMember, I never see it in Excel again.

Someone solved this?
Left by Alejandro Graf on Sep 20, 2013 4:51 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
@Alejandro - Actions will not display if they have any sort of syntax error, so there is probably an issue with the way you've introduced the .CurrentMember reference.
Left by Darren Gosbell on Sep 21, 2013 9:40 AM

# re: SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Requesting Gravatar...
Thanks Darren. Sure there was.
Left by Alejandro Graf on Sep 22, 2013 4:18 AM

Your comment:

 (will show your gravatar)