Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

The question of Sharepoint and the Microsoft BI Strategy

It’s been clear for a while now that Sharepoint is at the heart of Microsoft’s BI strategy. The first sign was the way PerformancePoint was touted as a replacement for Proclarity. Then came the news that if you wanted to share models between multiple users in PowerPivot, you needed Sharepoint 2010 Enterprise Edition. In Denali, if you want to use cool new stuff like Crescent or the new SSRS alerting functionality you need Sharepoint. But is this a good thing for Microsoft BI? Behind the scenes a lot of people have been debating this question for a long time, so I thought it was an appropriate subject for a blog post – and you know how I like controversial topics…!

Let me start by saying that I have I have an opinion on this but not one that I feel 100% sure of asserting: I have the nagging feeling that my own view of the market is too limited to know whether the Sharepoint strategy is good or not, so my mind isn’t completely made up (and in fact the more I think about this issue, the more unsure about my opinion I am). Also, I don’t think anyone has objections to the purely technical reasons for the Sharepoint strategy – after all, why should the various Microsoft BI teams be in the business of building portals when Microsoft has its own, extremely successful portal they can integrate with, which gives them a lot of rich functionality for free? The question is essentially a commercial one: will more customers buy Microsoft BI as a result of increased integration with Sharepoint (in turn leading to Microsoft and its partners making more money, which is all anyone really cares about), or will a Sharepoint dependency actually put customers off and drive them into the arms of Microsoft’s competitors?

The argument in favour of the Sharepoint strategy goes something like this:

Microsoft’s BI products need portal functionality. Time and money for development of BI products is limited, so if the portal functionality can be got from Sharepoint then it can be delivered quicker, at a lower cost, and with time and money left over for other new functionality that would not be possible otherwise. More and better functionality means the customer is more likely to buy.

Integrating with Sharepoint also gives the wider Microsoft BI offering a coherence it wouldn’t otherwise have (and something it has historically lacked), and the whole ends up being greater than its constituent parts. This lack of overlapping functionality looks good in front of the customer, and also increases the opportunity to cross-sell BI to existing Sharepoint customers and vice versa.

Sharepoint is massively successful, one of Microsoft’s leading server products, so most customers you want to sell BI to will have Sharepoint anyway; therefore there will be little resistance to buying new tools that have a dependency on Sharepoint. The Sharepoint market is so large that even if only a small percentage of it is interested in or able to use MS BI, that’s still a massive potential market.

Presumably, at some point there will be a fully-featured “Sharepoint in the cloud” with all the BI features baked in, which means that it will be even easier for companies to adopt it.

Microsoft is well aware of the arguments against Sharepoint that are listed below, and because it wants the Sharepoint strategy to work it is taking action to address these problems of cost, complexity and uptake. One example is the increasing number of Microsoft BI appliances that are available, where all of the tough configuration decisions are made for you.

The argument against is this:

Sharepoint is expensive (or at least perceived as expensive) in terms of license costs, infrastructure and administration, so it makes the overall MS BI solution more expensive to have a dependency on it.

Sharepoint is a complex product (or at least perceived as complex), and Microsoft’s BI tools are pretty complex as well; integrating the two makes something even more complex. As a result, whereas in the past a single BI guy could just install SSAS, SSRS and so on on a server, now you need a BI guy and a Sharepoint guy to do all the setup and admin, which doubles the cost of labour; the added complexity also makes it more likely that the setup and admin will take longer. Microsoft BI products have traditionally seen a lot of their adoption come from internal IT departments taking the ‘it’s effectively free, so let’s install it somewhere and see what it does’ path, and this will become much less common because of the added overhead of Sharepoint.

The added dependencies between Sharepoint and BI could actually make it slower to deliver new features because now there are multiple MS dev teams that need to work together, co-ordinate functionality and release cycles, and deal with conflicting priorities. History has shown that MS dev teams don’t always do this well (think of Excel and SSAS support), and even when they do some compromises are inevitable.

