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 ASFINDCURRENTMEMBER.FINDCURRENTMEMBER()
SELECT
MEASURES.TEST ON 0,[Customer].[Customer Geography].[Postal Code].&[2450]&[Coffs Harbour].
CHILDRENON
1FROM
[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.
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.
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.
Thanks Mosha, just goes to prove how little work I\’ve done with stored procedures!
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.
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