Contact details

A few months ago Live Spaces introduced a new feature which has allowed people to send messages via the blogs it hosts. Lots of people contact me in this way to ask me questions etc, and that’s fine – but at least 30% of messages I get I can’t reply to because of how the person sending the message has set up their Windows Live Profile. So, if you’d like to get in touch with me please email me direct (you can find my email address at instead! Peter, if you’re reading…
OK, after having received my latest message I can’t reply to, I’m just going to switch off this functionality. It’s a bit stupid of the Live Spaces team to let people send messages that can’t be replied to.

One-to-One Attribute Relationships: Forks versus Chains

It’s a fairly common occurrence to have several attributes in a dimension describing the same thing: products can have long and short descriptions, for example, or you might want to display dates in different formats, or show the full names of states or just their abbreviations. AS doesn’t give you any functionality to specifically handle this – something I’ve heard from people who’ve used other OLAP tools is that it would be great to set up ‘aliases’ for attributes, rather like how you can set up translations – so you just have to design it into your dimension. One thing I’ve noticed, however, is that when you’re designing attribute relationships to cater for this scenario it’s tempting to do so in a particular way that leads to problems later on; at least I’ve done this in the past and seen several other people do the same thing, so I thought it was worth blogging about. 

Here’s an example Time dimension based on the Adventure Works relational data:


We’ve got a simple Year-Quarter-Month-Date structure, but we want to see the same structure once with a short description (eg ‘2004’ for the year) and once with a long description (eg ‘Calendar Year 2004’). There are two natural user hierarchies that reflect these two choices, but notice the way the attribute relationships are designed: they take no account of the fact that there’s a one-to-one relationship between the two sets of attributes. If you use BIDS Helper’s excellent ‘Visualise Attribute Lattice’ functionality you can see the relationships make a fork shape:


What’s wrong with this? Well, rather than answer that question, let’s look at my preferred alternative:


Here’s the accompanying lattice:


You can see that instead of a fork shape we’ve now got a long chain, and the one-to-one relationships I mentioned earlier are now in place; remember that this doesn’t affect the way the dimension appears to the end user at all. Incidentally, you might wonder like I did if the Cardinality property of a relationship should be changed from ‘Many’ to ‘One’ for one-to-one relationships: it’s probably worth doing but according to BOL it doesn’t have any effect at the moment:

The advantages of this design are as follows:

  • More efficient autoexists. In the fork approach, if you crossjoin Year with Month Long Desc AS will have to resolve that relationship through the Date attribute; in the chain approach because there’s a direct relationship between the two working out which members on Month Long Desc exist with Year is going to be faster. You’ll probably only notice this on fairly large dimensions though. UPDATE – see Mosha’s comment below, this isn’t correct.
  • In general, I reckon that the aggregation design wizard will produce better designs with the chain approach. Again I’m not sure how much difference there will really be, but when I ran the wizard on a test cube containing just my fork dimension  it came up with three aggregations at Month/Quarter Long Desc; Month Long Desc/Quarter; and Year. When I did the same thing on a test cube containing just my chain dimension I got four aggregations at Month; Quarter; Year; and All. In the latter case, of course, the Calendar Long Desc hierarchy can benefit from aggregations built on the non-Long Desc attributes, and overall the aggregation coverage seems better. Results, however, may well vary in the real world…
  • Scoping calculations becomes much, much easier with the chain approach rather than the fork approach. Imagine that you want to implement something like a Total-to-Date calculation, which sums up a measure from the beginning of Time to the currentmember on Time. Naturally you want it to work on both your user hierarchies, but you’ll find that with the fork approach it becomes fiendishly difficult to get your scoping correct – it’s a lot harder than you think, and in fact I’ve not come up with a satisfactory solution. With the chain approach however there’s a really elegant solution: because of strong hierarchies, every member you select on the Calendar hierarchy automatically forces the currentmember on the Calendar Long Desc hierarchy to the equivalent: so, selecting ‘2004’ on Calendar changes the currentmember on Calendar Long Desc to ‘Calendar Year 2004’. This means you can scope all your calculations on the Calendar Long Desc user hierarchy and they’ll automatically work on the Calendar hierarchy; for example, here’s how to do the TTD calc:

Create Member CurrentCube.Measures.TTD as null;

    Scope([ForkTime].[Calendar].Members, [ForkTime].[Calendar Long Desc].members);
        This=sum({null:[ForkTime].[Calendar].currentmember}, [Measures].[Sales Amount]);
    End Scope;
End Scope;

(Thanks are due to Richard Tkachuk for answering some emails on these issues)

Last Night’s BI Evening

Just a quick post to say thanks to everyone who attended last night’s BI evening in London. I thought it went really well – we had some good discussions springing from Mark and Suranjan’s presentations. I’m very grateful to Vaughan, Sandra and Sanjay of IMGroup for hosting us and providing so much beer and pizza.

I think I’ll try to organise the next one around December when more details on Katmai should have emerged. Since the last few have been in London, perhaps we should go back to TVP to avoid any accusations of being too London-centric…

PerformancePoint launches…

The press release is here:

…but there hardly seems to have been the kind of firestorm of enthusiasm I would have expected surrounding something like this. Perhaps I read the wrong blogs, perhaps everyone’s at PASS, perhaps everyone’s been playing with the CTPs so long it hardly seems news that it’s finally released.

SQLCAT sessions at PASS

Conferences, conferences, conferences… so many to choose from, and most people (me included) can only attend one a year. I spent my money on attending the BI conference in Seattle and so decided not to go to PASS; naturally when Mosha announced his MDX session I was kicking myself and considering splurging a few grand just to see him, although it’s a good thing I didn’t. But if you’re on your way to PASS and looking for some good content then I’ve been asked to direct your attention to the presentations the SQLCAT team are doing:

Various PerformancePoint Information

Things are warming up in advance of the release of PerformancePoint, I see…

First of all, there are some launch events in New York and London:
I’ve registered for the London event, so say hello if you’re going! I see it’s at Vinopolis, so hopefully the drinks will be good; pity it’s on a Tuesday when Borough Market is closed.

If you’re trying to find out more about PerformancePoint, Microsoft have clearly done a deal with the folks at the OLAP Report (why not make my day and buy a subscription?) and made their writeup on it available as a free download from the Microsoft site:
As you’d expect from Nigel and co it’s pretty even-handed and not afraid to make the odd criticism where necessary.

Books are also starting to appear. I see Nick Barclay has announced two sequels to the excellent BSM book he and Adrian Downes wrote from last year covering the monitoring/analytics and planning sides of things:
This made me wonder what other books might be in the pipeline. A quick look on Amazon revealed:

"Microsoft Performance Management: with Microsoft Office PerformancePoint Server 2007" by Joey Fitts and Bruno Aziza

"Business Intelligence with Microsoft Office PerformancePoint 2007" by Craig Utley

"Administrator’s Guide to Microsoft Office 2007 Servers" by Peter Bruzesse

%d bloggers like this: