Choosing a business intelligence tool in SharePoint Server 2010
Published: May 12, 2010
Microsoft has several business intelligence (BI) tools and
applications that have BI features, each of which is important to
understand as you decide what will work best for your situation. The BI
tools that you should use depend on the specific problems that you are
trying to solve.
Your daily business activities have associated information and
insights that emerge in three main areas of business intelligence:
personal, team, and organizational. There will be overlap across these
areas. For example, a company’s employees may use Microsoft Excel 2010
and Excel Services in Microsoft SharePoint Server 2010 to make relevant
business decisions at the corporate level. PerformancePoint Services
uses Excel, Visio Services, and Excel Services to complement its BI
tools to deliver a corporate dashboard that may reflect elements of
personal and team BI. By design, all Microsoft BI products interoperate
so that teams and people inside an organization can move across the
continuum of personal, team, and organizational and have all products
work together.
The following diagram shows tools from both SQL Server and
SharePoint Server to explain categories in which each tool is generally
used.
Note:
|
|
This diagram shows products and tools from Microsoft
Office, SharePoint 2010 Products, and SQL Server, for which separate
licenses are necessary.
|
SQL Server provides a primary data infrastructure and business
intelligence platform for trusted, scalable, and secure data. SharePoint
Server can be used with SQL Server reporting and BI tools to show BI
data in meaningful ways. To learn more about how SQL Server supports
business intelligence in SharePoint 2010 Products, see Overview of SQL Server in a SharePoint environment (SharePoint Server 2010) and Overview of SQL Server in a SharePoint environment (SharePoint Foundation 2010).
The following sections explain when you might use a particular BI tool.
Services in SharePoint Server for business intelligence
Excel 2010
Excel 2010 is the end user's analysis tool of choice for
viewing, manipulating, performing analysis on, generating intelligence
from, and creating reports about an organization's data. For more
information about Excel 2010, see Microsoft Excel 2010 (http://go.microsoft.com/fwlink/?LinkID=195375).
Excel Services
Excel Services is a SharePoint Server 2010 application
service that provides server-side calculation and browser-based
rendering of Excel workbooks. Excel Services can be used for the
following:
-
Real-time, interactive reporting to include parameterized what-if analysis
-
Distribution of all or part of a workbook for analysis by multiple users
-
A platform for building business applications
For more information about Excel Services, see What's new for Excel Services (SharePoint Server 2010).
Visio Services
The Visio Graphics Service is a service on the SharePoint
Server 2010 platform that lets users share and view Microsoft Visio
diagrams. The service also enables data-connected Microsoft Visio 2010
diagrams to be refreshed and updated from various data sources. For more
information, see Visio Services overview (SharePoint Server 2010).
PerformancePoint Services
PerformancePoint Services in Microsoft SharePoint Server
2010 is a performance management service that uses tools to monitor and
analyze business. It provides tools for building dashboards, scorecards,
and key performance indicators (KPIs). PerformancePoint Services can
help people across an organization make informed business decisions that
align with company-wide objectives and strategy.
-
You can bring together data from multiple data sources
(including SQL Server Analysis Services, Microsoft SQL Server,
SharePoint lists, and Excel Services) to track and monitor your data.
-
The visualization Decomposition Tree is a new report
type that lets you visually analyze higher-level data values from a
multi-dimensional dataset.
For more information, see What's new for PerformancePoint Services (SharePoint Server 2010).
Matching a tool with a broad scenario
The following applications can be used in the scenarios described.
|
Tool
|
Scenario
|
|
Excel 2010
|
Giving users browser-based access to a
server-calculated version of an Excel worksheet. Use Excel 2010 and
Excel Services to view, refresh, and interact with analytic models
connected to data sources. Also use them for analysis, filtering, and
presentation of locally stored data.
|
|
Excel Services
|
Sharing content with multiple persons across an
organization. Excel Services lets you take authored content in Excel
2010 and make it available in an Internet browser. Excel Services is
also used with a model that can be widely distributed (for example, a
mortgage calculator). In both scenarios, Excel Services enables the
author to publish targeted content without making the underlying
intellectual property available to consumers.
|
|
PerformancePoint Services
|
Creating dashboards, scorecards, and key performance
indicators (KPIs) that deliver a summarized view of business a
performance. PerformancePoint Services gives users integrated analytics
for monitoring, analyzing, and reporting.
|
|
Visio Services
|
Building a visual representation of business
structures that are bound to data. Examples include processes, systems,
and resources. An engineer can use the visualization to create
data-bound objects to represent a process.
|
SQL Server Reporting Services in SharePoint Server
SQL Server Reporting Services provides tools and services to
help you create, deploy, and manage reports for your organization in
your own Web site or in SharePoint Server. It also provides programming
features that enable you to extend and customize reports. The report
authoring tools work with an Office-type application and are fully
integrated with SQL Server tools and components, and also the SharePoint
Server environment. You can build reports on SharePoint lists, publish
reports to SharePoint Server 2007 or 2010, incorporate reports inside
your portal by using a Web Part for reports, and fully manage your
reports published in SharePoint document libraries.
When you use SQL Server Reporting Services (SSRS) with
SharePoint Server, there are two modes to select from. The standard mode
is known as "Connected mode". It requires SharePoint Server, the SSRS
add-in, and the SQL Server 2008 R2 Report Server. The new mode is "Local
mode". It is a lightweight setup for Reporting Services to integrate
with SharePoint Server. It only requires SharePoint Server and the SSRS
add-in.
Use SQL Server Reporting Services when you want to deliver
reports that publish at set intervals and on-demand. It's also suitable
where report requirements are well established and customers are not
always familiar with the underlying dataset. To view an overview with
links to product documentation, see Documentation for SSRS reports in SharePoint (overview).
PowerPivot for Excel 2010
Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel
2010 extends Excel to add support for large-scale data. It has an
in-memory data store as an option for SQL Server Analysis Services. By
using PowerPivot for Excel, you can merge multiple data sources to
include corporate databases, worksheets, reports, and data feeds. There
are client and server components for PowerPivot. The client is an
extension to Excel workbooks that contain PowerPivot data that can be
published to SharePoint Server 2010. Microsoft SQL Server 2008 R2
PowerPivot for Microsoft SharePoint 2010 is the server-side component
that supports PowerPivot access in Microsoft SharePoint 2010 Products,
much like Excel, which can be published to Excel Services.
Use PowerPivot for Excel when you want to combine native
Excel functionality with the in-memory engine to let users interactively
explore and perform calculations on large data sets and quickly
manipulate millions of rows of data into a single Excel workbook for
ad-hoc reports.
For more information about PowerPivot, see Overview of PowerPivot documentation (SharePoint Server 2010).