SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Reposted from Chris Webb's blog with the author's permission.

I was chatting to a friend of mine a few days ago, and the conversation turned to Microsoft's bizarre decision to make two big BI-related announcements (about Mobile BI and GeoFlow) at the Sharepoint Partner Conference and not at PASS the week before. I'd been content to write this off as an anomaly but he put it to me that it was significant: he thought it was yet more evidence that Microsoft is abandoning 'corporate' BI and that it is shifting its focus to self-service BI, so that BI is positioned as a feature of Office and not of SQL Server.

My first response was that this was a ridiculous idea, and that there was no way Microsoft would do something so eye-poppingly, mind-bogglingly stupid as to abandon corporate BI - after all, there's a massive, well-established partner and customer community based around these tools. I personally don’t think it would ever happen and I don’t see any evidence of it happening. My friend then reminded me that the Proclarity acquisition was a great example of Microsoft making an eye-poppingly, mind-bogglingly stupid BI-related decision in the past and that it was perfectly capable of making another similar mistake in the future, especially when Office BI and SQL Server BI are fighting over territory. That forced me to come up with some better arguments about why Microsoft should not, and hopefully would not, ever abandon corporate, SQL Server BI in favour of an exclusively Office-BI approach. Some of these might seem blindingly obvious, and it might seem strange that I'm taking the time to even write them down, but conversations like this make me think that the time has come when corporate BI does need to justify its continued existence.

• From a purely technical point-of-view, while most BI Pros have been convinced that the kind of self-service BI that PowerPivot and Excel 2013 enables is important, it's never going to be a complete replacement for corporate BI. PowerPivot might be useful in scenarios where power users want to build their own models but the vast majority of users, even very sophisticated users, are not interested in or capable of doing this. This is where BI Pros and SSAS are still needed: centralised models (whether built in SSAS Tabular or Multidimensional) give users the ability to run ad hoc queries and build their own reports without needing to know how to model the data they use.
• Even when self-service BI tools are used it's widely accepted (even by Rob Collie) that you'll only get good results if you have clean, well-modelled data - and that usually means some kind of data warehouse. Building a data warehouse is something that you need BI Pros for, and BI Pros need corporate BI tools like SSIS to do this. Self-service BI isn't about power users working in isolation, it's really about power users working more closely with BI Pros and sharing some of their workload.
• Despite all the excitement around data visualisation and self-service, the majority of BI work is still about running scheduled, web-based or printed reports and sending them out to a large user base who don't have the time or know-how to query an SSAS cube via a PivotTable, let alone build a PowerPivot model. Microsoft talks about bringing BI to the masses - well, this is what the masses want for their BI most of the time, however unsexy it might seem. This is of course what SSRS is great for and this is why SSRS is by far the most widely used of Microsoft's corporate BI tools; you just can't do the same things with Excel and Sharepoint yet.
• Apart from the technical arguments about why corporate BI tools are still important, there's another reason why Microsoft needs BI Pros: we're their sales force. One of the ways in which Microsoft is completely different from most other technology companies is that it doesn't have a large sales force of its own, and instead relies on partners to do its selling and implementation for it. To a certain extent Microsoft software sells itself and gets implemented by internal IT departments, but in a lot of cases, especially with BI, it still needs to be actively 'sold' to customers. The BI Partner community have, for the last ten years or so, been making a very good living out of selling and implementing Microsoft's corporate BI tools but I don't think they could make a similar amount of money from purely self-service BI projects. This is because selling and installing Office in general and Sharepoint in particular is something that BI partners don't always have expertise in (there's a whole different partner community for that), and if self-service BI is all about letting the power users do everything themselves then where is the opportunity to sell lots of consultancy and SQL Server licenses? If partners can't make money doing this from Microsoft software they might instead turn to other BI vendors; I've seen some evidence of this happening recently. And then there'll be nobody to tell the Microsoft BI story to customers, however compelling it might be.

These are just a few of the possible reasons why corporate BI is still necessary; I know there are many others and I'd be interested to hear what you have to say on the matter by leaving a comment. As I said, I think it’s important to rehearse these arguments to counter the impression that some people clearly have about Microsoft’s direction.

To be clear, I'm not saying that it should be an either/or choice between self-service/Office BI and corporate/SQL Server BI, I'm saying that both are important and necessary and both should and will get an equal share of Microsoft's attention. Neither am I saying that I think Microsoft is abandoning corporate BI – it isn’t, in my opinion. I'm on record as being very excited about the new developments in Office 2013 and self-service but that doesn't mean I'm anti-corporate BI, far from it - corporate BI is where I make my living, and if SSAS died I very much doubt I could make a living from PowerPivot or Excel instead. Probably the main reason I'm excited about Office 2013 is that it finally seems like we have a front-end story that's as good as our back-end, corporate BI story, and the front-end has been the main weakness of Microsoft BI for much too long. If Microsoft went too far in the direction of self-service we would end up with the opposite problem: a great front-end and neglected corporate BI tools. I’m sure that won’t be the case though.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .