By: Ray Barley | Comments | Related: > SharePoint
Problem
Business users are clamoring for information. Their needs change on a daily basis. They want reports, scorecards, and slick visualizations presented in dashboards that are personalized and security-trimmed. Every time you complete one of these tasks, there's several more in the queue. They also want the ability to do their own ad-hoc reporting and analysis. You're struggling to keep up. What can you do? In this tip I'll review a new book that focuses on how to build and deliver BI solutions that leverage SharePoint 2010.
Solution
Microsoft SharePoint 2010 Business Intelligence Unleashed is a new book that focuses on leveraging SharePoint 2010 to design and develop BI solutions. Within the Microsoft BI stack SharePoint is the preferred way to package BI solutions. SharePoint provides a browser-based interface that is easy to use. With the 2010 version of the product, all the components that you need for a BI solution are nicely integrated into the product.
There are many excellent resources available that address implementing the data warehouse and SQL Server Analysis Services cubes required for a BI solution. This book does not cover these topics; rather it provides step-by-step coverage of how to leverage SharePoint to develop the end-user BI solution.
While designing and developing the BI solution has typically been done by IT professionals, SharePoint 2010 is the first version of the product that enables ordinary business users to participate. A BI solution built on top of SharePoint uses the familiar built-in features of the product such as document libraries, web part pages, and web parts.
The book is organized into the following sections:
- Part I - Getting Started explains how to prep SharePoint 2010 for designing and developing BI solutions
- Part II - Reporting Services focuses on the integration of SQL Server Reporting Services with SharePoint
- Part III - PerformancePoint Services discusses building dashboards
- Part IV - PowerPivot explains the integration of SQL Server Analysis Services and SharePoint 2010 that enables business users to develop pivot tables and pivot charts using Excel 2010
- Part V - Visio Services explores visualizing Visio diagrams and BI data
- Part VI - End-to-End Solutions provides a scenario-based BI solution
- Part VII - Troubleshooting highlights some solutions to common issues in designing and developing BI solutions with SharePoint 2010
In the following paragraphs I will provide some additional details about each section. With the exception of Reporting Services integration with SharePoint 2010, all of the features discussed require the SharePoint 2010 Enterprise edition.
Part I - Getting Started
Part I covers the Business Intelligence Center and Excel Services. The Business Intelligence Center is a site template specifically designed for reports, dashboards, data connections and so on. Excel Services is the component of SharePoint that allows users to publish Excel workbooks and render them in the browser (Excel is not required to view a workbook). In addition Excel Services includes a calculation engine and web services for programmatic access to workbooks.
Many people think you turn Excel Services on and it just works. While this may be true to an extent, there are many configurations and settings that can be tweaked to optimize performance. Knowing what is available will help you to provide the optimal user experience.
Part II - Reporting Services
Reporting Services can be run in native mode or SharePoint Integrated mode. In native mode you use the Report Manager web application to manage reports and you can render reports on a SharePoint page by using the web parts included with Reporting Services. SharePoint Integrated mode eliminates the Report Manager web application, stores reports in a SharePoint document library, and provides all report management functions via the SharePoint document library. You can render a report right from the document library or you can use the SharePoint Integrated mode web part to customize report rendering on a web part page.
Part III - PerformancePoint Services
PerformancePoint Services (PPS) is the component of SharePoint 2010 that is used to build and render dashboards. It was originally a separate product but now it is completely integrated into SharePoint 2010. This section covers how to setup and configure PPS then delves into the details of how to use the Dashboard Designer application to create Analytic Grids and Charts, KPIs, Scorecards, Dashboards and Filters. New features such as the Decomposition Tree and the KPI Details Report are also covered. PPS is truly the jewel of the SharePoint BI components as it allows business users to create their own dashboards.
Part IV - PowerPivot
Business users need to be able to access and analyze data to do their jobs. Over the years, Excel has become an indispensable tool for performing this data collection, analysis, and reporting. PowerPivot is an add-in for Excel 2010 that provides enhanced features for combining data from multiple sources and producing pivot tables and pivot charts. PowerPivot can process millions or rows of data quickly, enable the creation of relationships between data from disparate sources, all in the familiar Excel product. SharePoint 2010 provides a centrally managed repository for Excel PowerPivot workbooks providing easy access and the necessary security that is required.
Part V - Visio Services
A picture is worth a thousand words. This is why Visio Services in SharePoint 2010 is an extremely powerful BI tool. Visio Services enables data-bound Visio drawings to be viewed in a web browser. Authorized users can render the diagrams in the browser without having Visio or the Visio Viewer installed on their local computer. This section will show you how to share dynamic, data-driven Visio diagrams within your organization.
Part VI - End-to-End Solutions
This section of the book covers building a complete BI solution from scratch. The example includes creating a site collection based on the Business Intelligence Center site template, a report using Report Builder 3, and a dashboard using PerformancePoint Services. The dashboard includes a scorecard, analytic chart, the reporting services report, and filters that allow the user to select criteria from dropdown lists and refresh each of the dashboard components.
Part VII - Troubleshooting
The final section of the book discusses some common issues that you may encounter when building the BI solution with SharePoint 2010.
Next Steps
- SharePoint is at the center of the Microsoft BI stack, providing the end user delivery mechanism for the BI solution. This book is an attempt to provide the necessary information that you need to leverage SharePoint 2010 to build the BI solution.
- Follow this link on Amazon to order a copy of the book.
- Take a look at How to Configure PerformancePoint Services in SharePoint 2010 - Part I & Part II
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
