Our Blog

where we write about the things we love



SharePoint 2010 Business Intelligence

I spend a lot of time in the intersection between two traditionally distinct but increasingly connected worlds: SharePoint and Business Intelligence (BI).

To me, business intelligence isn’t just about tables, graphs, and dashboards. It’s about decision making and providing ready access to the information required for making smart, informed decisions.

SharePoint, on the other hand, specialises in the bringing together of all sorts of information; and managing, classifying and collaborating on it with business area and subject matter experts.

It’s in the intersection of these two specialties that makes SharePoint Business Intelligence an ideal place to present your decision makers with the information they need to make effective and informed decisions.

In this post I’m going to cover the various forms of BI integration in SharePoint and when each is most effectively used.

Note: Most of these offerings are only available with the Enterprise Edition of SharePoint (in fact BI is one of the main compelling features of the SharePoint Enterprise license). That said, the Reporting Services capability is still provided under the SharePoint Standard license, and indeed SharePoint Foundation. For that reason I have mentioned the coverage alongside the respective technologies’ headings below.


SQL Server Reporting Services [All editions]                             

SQL Server Reporting Services (SSRS) has been around since SQL Server 2000, but ever since SQL Server 2005 was released SSRS has been integrated into SharePoint so the reports themselves become documents in SharePoint document libraries.

This allows them to benefit from the following SharePoint capabilities:

  • classification with business terms (e.g. Department, Business Unit, Report Owner, Reporting Frequency)
  • participation in workflows (e.g. notification, approval)
  • version control
  • placement on web pages as a web part or as part of a dashboard of information

SSRS Reports in a SharePoint Document Library

SSRS Reports in a SharePoint Document Library 

SSRS web part

SSRS web part

In terms of when to use SSRS reports it’s worth noting that they are great at delivering a specific outcome with a specific experience. They can be highly interactive and highly capable with a very specific presentation format, but they can only answer the questions that the BA who designed them (or the developer who built them) had in mind. Through the use of parameters, groupings and expressions, the user can ask and answer a number of questions, but generally the flexibility is limited when compared to other alternatives.

For that reason, I often suggest to customers that they use SSRS reports when they have predefined data, a predefined layout, and a predefined interaction, or when they wish to prescribe how users can interact with the information. Your power bill is a good example of this – it always looks the same, it presents the same information in the same place each month (although the numbers seem to get ever-larger!) and there’s no further information you can glean from it, apart from what the utility provider had in mind.

That said, SSRS reports are very effective and come with many data visualisation capabilities and two highly effective editing tools: Microsoft Visual Studio for the developer and Report Builder for the Business Analyst.


The true power of Reporting Services lies in the hands of the Report Writers.

Excel Services [Enterprise Edition only]

With Excel Services we combine the number crunching and enhanced visualisation capabilities of Excel with the document management and web part sharing capabilities of SharePoint.

Excel sheets can be produced by analysts and business area experts – often in response to requests from the board or for annual reporting. Rather than living on the file system in an ever-increasing collection, these sheets can be managed, classified and collaborated in SharePoint document libraries and then brought to life on dashboards and web pages in SharePoint. Furthermore, they don’t require the end user to be running Microsoft Office to view the report – the browser handles all that.

As with SSRS, however, the end user can only glean the information that the person who built it had in mind. By using parameters and groupings the user can interact with the data, although flexibility remains somewhat limited. 

Excel Chart as a SharePoint Web Part

Excel Chart as a SharePoint Web Part 

The true power of Excel Services lies in the hands of the Number Crunchers.


PowerPivot Services [Enterprise Edition only]

A free extension to Microsoft Excel 2010, PowerPivot allows users to consume information from a variety of sources (e.g. SQL Server, Microsoft Access, XML, text, and various OLEDB and ODBC sources). Users can link to data sources and define relationships between disparate sources, allowing pivot tables to be created against quite logically separate information.

PowerPivot Services can be compared to PowerPivot in the same way as Excel Services can be compared to Microsoft Excel. It allows the PowerPivot sheet to be surfaced in SharePoint and there are a number of visualisations and schedules in place to ensure data can be interacted with and refreshed appropriately.

I often describe PowerPivot as a beautiful way of bringing information together from a variety of sources – but with a warning: You are relying on the analyst to determine and model the associations between the various data sources and what those business rules ought to be. It is therefore at the ad-hoc end of the spectrum, so while a potentially very powerful analytic tool I often suggest it is best for analysts that:

  • don’t have the luxury of a data warehouse and cubes, or wish to analyse data that is not yet in their data warehouse.
  • understand the data and the relationships between the systems intricately.
  • are aware of the potential to draw the wrong conclusions if they misalign their data.

