Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

My first Analysis Services stored procedure

with 6 comments

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

System.Collections.Generic;

using

System.Text;

using Microsoft.AnalysisServices.AdomdServer;

namespace

MDXDebuggingUtilities

{

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.
 

WITH

MEMBER MEASURES.TEST AS

FINDCURRENTMEMBER.FINDCURRENTMEMBER()

SELECT

MEASURES.TEST ON 0,

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

CHILDREN

ON 1

FROM

[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.
 
 

Written by Chris Webb

March 13, 2006 at 1:32 pm

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  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.

    Chris

    March 14, 2006 at 12:29 pm

  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.
     
    Mosha.

    Mosha

    March 14, 2006 at 3:21 pm

  3. Thanks Mosha, just goes to prove how little work I\’ve done with stored procedures!

    Chris

    March 14, 2006 at 3:38 pm

  4. 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. 

    Ramdas

    March 20, 2006 at 2:45 pm

  5. Check out my book list – most general Analysis Services books contain a chapter on MDX, although "Fast Track" (AS2K only) and "MDX Solutions" are the only dedicated MDX books:

    http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!322.entry

    Bill Pearson has also written a very good series of articles on MDX available here:

    http://www.databasejournal.com/article.php/1459531

    Chris

    March 20, 2006 at 9:56 pm

  6. […] of the leading SSAS cube gurus, said he was late to the game back in 2006, in a blog post titled “My First Analysis Services Stored Procedure.” How’s this for late in the game: I wasn’t even aware that CLR stored procedures could be […]


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 3,219 other followers

%d bloggers like this: