• Sidebar: Microsoft BI Tools and Applications
• Illustration: Dashboard Reports in SharePoint Server 2007

New business intelligence (BI) features could increase the appeal of SharePoint Server 2007, the latest version of Microsoft's portal and collaboration product. SharePoint Server 2007 allows nontechnical workers to define key performance indicators (KPIs), manage reports, and create dashboards—Web pages that display KPIs and related information, which could strengthen the company's hand in the BI market by helping organizations track and report performance with limited ongoing involvement of developers and IT. However, developers and IT workers will need to set up SharePoint Server 2007 carefully for its BI features to be very useful.

Web-Based Performance Tracking, Reports

SharePoint Server 2007, released to manufacturing in Nov. 2006, is the latest version of Microsoft's server application for hosting intranet Web portals. Along with refined features for collaboration and document management, SharePoint Server 2007 adds new reporting capabilities to Microsoft's growing list of BI tools and applications for business users and other nontechnical workers. BI is a catchall term for the collection, processing, and analysis of data from a range of business applications (such as transaction, customer relationship management, and inventory systems) to aid business-related decisions.

SharePoint Server 2007 builds on the free Windows SharePoint Services 3.0, the next version of the Web-based collaboration service of Windows Server.

SharePoint Server 2007 has the following BI-specific processes:

Performance tracking. SharePoint Server 2007 will include utilities for creating, publishing, and displaying KPIs, which measure the status of business metrics against predefined goals. Properly designed KPIs provide organizations with a comprehensive view of the factors (such as profits, customer satisfaction, and employee performance) that influence attainment of strategic objectives.

Managed reporting. SharePoint Server 2007 includes tools for publishing, displaying, and maintaining Web-based reports that draw data from a variety of data sources, such as SQL Server databases and Excel spreadsheets. In addition, SharePoint Server 2007 supports a type of report that Microsoft calls a dashboard, which is a complex report that consolidates information from multiple sources. For example, a dashboard could display a list of KPIs, a table from an Excel spreadsheet, and other data extracted from a business application, such as SAP.

Why SharePoint Server 2007?

Microsoft markets a number of BI tools and applications that build on or use SQL Server—Microsoft's database management system and BI application development platform—including Excel 2007, Business Scorecard Manager 2005, and the forthcoming PerformancePoint Server. SharePoint Server 2007's reporting features are another entry into this fray and could benefit Microsoft on several strategic fronts, tapping the market's thirst for easy-to-use BI tools, which will drive adoption of SharePoint while concurrently strengthening Microsoft's position in the lucrative market for end-user BI applications. (For a survey of Microsoft's portfolio of BI-related products, see the sidebar "Microsoft BI Tools and Applications".)

SharePoint Server 2007's appeal to customers is its promise of easy-to-use, broadly available BI features. The product introduces a new site called the Report Center, which houses the tools and libraries for building, storing, and managing KPIs, reports, and dashboards, and serves as the starting point for both authors of BI content and the users of that content. Those tools let nontechnical workers (such as business analysts) build BI-related Web pages and make those pages readily accessible to users by integrating them with the organization's portal sites—tasks that are traditionally the purview of corporate developers and IT workers.

SharePoint KPIs Track Performance

SharePoint Server 2007 performance tracking centers on features for creating and maintaining KPI lists. In general, SharePoint lists are a simple way to build and manage data tables such as announcements, contacts, and tasks (to-do list items). KPI lists, as their name implies, are tables of KPIs, and they store information such as a KPI's current value (typically, the current value of a business metric), its target goal, and a warning threshold, which determines the KPI's status indicator—a visual cue of where the KPI value sits relative to its goal.

A simple, form-based authoring tool in SharePoint Server 2007 helps nontechnical users create new KPIs and add them to KPI lists. Once created, those KPIs can be added to Web pages by inserting the new KPI list Web Part, which reads KPIs from a specified list and renders them on the page. (All SharePoint Server Web pages are made up of collections of modular, user-configurable Web page components called Web Parts, such as the KPI list Web Part.)

SharePoint Server 2007 supports the following four types of KPIs:

Manually entered KPIs. KPIs can be based on numeric values entered by hand. For example, an event planner managing a fund drive could use manually entered KPIs to track contributions, and the KPI's status indicator would change when the amount of contributions (the KPI's value) crossed the warning threshold or met or exceeded its goal. Because manually entered KPIs are static (i.e., they rely on data entered by hand), they will probably be used mainly for demonstrations or as a learning tool.

