Creating an Excel workbook that contains "one version of the truth" requires a collaborative effort between a SharePoint site administrator, a workbook author, and various end users. The following article is a roadmap that describes the basic process of publishing a secure workbook that contains "one version of the truth", who does what, and where to get more help information that describes the implementation details.

What is "one version of the truth"?

Step 1: Preparation

Step 2: Create document libraries on the SharePoint site

Step 3: Define trusted resources for Excel Services

Step 4: Set permissions

Step 5: Create an Office Data Connection file

Step 6: Make available the Data Connection Library in Office Excel 2007

Step 7: Create and publish the Office Excel 2007 workbook

Step 8: View "one version of the truth"

What is "one version of the truth"?

A common requirement when publishing a Microsoft Office Excel 2007 workbook that is connected to an external data source on Excel Services is to ensure that users always view a consistent set of values and formula results, and that the workbook uses secure and up-to-date connection information. This is sometimes called obtaining "one version of the truth".

For example, if you are reconciling budget data each quarter between different departments to provide a company-wide budget summary, you need to ensure that everyone is working with reliable and consistent data to ensure that the process goes smoothly with minimal disruption and disagreement over differing values between workbooks and the timing of formula calculations and results.

Step 1: Preparation

For best results, site administrators, workbook authors, and end users should all collaborate with each other to make the following preparations.

Gather Data   Identify and collect the following information:

• External data source connections and connection information, such as server, database, and cube names, queries, and credentials.
• Office Excel 2007 workbooks to publish.
• Appropriate users, permissions, roles, and client computer IDs.

Install software  Install the required software for the following users:

• A site administrator requires a full installation of Microsoft Office SharePoint Server 2007 that includes Excel Services.
• A workbook author requires Office Excel 2007.
• End users require access to SharePoint sites, and optionally to Office Excel 2007, to open published workbooks on their client computer.

For more information, see the Office SharePoint Server 2007 Planning Guide on the Technet Web site, and the following topics:

• Plan access to spreadsheets
• Plan authentication for external data
• Plan data connection management
• Plan Excel reports against OLAP cubes
• Plan Excel reports against relational databases
• Plan Excel Services data presentation
• Plan Excel Web Access Web Parts
• Plan integration with external data

Step 2: Create document libraries on the SharePoint site

Using Office SharePoint Server 2007 or Microsoft Windows SharePoint Services 3.0, a site administrator creates document libraries for storing the Office Excel 2007 workbooks and for storing the Office Data Connection (ODC) files (.odc).

For more information, see the Windows SharePoint Services 3.0 Help, and the following topics:

• Introduction to libraries
• Create a library

Step 3: Define trusted resources for Excel Services

Using Office SharePoint Server 2007 and the central administration Web application, a site administrator creates a trusted file location, a trusted data provider, a Data Connection Library (DCL) list, a trusted DCL, and defines the trusted location so that it only uses external data connections.

For more information, see the section, "Plan trust levels" in the article, "Plan Excel Services security" in the Office SharePoint Server 2007 Planning Guide on the Technet Web site.

Step 4: Set permissions

Using Office SharePoint Server 2007, a site administrator sets permissions on the DCL and defines a View Item Only permission level.

For more information, see:

• The article, "Plan Excel Services security" in the Office SharePoint Server 2007 Planning Guide on the Technet Web site.
• The following topics in Central Administration Help:
  • Choose which security groups to use
  • Define custom permission levels
  • Determine permission levels and groups to use
  • Plan site security
• The following topics in Windows SharePoint Services 3.0 Help:
  • About controlling access to sites and site content
  • About managing SharePoint groups and users
  • About security features of Windows SharePoint Services (version 3)
  • Manage permission levels
  • Manage permissions for a list, library, folder, document, or list item
  • Manage SharePoint groups
  • Permission levels and permissions

Step 5: Create an Office Data Connection file

Using Excel, a workbook author creates an ODC file, defines appropriate authentication settings, ensures that the ODC file is always used when the data is accessed, and saves the connection file to the DCL.

For more information, see the Office Excel 2007 Help and the following topics:

• Create, edit, and manage connections to data in a workbook
• Connect to (Import) external data
• Connection properties

Step 6: Make the Data Connection Library available in Office Excel 2007

Using Office SharePoint Server 2007, a site administrator makes one or more DCLs available in Excel, by defining a list of DCLs for use on client computers, and then by associating a client computer with a server computer either by using each user's My Site or by setting a registration key on the user's computer.

For more information, see the "Plan data connection management" topic in the "Plan for business intelligence" section of the Office SharePoint Server 2007 Planning Guide on the Technet Web site.

Step 7: Create and publish the Office Excel 2007 workbook

Using Office Excel 2007, a workbook author creates a workbook that uses the ODC file, saves the workbook to Excel Services, and then optionally selects specific worksheets or named items, such as charts or PivotTable reports.

For more information, see the Office Excel 2007 Help and the following topics:

• Differences between using a workbook in Excel and Excel Services
• Publish a workbook to Excel Services
• Define workbook parameters for use in Office Excel Web Access
• Edit or remove a workbook from Excel Services

Step 8: View "one version of the truth"

Using Office SharePoint Server 2007, end users display the workbook in Excel Services, interact with it, and optionally open the workbook on their computers for further analysis.

For more information, see the Office SharePoint Server 2007 Help and How-to home page.

Tip  You can also click the Help button on the Excel Web Access toolbar for an overview of all Excel Services Help topics.