Big Data optimize

SharePoint 2013 adds better in-memory analytics

Microsoft's business intelligence tools have arrived squarely on the doorstep of serious big data analytics.

Business_Intelligence_iStock.jpg
By Scott Robinson
In its quest to never be outdone in the features wars, Microsoft has drifted into the habit of bundling its most impressive new capabilities as peripheral trinkets. But in the case of SharePoint 2013, they've set a new record for understatement. Yes, SharePoint 2013 is feature-rich almost to a fault, and yes, most of the new stuff is big-banner game-changing (Social computing! SharePoint-in-the-cloud!).

But some of the most powerful items are geeky little widgets that barely get a mention on TechNet, and when it comes to SharePoint 2013, the In-Memory BI Engine tops the list.

Out-of-the-box Excel vs. Pricey business intelligence

Since Microsoft Office SharePoint Server (MOSS), Microsoft has faced a huge quandary: how to get more business-side users to exploit the rich business intelligence (BI) tools in SQL Server? Excel turns out to be the weapon of choice, since everybody and their great aunt know how to use it. As analytics have begun to supplant strategic imperative on the front lines of competition, this became an even stronger priority (in SP 2010), making Excel an indispensable component in the Microsoft BI toolkit. If I can play out ad hoc analytics via Excel, without the inconvenient, expense, and limited scope of a third-party or home-grown BI app, why in the world wouldn't I?

There are some good reasons, if you listen to the third-party app marketers. Excel is a disastrous BI client, they maintain, because it provides no means of detecting an error in the set-up of an analytical job; its ubiquity is a red herring, because it invites people into the analytics business who don't know squat about analytics; and it simply isn't designed to rally the processing resources that big data requires.

The first two are training issues, and not Microsoft's problem. But that last objection is a valid one, and has been no small hurdle for getting in-memory analytics onto the SharePoint community's radar, where it certainly should be. And now that hurdle has been cleared.

sharepoint.logo.png

Big data, big memory

The In-Memory BI Engine (Vertipaq, or xVelocity) is an efficiency enhancement that optimizes memory for high-volume analytical processing. Columnar storage, in-memory caching, compression and algorithms for aggregating and scanning data in parallel are leveraged to empower the business user to potentially process hundreds of millions of rows of data in an ad hoc process (the implementation of OData and the expansion of PowerPivot now empower that same user to gather those hundreds of millions of rows from many different sources, but that's a different blog entry).

The under-the-hood efficiency means that this data crunch happens in seconds, not minutes, and that is itself a game-changer for ad hoc analytics. But an even greater resource barrier has been the fact that Excel can't hold that much data in one sitting, and SharePoint can only store 2GB, single-object-wise.

However, that's no longer an issue. Excel Services can now make use of as much free memory as is physically available on the box, meaning that the ceiling in terms of in-memory processing is now virtually limitless: if you need more, just scale up. Therefore, the 2GB object size limit (which is actually a SQL Server limit) is unlikely to be a problem, because you don't need to do temporary storage if you've got that much memory available, and analytical results tend to be relatively small, compared to the body of data that produces them.

Microsoft's BI tools, then, are not only improved, but have arrived squarely on the doorstep of serious analytics. These new toys don't help the learning curve, but they certainly place unprecedented processing power in the hands of the business user. Just don't tell the BI app marketing guys.

2 comments
natemuleski
natemuleski

 There is a caveat involved with Excel/SharePoint BI and that is the licensing strategy Microsoft has employed with SharePoint 2013, SQL 2012, and Office 2013.

Assuming an On Prem deployment, in order to fully utilize BI you must have SharePoint Server 2013 Enterprise, SQL 2012 BI Edition (or Enterprise), and Office 2013 Professional Plus.

The one that really irks me is that they didn't include PowerPivot in any version of Excel 2013 except Pro Plus.   This was a freely available add-on for Excel 2010!   Professional Plus is completely unnecessary for many organizations and there is really no good reason for this change except as a way to force people to their subscription based software models.

Of course if you have SP and SQL you can try and build BI reports and dashboards within SharePoint, but it's not nearly as easy as building them in Excel and then uploading them to SP.

Mark W. Kaelin
Mark W. Kaelin moderator

Are you using SharePoint in your organization? Are you using any of the business intelligence tools that come with it? What analytical tools do you use?