SQL2005 SP1 CTP Released

The CTP of SP1 for SQL2005 is now out:
There’s also an updated version of BOL. No idea what fixes exactly have been included though because the link to the KB article in the readme file is dead! However, for Analysis Services, I’ve heard about one potentially interesting change with solve order – see the following thread on MSDN forums for the full story:
I wonder if the ability to cache the results of MDX Script calculations is also going to be there?
 
UPDATE: The KB article is now up and there are lots of AS fixes in there. Here’s the link:

Announcing Crossjoin Consulting

After two years in my current job I’ve decided to move on and set myself up an independent consultant. I’ve set up my own company and if you go to the website:
…you can see the formal write-up of what I want to be doing (cool domain name isn’t it?); it’s basically short engagements working with Analysis Services and MDX. My thinking is that there are lots of people out there who are working with Microsoft BI tools and for the most part being successful with them, but who might from time-to-time run into problems writing difficult calculated members or designing their cube and who need someone with my specialised skillset to help out for a few days or weeks. This is the kind of thing I enjoy doing most – in fact, it’s what I’ve been doing for free for people who post questions on the newsgroups, leave comments here or who email me direct for the past few years. I also want to add a bit of variety to my work: apart from the consulting, I want to do some training (I’m going to be an associate mentor with Solid Quality Learning), speak at conferences (see you at SQL Connections in Nice), and maybe develop for sale some Microsoft BI-based packaged solutions in conjunction with my friends in the UK BI mafia. I’d also be interested in working with MDX on other OLAP servers that support it – I’m sure there can’t be many Essbase or SAP BW consultants out there that know MDX, for instance.
 
Since I’m based in the UK that’s where I’d prefer to do most of my work, but I’m willing to travel anywhere necessary. However I’m going to experiment with attractively discounted rates for customers who will let me work from home: I know that in the BI world the data we work with is a precious commodity that you can’t simply email to all and sundry, but on the other hand I feel that some problems can be solved without ever seeing the data, sometimes without seeing the cube, so I’d like to see if I can make that model work.
 
Usually at this point in the "I’ve got a new job" blog entry the writer apologises for the fact that he’s going to be much busier in future and won’t be able to blog as much as he used to be able to do. Not true for me: I intend to blog more. I’ll be building into my schedule days of sitting at home, doing R&D, reading, blogging and answering questions on newsgroups. There are three reasons for this: 1) if I was away working all week, every week my wife would kill me, 2) it’s the only way I’ll be able to keep up with all the new developments in the world of Microsoft BI, and 3) what better way is there than blogging to reach my potential customer base?
 
So if you’ve got a problem and no-one else can help, and if I’m available, maybe you can hire me. Drop me a mail on info@crossjoin.co.uk to find out!
 
 

Analysis Services, Visio and Project

While doing some browsing this morning I had a look for any new Microsoft BI-related blogs that I hadn’t so far seen (see updated blogroll – the newest are at the bottom – if you have a relevant blog I don’t list please let me know). I found some good stuff, but one there was one thing I thought deserved a special mention: Eugene Asahara on his ‘KPI Cause and Effect Graph’
 
I also came across the Project 12 blog which, while not really to do with BI, has a post on Analysis Service integration in Project 12:
Nice to see that other product teams inside Microsoft are adding BI functionality; I guess other ISVs who use SQL Server as their relational source must be planning on doing the same thing.
 

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