Many customers do have Sharepoint, but not all of them have the editions or versions that the MS BI stack requires. And very often due to political divisions, an internal corporate Sharepoint team have their own agenda to follow which has no place for BI, and aren’t interested in upgrading to a certain version or otherwise accommodating the BI team when it might impact on their own goals.

Some customers do not have Sharepoint and have made a conscious decision not to have it; these customers include not only the die-hard anything-but-Microsoft shops but also some who would be interested in a solution with fewer dependencies. For these customers, a Sharepoint dependency removes all question of the use of MS BI.

The MS partner ecosystem, at least at the mid-level, is segregated into BI partners and Sharepoint partners, and while there’s a certain amount of convergence you still tend to find that many consulting companies are BI partners who do a bit of Sharepoint on the side or Sharepoint partners who do a bit of BI on the side, so not all of them are capable of selling or implementing an overarching BI-Sharepoint solution.

The nature of my work means that I get to see a lot of different Microsoft BI implementations, probably more than the average consultant. I reckon I work with around 30-40 different customers every year, ranging in size from one-man-bands to the biggest enterprises, and in the five or so years I’ve been in business I’ve only ever seen a relatively small number who actively use Sharepoint in combination with the rest of the Microsoft BI stack. If you work for a large partner that specialises in and actively sells Microsoft BI and Sharepoint you may have seen much greater use of Sharepoint than I have, and if you work for a specialist Sharepoint partner I dare say you only ever work with customers who are very committed to Sharepoint, so I’ll admit my point of view is biased. On the other hand I can’t deny the evidence of my own experience and as a result my natural inclination is to be slightly sceptical about the Sharepoint BI strategy, because I don’t see any basis for the claims that Sharepoint is a ubiquitous platform and one that users actively want to integrate with BI. I’d also add that a couple of years ago I was equally sceptical about Excel’s central role in the Microsoft’s SSAS and wider BI strategy, but now I see Excel used successfully on a wide range of SSAS projects and I’m very much more pro-Excel (although I’m not blind to Excel’s continuing shortcomings as an SSAS client tool for more advanced users). Maybe in a year or two’s time all my customers really will have Sharepoint, the Sharepoint strategy will bear fruit, and my fears will have been proved groundless.

So… what do you think about all this? What are your experiences with Sharepoint, do you have it in-house already or (if you’re a consultant) will you be able to sell a BI platform based on it? Please leave a comment…

Related

Post navigation

30 thoughts on “The question of Sharepoint and the Microsoft BI Strategy”

I think that in the very long term, relying on SharePoint is a good idea. However, in the short term, and possibly also in the medium term, this dependency and the lack of an entry-level license that doesn’t require all the Enterprise licenses are factors that give space to grow to competitors. Which, in a few years, will not want to lose their current investments.
I am in the middle. I agree on the strategy, I would change the tactic.

At our university sharepoint dependency is percieved as a liability, not as an asset. Currently just implementing Sharepoint for (some) BI functionality is way too expensive for us and a lot of other organizations i know have the same opinion. So companies need to do more with Sharepoint to justify the investment. This is usually difficult to organize/justify. Also, in hybrid/heterogenous BI solutions (ie combinations of vendors) M$ BI functionality/technology is actually less dominant due to this agressive cross-sell/dependency strategy with Sharepoint. Case in point, I’d like to do a POC of excel/powerpivot BI func to users, but because of architectural dependencies and restrictions this will not be possible.
I think M$ is shooting itsself in the foot here.

Having worked at Microsoft and now as a customer trying to implement this stuff I can say there is a disconnect. SharePoint is ubiquitous within Microsoft – is takes no more than a few minutes and a web form to get a new SharePoint site managed by MSIT. Now I work in a bank where there is no SharePoint infrastructure and the hurdle to deploy is much larger – frankly I’ve almost given up pushing it since the benefits don’t quite meet the effort required.
What could Microsoft do to make things better?
1. Have a free license that allows you to install SharePoint foundation + PerformancePoint + PowerPivot/Excel services on the same license at SQL Server.
2. Make the install and configuration as easy as running SQL setup (there is a small attempt at this when installing PowerPivot but I really want everything installed too).
3. Make SharePoint play nicely as part of a non-MS environment e.g. with security etc
I guess what I’m saying here is the appliance model is great but don’t make me buy the hardware too.

