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.
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.
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:
WITH MEMBER MEASURES.TEST AS BLAH
FROM [Adventure Works]
WITH MEMBER MEASURES.TEST AS ‘BLAH’
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…
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.