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

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.
 
 

Making Session-Level MDX Script Assignments

You’re probably aware of the fun things you can do with MDX Scripts on your cube, such as making assignments, but you may not know that you can do the same things within a session from a client tool. Here’s how to do it…
 
1) Open MDX Sample Application. Unfortunately I’m not joking, you can’t use SQLMS because it relies on setting a connection string property and SQLMS doesn’t support this for AS2005. Hohum. 
 
2) For the purposes of this demo we’re going to use the Adventure Works database. So, edit the text in the ‘Server’ textbox, so that it reads:
MyServerName; Cube=Adventure Works
The new ‘Cube’ connection string property allows you to specify the cube in the database you want your statements to apply to.
 
3) Run the following query and look at the values returned:
SELECT MEASURES.[INTERNET SALES AMOUNT] ON 0,
[Date].[Day of Week].MEMBERS ON 1
FROM [ADVENTURE WORKS]
 
4) Open a new query and run the following:
(MEASURES.[INTERNET SALES AMOUNT], [Date].[Day of Week].&[1])=0;
 
5) Rerun the query in step #3. You’ll see that the value for the member [1] is now zero, and the All Periods value has changed accordingly.
 
Apparently this is how Visual Studio works when you use the MDX Debugger to step through each calculation in your MDX Script.
 
I was talking to (excuse the name-dropping) Reed Jacobson at PASS Europe the other week about doing financial consolidation in AS2005 and the limitations of non-leaf writeback, and he mentioned that it would be interesting to explore using this functionality for this particular problem; a similar idea came up in a ng thread I was involved in earlier in the year. You would let your users do their "writebacks" as session-level assignments and then when they were ready to commit them, push them up to the server and append them to the cube’s MDX Script. Definitely worth further exploration…

Configuring Analysis Services 2005 for Kerberos

Microsoft, BI and Search

It’s inevitable, when you get a whole bunch of new functionality as we have with SQL2005, that you start thinking of the new types of applications that become possible. One of the things I’ve been thinking about for a while is how you could take the results of an RSS feed or a search engine search, do text mining on the results and build a cube to analyse what comes back. Quite an interesting idea, I think, and I know plenty of other people have been thinking along the same lines too, eg
And it’s not just in the Microsoft world that these ideas are cropping up. For example, only today I saw a reference to a (non-Microsoft) OLAP solution which built cubes from the results of text mining:
 
Anyway, on a different note, one of the fun things about blogging is all the rumours and snippets of information about new solutions coming soon, most of which I’m not really at liberty to discuss (not that I know much anyway). You get to put these snippets, rumours and other stuff you read on the web and put them together in a 1+1=3 operation… Here, for example, is a link that Jon-who-sits-next-to-me just sent which he saw on Slashdot:
How can Microsoft beat Google in the search game? There are some interesting hints on the second page of this article, for example:

He said that Microsoft’s goal — but not its initial offering — would go beyond finding URLs and instead focus in on the specific information sought by Internet users.

"Generally these days what you get back is URLs, and based upon research 50 percent of the time you do a search you don’t get the URL you’re looking for," he said.

Holloway said that the promise of Microsoft’s search capability is to dig down.

For example, he said, potential home-buyers might find a group of houses in the price range and with the precise amenities they are seeking.

Or a surfer might find a restaurant with the kind of menu a diner wants in a particular geographic area.

 
Hmm, is it me or is there a potential BI angle here? Dig down == drill down, perhaps? Slice, dice and analyse your resultset rather than just get a flat list of links? I wonder… 
 
UPDATE: Jon, bless his heart, has come up with another interesting link on this topic:
Don’t you just love wild speculation? The whole Origami thing is so last week… 
 
UPDATE#2: Now this could just be me reading way too much into something, but here’s another relevant link:
There’s a coincidence here that’s too good to be true…

RSInteract

I had a good time at the PASS European conference last week – had a few drinks, met a lot of people, and learnt a lot too. One of the sponsors of the event was a new company who have a product for Reporting Services that I hadn’t heard of:
What their product does is offer a user-friendly way of creating Reporting Services reports. At this point you’re probably saying, hold on – isn’t that what Report Builder does? Well, yes, and even though in their marketing materials they go to great lengths to say that they aren’t competing with Report Builder (a great example of the ‘big lie’ theory applied) anyone with half a brain can see that they are. Which, of course, might be a big problem if Report Builder didn’t suck… well, maybe that’s harsh, but I’ve yet to meet anyone who actually likes it. Its big problem is its confusing UI, which is in turn linked to RB’s ability to create queries which span multiple tables – it’s not easy to convey table relationships, which fields it makes sense to display next to each other etc. Bob SQL Reporting Services blog has some good detail on this:
 
Having seen some demos of it – although not actually used it – RSInteract has a slick AJAX-y UI, quite smart-looking and clear, so I’d put it that down in its favour. It achieves this through not being anywhere near as ambitious as Report Builder – you can only write reports which are based on one table, view or stored procedure as I understand it. On one hand this is quite limiting, and it does push work back to the IT guys to make sure all the data you want is available in that table/view/sp, but if you genuinely do want a tool that non-technical users can use then I think that’s a necessary sacrifice because it removes a lot of complexity.
One downer, at least from my point of view, is that I understand that their Analysis Services support isn’t ready yet, although it’s coming soon. Overall though, definitely worth checking out.

Configuring and Connecting Analysis Services 2005 Webcast

Excel 12 BI Video on Channel 9

Even though I’m sure many of you subscribe to the Excel 12 blog (a masterclass in how to disseminate technical information in a product’s beta phase – other product teams in MS would do well to follow its example) I thought I’d relay the news that a new Excel 12 BI video is available on Channel 9:
I’ve not watched it yet but it should be interesting. I also noticed today that a whole bunch of introductory BI videos are available here:
Again, I’ve only fast forwarded one or two of these so I can’t comment, but they’re worth a look for the creepy female android voice-over alone. It made me think of 2001 and what a conversation with speech-enabled BI system might be like:

Microstrategy Can Now Generate MDX

I saw this announcement a while ago and somehow didn’t blog about it, but it’s an important one: Microstrategy can now generate MDX as well as SQL, and this means it can connect to OLAP servers such as Analysis Services and Essbase (at least those were the only ones mentioned – what about other platforms that support MDX? I wonder if they’ve been tested). See the press release here:
This can only be a good thing for the MDX language and XMLA as a standard, although from Microstrategy’s point of view it’s probably intended to encourage shops with a mixture of OLAP tools in place using anything other than Microstrategy (such as Office 12, perhaps?) as the client tool for all of them.

MDX Solutions Sample Chapter

You can now download chapter one of the second edition of ‘MDX Solutions’ (which I’m a co-author of), along with the table of contents and the index, here:
The book itself is coming very soon – place your order now! Although there are several other good Analysis Services 2005 books out there now, this is the only one which covers the new MDX features and functions in depth.
 
UPDATE: the sample chapter has been changed to chapter 6, which is rather more meaty in terms of content. And I believe that the book is now published! George at least has a copy, and I guess I’ll be getting one soon. Hurrah!