I agree with Marijn that if the Sharepoint solution is meant to cover more than BI applications then customers won’t see this dependency as an issue. If a customer currently not using SharePoint is told to they must have SharePoint Enterprise to schedule automated data refresh in PowerPivot, then I think it would be easily perceived as an obstacle to the BI vision.

How to get customers that currently do not have SharePoint Enterprise (and could potentially afford it) to install it just on the basis of BI applications? Features like enterprise wide visibility of PowerPivot applications can add some strength to the argument of installing this environment. But will the argument be strong enough?

It would definitely be nice to have an entry-level, pure BI version of SharePoint. In my opinion, the issue is trying to sell a complex environment with many more features than the immediately needed to get the BI project rolling. The need to deal with those non-BI related features can add perceived complexities that increase the risk of failure for the project sponsors.

You’re asking the question the wrong way round. It’s not “will more customers buy Microsoft BI as a result of increased integration with Sharepoint”, it’s “will more customers buy SharePoint as a result of increased integration with Microsoft BI”. SharePoint and Office are the cash-cows.

Javier, James – I think the idea of a ‘lite’ version of Sharepoint for BI makes a lot of sense, and it’s something a lot of people have been asking for.

Mick – you’ve hit the nail on the head. Using BI as a means to sell more Office and Sharepoint makes (or at least might well make) a lot of commercial sense to Microsoft as a whole; it’s just a lot less attractive to pure-play MS BI partners. Should we all turn ourselves into Sharepoint partners then? *Can* we all turn ourselves into Sharepoint partners?

A very interesting post, on a topic I’ve been pondering for some time, and particularly since PowerPivot became widely available. There is definitely a disconnect between Microsoft’s “BI for the masses” mantra and the cost of SQL Server / SharePoint Enterprise licences. And as Boyan says, there are other, cheaper alternatives out there.

I saw a very interesting presentation a short while ago from the IT person at a successful 15-person business where they’ve implemented Tableau Server for the majority of their analytics requirements, all against SQL Server source data. An example of “BI for the Masses” which would never have got off the ground with Microsoft BI stack and SharePoint – although it might have done in earlier times with ProClarity!

I too think it’s a shame that there isn’t a lower cost entry into the world of shared PowerPivot models. We are at a stage where more and more business users want to explore their data and make the results available to their co-workers.

We’ve had customers who have been put off by SharePoint due to poorly thought out implementations from the past, and also customers where integration with SharePoint is seen as an advantage- and one who is considering upgrading to Enterprise, partially because of the BI.
So my experience is a mixed bag.
Personally, I feel that Microsoft would do well to make sure that tools such as PowerPivot, PerformancePoint and Crescent can work with or without SharePoint. The thing that SharePoint should add is the ability to bring it all together in a common management paradigm- but it shouldn’t be a case of “You want all the nice BI stuff? Buy SharePoint too”.
I agree that one of the ways MS BI became ubiquitous was the ‘you get it for free’ factor, and although that remains the case with the core products, all the ‘new stuff’ is SharePoint based.

Having said that, SharePoint is a popular platform for delivering all kinds of business critical functionality nowadays, not just BI. Either way, I don’t think it’ll be too long before either everyone has it and it isn’t a perceived obstacle, or Microsoft change their policy of which SharePoint version the BI is part of.

I think the SharePoint strategy makes sense. You need somewhere to host your BI and manage both the components and security — why build when you already have existing you can leverage. We have actually seen more interest in Microsoft BI since it got more cozy with SharePoint than before. Most organizations we have worked with are either considering them SharePoint and this shows them additional value, own the Standard CAL and are looking for reasons to move to the Enterprise CAL and this gives them one or already own SharePoint and have no idea that they already have a BI tool and don’t have to go out and buy another. I don’t believe the costs for SharePoint are a true objection. $6k for a server and $200 (give or take) per user is actually quite cheap considering everything you get.

