An open letter to Microsoft – BI challenges.

To preface this letter, let me just say that I have drunk the kool-aid, just like the Merry Pranksters drank Ken Kesey’s koolaid (http://en.wikipedia.org/wiki/The_Electric_Kool-Aid_Acid_Test
), and evangelise Microsoft products on a daily basis – indeed, on behalf of Microsoft in our Expedition Denali and SQL Server 2012 roadshows.

So don’t get me wrong, I *love* this stack, and develop on it as my first choice. I just want it to be the best it can be,  I have many challenges in this journey, and I thought I would share them, in the hope that things would improve.

Microsoft’s BI Program

There is none.

The SQL Server team is the biggest face of Microsoft BI, and yet so many things simply don’t fall within their ambit – Sharepoint BI, Office BI : other teams, and very little co-ordination between them.

And even within the BI team – the interaction between Reporting Services and Analysis Services has been terrible for years. PowerView is a view of how that could work, but PowerView is just a tiny piece of that
puzzle.

First, an introduction to the full tool set, and then my view on where the problems lie: from a messaging point of view, and some insights to the technical pieces.

SQL Server Analysis Services

Far and away the strongest tool technically: in its’ MultiDimensional incarnation, the world’s best-selling OLAP tool, and in thenew tabular mode called xVelocity (and I include PowerPivot and the column store indexes here – indices in real English) a great advance. I was very hesitant about the tabular mode at first, I have a large investment in
Multi-dimensional, but after working with it – I love it, technically awesome, and the ability to combine non DB sources. This truly is the future.

A bit of a mixed story on messaging at first, as per Chris Webb’s (@Technitrain) initial reaction here, https://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/

But it got a lot better, as here: http://cwebbbi.wordpress.com/2011/05/17/good-news-on-the-future-of-analysis-services/

(Chris’ reactions pretty much mirror mine, so I forbore on commenting then or now)

A challenge for me as a consultant is that there are less bits to play with – I’d have loved to be able to build Multi-dimensional, and have column-store as a new aggregation type. But I can live with the way it is.

Biggest challenge: people with existing multi-dimensional cubes. This is more an opportunity than a challenge to me, as (and you heard it first here), Pragmatic Works South Africa has a conversion tool being built.

SQL Server Reporting Services

Reporting Services became a BI tool in the 2008 release of SQL Server – 2000 and 2005 simply didn’t quite count, but the new charts, and Report Builder 2 made it a real BI tool for the first time. Not a self-service
BI tool quite, but a real tool.

Reporting Services has the advantage of being able to pull from multiple sources – and this is its’ greatest weakness – oft times, people try to make it do too much rather than build a cube (I will elaborate on this later)

I often put Reporting Services in the “Operational Reporting” leg of my “Choosing a Microsoft BI tool” presentation, but with some developer work it can be an interactive BI tool.

Little bit too complex for an average user to build reports themselves however.

SQL Server Integration Services

Great tool, no comments here, it’s positioned perfectly, so I’m not going to talk about it today.

Sharepoint 2010 – Performance Point

Crippled in marketing by the failure of the other part of Performance Point, the budgeting and planning tool (I hesitate to call it a Corporate Performance Management tool, it didn’t quite make the grade), PPS was
originally the Business Scorecard Manager, and then got rolled into the Sharepoint licence during its’ 2007 release.

Great improvements in 2010, and I have rolled out many BI implementations based on Performance Point (some of them award winning – see here for a solution we took live the day Sharepoint 2010 was released. http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000007013
Watch the video, some of my dashboards are in there)

Performance Points greatest strength was its’ ability to combine Business Intelligence data with unstructured data on the same dashboard– and filter both. For instance, choose a particular store and have that store name filter both the scorecards, as well as the search box for documents aboutthat store. We pushed this hard, and include things such as news feeds.

Service Pack 1 for Sharepoint broke this, due to a bug whereby filters only populated the PerformancePointConnections list, and not Filters or Parameters. We’re publishing our own fix for this on CodePlex soon, but the fact that it happened is an indication that the dev team has no idea how the product is being used. The dev team (Alyson Powell, Jason (can’t remember) surname) who’ve all left never had this issue…. Shout out to them for loads of help on 2007

I comment here on Dan English’s blog, as well as on the PPS team blog, and I still have no idea how to log a PPS bug? No connect items available

http://denglishbi.wordpress.com/2011/06/29/performancepoint-2010-cascading-apply-filters-sp1-features/#comment-1375

http://blogs.msdn.com/b/performancepoint/archive/2011/06/07/what-s-new-in-performancepoint-services-and-sp1.aspx

Sharepoint 2010 – Excel Services

This is a great story – take your Excel spreadsheets, which are connected to data, and publish them live on Sharepoint, and they’re still interactive. Pity it sits in parallel with everything and you can’t click on an Excel Slicer and filter an associated PPS scorecard.

Sharepoint 2010 – Visio Services

Ugh. Don’t. No support for paramerised queries without a lot of custom code. Essentially only used for system monitoring with live data.

SQL Server 2012 – PowerView

Wow, amazing, a #Tableau beater – that was the marketing message.

Except that PowerView has these 3 restrictions:

1. Only works from xVelocity
2. Requires Sharepoint Enterprise
3. Doesn’t work properly inside Sharepoint

OK, so let’s take a step back: What is PowerView *for*? Data exploration and presentation – so not management reports with tons of formatting like reporting services, not monitoring like PerformancePoint, not
tabular slice and dice like Excel – rather Visual slice and dice. Which it does a great job of, animated scatter plot is *awesome*.

