In-Depth
Inside SharePoint 2013's User-Driven BI and Big Data Capabilities
The Microsoft collaboration platform offers new ways for IT to deliver dashboards and provide extended analytics capabilities.
The arrival of SharePoint 2013 brings numerous new features to the product. One that will prove critical in helping managers and workers make decisions is vastly improved business intelligence (BI) capabilities and support for big data.
Microsoft didn't shirk in any area, but BI was clearly in its crosshairs when it decided where to invest the most. In addition to some relatively minor improvements, such as the Chart Web Part, Microsoft made significant enhancements to Excel Services, Visio Services and PerformancePoint Services.
The SharePoint team at Microsoft emphasized these major subsystems both on the server side and the client side. Excel Services supports much larger data sets than ever before and, for the first time, leverages PowerPivot. Visio Services, often overlooked, can now use Business Connectivity Services (BCS) to link with external data sources, and the UI now plays nicer with the standard SharePoint look and feel via themes. Meanwhile, PerformancePoint now plays better with SQL Server Analysis Services and has a superior migration story -- all with an enhanced UI and first-class iPad support.
After an extensive evaluation of the BI features in SharePoint 2013, we've detailed the key enhancements to Excel, Visio and PerformancePoint services that will significantly advance the way users analyze information.
Excel Services
Microsoft introduced Excel Services as part of Microsoft Office SharePoint Server (MOSS) in 2007, and it has become the cornerstone of its self-service BI offerings. At first, MOSS 2007 took considerable effort and some administrative leverage to make BI available to end users. Now it's more mature and can help any business build real, Excel-based applications, from simple dashboards to more-complex data modeling and analysis functionality.
The key capabilities offered by the latest iteration of Excel Services build on this basic premise: End users know more about what they need and can react faster to changes than developers. Meanwhile, most businesses have more than a few Excel experts.
If you meet these two criteria, and have a SharePoint 2013 farm (Enterprise Edition), you can start making your own shared Excel apps.
Getting things moving is a fairly straightforward process, but it relies on some in-depth knowledge of data to make real BI magic happen. The first challenge is to provide a spreadsheet with the data that users will need. This can be accomplished in one of two methods -- either populate a spreadsheet with data extracted from a line-of-business (LOB) data source or, better yet, create and use a live connection to back-end systems using the native data connection capabilities in Excel. Excel Services 2013 data connections can be made to nearly any data source, including the Open Data Protocol (OData), and can support multiple data connections at once.
The next step is to start using Excel to calculate, pivot and chart. It's that simple. There are no new languages to learn and no development to be performed -- just calculations, pivot tables and your stable of favorite Excel tricks and clever workarounds. Then, upload the Excel workbook to your handy SharePoint document library, and let Excel Services do the heavy lifting from that point on. Once the workbook is uploaded, you'll be able to:
- View the workbook in the browser: Using Office Web Applications (OWA), users can view and even edit documents without the need for Excel. Granted, it's a reduced functionality set, but it still allows for quick, collaborative edits that will keep everyday work and projects on track.
- Leverage the power of your expensive server farm: Excel Services can break apart complex problems and distribute the calculations across all the expensive, powerful servers that make up your SharePoint farm. You can even schedule calculation jobs to be performed in off hours to mitigate the impact on server performance.
- Build your own dashboards: Using the Excel Viewer Web Part, you can put Excel on nearly any page in your portal. You're not limited to showing a full spreadsheet -- you can also limit the display to a single chart, range or pivot table. Building a dashboard is now as easy as building a spreadsheet and dropping a Web Part on a page.
The core functionality described here has been standard-issue in Excel Services since SharePoint 2010. What's worth getting excited about in Excel Services 2013? Plenty. It's all about scale.
Access Big Data
In previous versions of Excel Services, the toolset was very good at handling small data sets and performing fairy complex calculations. The problems came when users would try to process larger data sets, or perform very complex modeling. Server resources became an issue; in many cases, this could impact overall SharePoint performance.
To help alleviate these scaling issues, Excel Services 2013 now allows for much bigger data sets. Yes, the Excel workbook file itself is limited by the 2GB file-size cap on all SharePoint 2013 servers, but the only limit on how big the spreadsheet can get once opened is the memory resources and disk space on your servers. This can permit in-memory data sets that span millions of rows.
Improved PowerPivot Utilization
So, how do you process all that data in a timely manner? With PowerPivot, of course. This technology was introduced in SharePoint 2010 but now offers a much deeper integration with Excel Services. Using the VertiPaq engine, it uses some pretty clever tricks -- such as in-memory compression and data filtering -- to make surethat operations on your large data sets can happen blazingly fast.
PowerPivot also gives users the ability to create relationships between data from different sources, allowing users to view complex data in a more refined, normalized form -- even from multiple data sources. And if users are hindered by Excel formula limitations, more adventurous data modelers can create advanced formulas that use the Data Analysis Expressions, or DAX, expression language.
These capabilities lead to the kind of large-scale data control that was previously only available to developers. They open the door to end users who already have years of experience crafting data into workable models. These users can now use SharePoint 2013 as a platform to collaborate and share their findings, allowing businesses to be more agile and creative in their approach to BI.
Power View
Excel Services 2013 can now take better advantage of Power View. This data modeling and visualization engine sits on top of the SQL Server Reporting Services add-in to SharePoint and provides a wealth of interactive data modeling and visualization tools. As with PowerPivot, Power View functionality blurs the line between self-serve and provider-driven BI. It requires some fairly heavy lifting and technical savvy to set up data sources, define data relationships and so forth. However, once this is done, users will be able to craft the visualization that they need to do their jobs, and be able to leverage tools such as dynamic charts and graphs, runtime data filtering, slicers that let users interactively cross-filter and highlight data, and viewers that allow users to access multiple views of reports.
Visio Services 2013
Visio Services is probably the most underutilized arrow in Microsoft's quiver of BI tools, which is odd because it provides the most attractive ways to interact with business data. It's used in a manner that's similar to Excel Services- enabled worksheets. Users can connect live data to their Visio diagrams, have elements update based on that data, and then publish the new dynamic diagram to a SharePoint document library. Then they can display all or part of a diagram on any Web page with the Visio Web Access Web Part.
This allows users to create a much more visually appealing display than a simple pie chart or graph. Would it help to show data from your nationwide business over a map of the country? Need dynamic network information laid out over a live network diagram? Visio Services can do that -- and look good in the process.
In terms of raw data management and calculation power, however, Visio Services is no match for Excel Services. It doesn't have the formula management or data modeling capabilities that are required for deep analysis, nor will it handle the large datasets that Excel Services manages so nicely. However, there are a few updates to Visio Services that can make creating and sharing dynamic diagrams easier and more functional in SharePoint, including:
- BCS connectivity: Users can connect diagrams directly to external data via SharePoint BCS. This opens up a wealth of opportunities for user-driven data displays. Because SharePoint can provide and control access to key data, administrators can easily share data sources through the SharePoint interface.
- Commenting: One of the challenges of many BI solutions is getting feedback from end users. Visio Services 2013 makes it possible for users to comment on a full diagram or even a single shape. This allows teams to collaborate on data more effectively.
- Better, more flexible security: Visio Services can now leverage more types of data connections directly from within Visio, allowing for improved authentication management and giving better access to external data providers. It can also directly use the Secure Store Service to centralize data security management.
- More-dynamic interfaces: Visio 2013 provides a slew of new visualization tools, including new shapes, 3D beveling, reflections and themes. This makes it easier for users to not only get their data to other consumers, but to make it jump off the page.
PerformancePoint
Microsoft provides a dashboard-building capability and dynamic end-user querying experience to developers, power users and end users in the form of PerformancePoint. PerformancePoint is a dashboard-builder and data-visualization engine that started life as a standalone product, was merged with SharePoint 2010 and is now enhanced in SharePoint 2013 in some very helpful ways.
PerformancePoint breaks neatly in half: One half is a designer tool, called the Dashboard Designer; the other is its output, the dashboards themselves. Dashboard Designer is a desktop tool that looks and feels like everything in the Microsoft Office family, Ribbon and all. Designers use this tool to define data sources and regions on a page to hold such artifacts as filters, grids, charts and graphs. With the push of a button, Dashboard Designer creates Web Part Pages and connected Web Parts that represent the dashboard. Users access SharePoint using their Web browser, and because the dashboards are nothing more than Web Parts and Web Part Pages, they render just like anything else in SharePoint. These Web Parts are sophisticated and allow users to use filters for dynamic querying and, even better, drill down into the data in a variety of helpful ways.
SharePoint 2013 introduces some new UI enhancements and some server-side enhancements that continue to help PerformancePoint evolve toward a truly mature product.
UI Enhancements
In addition to a few trivial enhancements (such as adding a Dashboard Designer button to the Ribbon), SharePoint 2013 adds two major new enhancements to the UI that will be welcome to existing 2010 users:
- Theme support: It was nearly impossible to change the look and feel of core PerformancePoint functionality prior to this new release. It's now much easier. When you publish a dashboard from the designer to SharePoint, it will (for the most part) adopt the theme configured for the site in which it's deployed. Because the dashboard is just a set of pages with Web Parts, site owners can change the theme or create new custom themes without ever touching the Dashboard Designer itself. One important caveat: Chart Web Parts do not conform to the theme. Microsoft explains that these graphical elements are created on the server itself and served up as images to the browser. There are both performance and technical hurdles (primarily performance) that ultimately forced Microsoft to make this decision. That said, efforts have been made to enable charts to "play nice" with existing themes.
- iPad interface: The iPad is now a first-class client to PerformancePoint dashboards. This means, among other things, that iPad-using executives can access your PerformancePoint dashboards using this highly interactive form factor -- including the ability to use touch intuitively and effectively.
Server-Side Improvements
Microsoft made some less glamorous but very useful enhancements to server-side features:
PerformancePoint 2013 extends the SharePoint dashboard creation and delivery capability on both the UI and the server side. Some of the changes -- such as a friendly iPad interface and proper SDLC support -- really move the ball forward (although some organizations will say it's finally almost at the starting line). Others changes are less revolutionary. If you looked at and passed over PerformancePoint in the past, these new features should be enough to justify a second look. And if PerformancePoint is already integrated into your environment, these changes will be welcome.
Extended BI Functionality
It's apparent that Microsoft is serious in its campaign to "enable the knowledge worker." SharePoint 2013 will greatly expand Redmond's BI feature set. It's obvious that the goal of the extension of existing services is to make more functionality available that was normally sequestered away from the everyday user.
SharePoint 2013 expands on this functionality by leveraging big data; by pulling in information from throughout the enterprise; and by expanding each user's ability to present data in an attractive manner. If businesses embrace this methodology, it could mean more effective, more agile and more informed workplaces beyond the IT and development world.