If these concepts are respected, PowerPivot presents an incredibly fast and effective way to combine information from disparate sources.

PowerPivot Gallery in Carousel View

PowerPivot Gallery in Carousel View

The true power of PowerPivot Services lies in the hands of the Business Analysts.


PerformancePoint Services [Enterprise Edition only]

Now we come to a technology that can start answering some of the ‘Why?’ questions and the ‘What about this?’ questions. PerformancePoint is all about creating Reports, Scorecards and Dashboards that consume data from data warehouses and cubes. Here we are standing on the shoulders of the data warehouse designers and all the hard work they have done in combining and transforming data into a ‘single source of the truth.’ Now we want to start surfacing the value of the aggregated information out to the business.

Business Analysts and Report Writers can create and deploy reports and scorecards using the provided Dashboard Designer tool, but with a difference. This time the end user can actually ask some of the questions that the designer may not have had in mind because they have the full power of the aggregated data behind them.

For example, a chart might present sales by region for 2011, but the user may wish to see a breakdown of the sales generated in the Northern Region by the products that were sold. This would have been quite difficult with SSRS or Excel unless the report or sheet designer was aware of this requirement and had built it in to the report or sheet. In Performance Point it’s a matter of right-clicking on the bar and selecting Drill down into Product. Additionally drill downs can be presented as a decomposition tree. All of these views and presentations can be selected and changed by the user, sorted, filtered and exported for further analysis.  

PerformancePoint Analytic Chart and User Decision

PerformancePoint Analytic Chart and User Decision 

PerformancePoint Decomposition Tree

PerformancePoint Decomposition Tree

In my opinion the Dashboarding capability of PerformancePoint is quite limited and I find it much more useful assembling dashboards manually from the individual PerformancePoint scorecards and reports, together with other useful SharePoint webparts such as list views, contacts and SSRS and Excel web parts.

The true power of PerformancePoint Services lies in the hands of the End Users.


Visio Services [Enterprise Edition only]

Microsoft Visio has also taken up residence in SharePoint and has its own Service Application. The main benefits of this are:

  • As discussed above, we can treat Visio documents in document libraries exposing them to all that SharePoint offers (i.e. versioning, metadata, collaboration, etc.).
  • Users can view Visio documents without having Visio installed on their PC.
  • Visio diagrams can be data-bound to data sources in the enterprise. This means, for example, that diagrams relating to a supply chain, network infrastructure, or floor plan can be coloured according to metrics in the business. Consider as another example your organisation’s strategic goals, coloured according to KPIs in the business that indicate progress toward these goals.

Visio also makes a foray into SharePoint territory as a visual design surface for SharePoint workflow and as a means to view the progress of a given workflow through its stages. But that’s probably a topic for another day.


Chart Web Part [Enterprise Edition only]

The Chart Web part allows graphs to be inserted into web pages in very similar fashion to those of Excel.

There are many chart visualisations available, as well as many ways to connect to data (including SharePoint list data, line of business systems and Excel Services). There are some limitations with this: for example you have to graph columns of data against other columns. In particular you don’t seem to be able to aggregate data in a chart (e.g. plotting a value against the number of times the value occurs) which is trivial in Excel. That said, there are a lot of advanced settings that can enable some quite compelling charts to be displayed. 

Sample of Chart Web Part

Sample of Chart Web Part


Putting it all Together: Dashboards and the BI Centre

Once you have some or all of these pieces in place, it’s a matter of deciding what tool is appropriate for what type of job and how you wish to present it to your Business Users. From individual reports for general use, graphs for the cafeteria and annual reports, through to targeted individual dashboards for managers and executives, there’s something in there for everyone.

What Tool is Right?

What Tool is Right?

If you have the Enterprise Edition of SharePoint there’s even a logical home for all the BI content: the Business Intelligence Centre Site Template. For other editions, it’s just a matter of creating lists and libraries manually to hold the SSRS reports and data sources. You can then assemble and present the web pages and dashboards of information to your heart’s content...


In summary

There is a wealth of Business Intelligence integration potential in SharePoint, most of which really comes to life with the Enterprise Edition and the backing of a data warehouse, but without both (or either) of these, there are still some highly effective ways to surface your business data in SharePoint.


Posted by: Bryce Saunders, Senior Consultant | 22 March 2012

Tags: SharePoint

Top Rated Posts

Blog archive

Stay up to date with all insights from the Intergen blog