Skip navigation
Business Intelligence in SharePoint 2010

Business Intelligence in SharePoint 2010

New components make SharePoint a top BI platform

Business Intelligence (BI) in Microsoft SharePoint 2010 is an enormous topic area. The goal of this article is to make sense of and introduce the key BI framework components in the SharePoint 2010 platform.

In doing so, I hope to bring focus to the blurry line between BI tooling and applications that, together, contribute to the richness of this platform's offering. BI framework components for SharePoint 2010 include Microsoft Excel, Excel Services, SQL Server Reporting Services (SSRS), PerformancePoint Services, and SharePoint itself.

 A wide range of applications augment the BI framework, and they fall roughly into three categories: data sources, integration tools, and UI components.

Data sources include relational databases (e.g., SQL Server), multidimensional sources (e.g., Analysis Services), data feeds (e.g., SharePoint lists), and text files (e.g., an XML file). Integration tools pull data so they can be consumed by the BI framework components such as Business Connectivity Services (BCS) and SQL Server Integration Services (SSIS).

UI components range from tools that facilitate the collection of data, such as Access coupled with Access Services and InfoPath Form Services to tools that supercharge visualization capabilities, like Visio coupled with Visio Services.

These powerful tools that augment the BI platform are mentioned only in context with the BI framework components and each deserves deeper exploration in other articles.

Excel and Excel Services

Microsoft's mission for Excel has been clear since the release of Microsoft Office SharePoint Server (MOSS) 2007: Make Excel pervasive, centralized, easy to use, and a powerful BI development platform.

The primary components that make this possible are Excel 2010 with the PowerPivot for Excel 2010 add-in, Excel Services in SharePoint, and SQL Server Analysis Services (SSAS) in SQL Server 2008 R2.

 SSAS 2008 R2 includes the Analysis Services with SharePoint Integration feature (aka PowerPivot for SharePoint or Gemini BI). This feature, like Excel Services, is required to deploy a PowerPivot-enabled Excel 2010 workbook to SharePoint 2010. Both Excel Services and PowerPivot for SharePoint require the Enterprise SKU of SharePoint 2010.

Excel 2010

The BI development capabilities alone in Excel 2010 make it worth the upgrade from previous versions of Excel. Although there are numerous improvements to this application, the focus here will be on how the BI story has improved.

Without SharePoint 2010, you still have the ability to create PowerPivot-enabled workbooks to connect and consume data, leverage Named Sets, and create multidimensional expressions (MDX or Analysis Services queries).

These features simplify Excel's ability to interact with and present BI data. From a reporting perspective, Excel 2010 has been enhanced with visual components such as Slicers and Sparklines.

PowerPivot Client Add-in

The free PowerPivot add-in supercharges Excel so that it can consume, filter, and analyze massive amounts of data derived from data warehouses and other stores. Behind the market name, PowerPivot is an Analysis Services add-in.

This add-in makes Excel a first-class consumer of Analysis Services data and many other data sources. Figure 1 shows the PowerPivot application launched from the PowerPivot window option on the PowerPivot tab in Excel 2010. There are two versions of PowerPivot, one for Excel 2010 32-bit and one for the 64-bit version.

Data Analysis

Excel 2010 lets you create Named Sets and Dynamic Sets, perform what if analyses, and add Slicers and Sparklines to your Excel reports. In addition, features such as Repeating Item Labels and CUBE-specific functions marry the capabilities of OLAP with the UI-rendering power of Excel.

After you set up a list in Excel that consumes data from Analysis Services and you've configured filtering on rows, you can take advantage of an Analysis Services scripting language feature called Named Sets. Named Sets are created using the Analysis Services MDX query language.

The output of a Named Set is a group of items that can be reused elsewhere, such as in reports or other MDX queries. You can also configure a Named Set to recalculate results based on filter context.

A Named Set configured in this way is referred to as a Dynamic Set. For more information on Named Sets, see PivotTable Named Sets in Excel 2010.

From OLAP data in Excel, you can also perform a what-if analysis on the data by adjusting a value to see how that change affects other parts of your calculation.

Forecasting analysis is a common scenario where this feature is extremely useful. In a what-if analysis, you review the data change and, if you decide the change should be made, commit the change to the back-end data source right from Excel.

If you decide the change shouldn't be made, you can discard it without affecting the back-end data source. For more information on this feature, see Excel 2010 PivotTable What-If Analysis (Writeback).

Filters have been a feature of Excel pivot tables for many years but haven't been easy for all Excel users to discover. In Excel 2010, Slicers provide a more visually compelling alternative to pivot table filters.

Slicers can filter the returned data just like pivot table filters do, but they can also filter data on other Slicers on the page through cross filtering, as Figure 2 shows.

In this case, The Quarter Slicer shows quarters 1 to 3 selected, while the Category Slicer shows Beverages and Dairy Products selected. As a result, the chart shows sales data for Beverages and Dairy Products for quarters 1 to 3. Like many other visual elements in Excel, there's a rich set of formatting options available for Slicers.

After using the Excel 2010 BI features outlined here and many others that you'll discover in the product, you're left with a local workbook. Although you could email the workbook to other users who need to review the data, centralizing the report data and making parts of the report data reusable is the next logical step.

This is where Excel Services and PowerPivot for SharePoint enter the picture.

Excel Services

From its introduction in MOSS 2007, Excel Services allowed for centrally viewing and interacting with Excel data from SharePoint rather than proliferating workbooks or worksheets over the network by email or other means.

Even with this centralized control, it was common for users to export all sorts of data into Excel and perform ad hoc analysis locally.

Microsoft's goals for Excel Services in SharePoint 2010 is to make it the first place you go to render previously created Excel visualizations (workbooks and chart data) and perform ad hoc BI analysis tasks.

Another important goal is to maintain visual fidelity between Excel 2010 and Excel Services in SharePoint 2010. Excel Services continues to be part of the SharePoint Enterprise SKU in SharePoint 2010.

In MOSS 2007, the web-based dashboard presented by Excel Services was simply a pivot table. This is a common dashboard to create in MOSS 2007 with Excel Services. It supports sort and filter capabilities.

Excel Services for SharePoint 2010 takes this capability further with added interactivity with immediate click filters and Slicers. In addition, you can now enter formulas into cells in the web browser and formulas auto-complete, essentially behaving like they do in the Excel client. Figure 3 shows an Excel workbook with two tabs, Source Data and Report.

The Report worksheet contains a pivot table, chart, Slicer, and Sparklines. Quarters 1, 2, and 3 are selected in the Slicer.

Figure 4 shows the same workbook deployed to SharePoint 2010 and, using Excel Services, rendering perfectly in Google Chrome. Google Chrome isn't officially supported. However, cross-browser support is significantly better with SharePoint 2010. For more information on support, see the article "Plan browser support (SharePoint Server 2010)".

The view in SharePoint 2010 goes beyond the single pivot table to show you data from other members participating in the query. Once published via Excel Services, Excel Workbooks and the visualizations created within them become available beyond these common interfaces (Excel and the web browser) because of the REST API.

With REST, the data can be delivered to many other application endpoints and in many different forms from images to Atom feeds.

With REST, users can connect to Excel data through Excel Services from any REST-enabled client. For example, this URL, client4/sites/BI/_vti_bin/ExcelRest.aspx/PowerPivot%20Gallery/Products%20Report.xlsx/model/Charts('Chart%201')?$format=image, entered in a browser returns a chart from an Excel Services workbook, as Figure 5 shows.

The new Excel web part replaces the awkward parameter pane that was present in the equivalent web part in MOSS 2007. Now, you can click in the Excel web part, update cell values, and other web parts on the page will update based on that simple, interactive change.

This is achieved through the web part consumer/provider connection interface. Also, page post back is gone because of the asynchronous callback capability provided by AJAX. 

PowerPivot for SharePoint

After you have created your PowerPivot-enabled workbook in Excel 2010, you can make the capabilities you've developed in the workbook available to a wider audience by publishing it to SharePoint 2010.

To leverage the capabilities built into your PowerPivot-enabled workbook, SharePoint must be running both Excel Services and PowerPivot for SharePoint (aka Analysis Services with SharePoint Integration or Gemini BI).

PowerPivot for SharePoint is installed by default on a standalone instance of SharePoint. Currently, PowerPivot for SharePoint requires that you have either the Enterprise/Enterprise Evaluation, Developer, or Data Center editions of SQL Server 2008 R2.

For the November CTP version of SQL Server 2008 R2, the Enterprise Evaluation edition is widely available. To get additional information on other requirements and installation of PowerPivot for SharePoint, see the Microsoft article, "How to: Install PowerPivot for SharePoint on a New SharePoint Server."

If you have an existing SharePoint farm and PowerPivot for SharePoint isn't installed, see "How to: Install PowerPivot for SharePoint on an Existing SharePoint Server." Note, SQL Server PowerPivot for SharePoint appears as Analysis Services with SharePoint Integration in SQL Server 2008 R2 November CTP setup routine, as Figure 6 shows.

In the SharePoint 2010 RC Central Administration interface, the PowerPivot service application is named SSAS Mid-Tier Service. Also, the site collection-scoped feature to make PowerPivot for SharePoint available in your site collection is currently named Analysis Services Integration Site Collection Feature.

There are other name changes from the beta versions of PowerPivot and their related services. However, the name changes I provided should help you through the installation documentation with the SharePoint 2010 RC and the SQL Server R2 November CTP. The names should stabilize at this point.

After installing and deploying PowerPivot for SharePoint to a site collection in your SharePoint farm, you'll see the PowerPivot list and site template that Figure 7 shows in the SharePoint Create dialog box.

The PowerPivot Gallery allows you to upload PowerPivot-enabled worksheets and other related artifacts to the library. Use this template when you want to add the gallery to an existing SharePoint web.

The Data Feed Library contains the Data Service content type. You use this library to upload online data sources that provide XML tabular data in the Atom 1.0 syndication format. For example, you can publish tabular data from an Excel Services spreadsheet using the REST API into the Atom format.

Other users can then connect to this data feed to retrieve data from an online data service. The PowerPivot site template creates a web containing the PowerPivot Gallery and other collaboration components.

Reporting Services

Reporting Services is a mature part of the SQL Server BI stack. As SharePoint picked up steam with Windows SharePoint Services (WSS) 3.0 and MOSS 2007, Microsoft decided to create an integration capability with SharePoint with the release of SQL Server 2005 SP2.

This integration mode is called Reporting Services in SharePoint Integrated mode. This capability has steadily improved, and SQL Server 2008 R2 brings major improvements to the Reporting Services infrastructure.

Reporting Services in SharePoint Integrated mode, since first introduced, has been difficult to install and configure. For example, installing the Reporting Services add-in often fails and rolls-back the installation without reporting in the interface the reason for the failure.

It's only after you review the log file it generates in the %temp% folder that you can discern the reasons for the failure.

With the release of SQL Server 2008 R2 and SharePoint 2010, installation and configuration is considerably easier and more flexible. For example, you can now install the Reporting Services add-in for SharePoint 2010 whether or not Reporting Services or even SharePoint 2010 is installed.

The configuration elements for Reporting Services include the Reporting Services Configuration Manager, part of the SQL Server installation. Using this tool, you configure Reporting Services for SharePoint Integrated mode. The other piece is the Reporting Services add-in for SharePoint.

You can benefit from some of the improvements in Reporting Services even in WSS 3.0 or MOSS 2007 by running Reporting Services in SQL Server 2008 R2. I'll point out the benefits for WSS 3.0 or MOSS 2007 later in this section.

The Reporting Services add-in, however, is specific to the version of SharePoint. For my configuration of SharePoint 2010 RC, I used SQL Server 2008 R2 November CTP Reporting Services add-in for Microsoft SharePoint Technologies 2010 (RS add-in). You can download this build at the Microsoft site.

There are two modes of operation for Reporting Services in SharePoint Integrated mode, local mode and connected mode. Before SharePoint 2010 and SQL Server 2008 R2, the only mode available was connected mode.

In connected mode, Reporting Services is a requirement to run the RS add-in. Local mode is a SharePoint 2010 feature and isn't available with the RS add-in for Microsoft SharePoint Technologies (the RS add-in version for WSS 3.0/MOSS 2007).

In local mode, the RS add-in doesn't require Reporting Services. Instead, the Report Viewer web part (part of the RS add-in) renders locally uploaded SSRS reports, created in native mode or SharePoint Integrated mode, and will render SharePoint Access Services reports.

Being able to render Access Services reports is one reason why it was important for Microsoft to break the Reporting Services installation requirement from the RS add-in. Access Services doesn't rely on Reporting Services.

To operate in local mode, you must enable the SharePoint Session State service. You can enable it via the Enable-SPSessionStateService PowerShell cmdlet included in SharePoint 2010. When you run this cmdlet, it creates a session state database and enables the Session State service.

Local mode is possible because the Reporting Services proxy is now gone from the configuration of the RS add-in. The Reporting Services proxy is the web service endpoint you have to provide SharePoint so it can interact with the Report Server running in SharePoint Integrated mode.

A benefit of removing this dependency within the RS add-in is that the additional authentication hop that previously occurred between the Report Viewer web part and the SSRS proxy is now gone.

The more authentication hops that must occur, the greater the likelihood of an authentication failure. Note, the HTTP request (web service) call between the proxy and the Report Server hasn't changed in the latest version of the RS add-in.

If you need Reporting Services (connected mode), you still install it separate from the Reporting Services add-in for SharePoint, and it still requires the SharePoint object model to run in SharePoint Integrated mode.

The Reporting Services Configuration Manager in SQL Server 2008 R2 is the same tool used to configure Reporting Services in earlier versions of SQL Server. One notable improvement as of SQL Server 2008 was that the Report Server no longer requires Microsoft IIS.

Instead, it uses http.sys. Http.sys is a kernel mode driver in Windows Server 2003 and later that handles HTTP requests. Configuring SSRS integration in Central Administration, which is made available by the RS add-in, automatically switches Reporting Services from local mode to connected mode.

Microsoft recommends that if you plan to use connected mode, install and configure Reporting Services for SharePoint Integrated mode and SharePoint 2010 before installing the RS add-in. This approach simplifies the overall installation and configuration.

A feature of SSRS 2008 R2 is the ability to render reports regardless of the default alternate access URL set for a SharePoint web application. Before SSRS 2008 R2, the only zone from which reports could be rendered was the default zone.

By using SQL Server 2008 R2 in either SharePoint 2007 or SharePoint 2010, reports can be rendered from any of the available zones. This feature alone might be enough reason for some companies to consider upgrading to SSRS 2008 R2.

Other Notable Improvements in Reporting Services

There are still two ways of viewing reports, either directly from the library where you published a report or via the Report Viewer web part (called SQL Server Reporting Services Reports) in the web part gallery.

A significantly improved capability of the SSRS 2008 R2 RS add-in is AJAX-style rendering. Now, selecting values for parameters or performing other operations that used to cause data to be sent back to Reporting Services doesn't cause a page post back.

Instead, the Report Viewer web part uses the AJAX asynchronous call-back mechanism to give the component a true Web 2.0 feel.

When building SSRS reports, SharePoint lists are now easier to configure as a data source with the Microsoft SharePoint List Data Extension, as shown in the Data Source Properties in Report Builder 3.0.

This interface looks similar in Business Intelligence Development Studio (BIDS) when you configure a data source for a Report Server project. As you can see from the image, there's extensive out-of-the box support for a variety of data sources.

After you select the Microsoft SharePoint List data source, you simply specify the SharePoint web containing the list you want to query. This extension operates similarly to the XML Data Extension except that it makes it a bit easier to discover lists in a SharePoint web.

Otherwise, the same limitations outlined in “Data Presentation Techniques for SharePoint Lists”  exist for the SharePoint List Data Extension. For example, joins across lists is still not possible when using the List Data Extension.

Because you can now join multiple data sources in 2008 R2 via a Lookup function, you might be able to achieve something similar to a SQL join. However, this is not a high-performance join.

If you want to join a lot of data, use PowerPivot or the SQL data source, not the Lookup function.

There is now a Report Server File Sync web-scoped feature (part of the RS add-in) that lets you synchronize new Reporting Services artifacts (e.g., .rdl, .rsds) back to a connected SSRS server. This is a connected mode feature and is only available in SharePoint 2010.

For more about configuring SSRS in SharePoint 2010, see Configuring Reporting Services for SharePoint 2010 Integration. If you don't have experience with setting-up Reporting Services in SharePoint Integrated mode, be sure to do your research and practice the installation and configuration before embarking on this effort in a production environment.

Building Reports

Once the Report Server Integration Feature (site-scoped) is enabled in a site collection, you can then add the Report Server Content Types to a library, as Figure 8 shows. Note, the Report Server Integration Feature doesn't add a Reports Library list template.

The Reports Library list template becomes available when you enable SharePoint Server Enterprise Site features (web-scoped). Out of the box, the Reports Library is configured for creating new Excel Services workbooks.

However, you can easily add the Report Server content types to this library, if this makes sense for your content design. 

You can define shared data sources that you can publish to SharePoint, just as you were able to do in previous versions of Reporting Services in SharePoint Integrated mode. You now have the additional capability to publish report parts, like datasets and reports.

Microsoft calls this shredding the report into pieces. In Report Builder 3.0, you select Publish Report Parts from the button at the top left of the interface. Report Builder will show the Publish Report Parts dialog as shown in Figure 9.

The figure also shows how the report parts, shared data connection, and dataset appear in the target report library in SharePoint. You must use a shared data source in order to publish a data set to share.

The list of supported data sources hasn't changed significantly from SSRS in SQL Server 2008 and in SQL Server 2008 R2. However, what you can do with the data sources because of the enhancements in the rest of the SharePoint 2010 BI platform is significant.

For example, from an Atom feed, data from reports can be rendered through a PowerPivot-enabled Excel workbook. You can create the Atom export file from the orange icon to the far right in the navigation bar that appears in a report you publish to SharePoint, asFigure 10 shows.

Report Builder 3.0 is new with SQL Server 2008 R2 and worth the upgrade. It's a click-once application, as it was with Report Builder 1.0. If you install SQL Server 2008 R2 and Reporting Services on your web front end (WFE), Report Builder will work automatically.

If not, you have to provide SharePoint with a pointer to the Report Builder click-once installation. You configure this by setting the Custom Report Builder Launch URL value from SharePoint 2010 Central Administration, General Application Settings, Reporting Services, Set Server Defaults. Report Builder is an ever-improving tool, and BIDS is still fully supported if you prefer to use Visual Studio.

For the most part, parity has been maintained between BIDS and Report Builder. The Report Gallery is not available in BIDS.

However, you can publish from BIDS to the Report Gallery. Or, by integrating BIDS with other versions of Visual Studio, you get source control integration for your reports. In addition, in BIDS you have the ability to use SPROCs and parameters rather than queries to build your datasets.

There is also a stand-alone (local installation) MSI for Report Builder. The current version and download is SQL Server 2008 R2 November CTP Report Builder 3.0. Report Builder 3.0 includes more visualization components, like Sparklines, Data Bars, Indicators and even Maps. In addition, the Report Builder expression language has been improved.

Some mapping functionality is limited by legal issues so the product ships maps only for the US. However, you can load a map using ESRI format shapefiles or load map information from a SQL spatial query using the new SQL Spatial datatype. The datatype allows you to define shapes in a column table and use that for rendering.

Often, you'll want to render a single report to a number of different targets, such as PDF or XLS. Prior to the latest version of Reporting Services, a typical approach was to create multiple reports, one for each export target.

You now have the ability to specify portions of a page layout for different targets. This is important when you're dealing with PDF output with fixed page breaks or Excel where expected column breaks can be altered by header images in the page layout.

Export to Excel is still in Excel 2003 format, which limits exports to 65,000 rows. There are third-party solutions.

Alternatively, use the SSRS report as a data source for Excel PowerPivot, and you can get past this limitation within Excel 2010. In addition, what gets exported to PDF is controlled by the PDF rendering engine, not SSRS. Therefore, you will still have to extensively test your PDF exports to make sure what gets rendered is what you intended to render.

There are many other new features that are worth exploring. To learn more about what's new in SQL Server 2008 R2 Reporting Services, see the SQL Server Reporting Services team blog.

Also, for download pointers and descriptions of what makes SQL Server 2008 R2 so compelling, see, Microsoft SQL Server 2008 R2 November Community Technology Preview Feature Pack.

PerformancePoint Services

You use this component to build rich dashboards in SharePoint. Consider PerformancePoint as an aggregation point for data you've created elsewhere in data sources such as back-end relational data stores, SharePoint lists, OLAP cubes created in SSAS and data published to Excel Services.

PerformancePoint provides a set of visualization components out of the box so that you can build rich dashboards from a variety of data sources. In addition, it works with content sources, such as spreadsheets and SSRS reports so that you aren't limited to the visualizations provided by PerformancePoint.

PerformancePoint Services is part of the SharePoint 2010 Enterprise SKU. Therefore, like Excel Services and PowerPivot for SharePoint, you must have the enterprise SKU of SharePoint to leverage this BI capability.

PerformancePoint contains a number of items, including PerformancePoint Services, which runs as an application service in the farm, and a site-collection-scoped SharePoint feature named PerformancePoint Services Site Collection Features.

When you enable this feature in a site collection, you get PerformancePoint web parts, such as the PerformancePoint Scorecard, Filter, and Report. Like the Reporting Services Report Builder, PerformancePoint includes a click-once designer tool named Dashboard Designer.

When you add the PerformancePoint Data Source to a SharePoint library and then attempt to create a PerformancePoint data source from it, the DashboardDesigner.exe click-once application will launch (as Figure 11 shows) so that you can install it.

Like all click-once applications, once the Dashboard Designer application is installed it won't install again. The next time you launch a click-once action, like selecting a content type that relies on the click-once application, click-once verifies the application is installed and then launches it.

 You use the Dashboard Designer to create PerformancePoint dashboards and their sub-components (like KPIs and Score Cards) and deploy them to SharePoint.

This is the same model you use to create Reporting Services reports in Report Builder. In both cases, you must first have one or more back-end data sources defined, such as an Analysis Services cube, a SQL Server dataset, or a SharePoint list. You can also create a PerformancePoint dashboard directly in SharePoint.

Just like Reporting Services, PerformancePoint can post content to a SharePoint list. It uses custom content types all prefaced with PerformancePoint: KPI, Scorecard, Indicator, Report, Filter, and Dashboard.

If you create a SharePoint 2010 Business Intelligence Center site collection, a PerformancePoint Content library is automatically created, which will include all of the PerformancePoint content types.

PerformancePoint in SharePoint 2010 builds-on the existing BI features, like Excel Services and Reporting Services. In SharePoint 2010, you manage PerformancePoint like any other set of SharePoint Features rather than as a separate component.

From a security perspective, PerformancePoint now leverages the existing SharePoint security model. Because it leverages this model, you can now have different sets of users with different roles with respect to PerformancePoint dashboard access.

There have been big improvements to performance and scale because processing can be removed from the WFEs and moved to the SharePoint Application Server. This wasn't possible in the previous version of PerformancePoint.

You use templates to change the look and feel of PerformancePoint services. The Reusable Filter object is now available and works with other web part types so that you can interact more seamlessly with other SharePoint BI components.

A new report in SharePoint 2010 is the Key Performance Indicator (KPI) details report. You can configure this in Dashboard Designer. On a scorecard, you click on a KPI, and the KPI details report shows exactly what data is responsible for the value of the KPI and it includes a description of the KPI.

Another useful feature for complex dashboards is view stacking. With this feature, you can include multiple reports in a single web part and have a drop-down that allows a user to select which report to view.

When you combine this with the conditional display capability, selecting an item in one web part causes another web part to render conditional content, which makes for even better use of limited screen real-estate.

The most common form of conditional display is a master detail data display, such as selecting a KPI from a PerformancePoint dashboard that shows an Excel Services published workbook with details about that KPI.

With the conditional display feature, one web part sends parameters to another web part to render data. This isn't new to SharePoint or ASP.NET, but it demonstrates how tightly integrated PerformancePoint is with this mature web part connection model.

Besides the scorecard and KPI details reports, there are analytic report types that are native to PerformancePoint Services: pie, grid, line graph, and bar chart. These report types include built-in drill-down. Therefore, you don't need to design drill-down capabilities when you use these report types.

Calculated metrics, also known as calculated KPIs, is a new feature in PerformancePoint. This feature allows you to create calculations separate of a back-end data source, like Analysis Services, and instead use the Dashboard Designer, to perform calculations.

This is available in the Select a Data Source, Calculated Metric tab. There are predefined functions, but you can also perform your own calculations from the Blank Calculation template. When you perform calculations, you define variable names and then select data from available data sources, such as Excel Workbooks, SharePoint Lists, or Analysis Services cubes.

Being able to perform calculations across multiple data sources is the part of calculated metrics that makes it so powerful. For instance, one metric could be calculated from an Analysis Services cube while another metric is calculated from a SharePoint list or an Excel workbook.

Variance metric is another new feature. After you create a KPI, PerformancePoint will automatically display variance from a target in your scorecard.

You can see this by selecting metrics settings from a KPI's context menu. The variance will be part of the data that appears in the KPI cells of a scorecard. Finally, the Decomposition Tree, a feature that appeared in ProClarity, is now available in Dashboard Designer.

This feature keeps things sorted and keeps the top performers on the top as you analyze data in a PerformancePoint dashboard.

When you've finished your dashboard, Dashboard Designer now includes one-click deployment. This is vastly improved from the many-click deployment required to get a PerformancePoint 2007 dashboard deployed to MOSS 2007.

There is a lot more to the capabilities in PerformancePoint. If you plan on running the Enterprise SKU of SharePoint 2010, this will likely be the final destination for your advanced BI dashboards.

By leveraging all the other tools, like Analysis Services, Excel, Excel Services, Reporting Services, Access Services, Visio Services and others, the sky is the limit for the dashboards you can create in PerformancePoint.

Additional BI Components

The SharePoint 2010 site template, Business Intelligence Center, and web parts (Chart, Indicator Details, and the Web Analytics) participate in enhancing the BI framework.

They are not part of SharePoint Foundation nor are they part of Excel Services, Reporting Services, or PerformancePoint. These artifacts, however, are helpful for displaying BI data and can interact with many of the other BI components in SharePoint.

Business Intelligence Center is a good place to start if you want to explore SharePoint BI capabilities in a single place.

The site collection is primarily focused on providing PerformancePoint and Excel Services capabilities. Reporting Services is not part of this site collection, but is easily added. (See the Reporting Services section.)

Use the Chart web part to retrieve data from other web parts, SharePoint lists, or external content types created via Business Connectivity Services.

If you have Excel Services installed in the farm or another accessible farm, you can connect to the associated excelservices.asmx Web Service and pull data from a published workbook by providing the path to the workbook and a data range (or named range).

Use the Indicator Details web part to display data from a list created from the Status List template or a list containing indicator content types. The Status List template can retrieve data from a SharePoint list, Excel Services, SSAS, or from a manually entered value in a Status List.

Use the Web Analytics web part to get an idea of how a particular site collection is being used. This web part shows the most viewed content, most frequent search queries from a site, or most frequent search queries from a search center.

This whirlwind exploration of SharePoint BI should give you a good sense of how extensive the BI platform is. Microsoft has worked hard to provide a consistent approach to integrating components into the platform.

Excel and SharePoint are now powerful Analysis Services endpoints as a result of PowerPivot technologies. Excel Services is significantly better at maintaining fidelity between the Excel UI and the browser UI.

Reporting Services has matured nicely, largely as a result of SQL Server 2008 R2 and related technologies. PerformancePoint now feels like part of SharePoint rather than a bolt-on.

The PerformancePoint Dashboard Designer is significantly more powerful now when combined with SSAS. SharePoint 2010 is poised to be one of the most powerful BI platforms on the market.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.