Let’s look at the restrictions: requires a model in the backend, and that model is the latest and greatest. Model can be created in PowerPivot, and published to Sharepoint as the mechanism of sharing, and there we have a great reason for Sharepoint. I’m totally on board with these two decisions, and quite frankly anybody who goes “we should be able to buy it on its’ own” simply doesn’t understand Microsoft’s market. Sharing of information happens in Sharepoint, which is the platform for all these applications. Deal with it….

The last one is totally insane.

PowerView opens in a new, fullscreen window, which cannot accept parameters, and (being full browser window) cannot be embedded in a Sharepoint page. So all the benefits of being inside Sharepoint – gone.

Update: I was informed that it is possible to embed a PowerView report in a Silverlight webpart, but I have yet to get it to work. I will blog here when (and if) I confirm how this can be done.

Orphan Child - *Data mining* (Statistical Analytics)

The first piece, off the bat, is that calling it “Data Mining” is a mistake, as most people call OLAP data mining.

The second is adoption problems – the lack of the 64 bit Excel add-in hindered adoption when PowerPivot came out, and this also has not been pushed nearly as hard as it could be. Download here:

http://www.microsoft.com/en-us/download/details.aspx?id=29061

The statistical analysis toolset is very powerful – what needs to be exposed in Visual Studio is more ability to modify the algo’s, and then – it needs to be easier to expose these in tools.

Excel addin – *awesome*

PowerPivot needs to be able to use these *inside the model*

PowerPivot’s support for any analysis is pathetic. A SINE function for my “Let’s find a planet around another star” presentation – not there. I did it in Excel,and imported a SINE table. *Ugly*

These algos should be made available *in database*. KXEN (the acknowledged leaders in this field) can do this even for SQL, and SQL should be able to do it.

This would drive adoption of one of the most powerful tools in the SQL toolbox.

Marketing Messages

The first problem that I have with the structure of this is the complexity ~ not from a technical POV, there can be good technical reasons for the split. But from a marketing message, there is no “Microsoft BI tool is X”

To give you one example: I am engaged at a client to do a migration from Business Objects, to Microsoft Reporting Services.

Now, we’ve done a POC of Analysis Services, they like it, they also want to provide a PowerPivot services platform to support their users.

But. For this migration, *absolutely* no Analysis Services. The {industrynameofclient}wide policy
is that they are migrating from BO to SSRS. And the logic that is encompassed in the BO Universes?

Hey, just code it into the RS SQL statements. With cross database and cross server joins.

My choice as a consultant at this point, having been argued down that this is a political decision, and reporting services was chosen as atechnology, but analysis services was not, is either to bite the bullet and do the extra work to make it work, or walk away. (And I’m not going to share my approach, sorry :) .This is as much as I can share….)

This scenario should never have occurred. From a licencing point of view, they own SQL Server, so using different components should make no political difference. An analysis services cube is the equivalent (in some ways) to a BO Universe, and thus SSAS + SSRS = the old BO. But the marketing message has put them as different beasts.

PerformancePoint – still recovering from the PerformancePoint Planning debacle, the orphan child needs to be pulled into the BI folder, as part of the “MS BI tool”. No real need to change it technically – both SSRS and PPS are Sharepoint service applications. But market it as a single tool.

Excel/Excel services – well, they stand very strongly on their own two feet, and I expect this only to go from strength to strength with Office Web Apps in future releases (no, I am not privy to what is in Office vNext, so I feel free to speculate). Again though, this message needs to be a single BI message.

Self-Service BI

It seems that QlikView has had a bigger effect on MS than it should.

Self-Service BI for the majority of users does not mean that they build their own model or explore from scratch. It means that, when a store’s sales are down, they can drill in, see which product line it is that is down, and find out which sales team to shout out.

The full self-service model is necessary, and *technically* I believe the MS stack of “build in PowerPivot, share to the team in Sharepoint, pull into cube and manage through IT for enterprise wide deployment” is absolutely the strongest. But the marketing message is very purely personal and team BI, and not enough focus on Enterprise BI.

MVP Program

Near as I can figure, there are SQL MVPs, and Sharepoint MVPs. (And Office, and OneNote etc), but no specific callout for a BI MVP. This is a great opportunity lost.

Technically

In short: all the pieces need to play together.

Results from unstructured search using FAST or normal Sharepoint search must be displayed and filtered side by side with a Performance Point scorecard – clicking the scorecard for a particular store must then filter the list of documents from the search, the list of products with their images displayed alongside from PowerView must filter as well, and the Excel Pivot
table included at the bottom showing a list of sales people and their sales must also filter.

All this (excluding the PowerView) is functionality already built into PPS – some work on the designer so that a single designer can incorporate all of this functionality would be great.

PowerView needs to be built into a web part – one that can receive and can send connections – just like SSRS has been able to do. Not a new type of functionality, just taking that same concept and pulling it into everything. Using a PowerView slicer to send values to a search box, a PPS scorecard, and an Excel Slicer – wow.

PerformancePoint needs the impersonation pieces that SSRS and PowerView has – I have no issue setting up Kerberos for my clients, but it is a stumbling block.

Excel Services needs them too.

Owing to a post from Robert MacClean (http://www.sadev.co.za/content/why-harder-you-work-prove-microsoft-you-know-better-less-chance-it-will-ever-happen ) , I’m not going to dive too deep into what I think should be done technically
more than the above “take what you build and apply it consistently across the tools”

Overview

The MS BI stack is great – in its’ individual pieces. These pieces need to be pulled together into a coherent whole across the different products, and branded as a single entity. I agree with the “SQL/Sharepoint/Office” licence stack, but a vertically integrated product development and marketing team for BI across the 3 products needs to be instituted.

*I drank the kool-aid,
but now you need to give me a single flavour*