Microsoft BI, just like any product is not for everyone. If you are a 15 user company, then Microsoft is probably not your best bet, neither is Cognos or Business Objects. If you are 500 users then it is perfect. Everything is relative.

I think the real problem is that Microsoft is not good as elaborating on all the value you get out of SharePoint. It seems to always come back to document management, intranet and collaboration and some of the most beneficial stuff to organizations gets glossed over.

Funny that you bring this up becuase I am right in the middle of a BI rollout along with SharePoint. We have the same issue where our global IT team is rolling out SharePoint but BI is the last on the list for inclusion int the environment. So we deployed a local instance of SharePoint to support the local BI need (Integrated SSRS and Performance Point). What we did not think about at the beginning was that we have to align not only the need of BI with the business and how it is used and surfaced through a SharePoint portal, but all of the little things like site structure, security alignment beween SSAS, SSRS, SharePoint, etc… becuase we are only using SharePoint for BI. These have become real headaches and actually caused us to stop and rethink how we were going about the implementation. And I can’t even begin to explain the difficulties with getting Kerberos to work for the double-hop issues.

I like SharePoint and I am excited about what it can offer, but you aren’t kidding about the need for an architect to help implement and setup SHarePoint, even for environment without doc management, workflow, project, etc… I like the idea of having a free version that supports only the BI stack. It should be much simpler to implement, just a few site templates and only the services/accounts needed to run PPS, Power Pivot and SSRS.

great article. I totally agree on the MS partner argument, as our company does a lot of Microsoft BI and only a little SharePoint 😉 We also see more clients using SharePoint so we are adapting SharePoint more and more, at least the BI features. Since we also have a strong .NET development background we are not totally lost with other SP topics either. But in summary I follow your argument.

Another problem is that many (big) clients are buying a SharePoint to enable team collaboration, but many are not aware of the BI capabilities the SharePoint offers. Here I see some work for us.

For all German readers among us, on our company’s blog we have a series on Sharepoint 2010 and BI, the second article just appeared. See here

Curious what you think the more widespread adoption of SP Online going forward will mean for the platform’s BI usage? Many users going to the cloud right now are smbs with less need for complex bi solutions, but I think that’s all going to change in the next five years.

