IDC Report on the BI Market

Courtesy of Sam Batterman, I’ve just come across the following IDC report on the BI market which is available as a free download from Microsoft here:
I’m sure this is destined to be quoted in thousands of Microsoft BI sales pitches over the next year or so (it’ll make a nice change from relying on the OLAP Report all the time); while the point that Microsoft had much larger growth than any of its major competitors in the BI space in the last year is the one that’s going to get the most attention, there’s a lot of other interesting stuff in there too. The theme that larger vendors like Microsoft are going to be more successful over the next few years than pure-plays like Business Objects and Cognos is echoed in an entry on Andy Hayler’s blog today:

Alternative Calculated Member Definition Syntax

Now that BOL is being overhauled my list of things that it misses out is getting shorter and shorter…
Anyway, did you know that there are two syntaxes you can use to define calculated members in your MDX Script? There’s good old CREATE MEMBER, which Visual Studio itself uses, and there’s also the more concise CREATE syntax. Here’s the BNF:

CREATE  [HIDDEN] [<parent>.]<name> = <expression>

Nothing special about it really; just note that if [<parent>] is missing it creates the member on the Measures dimension, and if you need to specify properties such as FORMAT_STRING etc for the calc then you need to either use assignments or edit the DDL of the MDX Script.
The only person I’ve ever seen use this syntax is Mosha in a few of his blog posts. I don’t know why it was introduced apart from the fact that is is more concise; I do know that in one instance it behaves differently from CREATE MEMBER. Take the following MDX Script an put it onto the Adventure Works cube:

–an example of the old syntax



AS 1,


= 1 ;

–an example of the new syntax


[New Style] = 1;



([Date].[Day Name].&[1]);




SCOPE([Date].[Day Name].&[2]);





AS 1,


= 1 ;


If you run the following query:


{[Measures].[Old Style], [Measures].[New Style], [Measures].[Old Style 2]} on 0,

[Date].[Day Name].members on 1

from [adventure works]


You’ll see that the cell for [Old Style 2] and [Sunday] doesn’t contain the value 100, whereas the cell for [Old Style 2] and [Monday] does. When I found out about this I was told that it was a bug, but was also the result of mixing old style calculated member definitions with MDX Script assignments, and that if you were going to be doing assignments in your cube then you should always use the new style calculated member definition syntax. So perhaps it is a good idea to use the new style syntax all the time – although I know in my case, old habits will die hard.

User Hierarchies and Attribute Hierarchies

There’s been an interesting discussion on the MSDN Analysis Services Forum recently about the interaction of user hierarchies and attribute hierarchies in the following two threads:
Basically the problem is to do with what happens when you have an attribute hierarchy and a user hierarchy which is based on that attribute hierarchy, and you include the two in the same MDX query – the behaviour when you put one of them in the WHERE clause and one on a visible axis is different from when both are included on a visible axis and regardless of whether what’s happening is by design or not (and I’m not sure, but I suspect it is) it’s certainly the case that users are going to be very confused by what they see. This is one of those issues that you can’t believe you’ve never seen before.
For me this raises the whole question of whether it’s good design to only expose attribute hierarchies to users, or to only expose user hierarchies, or to expose a mixture of both. Up to now I would have said that it didn’t matter what you did but now I’m not so sure – perhaps it is better to only expose a given attribute either in its attribute hierarchy or in a user hierarchy, but never in both. But what happens when you want to expose the same attribute twice in two user hierarchies? Hmm, some more testing/thinking needed on this topic… in the meantime, I’d be interested in hearing everyone’s thoughts on this too…
UPDATE: as per David’s comment below, it looks like this will be fixed in SP2.

July BOL and Samples Update

You can download the new Books Online here:
…and the new samples (which includes an updated version of ascmd) here:
BOL was an absolute shambles, at least as far as AS went, at RTM. Credit to Microsoft for making such an effort to turn it around and provide such regular updates.

So, farewell then Office Web Components

I heard a few months ago that Office Web Components were not going to be included in Office 2007 and wouldn’t be developed any further; here at last is the official confirmation from the Excel 2007 blog:
We’re meant to use Excel Services or Excel instead – and I’m sure that you’ll probably agree that neither of these are ideal (unless you’ve drunk the Office 2007 Kool-aid) replacements for scenarios where people have been using OWC to connect to Analysis Services. For instance, how would you replace OWC in BI Development Studio with either Excel or Excel Services? What we really need are a set of .Net pivot table controls similar to CellsetGrid or the Dundas equivalents. I feel a Connect coming on…
UPDATE: I just read the following interview with Alex Payne on

I quote: "[So one of] the things we’re going to do is make the ProClarity objects be able to be used by more people than ever before….When I look at things like decomposition trees and perspective and other things they have in their arsenal, we’re going to use that stuff to help us go deeper. " Perhaps we’ll see the Proclarity controls positioned as the OWC/AS replacement?

Book Review: The Microsoft Data Warehouse Toolkit, by Joy Mundy and Warren Thornthwaite

