While Jamie (who I saw through a window today, although I didn’t manage to say hello) is wondering about whether SQL Server Data Services will ever include a cloud-based OLAP engine, Panorama have just announced their own equivalent called Panorama PowerApps:
http://www.panorama.com/blog/?p=111
http://panorama.com/powerapps/

And guess what, it’s queryable through MDX! That means that not only will you be able to query it through Google Apps but also Excel. I’ve signed up to be a beta tester, so I’ll blog more when I have a chance to check it out.

Yet another excellent paper on optimising distinct count measures from the SQLCat team:
http://sqlcat.com/whitepapers/archive/2008/04/17/analysis-services-distinct-count-optimization.aspx

Actually I’m beginning to wonder whether I should be linking to the SQLCat team site – in the same way I never link to Mosha because I assume that everyone who reads my blog reads his too, then I would hope everyone subscribes to the SQLCat team blog as well.

One topic missing is a comparison of the performance of distinct count measures with the technique of using many-to-many dimensions to get the same result that Marco Russo describes in his famous m2m white paper:
http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/language/en-US/Default.aspx

Marco presented on this at PASS Europe and mentioned (which tallies with my experience) that this approach can perform as well as, and sometimes better than, a distinct count measure.

I was just reading this article on TWDI:
http://www.tdwi.org/News/display.aspx?ID=8876

It’s on illuminate and their ‘correlation DBMS’ – not relational, not OLAP, not COP, but a ‘value-based’ system. All very fascinating indeed, although their web site is yet another one of those irritating ones that leaves you with the feeling that some important technical detail and information on where they’re positioning themselves is missing. This entry on their blog:
http://www.ianalyze.net/2008/02/which-tool-do-i-use.html
is a case in point – what is it exactly that illuminate can do that these other platforms can’t? I guess it’s the kind of query that they talk about here:
http://www.ianalyze.net/2008/03/incremental-approach.html
…which is certainly not the kind of thing that OLAP is good at, or even meant to be good at.

I might have completely the wrong end of the stick, but didn’t Microsoft get its hands on something similar when it acquired FAST last year (blogged here)? Is this it:
http://www.fastsearch.com/FAST_Database_Offloading_Solution_Product_Sheet_SJgpx.pdf.file
? I’m sure I remember reading about how FAST could speed up DW-style queries by loading all the data into an index; I’d be interested to hear from anyone who can set me straight on this. If I’m right, then perhaps some of the comments I made when talking about COP databases last year about how they would fit into the Microsoft BI stack would also be relevant here.

BARC, the publishers of the BI Survey, have asked me to publicise a new survey they’re doing on BI Competency Centres:

http://bi-institut.de/web/orga_of_BI/

It should only take a few minutes to complete, and they donate €1 to charity for each participant.

Simon Sabin has a good list of the new features in SQL Server tools for 2008:

http://sqlblogcasts.com/blogs/simons/archive/2008/03/11/SQL-Server-2008—Whats-new-in-the-tools.aspx

Top of the list is the fact that you will be able to set connection string properties when you connect to AS in SQL Management Studio; it’s not there yet, but I guess it’ll appear in CTP7. This means (sniff) I’ll no longer need MDX Sample App any more…

I don’t know why I didn’t think of doing this, but after being prompted by Richard Tkachuk I ran the original query from my post of last week on setting non_empty_behavior for YTD calculations on Katmai CTP6:

with member measures.ytdsales as
sum(
periodstodate([Date].[Calendar].[Calendar Year],[Date].[Calendar].currentmember)
, [Measures].[Internet Sales Amount])
select {[Date].[Calendar].[Month].&[2003]&[12].children} on 0,
non empty
[Customer].[Customer Geography].[Customer].members
*
[Promotion].[Promotion].[Promotion].members
on 1
from [adventure works]
where(measures.ytdsales, [Product].[Subcategory].&[2])

And guess what – it ran in 7 seconds. That’s 7 seconds on a virtual machine on my laptop without that massive extra fact table, and that’s the same time as the optimised query running on AS2005 directly on my laptop with no VM slowing things down; remember that the query above was running in 1 minute 10 seconds on AS2005. That’s impressive.

It was SQLBits II on Saturday, and I just wanted to say thanks to everyone involved for making it yet another massive success: Tony, Simon, Martin, Allan, Darren, Clare Burgess from Microsoft, all the sponsors, all the speakers, all the helpers who volunteered to stuff bumpf into bags on Friday night, type in feedback and other such thankless tasks, and most of all the attendees. As for those of you who signed up, confirmed you were coming and then didn’t (unless you had a good excuse like the trains being messed up), well I’m giving you an extra hard stare right now – if fifteen more of you had turned up then I would have won the attendance sweepstake…

I took a couple of photos on the day but didn’t get around to uploading them onto my laptop before I got on the plane to Switzerland yesterday. When I get home I might post them to the SQLBits group I created on Facebook a few months ago and which has been pretty much dormant ever since; if anyone else has any photos that might be a good place to post them. Martin and Tony certainly took loads of pictures and I’ll see if I can get hold of theirs too.

Next stop DevWeek next week and then PASS Europe in a month’s time. If you going to either of those and see me, say hello!

Over the last few months I’ve been working in secret on a new MDX book, and the time has now come to announce its publication. I think you’ll find I’ve taken a new and very relevant slant on the subject:

Thanks to Denis Gobo for the link to the publisher…

UPDATE: Umm, you do all realise this is a joke, don’t you? Follow the link behind the jpg to the website where I made this cover, it’s guaranteed to keep you amused over lunch. Sorry for any confusion!

One of my new year’s resolutions last year was to learn PerformancePoint, which I’ll be honest I’ve completely failed to do. I mean, I’ve played around with it, went to an airlift and seen more presentations on it than I can shake a stick at but I’ve not done anything serious with it yet; perhaps that’s because only a few projects are actually using it at the moment and in my line of work, I only get called in at the end of a project when things have gone wrong 😉

Anyway, to save my blushes the first time I need to work with it, Nick Barclay sent me a copy of one of the books he co-wrote with Adrian Downes on the subject, "The Rational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007". I liked the book he and Adrian wrote on Business Scorecard Manager and a lot of the things that were good about that book can be repeated for this one too: it’s clear, it’s concise (like all the Rational Guide series), it’s well-written and it tells you just about everything you need to know. I guess no-one can claim to be a complete PerformancePoint guru simply because it’s a new product and best practices only emerge after a year or so of use in a lot of different projects, but Nick and Adrian have clearly been using the betas a lot and have already got some good practical tips to offer (such as the odd RTM bug). All in all, if you’re about to embark on your first PerformancePoint project you’ll probably want this book by your side; oh, and if you want a second opinion on it, Teo Lachev liked it too.

You can buy the book from Amazon UK here. There’s also a companion book on the planning side of PerformancePoint too coming, but I’m not sure when – Nick, Adrian, perhaps you can comment?

It’s getting close to the deadline to submit a session for SQLits II next year. I know what you’re thinking, you’d like to attend but don’t have the time to prepare a presentation. Aren’t you tired of hearing the same old people (eg me) present though? Wouldn’t you like to share your hard-won knowledge with the community? Get your 45 minutes of fame and adoration? Of course you would. So why not go to

http://www.sqlbits.com/information/SessionSubmission.aspx

…and submit something right now? I’d especially like to see some more BI sessions.

BTW, if you’re on Facebook you can join the SQLBits group to stay up-to-date with all the latest news and meet up with other people who’ll be attending.