Claire – well, it will certainly remove some barriers to adoption but any scenario where sensitive data (and in BI, you tend to work with a company’s most sensitive data) gets sent outside the company, whether it’s to the cloud or a hosting provider, is going to ring a lot of alarm bells. In Europe especially there are concerns about the security of cloud-based solutions; about the legality of certain types of data leaving the EU; and about the fact that MS, as a US company has to comply with the Patriot Act (see http://www.winrumors.com/microsoft-reveals-european-cloud-data-is-vulnerable-to-u-s-patriot-act/).

Office 365 could make the infrastructure headaches go away. The OData protocol could make the integration issues go away as well. If u have SharePoint, the BI extensions are great otherwise its a pain. I personally think the SharePoint model for BI has failed.

I think that MS, as it now has put BI portals on the oil tanker of SharePoint, should really work hard to make us, primary not SharePoint consultants, being able to install the BI parts, within 1-2 hours on Windows 7 64 bit, and run it under a local service account, like the rest of the MSBI platform. Installation of SharepOint should be more easy and the platform should be more easy to learn. I would also like to see a Sharepoint BI edition with only the relevant parts for a BI portal. With such a setup we BI consultants can support the BI parts of SharepOint.

I think that Microsoft made a mistake. you can buy bi solutions separately to the main corporate
portal but can integrate into it.
that is why people like qlikview, you dont have to have 3 different experts to build a simple bi
soluion.
even panotma cognos and BO are not “portal” dependent.

Chris – Thanks for sharing your thoughts on this. One statement of yours really resonated with me: “….whereas in the past a single BI guy could just install SSAS, SSRS and so on on a server, now you need a BI guy and a Sharepoint guy to do all the setup and admin, which doubles the cost of labour….”

As a BI gal, I’ve experienced this. We have staff at my consulting firm with the varying expertise to get it done, but it is a matter of time, people, and of course more money. Makes it very easy to underestimate the effort of a new implementation.

Thank you Chris, very interesting article. I’m usually a silent reader of your blog, but this post really reach me.

I am probably working at the extreme opposite of you, which means I work with about a single client per year, maybe even for more than a year. In general, we talk about big companies who have big BI needs and have big budgets to solve it.

Sharepoint is definitely a plus for these projects. My problem would be more with the number of tools that are offered, not the quality of those. I — love — core BI products that are SQL Server, SSAS and SSIS. I can’t wait for Vertipaq, as I clearly see it’s place in the stack.

Now for the reporting side… Between PerformancePoint, SSRS, Report Builder, PowerPivot, Excel/Excel Services, PowerPivot, Crescent, … Could we settle down to two products someday? One for corporate reporting which would allow full flexibility and power, but require more *skills*, and one for self service which would be simple to use?

If Microsoft wants to go ahead and make Sharepoint a requirement for BI stack, they at least have to make it the central point for reporting. PerformancePoint should be a core component on Sharepoint, and it has to be that central point where you can either author fully featured corporate reporting, and offer a self service BI tool for users that goes there.

It’s hard to explain to your client that they need a tool for each and every of their needs. “Oh yeah, we have Key Performance Indicatorss in the SQL Server Analysis Services cube that are display in your SharePoint Portal via PerformancePoint Services, but you know, if you want to have self service BI, you should really consider PowerPivot and it’s SharePoint component. Yeah, there’s Data Analysis Expressions to learn, but hey! Oh, and for fine grained corporate reports, we definitely need SQL Server Reporting Services. And you know, those reports in SSRS can be authored using Business Intelligence Development Studio, or Report Builder. Wait, ever told you you can use notepad also, and edit it plain XML?”.

That’s confusing for a client. I mean, my job as an expert is to deliver the solution and make sure the client understands it and can make it evolve, but at what cost? At least, I know I will have job for as long as Microsoft makes BI solutions. In my last mission, every time my client came to ask me for something new, it was that kind of question : “Hey Dominic, I would like to achieve [X] in our Bi solution, what new tool do we need?”.

To answer your orginal question “will more customers buy Microsoft BI as a result of increased integration with Sharepoint (in turn leading to Microsoft and its partners making more money, which is all anyone really cares about), or will a Sharepoint dependency actually put customers off and drive them into the arms of Microsoft’s competitors?”

The answer is both yes and no. The anti-Microsoft crowd will shun it as they do with everything else Microsoft. After all, if it doesn’t run on *nix it can’t be enterprise ready . Power to the penguins blah, blah, blah.

If you’ve watched the market, the answer is probably yes (look at the numbers, look at the job postings).

Yes, Virgina, you do need an Architect ( You should have had one for Cognos, Business Objects, Microstrategy, etc. as well). Good Architects actually decrease overall cost. You should also have a good DBA that understands how to design reporting databases (data marts, data warehouses, etc.) Without these two, you (your client) is likely going to experience problems in production.

While I will most definitiely agree that there is A LOT of complexity associated with the MS BI stack, there is also a lot more flexibility and functionality as well. Even though I’m a fan, I hate installing and configuring the MS BI Stack.

Right now, I see two big problems with the current Microsoft roadmap. As others have alluded to, the entry price is expensive (if you are only evaluating the front end tools). The current BI SKU’s are the most expensive; the bang for the buck can be very hard to justify because tools like PerformancePoint are still not very mature. That could be remedied fairly quickly.

The biggest weakness I see in the MS BI stack is Microsoft’s mobile strategy (there isn’t one). Smart Phones, IPads and Andriod Tablets are here to stay, get over it. Microsoft doesn’t really have a story in this space. None of the tools work particularly well on mobile devices.

Business Intelligence should live in the domain served by the corporate portal and SharePoint is a good choice. It should be displayed based on business needs side by side with other content types, not a seperate stand alone silos. The older stand alone BI solutions make BI less relevent to the organization as a whole and cost too much to support. Companies are looking to leverage existing investments (Office, SQL Server, SharePoint, etc.) with minimal increases in additional cost. Stand alone, boutique BI software sales and solutions are as a whole more costly to build, license and support. The same stand alone solutions still require installation of the same infrastructure components (SQL Server, SSAS, SSIS, etc). You should still be using Kerberos and not NTLM. If you are using service accounts to access data, you are ALWAYS wrong (can we say HIPPA, SOX, etc.) The good ole service account days are very long gone, if you a consulting firm doing this, you are begging to be sued.

While the SQL Server SKU provides many excellent tools, it isn’t BI. It is a means to a BI end. It has a database engine, an OLAP engine, an integration engine and a reporting engine. It doesn’t do what a user thinks BI is. You cannot slice and dice, drill up, down and across in SSRS (Yes, I know can can “fake it”). Contrary to what many believe, you cannot produce an enterprise dashboard with SSRS. SSRS reports are reports and a report is not BI. While you can make them look pretty and you can produce high quality information, the simple fact is that cannot actively analyze that information in SSRS. While reports work well as an endpoint in a BI workflow, they do not work well as an entry point.

As to the “install everything on one box” (SSDB, SSAS, SSIS, SSRS) crowd, I say thank you. I’ve earned a good living fixing that very expensive and usually unsecure, poorly performing mistake. While that may work on a developer machine (sorta), that will not work well (if at all) in a production environment. The product documentation clearly states this.

Report is not BI??? SSRS is an important part of MS BI product. A BI solution must consist of both reporting and analytical presentation tools. You cannot only have analytical grids and charts. Decision makers need reports, and SSRS is an ideal tool for creating great interactive reports using OLAP data.

I’ll not sit on the fence. I think it’s a BAD, BAD idea. My previous gig (small company) did not have SharePoint, and after evaluation, decided to use Tableau. Former coworker (Fortune 500) now also has turned away from the Microsoft BI offerings and is exploring Cognos even though SSAS is his core presentation server. Why? Even though they have SharePoint, they just upgraded to 2007, and well, Office is a version back as well. Rolling out the latest and greatest in a huge global enterprise is a political nightmare. Huge mistake, MSFT. Huge.

[…] outweigh the disadvantages. Even for those people for whom Crescent is not an option because of the Sharepoint dependency, but who want to do a mixture of traditional ad-hoc analysis and detail-level reporting, I suspect […]

If the goal is to get more Sharepoint licenses at the big companies, it might work. Some smaller or midsize companies might look around at some of the other smaller analytics packages for reporting on top of their SQL server, so I think they’ll lose some share there. It will add some level of complexity to the implementations, especially for current non-Sharepoint clients and smaller customers. It sounds like MSFT is OK with losing a few BI customers in exchange for solidifying or increasing Sharepoint footprints.

Let’s take PowerView from MSSQL 2012. It is an SSRS add-on, but needs SharePoint Enterprise. Why??? Is MS planning for SSRS to work only in Sharepoint point mode in the future?

MS BI is about SSIS, SSAS, SSRS, and using some great MS partner third party tool for front-end, like Component Art, Dundas, Panorama, MicroStraegy (free and great). Sharepoint Enterprise is just too limited in terms of BI, mega expensive, and a big administrative headache. But for BI, SharePoint is highly recommended to any company which is serious about wasting a lot of money.

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 7,729 other followers

Public Power BI and SSAS Training Courses

I'm running several SSAS, MDX and Power BI-related training courses through Technitrain in 2017. Check out the Technitrain course catalogue for full details, and to see other upcoming courses from the likes of Alberto Ferrari, Andy Leonard, Allan Hirt and Alex Yates.

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk