SQL Server Best Practices Analyzer CTP

I’ve just downloaded the CTP of the SQL Server Best Practices Analyzer (see Paul Mestemaker’s post here http://blogs.msdn.com/sqlrem/archive/2007/02/21/SQL-2005-BPA-Feb-CTP-released.aspx for more details) and, shock horror, it analyses Analysis Services databases and Integration Services packages too! The advice it gives is quite sensible – things like ‘set up attribute relationships’ and ‘put distinct count measures in their own measure group’ – so it’s definitely worth checking out.

You can download it here:

SP2 is released

You can download it here:
And from the "What’s new in SP2 readme":
For more information about SP2 in the Microsoft OLAP blog by Mosha Pasumansky, see the Additional Resources section on the SQL Server 2005 – Analysis Services page on the Microsoft TechNet Web site.
Mosha, it’s official, you are the new BOL!

Anyway, seriously, as I said on Friday there’s some new stuff in the samples:


The Aggregation Manager is a tool which lets you view, design, edit, and add aggregations based on the information collected in the query log.


The ASTrace utility provides you with the ability to capture an Analysis Services trace and log it into a SQL Server table. The table can then be queried later or read using SQL Server Profiler.


The Analysis Services Upgrade Verification tool lets you compare Multidimensional Expressions (MDX) query results and performance between a Microsoft SQL Server 2000 Analysis Services databases and a Microsoft SQL Server 2005 Analysis Services database. This tools helps you verify that your upgrade from SQL Server 2000 to SQL Server 2005 was successful.

… it looks like there are several new AMO samples. But ASUV – I hadn’t heard about this, but it sounds quite interesting. I’ll have to take a closer look.

Flatland: A Romance of Many Dimensions

After a long, hard day struggling with your cube, what better way to relax than with some multidimensional entertainment? Over the years I’ve had lots of conversations with people in the BI industry about the problems people have with learning to think multidimensionally, and something that has cropped up several times is the book "Flatland: A Romance of Many Dimensions", written over a century ago by Edwin Abbott. It’s a book I’ve been meaning to buy for a while, and a quick browse on Amazon reveals that several other people have written similar books; indeed, as Christian Wade pointed out to me last year, there seems to be a whole internet subculture to do with imagining what life with more or less dimensions would be like, for example:
Anyway, the reason I’m blogging about this now is that Flatland, with a little bit of updating (as is always the way), has been turned into a film:
Seems to have some good reviews too… I wonder if it’ll ever get a release in the UK? They should organise a gala showing at the Microsoft BI Conference!

SP2 Coming Monday?

I’ve heard the rumours it was almost ready, and now according to Russell Christopher, SP2 could be ready for download on Monday:
But then again, I’ve been told that SP2 was ‘coming soon’ since about October… From an AS point of view the thing to look out for will be the new version of the samples, which will include a tool for designing aggregations manually (discussed in the AS2005 Performance Guide) and also a bit of code showing how to run traces.

Microsoft Dynamics CRM Analytics Foundation

Earlier this week Microsoft released something called ‘Microsoft Dynamics CRM Analytics Foundation’:
The name’s a bit of a mouthfull, but basically it’s a shared-source solution (including AS2005 databases) for building BI solutions on top of Microsoft Dynamics CRM. It’s available through Codeplex, and you can find out more about it on the project page:
I guess that there are going to be other similar packs available for other Microsoft products, Dynamics and otherwise, soon. Making it shared source is a nice idea too, although free stuff never quite has the same credibility as something you’ve paid for – not that customers won’t be paying partners to implement and customise all this, I suppose.

Lose those single quotes!

Although they’re no longer necessary in AS2005 MDX and I no longer bother with them, I’ve seen a lot of examples of people still using single quotes in MDX in calculated member and set definitions. Up until recently I thought this was just a matter of taste and that it did no harm to leave them in, but last week I realised that if you do leave them in it makes debugging MDX queries much harder. To illustrate, run the following two MDX queries:

FROM [Adventure Works]


FROM [Adventure Works]

Both return errors. The first gives this error message the single cell returned:
VALUE #Error Query (1, 30) The dimension ‘[BLAH]’ was not found in the cube when the string, [BLAH], was parsed.

but the second gives this error message in the same place:
VALUE #Error The dimension ‘[BLAH]’ was not found in the cube when the string, [BLAH], was parsed.

So you can see if you don’t use single quotes and there’s an error somewhere in your calculated member definitions you get the row and column where the error was found (highlighted in bold); if you do use single quotes you don’t get this useful information. For some of the three or four page queries that I sometimes have to debug this can save a lot of time…

Twelve ‘Next Big Things’ that didn’t happen

On one of my periodic visits to the OLAP Report website I noticed this commentary, which struck such a chord I had to link to it:

I don’t think I’ve seen so many marketing concepts skewered so accurately in one place. Several of the dozen are relevant to current Microsoft product strategy, and while you could argue Nigel & co are unduly cynical I think a little cynicism is necessary to counter the overwhelming amount of hype that the BI software industry generates.

%d bloggers like this: