Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for September 2007

Contact details

with 3 comments

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 http://www.crossjoin.co.uk/contact.html) 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.

Written by Chris Webb

September 30, 2007 at 10:10 pm

Posted in Off-topic

BI Virtual Conference

leave a comment »

Looks like there’s going to be a ‘BI Virtual Conference’ on Octobr the 4th:
Justa marketing exercise for PerformancePoint of course, but some of the presentations might be interesting.

Written by Chris Webb

September 27, 2007 at 10:31 pm

Posted in Events

One-to-One Attribute Relationships: Forks versus Chains

with 3 comments

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:

forktime

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:

forklattice

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

chaintime

Here’s the accompanying lattice:

chainlattice

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:
http://msdn2.microsoft.com/en-us/library/ms176124.aspx

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(Measures.TTD);
    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)

Written by Chris Webb

September 27, 2007 at 10:17 pm

Posted in Analysis Services

Last Night’s BI Evening

with one comment

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…

Written by Chris Webb

September 27, 2007 at 10:08 pm

Posted in Uncategorized

Details on MS MDM emerge

with one comment

Kirk Haselden has revealed a few details about what’s going to happen with Microsoft’s new MDM stuff on his blog:
As the url above suggests, there’s also a new page on the Microsoft website too:
 

Written by Chris Webb

September 24, 2007 at 10:46 pm

Posted in On the internet

PASS Europe 2008

leave a comment »

After a year off, it looks like there’s going to be a European PASS conference in 2008:
 
I’ll do my best to be there. Thanks for he tip Markus! 

Written by Chris Webb

September 22, 2007 at 9:39 pm

Posted in Events

PerformancePoint launches…

with 3 comments

The press release is here:
http://www.microsoft.com/presspass/press/2007/Sep07/09-19OfficePerformancePointPR.mspx

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

Written by Chris Webb

September 20, 2007 at 11:50 pm

Posted in Uncategorized

Follow

Get every new post delivered to your Inbox.

Join 3,189 other followers