Excel Services is part of a collection of Office SharePoint Server features collectively called Business Intelligence that an individual, a team, or an entire enterprise can use. These features are designed to work together and support quick, robust development of customized decision-making tools that can access a variety of data sources — often without the use of code.

The Report Center

The Report Center provides a central location for various Business Intelligence data and objects, and contains special document libraries for storing reports, lists, Web Parts, Web Part Page templates, and .odc files. Within the Report Center, users can also search for items by using categories, view a calendar of upcoming reports, and subscribe to reports that they find relevant.

By default, an Excel workbook published and saved to a document library in the Report Center is single-click enabled to open the workbook in browser view, which is a convenient way to see the workbook without adding it to a Web Part Page.

The KPI List Web Part

The KPI List Web Part gets data from SharePoint lists, Excel workbooks, Microsoft SQL Server 2005 Analysis Services, or manual data entry, and then displays a Key Performance Indicator (KPI), which is a visual cue that communicates the amount of progress made toward a measurable goal. By using KPIs, you can easily visualize answers to the following questions:

What am I ahead or behind on?
How far ahead or behind am I?
What is the minimum I have completed?

Users can even drill down on the KPI items to see the detail behind the visualization. For instance, if the status of a KPI is red (indicating a problem), clicking on that KPI will automatically take the user to a report page that shows how the trend of the KPI over time, what the thresholds are, and the data that was used to determine the current status of the KPI.

Each area of a business may choose to track different types of KPIs, depending on the business goals that they are trying to achieve. For example, to increase customer satisfaction, a call center might set a goal to answer a specific number of calls within a shorter period of time. Or the sales department might use KPIs to set performance goals, such as the number of new sales calls made per month.

Filter Web Parts and the Apply Filter Button

You can use the Filter Web Parts to display only the subset of data that you are interested in viewing in other Web Parts and optionally the Apply Filter Button to perform the filter operation. For example, a data source can contain a five year history of multiple products for the entire country/region. By using the Filter Web Parts and Apply Filter Button, you can simultaneously display pertinent data for only one sales region, one product, or the current year in several Excel Web Access Web Parts.

Office SharePoint Services has a number of different Filter Web Parts that enable you to enter or to choose one or more values to change the contents of one or more Web Parts on a page to display exactly the information that you need.

Office Shared Services Dashboards

Microsoft Office SharePoint Server 2007 Dashboards are tools that are used to communicate status, observe trends, anticipate problems and opportunities, make decisions, and drive actions — often with graphics and charts. A Dashboard is a Web Part Page that displays information, such as reports, charts, metrics, and Key Performance Indicators (KPIs), from disparate data sources.

You can create your own dashboard by using a Dashboard template to quickly connect existing Web Parts, add or remove Web Parts, and customize the appearance of the page.

Information Rights Management (IRM) is a way to provide privacy protection for a Microsoft Office document and to ensure that sensitive information is only viewed by appropriate people. For example, you may want to report quarterly financial data only to select members of an executive committee one month before the data becomes publicly available in a financial statement, so they have time to prepare public relation responses and make appropriate business decisions.

Windows SharePoint Services Version 3.0 or later supports IRM on a document library and all the documents in that library (whether or not those individual documents are enabled with IRM). Once the document is uploaded to a document library enabled with IRM, the document, in effect, becomes IRM-enabled.

Excel Services does not support loading Excel workbooks that have been enabled with IRM, and it does not load an Excel workbook if it is enabled with IRM or comes from a document library enabled with IRM. However, if you want to take advantage of IRM, you can load an Excel workbook without IRM into Excel Services, open the workbook as a snapshot, and then save the snapshot to a document library that is enabled with IRM.

For some Excel workbooks saved to Excel Services, all the data is stored in the workbook. To update the data in Excel Services, the Excel workbook must be saved again. For other workbooks, there are one or more connections to external data sources, such as a database or OLAP cube. These connections contain information about how to locate, log in, query, and access the external data source. Although this connection information can be stored in the workbook, often it is stored in an Office Data Connection (.odc) file, especially when the data is shared by many users and the connection information needs to be updated. The workbook author or an administrator can create the connection information by using Excel 2007 to author the connection, and then to export the connection information to a .odc file.

A Data Connection Library (DCL) is a special SharePoint document library that can be defined as a trusted location library and that makes it easy to store, secure, share, and manage .odc files. For example an administrator may need a to move a database from a test server to a production server, or update a query that accesses the data. By using one .odc file saved in a DCL, administration of this connection information is much easier and the user’s access to data is more convenient because all workbooks use the same connection file and a refresh operation, whether on the client or server computer, gets up-to-date changes to that connection file. You can even set up Office SharePoint Server and a user’s client computer to automatically detect changes to the connection file and use the most up-to-date version of that connection file.

Of course, there are countless ways that you can use Excel Services, but the following is a representative list of scenarios and examples to help you better understand how you might use Excel Services.

Business intelligence dashboards An executive committee has access to several company dashboards that act as an up-to-date financial scoreboard for the company. To continuously assess company performance, the main dashboard summarizes Key Performance Indicators (KPIs), such as sales goals, target revenues, and profit margins, on a monthly basis. Additional dashboards summarize market news to help analyze financial risk for current and new projects, and to display charts of critical financial data to help evaluate different investment portfolios.

Marketing analysis information system A marketing department in a company that sells athletic clothing and equipment maintains an information portal page that summarizes key demographic data, such as gender, age, region, income-level, and preferred leisure activity. Most employees in the marketing department can optionally open the Excel workbooks on their computer and do “what-if” analysis of all data, or print well-formatted reports. Over time, users can also easily add reports for others to share.

Professional sports players statistics A major league sports organization shares past and present statistics on all players’ performance and salaries. This data is used to make trades and to negotiate salary contracts. New reports and analyses are created, revised, and shared by owners, especially during the pre-season.

Retail store decision-making tool A retail chain summarizes critical point-of-sales data on a weekly basis and shares it with suppliers, financial analysts, and regional managers. Reports include current items below inventory, top 20 selling items by sales categories, important seasonal data, and transaction counts by each store.

Sales account management report system A sales group accesses a set of daily briefing reports that capture key data such as the top sales people, progress towards monthly sales targets, successful sales programs, and low-performing channels of distribution. Additional reports summarize sales by key variables, such as region, product line, and month, sales calls per week, and the number of closed calls. When individual sales people display these reports, they can automatically see their sales numbers because the system identifies them based on their user name.

Engineering project daily summary An engineering group develops a Web Part Page that summarizes key project schedule data such as bug counts, status of specifications, progress diagrams, feature trends and priorities, and links to key resources and contacts. The data is drawn from several external data sources, such as project databases and lists of specifications.

Proprietary financial analysis calculation model A large financial institution has researched and developed a pricing model that is private intellectual property. The results of the formula need to be shared with some investment managers, but the formula that is used to calculate the pricing model must be secure and never be publicly revealed. This pricing model is extremely complex and takes a long time to calculate. Every night, the pricing model report is calculated and created on a fast server, saved to a trusted location, and displayed on a Web Part Page, but only to those who have appropriate permission.

Excel Services is part of Microsoft Office SharePoint Server 2007. Excel Services is built on ASP.NET and Windows SharePoint Services 3.0 technologies. There are three core Excel Services components:

• Excel Web Access
• Excel Web Services
• Excel Calculation Services

Excel Services handles communication among the three components and load-balances the requests made to Excel Calculation Services.

The Excel Web Access, Excel Services, and Excel Calculation Services components can be divided into two major groups: the components on a front-end server (also known as the “Web front end”), and the component on a back-end application server.

Excel Web Access

Excel Web Access is an Excel Services Web Part in Office SharePoint Server 2007 that renders (in other words, creates the HTML for) live Excel workbooks on a Web page, and allows the user to interact with those workbooks and explore them. Excel Web Access is the visible Excel Services component for the user. You can use Excel Web Access like any other Web Part in Office SharePoint Server 2007. Excel Web Access does not require anything to be installed on the user’s client computer.

The Excel Web Access Web Part properties are also customizable. For more information, see the Microsoft.Office. Excel.Server. WebUI namespace reference documentation.

Excel Web Services

Excel Web Services is the Excel Services component that provides programmatic access to its Web service. You can develop applications that call Excel Web Services to calculate, set, and extract values from workbooks, as well as refresh external data connections. Using Excel Web Services, you can incorporate server-side workbook logic into an application, automate the updating of Excel workbooks and create application-specific user interfaces around server-side Excel calculation.

Excel Calculation Services

The role of Excel Calculation Services is to load workbooks, calculate them, call custom code (user-defined functions) and refresh external data. It also maintains the session state for interactivity. Excel Calculation Services maintains a session for the duration of interactions with the same workbook by a user or caller. A session is closed when the caller explicitly closes it or when the session times out on the server. Excel Services caches the opened Excel workbooks, calculation states, and external data query results, for improved performance when multiple users access the same set of workbooks.

Load-Balancing

In multiple-server configurations, Excel Services load-balances requests across multiple Excel Calculation Services occurrences in a farm configuration. If your installation includes multiple application servers, Excel Services will balance the load to ensure that no single application server is overloaded by requests. Administrators can configure the load-balancing behavior.

Web Front-End and Back-End Application Servers

The Excel Web Access, Excel Services, and Excel Calculation Services components can be divided into components on the Web front-end server and those that live on a back-end application server. The Web front end includes Excel Web Access and Excel Web Services. The Excel Calculation Services component resides on the back-end application server, alongside any user-defined function assemblies that an administrator may have added.

In the simplest configuration—that is, a single computer running Microsoft Office SharePoint Server 2007 as a stand-alone installation, all three components will be installed on the same computer. However, in a typical enterprise environment with a large number of users, the components on the Web front-end server and the back-end application server would be on different computers in a farm configuration. It is possible to scale the Web front-end server out independently from the back-end application server. For example, you can have more Web front-end servers than back-end application servers, and vice versa, depending on your organizational needs.