Configuring Excel Services for PerformancePoint Dashboard Designer - prerelease topic !
There is some customer pain around configuring Excel Services to which I am addressing in a topic (yet to be published) for PerformancePoint. This post is a sneak preview (pre-edited). This topic will be located underneath the topic Configuring SharePoint Products and Technologies for Monitoring Server. This is one of several topics that our team will write to address customer pain points. Tell me what you think.
Configure Excel Services
Introduction
In Microsoft Office PerformancePoint Server 2007 Monitoring and Analytics (PPS M&A), you can use publish an Excel workbook to Excel Services which becomes one of the potential data sources for a dashboard designer. You can use it to load, calculate, and display Office Excel workbooks to a document library.
You can then use the report wizard in DashboardDesigner to select a specific spreadsheet or a particular object such as a PivotTable, and include it as a report in a dashboard with full Excel functionality. Excel Services is a part of Microsoft Office SharePoint Services (MOSS) 2007. To learn more about Excel Services security, see http://technet2.microsoft.com/Office/en-us/library/a49883a7-de84-4a66-8fa0-7c7d125f237b1033.mspx?mfr=true
The following are the three locations that need to be configured.
Configure the settings for the Excel Services unattended service account.
The unattended service account is a low-permissions account that Excel Calculation Services can impersonate when establishing a data connection that uses single sign-on (SSO) credentials from an environment that is not Windows-based, or None, as the authentication method.
Note This procedure is a requirement only for servers that do not have Kerberos enabled. If you have Kerberos enabled, you can skip this section and proceed to Configure settings in Excel Services Trusted File Locations.
Configure the settings in Excel Services Trusted File Locations
Trusted file locations manage who can act as an author for workbooks that are available under Excel Services. The trusted locations can be designated as SharePoint locations, UNC paths, or HTTP paths. Excel Services loads workbooks only from directory paths that the administrator designates.
Configure the settings in Excel Services Trusted Data Connection Libraries
Trusted data connection libraries are SharePoint document libraries that contain Office data connection (.odc) files. The .odc files are used to centrally manage connections to external data sources.
This section gives instructions to configure so that Excel Services can be used as a data source for PerformancePoint Server M&A.
Pocedures
Configure the settings for the Excel Services unattended service account
1. Click Start, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
2. On the Central Administration home page, click Shared Services Administration.
3. On the Manage this Farm's Shared Services page, click the default shared services provider that you want to change.
4. On the Shared Services Administration page, click Edit Excel Services Settings under Excel Services Settings.
5. Next to External Data, enter the Unattended Service account information. Make sure that you use an account that has limited Write permissions.
Add a trusted file location
1. Click Start, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
2. On the Central Administration home page, click Application Management.
3. On the Application Management page, in the Office SharePoint Server 2007 Shared Services section, click Create or Configure this Farm's Shared Services.
4. On the Manage this Farm's Shared Services page, click SharedServices1 (Default). This is the Shared Services Provider (SSP) that you will configure.
5. On the Shared Services home page, in the Excel Services Settings section, click Trusted file locations.
6. On the Excel Services Trusted File Locations page, click Add Trusted File Location.
7. In the Address section, type the location and name of the SharePoint Office SharePoint Server 2007 document library that you want to add as a trusted file location in Excel Services. If the document library is stored in the Microsoft Windows SharePoint Services 3.0 content database, ensure that Windows SharePoint Services 3.0 is selected as the Location Type.
8. In the External Data section, select the type of data connections that you will allow workbooks in this trusted file location to contain and click OK.
Add a trusted data connection libraries
1. From Administrative Tools, open the SharePoint Central Administration Web application.
2. On the Central Administration home page, click Application Management.
3. On the Application Management page, in the Office SharePoint Server 2007 Shared Services section, click Create or Configure this Farm’s Shared Services.
4. On the Manage this Farm’s Shared Services page, click SharedServices1 (Default). This is the Shared Services Provider (SSP) that you will configure.
5. On the Shared Services home page, in the Excel Services Settings section, click Trusted data connection libraries.
6. On the Excel Services Trusted Data Connection Libraries page, click Add Trusted Data Connection Library.
7. Type the address of the data connection library that you want to configure as a trusted data connection library and click OK.
-------------------
Excel and Dashboard Designer modifications
Modify the Excel spreadsheet
There are a few modifications that need to be made on the Excel spreadsheet and in Dashboard Designer. I will publish this later.