At long last I’ve got round to reviewing the last big SQL2005 BI book sitting on my bookshelf: "The Microsoft Data Warehouse Toolkit" by Joy Mundy and Warren Thornthwaite. It’s another very positive review too; although I should declare that I got the book as a freebie, hopefully you’ll believe me when I say I’m not biased by this and if anyone cares to send me a copy of a rubbish book on this topic I’ll be only too happy to slag it off in public!
Let me start by saying a word about the positioning of this book. If you’ve already got other Kimball Group classics like ‘The Data Warehouse Toolkit’ you may be worried about the overlap here; similarly if you’ve got a book like Teo Lachev’s ‘Applied Analysis Services’ you may be thinking that you don’t need another one like it. In my opinion ‘The Microsoft Data Warehouse Toolkit’ sits squarely between the two camps: it’s all about how you apply Kimball methodology to a data warehouse/BI project using the Microsoft platform, and while there is a certain amount of shared ground with the two books I’ve just mentioned I think there’s more than enough valuable information in here that you won’t find anywhere else to make this a worthwhile purchase. It means you no longer need to do as much work joining the dots between how Kimball et al tell you to design your system and what Lachev et al say you can actually do with the tools you’ve got at your disposal.
Let me give you two examples of the kind of issues it deals with. The chapter on real-time data has the most level-headed discussion of this subject that I’ve read, telling you what it means, when you do really need real-time data and when you don’t, as well as telling you how to design a real-time system and making some important technical points about the strengths and weaknesses of Analysis Services. Similarly there’s a chapter on the unsexy and usually neglected topic of operations and maintenance, telling you things like what you need to monitor (disk space, usage etc) and roughly how you need to go about doing all this. Again all the important technical points are made but there’s not too much technical detail – a good balance is struck between this and the higher-level design aspects. This also makes it as good a read for project managers as it is for architects and developers.
Another good thing about the book is the way it is structured. Although you can read each chapter on its own out of context, the book discusses issues in the same order as you’d encounter them in a project. So the first chapter covers defining business requirements, and we then move on to designing the dimensional model, building the relational data warehouse, ETL, Analysis Services, Reporting Services and so on. This means that the project manager or anyone new to BI has a clear list of the tasks that need to be undertaken on this kind of project and can plan ahead more effectively, and serves as an important checklist for people like me to tend to get carried away with the more interesting jobs to the detriment of the duller stuff. The breadth too is impressive, and since no-one can be an expert in every part of the SQL2005 toolset it’s useful to have a reference which can help fill the gaps in your knowledge.
If there’s one criticism I could make (I always try to make at least one) it’s that it’s almost too early in the lifecycle of SQL2005 to be able to write authoritatively on even high-level design. It’s not something that could have been avoided though: I learnt when co-writing ‘MDX Solutions’ that books written about new software have sales cycles, and that if you don’t get your book out within a few months of the release of the software you’re writing about then booksellers won’t place big orders for it, however good it is, so as an author you can’t afford to wait a year or so until you feel you know the product completely. Take the example of the recent about-turn on cubes whether you should design one cube with multiple measure groups or multiple cubes linked together that Teo blogged about recently here. This is a fundamental design decision and on P322 the authors state that "The best practice in AS2005 is to define a single cube for a database", which is exactly what I would have said up to a few weeks ago but which now seems to be wrong. The point here is that no-one could have known about this early enough to put it in a book – these things only emerge after months or years of experience with real implementations. Joy Mundy is clearly well ahead of me and just about everyone else on this issue though, as Deepak pointed out to me recently that in her recent webcast "Designing a Scalable Data Warehouse/ Business Intelligence System" that one of her bullets reads "Create several smaller cubes with related measure groups, rather than one big cube per database". Perhaps the advantage of publishing early is that it gives you the chance of writing a second edition at some point in the future.
But I digress. The vast amount of experience that the authors have got in designing BI systems is apparent throughout this book and I can wholeheartedly recommend it. The book’s websites can be found here if you want to find out more:
…and the second site in particular contains some useful links and downloads which are worth checking out even if you don’t buy the book.
If you’re interested in other SQL2005 BI books, then check out my book list here:
The only book on there that isn’t published and that I really want to read is "Microsoft SQL Server Analysis Services 2005" by Irina Gorbach and various other members of the dev team – I’ve heard it’s got good information on the internals of AS2005 that isn’t available anywhere else.

SQL2005 Upgrade Technical Resource Guide

I knew a while ago that some of my colleagues at Solid Quality Learning had created a guide on upgrading to SQL2005; it was only when I got round to looking at it that I realised that it had a large and useful section on upgrading from AS2K to AS2005. You can download it here:

Business Intelligence Architecture and Design Guide

A number of other bloggers out there have already mentioned this, but I thought I’d post the link just in case anyone missed it: the Patterns and Practices group at Microsoft have just released the final draft of the ‘Business Intelligence Architecture and Design Guide’. You can download it here:
I’ve skimmed through the section on OLAP and it’s a bit of a mixed bag. There are some interesting points made but nothing new; it tends to degenerate into "use <insert feature name> to be able to do <insert what feature is used for>", which, to be honest, is not the level of detail I want in a guide like this. I’m holding out for the 2005 version of the Analysis Services Performanc Guide which I understand is in the works – that should contain some really useful information…