This article can also be found in the Premium Editorial Download "SharePoint Insider: SharePoint-BI integration: BI data for everyone."
Download it now to read this article plus other related content.
One of the big selling points of today’s business intelligence (BI) tools is that they enable mere end users – as opposed to super-smart IT analysts – to
Typically, information is delivered to those decision makers via customized BI dashboards, scorecards and reports, most often presented through a dedicated BI application or Web portal. That likely will continue to be the case for some time to come, but we’re starting to get other options. Specifically, for a BI system based on SQL Server, you can integrate a great deal of information right into SharePoint 2010, putting BI data front and center in your organization.
SharePoint, in fact, has been a potential target for BI for a long time. After all, as more organizations put more and more data into SharePoint, more business users are spending more time working in the collaboration software. So why not put BI data where users’ eyes are already pointed? In the past, SharePoint-BI integration required third-party tools and, in many cases, custom development. That still might be necessary depending on the circumstances, but the integration process has become easier thanks to new BI-focused features released by Microsoft in the latest versions of several products.
For example, SQL Server Reporting Services can now deliver reports directly to SharePoint 2010. Better yet, report definitions can be stored in SharePoint, along with report parts. The definitions, which detail different report designs, can then be shared with other people in an organization, enabling them to use existing reports as a starting point in creating new ones.
Report parts are components – charts, graphs, tables and so on – that are meant to be used in reports; within SharePoint, less-technical users can assemble reports simply by specifying some columns and plugging in a few pre-designed parts. Meanwhile, users who are more technically skilled can design and share their own report parts, helping the entire organization to collaborate more effectively. Report designs can also be version-controlled in SharePoint, providing a history of previous versions for tracking purposes and for use in restoring a particular design if someone makes unwanted modifications to it. With the right architecture in place, reports can even be distributed to business partners and other external users via extranets or the Internet.
SQL Server 2008 R2’s Master Data Services (MDS) tool also integrates with SharePoint. MDS is master data management software designed to create a “single version of the truth” for an organization’s most important data, helping to ensure that all business users are looking at consistent information, including within BI systems. Changes to MDS-controlled master data are handled via a workflow process, and SQL Server supports sending data to SharePoint 2010 and using a SharePoint workflow to manage approvals and data validation. MDS data can also be published to SharePoint, usually in read-only mode – making it accessible through a unified console to everyone who can benefit from it, without allowing everyone to change the data.
PowerPivot puts exclamation point on SharePoint, BI combination
Probably the most exciting BI-related feature for SharePoint is Microsoft’s PowerPivot extension for Excel 2010. Building on the PivotTable technology included with the last several versions of Excel, PowerPivot uses a combination of server- and client-side processing to perform in-memory analytics on complex data sets. Those data sets can contain tens of thousands, or even millions, of rows, enabling your organization’s Excel jockeys to create complex data analyses without having to wait for IT to load information into a data warehouse or spin up a new data mart. Best of all, completed PowerPivot tables – along with graphs and other data visualizations – can be exported to SharePoint 2010 and shared with other users.
PowerPivot for SharePoint isn’t for faint-hearted organizations: It works best in conjunction with SQL Server systems sporting multiple processor cores and gigabytes of memory, key prerequisites for the kind of in-memory analytics enabled by the software.
But with the right hardware, combining SharePoint and PowerPivot truly democratizes BI. Business analysts and decision makers with strong Excel skills can simply draw data from Excel and other data sources, crunch the numbers in real time and make the resulting PowerPivot workbook files available in SharePoint for collaboration purposes. The IT department doesn’t completely step out of the picture, though: IT staffers can use the software’s management dashboard to track user-created analytical spreadsheets, apply version controls through SharePoint and monitor resource utilization on the servers.
The whole point of BI has always been to pull multiple data sources together into a single view, helping business executives and other users make better decisions by giving them clear, concise data. With the combination of SharePoint 2010 and SQL Server 2008 R2 – not to mention a healthy dose of Excel via PowerPivot – you can more easily surface BI capabilities to more users while still keeping the underlying data safe and secure. Putting BI data in more hands ideally means even better business decisions, and that’s something worth working toward.
Out with the old, in with the new
Business intelligence (BI) is one of the software industry’s fastest-growing and more dynamic product categories. BI vendors are continuously introducing significant new features and capabilities – and that means you might have to adopt a somewhat more-aggressive-than-usual approach to software upgrades in order to take full advantage of your BI tools.
For example, while SQL Server 2008 and SharePoint 2007 support some useful BI features, accessing the best and broadest functionality requires the latest that Microsoft has to offer: SharePoint 2010, SQL Server 2008 R2, Excel 2010 and the PowerPivot for SharePoint add-in.
The Excel piece of that is often the most politically difficult in organizations, because deploying a new version of Office can be a big undertaking. Keep in mind that Office 2010 is file-compatible with older versions such as Office 2003 and 2007, so it’s feasible to simply upgrade the users who will make the most use of BI tools like PowerPivot.
It’s also not uncommon to dedicate one or more SQL Server systems to BI uses, and it’s possible to set up a standalone SharePoint farm to integrate with those SQL Server machines. You don’t have to upgrade every SQL Server or SharePoint server to the latest software releases, but running the latest versions on key systems will help maximize your organization’s BI capabilities.
About the author
Don Jones is a senior partner and principal technologist at strategic IT consulting firm Concentrated Technology LLC.