Excel-based KPIs. KPIs can be based on data in Excel 2007 spreadsheets stored in SharePoint document libraries and configured as trusted locations for Excel Services. (Excel Services is a new SharePoint Server 2007 feature for server-side spreadsheet publishing and calculation. For details, see "Excel Server in SharePoint 2007 on page 22 of the Nov. 2006 Update.) KPIs based on data in Excel spreadsheets contain a value, goal, and warning threshold. The KPI value is extracted from a spreadsheet cell; the goal and warning threshold can be entered manually or based on cells in the same spreadsheet used for the KPI's value. Updating the KPI requires Excel Services to open and calculate the spreadsheet containing it. The KPI's creator can specify whether the KPI updates automatically each time it is viewed or only when the viewer requests an update.

Analysis Services KPIs. SharePoint Server 2007 KPI lists can link to Analysis Services KPIs, a new feature in SQL Server 2005 that lets SQL Server developers build KPIs from data stored in Analysis Services cubes (multidimensional databases that summarize historical data). Analysis Services KPIs provide SharePoint users with a more powerful analysis tool than KPIs entered manually or based on Excel spreadsheets. For example, Analysis Services KPIs support trends, which indicate whether or not a value is likely to meet its goal. Furthermore, Analysis Services KPIs are hierarchical; a KPI can be based on one or more child KPIs. Authors of KPI lists have the same update options for KPIs based on Analysis Services as for those based on Excel spreadsheets. However, updating an Analysis Services KPI via SharePoint Server 2007 causes the KPI to be read from Analysis Services; it does not trigger an update to the cube underlying the KPI. Such updates are managed outside of SharePoint by SQL Server's management tools.

SharePoint list-based KPIs. KPIs can be based on information in other SharePoint Server 2007 lists. For example, a project manager could create a KPI to track the fraction of completed work items compiled in a SharePoint Task list.

Report Distribution and Maintenance

SharePoint Server 2007 introduces several features that support the creation and management of reports.

Report Libraries

SharePoint Server 2007 introduces a special document library type for storing and managing reports. In general, SharePoint document libraries store files and provide additional capabilities not available in the Windows file system, such as file versioning and the ability to check out a file for exclusive use. Report libraries offer other features of interest to report consumers, such as ready access to a report's revision history to enable historic analysis.

Report libraries support several report flavors:

Excel reports. Reports can be based on data in Excel 2007 spreadsheets—these can be as simple as spreadsheet tables or as complex as PivotTables based on data stored in Analysis Services cubes. Excel reports are published in report libraries using Excel Services and are thus restricted to those Excel features that Excel Services supports. (Excel Services does not support spreadsheets that contain VBA macros, for instance.)

Reporting Services. SharePoint Server 2007 will also support reports created with SQL Server Reporting Services—SQL Server's facility for creating, publishing, and managing reports. Specifically, SQL Server 2005 SP2, which is planned for 2007, will provide utilities that allow users to publish and manage Reporting Services reports in SharePoint report libraries, and Web Parts that can display those reports in SharePoint Web pages.

Dashboards are complex reports that combine information from multiple sources. Authors build dashboards by configuring Web Parts in a dashboard page template—the template contains Web Parts for inserting KPI lists and Excel tables among others, and authors can insert other Web Parts into the template. The template also contains a placeholder for SharePoint Server 2007's new Filter Web Part, which gives users viewing the dashboard a simple way to filter data (such as the data in a KPI list or Excel table) displayed in the dashboard.

(For an example of a SharePoint Server 2007 dashboard, see the illustration "Dashboard Reports in SharePoint Server 2007".)

Business Data Catalog

A new SharePoint service called the Business Data Catalog (BDC) stores information used to connect to and extract data from other data sources, such as line-of-business (LOB) applications, allowing data from those sources to be used in SharePoint Web pages and lists. Administrators and developers configure and register data sources in the BDC, which involves creating a set of instructions (written in XML) that SharePoint uses to connect to a data source and call and extract data from it. Thus, the BDC does not contain actual business data, but rather information that SharePoint uses to access it when requested.

After data sources are set up and registered in the BDC, their data can be included in SharePoint Web pages. For example, the Business Data List Web Part could be inserted into a dashboard to display a set of customer records from Siebel or inventory data stored in SAP.

Planning, Training Required

SharePoint Server 2007's BI features are meant to let nontechnical workers build and publish BI Web pages, and the promise of more accessible BI will undoubtedly generate interest among prospective customers.

However, careful planning will be required of business analysts, developers, and IT workers for authors to make effective use of SharePoint Server 2007's most powerful BI features. For example, analysts will need to translate an organization's stated objectives into a set of business measures that accurately and concisely convey the organization's performance, and those analysts will need to work with developers and administrators to map out the data sources to provide the measures. Because many organizations' most relevant data will be stored in business databases, data warehouses, and LOB applications, rather than Excel spreadsheets and SharePoint lists, developers and administrators will need to prepare those sources (creating cubes and configuring and registering data sources in the BDC, for instance) so that authors can create KPIs, reports, and dashboards based on them. Without such planning, organizations could find SharePoint Server 2007 an ineffective reporting tool, or worse, one that invites misuse and wasted effort as users build content-rich pages that impart no useful information.

Furthermore, SharePoint Server 2007's many moving parts could confuse workers creating or customizing KPIs, reports, and dashboards. By design, SharePoint Server's architecture is highly componentized. A large variety of Web Parts gives workers a great deal of flexibility when creating sites, but this variety also adds complexity. It will take time, and probably training, for users to figure out how to best combine SharePoint's many parts into useful reports, KPIs, and dashboards.

Finally, overlap between SharePoint 2007's reporting features and Business Scorecard Manager (BSM) 2005 will probably confuse customers and partners. Although Microsoft positions each for different scenarios—targeting BSM at corporate-wide performance management projects and SharePoint 2007 at smaller efforts conducted by teams or departments—the products bear a marked resemblance to each other. It seems likely that Microsoft will eventually consolidate some of the overlapping features in one of the products. If Microsoft decides that its strategic product for KPIs is PerformancePoint 2007 (the successor to BSM), companies could find that the effort put into building KPIs in SharePoint Server 2007 will need to be redone.

Resources

SharePoint Server 2007 was released to manufacturing in Nov. 2006 and will be generally available by the end of the year. Most SharePoint BI features will require the purchase of SharePoint 2007 Enterprise Client Access Licenses (CALs) for all users, in addition to the SharePoint Server 2007 Standard CALs. Official pricing has not been announced, but when purchased with three years of upgrade rights the Enterprise CAL is likely to cost about US$120 and the SharePoint 2007 Standard CAL about US$150. Prices will be lower in larger quantities.

The SharePoint Server home page is office.microsoft.com/en-us/sharepointserver/default.aspx.

The Office 2007 Web site is www.microsoft.com/office/preview.

Download the SharePoint Server 2007 beta at www.microsoft.com/office/preview/beta/getthebeta.mspx.

The SQL Server 2005 BI platform is covered in the Sept. 2005 Research Report, "SQL Server 2005: Microsoft's Business Intelligence Platform."

Excel Services is described in "Excel Server in SharePoint 2007" on page 22 of the Nov. 2006 Update.

New BI features in Excel 2007 are outlined in "New OLAP Features for Excel" on page 18 of the May 2006 Update.