Redundant Attribute Relationship Health Warning!

My ex-colleague Jon might spend far too much time pseudonymously posting silly comments on blogs, but he deserves an extra helping of chocolate mousse for finding this problem to do with redundant attribute relationships – this is something everyone designing AS2005 cubes needs to be aware of!
Imagine you had a Geography dimension with three attributes: City (the key attribute), State and Country, where each Country has many States, and each State has many Cities. Now you probably already know that if you have built your dimension with the wizard, the chances are that your dimension will have two relationships pre-built – between City and State and City and Country – and that you need to change this so that there are relationships between City and State and State and Country in order to get optimal performance and to help aggregation design. AS2005 understands transitive relationships between attrbutes, and so even though there are just two relationships built in your dimension it knows that because City is related to State and State is related to Country, there is a transitive relationship between City and Country.
BUT, did you know that bad things can happen if you actually build the relationship between City and Country in your dimension? Before Jon found out about this, I had understood that the only effect was that having this redundant relationship might increase the size of the dimension and increase processing times. However, it turns out there are two other potentially serious side-effects:
1) MDX Script Scopes may include unexpected extra cells. So, for example, if you’re assigning values to a subcube in your MDX Script (and most of the calculations created by wizards in BI Dev Studio do this) then you may see incorrect results with the redundant attribute relationships in place.
2) Querying fact data may ignore some slices or return Nulls on certain related attributes when the measure group has granularity on an attribute different from the key attribute.
Apparently what’s happening is that, using our example, the redundant relationship between City to Country would cause AS to ignore the legitimate relationship between State and Country. Even more confusingly, if you were to create a user hierachy from Country to State to City then the incorrect behaviour would go away.
So the moral of all this is to be extra-careful when designing your dimensions and not to let any redundant attribute relations remain in there. This problem won’t be fixed in SP1 but there will be a warning in the dimension editor UI when redundant relationships are detected, though, which should make more people aware of it.
Apart from Jon, thanks also to Matt Carroll and Marius Dumitru on the AS dev team for taking the time to explain what’s going on here to me.

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

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:
[Date].[Day of Week].MEMBERS ON 1
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…
%d bloggers like this: