My first Analysis Services stored procedure

Several people have asked me about Analysis Services stored procedures recently and somewhat shamefully I’ve had to admit to them that I hadn’t actually done anything with them yet. So this week I’ve vowed to dust off my C# (I can’t believe how much I’ve forgotten), write some stored procs and post the code here. I’ve got some ideas on where they can be useful and hopefully I’ll be able to implement what I need.
The first problem I tried to solve was this: how do you return the name of the currentmember on all dimensions in your cube in a query? I did a series of postings on this last year and although it’s possible to do in pure MDX it isn’t elegant:
In AS2005 where the currentmember on one attribute hierarchy can change depending on the currentmember on another on the same dimension, knowing what the currentmember is on all your hierarchies can be useful for debugging. Luckily, it’s extremely easy to create a stored procedure to return this information. Here are the steps:
1) Create a new .NET dll project, making sure the resulting assembly is called FindCurrentMember.
2) Add a reference to Microsoft.AnalysisServices.AdomdServer
3) Copy the following code into your project:

using System;





using Microsoft.AnalysisServices.AdomdServer;




public class MDXDebuggingUtilities


public static string FindCurrentMember()


string output="";

foreach(Dimension d in Context.CurrentCube.Dimensions)


foreach (Hierarchy h in d.AttributeHierarchies)


output +=

"Dimension: " + d.Caption + " Hierarchy: " + h.Caption + " CurrentMember: " + h.CurrentMember.Caption + " *** ";



return output;





4) Build the dll
5) Go to SQL Management Studio, connect to your server, expand a database (we’ll use Adventure Works DW here) and right click on Assemblies
6) Select ‘New Assembly…’  and add ‘FindCurrentMember.dll’
7) Run the following query to check it’s working – beware that because Adventure Works is a pretty complex cube there are an awful lot of currentmembers to look at! It might be easier to double-click on the cell you want to look at so that the Properties window appears, click copy and then paste everything into notepad so you can look at the values.






[Customer].[Customer Geography].[Postal Code].&[2450]&[Coffs Harbour].


ON 1


[Adventure Works]

Obviously there are a lot of improvements/optimisations to be made in this stored proc (for example, it would be good to be able to specify just one dimension to look at the currentmembers on) but I thought I’d post it up as a simple example of using Context and the Dimension and Hierarchy objects anyway.

6 thoughts on “My first Analysis Services stored procedure

  1. No, there\’s no particular reason why I added it specifically to Adventure Works. You\’re right, if it was being used as a general debugging tool then it would make more send to add it to the server assemblies.

  2. Hi Chris
    Just wanted to point out, that step 5 is somewhat abnormal here. Usually, you manage your whole project in single place, therefore you would add assembly in BI Dev Studio, not in SQL Management Studio, since otherwise next time you update your project it will disappear.
    And probably it will be easier to call this proc from HTML action, and build a nice HTML inside, such that one won\’t need to scroll the enormious string.

  3. Hi chris,
    This is very good information. Where would be a good place to look for begining MDX, I am a beginner with respect to MDX. I would like to learn about using MDX on Analysis Services Cubes. 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s