Microsoft Office SharePoint Server 2010 is a versatile product. It enables people to work together in one environment where they can easily store and share information with others and also search for business information - regardless of who created it, what format it is in, or where it lives.
The management information stored in your data warehouse is one of the most important pieces of business information that is available within your organization, so it makes perfect sense to also integrate this in SharePoint Server since business people will be able to then access all their information –whether it will be quantitative or textual- in one central place and portal, in a familiar environment.
Publishing Business Intelligence content into SharePoint can be done by integrating the different front end applications like SQL Server Reporting Services, Microsoft Excel, Microsoft Excel Power Pivot and Microsoft Visio. Next to integrating existing front ends, some new BI front end applications will also become available when using SharePoint: Performance Point Server Scorecards and Dashboards, Pivot Viewer, Power Pivot for SharePoint, SQL Server Reporting Services Report gallery and Central connection library.
So adding the Microsoft BI tools to the SharePoint server does not only enable you to integrate all existing documents and applications in one location, but also provides you with additional ways of visualizing your information through new Business Intelligence functionaliy.
In this insight, we have a more detailed look at the different BI components that integrate with Microsoft Office SharePoint server 2010.
Central Connection Store
In SharePoint 2010 it is possible to create libraries that contain different types of connections. These connections can be Office connections (e.g. for Excel, Visio …) or connections used for Reporting Services reports, Power View and Performance Point.
Having these connections in a central location makes it possible for end users to just use these existing connections without having to go through the processes of creating a connection themselves, and remembering things like server name, database name, authentication type ...
But also from an administrative point of view having a central connection library makes life easier. When a back end environment is changed, only a limited amount of effort is needed from the administrators and end users. The central connection needs to be changed, and from that moment on all users that connect using the central connection are on the new environment or database without having to change anything.
SQL Server Reporting Services
Reporting Services is the reporting engine in the SQL Server suite. It comes with its own web portal environment by default, which makes it easy for users to find all reports in one location. Next to having all reports in one location, the web portal also provides the possibility to create different kinds of subscriptions (to e.g. automatically receive the report in an email when data has been updated) with different kinds of rendering options ( xls, pdf, html, … ), run reports on snapshots of data, author reports with report builder, alter report data sources and set/change default parameters or other report related properties.
But in integrating Reporting Services in SharePoint 2010 additional possibilities arise as well. In SharePoint 2010 it is possible to create reporting galleries. These reporting galleries make it possible for end users using Report builder to reuse existing components like matrixes and graphs in their own reports.
Figure
2: Reporting Services 2012 Report Builder Report Part Gallery SharePoint 2010
With the latest edition of Reporting Services, Microsoft SQL Server Reporting Services 2012, it is also possible to create "alerts”. Alerts can be seen as notifications an end user can define on data in a report. When an end user creates an alert, he actually creates a notification according to a rule that he defines. This creation of alerts is only available when Reporting Services is integrated in SharePoint Server.
Figure 3: Reporting Services 2012 Report in SharePoint 2010 Creating a Data Alert
Office services
While Office integration in SharePoint is not specific for Business Intelligence, it is an important driver. The integration of Office documents is not limited to storing them in a central location, but goes much further with use of the different ‘services’. These services do not only make it possible to open and view the document without having the need for a local Office installation, but also to change and interact with the information.
The most known Office service in relation to BI is probably "Excel Services”. Excel Services makes it possible for a user to open an Excel workbook, alter page filters, drilldown in a PivotTable… And when you need more authoring functionalities you can open the workbook in a local Excel application with the click of a button.
And this is true for all Office services, be it Excel Services, Visio Services or Word Services.
PerformancePoint Services
Performance Point Services 2010 is the solution provided by Microsoft to build dashboards that can be made available to users through SharePoint. With the Dashboard Designer it is possible to create KPI’s, bundle these KPI’s on a scorecard and finally arrange these scorecards on a dashboard. Next to creating KPI’s with the standard traffic light icons, it is also possible to create graphs.
Next to creating graphs or reports in the dashboard designer, it is also possible to reuse existing reports created in Reporting Services or Excel services. Reusing the existing reports gives the designer the possibility to compose his own dashboards. Not only combining existing reports in dashboards is interesting, but also adding filters components to link these filters to the reports is very useful. Reporting Services and Excel services provide the possibility to work with parameters and because the filter component on a dashboard can then be linked to these parameters it becomes possible to filter data in several reports with only one filter component. When the filter component is changed all linked reports are automatically updated.
Figure 4: SharePoint 2010 PerformancePoint Services Dashboard
Another nice visualization available in PerformancePoint Services is the Decomposition Tree. It is one of the components that was available in ProClarity, software that was bought by Microsoft in 2007, and now it is available again in PerformancePoint Services. Although not directly available when creating reports, the decomposition tree is accessible when right clicking on a value that sits in a view that holds data coming from SQL Server Analysis Services. You would typically use a Decomposition Tree to see how an individual value in a report or a scorecard can be broken down into its contributing members. The Decomposition Tree automatically sorts results and applies an inline Pareto chart to the data, so you can quickly see the highest contributors to a particular report value. You can also see trends across individual members that contribute to an overall value.
Figure 5: Decomposition Tree PerformancePoint Services 2010 in SharePoint 2010
PerformancePoint Services is only available through SharePoint.
PowerPivot for SharePoint
Once models in PowerPivot for Excel have been created, the next step is sharing that information with others. But as with normal Excel workbooks there are not that many good sharing techniques available. A workbook of several megabytes is not something you would want to mail around. Putting it in a shared folder has its complications as well. That is where SharePoint 2010 can help with the PowerPivot for SharePoint services.
PowerPivot for SharePoint does not only enable the business user to share his created model and reports with others, but also adds other advantages of PowerPivot for Excel.
Since pictures say more than a thousand words, the PowerPivot gallery was added on top of the normal library list. This type of library has several skins that can be applied to preview the reports:
- Gallery : This shows all workbooks in list like view with a thumbnail picture for all Worksheets in workbook.
Figure
6: PowerPivot Gallery View With PowerPivot Workbooks And PowerView Reports
- Theater: This shows a directional area on the bottom of the page. As you click the arrows, the primary image on the screen reflects an image of a worksheet from a workbook in the gallery. On the bottom left corner of the screen, the name of the workbook that is associated with the current worksheet is shown.
Figure 7: PowerPivot Theatre View With PowerPivot Workbooks And PowerView Reports
- Carousel: This shows a carousel of pictures. This picture is the primary picture that is associated with that particular workbook.
Figure 8: PowerPivot Carousel View With PowerPivot Workbooks And PowerView Reports
Another important advantage is the integration with Excel Services. As with a ‘normal’ Excel workbook it is possible to open a PowerPivot Excel in Excel Services. This enables the user to browse the workbook and use the reports without having to download the Excel workbook. Not even a local Excel installation is needed. When opening the workbook, the data of the model is loaded into the Analysis Server running on the SharePoint farm so that every user can benefit from a high performance experience.
Most of the data used in these models needs to be refreshed frequently. So instead of requiring users to manually download the workbook to their desktop, you can refresh the data set and upload an updated copy of the workbook in a batch process overnight. PowerPivot for SharePoint allows business users to setup a data refresh schedule per workbook.
Power View
Power View is the brand new data visualization tool that is part of SQL Server 2012. Although part of the SQL Server 2012 product suite, SharePoint is required to use this new tool. It is currently only possible to create reports on top of a tabular model ( see here for an introduction to the differences between a tabular model and a multi-dimensional model). This can be a PowerPivot workbook published in SharePoint or a Tabular model hosted in SQL Server Analysis Services.
Figure 9: PowerView Report In Design
The purpose of this module is to be able to present the data in an intuitive and meaningful way using highly interactive visualizations, animations, smart querying and rich storyboarding (a feature that allows users to embed charts and graphs in PowerPoint presentations making it possible to show the effects of data changes) presentation capabilities. With this tool you can forget all the complex multidimensional concepts like dimensions, measure groups etc. and focus only on data presentation. PowerView is an answer from Microsoft to the success of some of the paradigms that QlikView introduced.
Figure 10: PowerView Report Exported To Microsoft PowerPoint
Other advantages that SharePoint offers when integrating
Until now we have mainly discussed the different components or applications that integrate with SharePoint 2010. But next to these BI applications that have integration with SharePoint 2010, there are also several standard SharePoint components that can be used for Business Intelligence.
Having the information available in only one environment is the most obvious advantage. If someone wants to get a report from Reporting Services and also open an Excel workbook created with PowerPivot, this can be done from one single environment. Furthermore, security can be set up for just that one environment and is not scattered all over the reporting portal and shared folder structures.
Searching for information is possible when all reporting is stored in SharePoint. If you want to have information on a specific topic, you can easily use the search functionality of SharePoint and it will return all matches independent of the location of the information, be it in an Excel workbook, a Reporting Services report or Dashboard.
Another possibility in SharePoint is to create webpages. These webpages then contain several Web Parts that show information. This information can be a library list, Business catalog information or specific reports. So with these web parts it is possible to create a page that shows a Reporting Services report, an Excel workbook, a dashboard and other information on one page.
Figure 11: SharePoint 2010 webparts
Conclusion
Microsoft has made tangible enhancements to the integration of Microsoft Business Intelligence in SharePoint. Over the last years, Microsoft has invested strongly in the integration and will continue to do so over the next years. Microsoft believes that SharePoint is the central application in the information worker’s professional life, also for any kind of Business Intelligence needs.
Armed with that vision, Microsoft has made some bold decisions by making some of the new features in SQL Server 2012 exclusively available within SharePoint 2010. Power View and data alerts on Reporting Services report are currently only available in combination with SharePoint 2010.
Next to some components only being available in SharePoint 2010, it is also important to keep in mind which edition of SharePoint you are using. Most of the previously mentioned integrations are only available in the Enterprise Edition, being the top line edition license-wise.
So for organizations that do not already have SharePoint in house, when they start with Microsoft Business Intelligence, there is a significant investment to be made. For organizations that have already implemented the Enterprise Edition of SharePoint, we encourage them to integrate their Business Intelligence by deploying Reporting Services and the above mentioned modules in this integrated mode. This will already enable them to reap significant integration benefits and it will help them to fully leverage the very advanced SharePoint and Business Intelligence integration that Microsoft is offering in their latest releases.
