Roadmap for publishing an Excel workbook as "one version of the truth"

Creating a Microsoft Excel workbook that contains "one version of the truth" requires a collaborative effort between an administrator of a Microsoft SharePoint site, 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" to a SharePoint site, who does what, and where to get more help information that describes the implementation details.

Important    The ability to publish an Excel workbook to a SharePoint site is available only in Excel 2010 and Excel 2007.

In this article


What is "one version of the truth"?

A common requirement when publishing an Excel 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.

Top of Page Top of Page

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.
  • Excel 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 SharePoint Foundation 2010.
  • A workbook author requires Excel 2010 or Excel 2007.
  • End users require access to SharePoint sites, and optionally to Excel, to open published workbooks on their client computer.

For more information, see the SharePoint Server Tech Center on the Technet website, and search for the following topics:

  • Plan access to Excel worksheets
  • Plan authentication methods
  • Plan data connection management
  • Plan Excel reports against OLAP cubes
  • Plan for business intelligence
  • Plan external data connections for Excel Services

Top of Page Top of Page

Step 2: Create document libraries on the SharePoint site

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

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

  • Introduction to libraries
  • Create a library

Top of Page Top of Page

Step 3: Define trusted resources for Excel Services

On a server running Windows SharePoint Services 3.0, a site administrator runs the central administration Web application and 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.

A trusted DCL is not required to publish an Excel workbook to a server running SharePoint Foundation 2010.

Top of Page Top of Page

Step 4: Set permissions

Using SharePoint Foundation, 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, " at the SharePoint Server Tech Center on the Technet website.
  • The following topics in Central Administration Help for SharePoint Foundation:
    • Choose which security groups to use
    • Define custom permission levels
    • Determine permission levels and groups to use
    • Plan site security
  • The following topics in SharePoint Foundation Help:
    • About controlling access to sites and site content
    • About managing SharePoint groups and users
    • About security features of Windows SharePoint Services
    • Manage permission levels
    • Manage permissions for a list, library, folder, document, or list item
    • Manage SharePoint groups
    • Permission levels and permissions

Top of Page Top of Page

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 Excel Help and the topic Connect to (import) external data.

Top of Page Top of Page

Step 6: Make the Data Connection Library available in Excel

Using SharePoint Foundation, 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 "Plan data connection management" topic in the "Plan for business intelligence" section (under step 2), at the SharePoint Server Tech Center on the Technet Web site.

Top of Page Top of Page

Step 7: Create and publish the Excel workbook

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

For more information, see the topic Publish a workbook to a SharePoint site.

Top of Page Top of Page

Step 8: View "one version of the truth"

Using SharePoint Foundation, end users display the workbook in the web browser, edit it by using Excel Web App, and optionally open the workbook on their computers for further analysis.

Top of Page Top of Page

 
 
Applies to:
Excel 2010