Creating a SSAS Rowset Action

In the last post (http://blogs.pragmaticworks.com/brian_knight/2009/09/calling-an-external-query-from-mdx.html), I showed you how to install an external assembly for Analysis Services to query a relational data set, whether in SQL Server or any other database like DB2. This enables you to close the loop as a user is analyzing the data to see live real-time data. Now that you have that installed, let’s create an action to open the recordset with a right-click action in Excel or most cube browsers.

To do this example, I’ll be using the AdventureWorks cube, which you can download here: http://msftasprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18652 and the assembly from my last post.

In this example, you’ll see how to navigate from the AdventureWorks cube to the AdventureWorksDW relational database, which will give the user more relational results back from the warehouse. Let’s start by adding a new action to the sample actions already in AdventureWorks as shown in the below image. Name the action whatever you wish and set the Target Type to Cells. You can also set it to an attribute or hierarchy. This option is really based on what you want the user to be able to right-click on to see your action. By setting the option to Cells, a user my right-click on a measure.

For the Action Expression option, use the following code. As you can see, it’s calling the assembly from my last blog, passing in the sample query and a parameter of the product name. Notice that the single-quotes have to be doubled up in some spots to handle the string product name being passed in. This can of course be a stored procedure as well.

"call PWSSASHelper.Query('provider=sqloledb;server=localhost;database=AdventureWorksDW;trusted_connection=yes','SELECT * FROM dbo.DimProduct where EnglishProductName = ''"+[Product].[Product].MemberValue+"''');"

If you want to get a bit more fancy, you can also change the caption (the default caption is the action name). In my case, I wanted to put the product name in the caption by setting the Caption as MDX option to true and using the following code for my caption:

"See All Info About: " + [Product].[Product].MemberValue

Here’s the complete action configuration:

Once complete and deployed, you’ll be able to browse the cube in Excel or in your favorite cube browser with action support and right-click on a measure and see the following caption and drill-through.

.

Once you select the drill through, you’ll be able to see a popup with the query’s data. In Excel, this would’ve opened in a new sheet.