Book excerpt: Post-installation configuration (Project Server 2010)
Applies to: Project Server 2010
Topic Last Modified: 2011-08-05
This book excerpt is from Implementing and Administering Microsoft Project Server 2010, published in 2010 by MSProjectExperts, a Microsoft EPM solution consultancy.
Implementing and Administering Microsoft Project Server 2010 is your essential reference guide for installing, configuring and deploying Project Server 2010 to your enterprise. This book begins with the organizational strategies you need to succeed with an EPM deployment and follows through with an implementation plan and step-by-step instructions for installing, configuring and deploying the Project Server 2010 platform to your organization. Loaded with best practices, warnings and tips from Project Sever gurus Gary Chefetz, Dale Howard and Tony Zink, this book sets the gold standard for Project Server implementation.
Book excerpt: Module Number 05, Post-Installation Configuration
Learning Objectives
After completing this module, you will be able to:
Configure Excel Services and PerformancePoint Services for your new Project Server site
Configure Secure Store Services for Reporting
Install the SQL Server Native Client and Analysis Management Objects
Tune SQL Server for best performance
Verify Project Server Functionality
Configure SharePoint Service Applications
Now that you have completed the installation procedure in Module 4, you have all the software parts in place where you need them. The next step is to wire them all together so that they can interact with one another as a complete system. For instance, you created a Global Group for Report Authors during the server preparation steps in the previous module, but you now need to connect that global group to the Reporting Database in Project Server, something you couldn’t do until you created a Project Server 2010 site on your system. Much of your work in this module involves making that type of connection between the various software components.
Configure Excel Services for Your Project Server Site
You now have a provisioned, but not completely configured, Project Server application and site. You can click the link on the Manage Project Web App Sites page and your new PWA site will launch. After you verify that your site exists, the next step is to configure your system for reporting. Project Server 2010 uses the SharePoint Server 2010 Report Center Web application to provide a central point for hosting reports, dashboards, and report connections. You can create a variety of reports automatically or manually author additional reports as your users require them. The Report Center is driven by Excel Services in SharePoint Server 2010.
Prepare to Configure Excel Services
For report authors to access the Project Server 2010 Reporting database from Excel 2010, they must be a member of the domain global group you created for Report Authors at the beginning of Module 4, and the global group must have db_datareader rights on the reporting database. The login must allow explicit access to the Project Server 2010 Reporting database to get schema information and data. Follow these steps to create the login:
On the computer running SQL Server or another computer with SQL Server Management Studio installed, launch Microsoft SQL Server 2008 SQL Server Management Studio and select the instance of SQL Server containing your Project Server 2010 reporting database and click the Connect button. Expand the Security folder and right-click Logins, and then select New Login from the popup menu as shown in the following figure.
Figure 5 - 1: SQL Server Management Studio
.gif "New Login")
The system displays the Login-New page shown in CNDJ6nn5us4RjIIAqgBLqQsCAAAACAAAAA4AAABfAFIAZQBmADIANAA4ADAAMwA1ADMANgAxAAAA REF _Ref248035361 \h Figure 5 - 2. Enter the name of the global group as domainname\groupname in the Login name field and click the OK button. If you do not remember the name, you can search for it using the Search button. In the search window, click on the Object Types button and select groups in the resulting window. Type the name of the group in the Enter the object name to select field and click the Check Names button to search. Click the OK button to select the located group.
Figure 5 - 2: SQL Management Studio Login – New Window
.gif "Project Server Image")
Select the User Mapping page. In the Users mapped to this login section, select the checkbox to map your Project Server Reporting database, and in the Database role membership for: section select the checkbox for db_datareader and leave the db_public role selected as shown in the following figure.
Figure 5 - 3: User Mapping page for SQL Server login
.gif "Project Server Image")
Click the OK button to complete the new login, and exit from SQL Management Studio if you like.
Configure Excel Services
Excel Services for SharePoint Server 2010 provides the reporting foundation for Project Server. In a simple sense, Excel Services provides a platform for sharing spreadsheets on the web. Excel Services not only provides a fully interactive presentation layer for Excel Spreadsheets on the web with a similar experience to the Excel client, it also provides a rich security framework within which report authors and administrators can control user access. This functionality includes the ability to secure spreadsheet elements at a granular level; you can secure parts of a spreadsheet while exposing others. For instance, you can show certain users only Summary task information displayed on one sheet in the workbook while preventing them from seeing the detailed task information driving the summary tasks and contained in a different sheet in the workbook.
Microsoft Excel 2010 provides lush new data visualization features and conditional formatting capabilities that allow you to create visually stunning reports. You can then drop these into SharePoint web pages as web parts to create executive and team dashboards to drive decision making and collaboration. Users can interact with spreadsheets on the web using a familiar interface presented through the web using the new Microsoft Office Web Applications. By integrating Project Server with Excel Services, Microsoft has taken the Project Web App interface from offering a paucity of reporting capacity to now providing a limitless data mining experience.
Note
Project Server 2010 is also compatible with Excel 2007.
Tip
Discussing Excel Services and focusing only on the way Project Server leverages Excel Services by default is barely scratching the surface of its capabilities within and way beyond your EPM implementation. To be effective as a Project Server administrator, you should learn as much as possible about Excel Services and the business intelligence capabilities that ship with Microsoft SharePoint Server Enterprise Edition.
You build Project Server reports by using Excel Spreadsheets connected to data sources such as the Project Server Reporting database and the Project Server OLAP cubes. Excel connects to data sources by using trusted connections that are defined and stored in a SharePoint document library. To determine the URL for your connection library launch your new Project Web App site and select Business Intelligence from the Quick Launch menu as shown in the following figure.
Figure 5 - 4: Select Business Intelligence from the Quick Launch menu
.gif "Project Server Image")
The system displays the Business Intelligence Center home page as shown in the following figure.
Figure 5 - 5: Business Intelligence Center Home Page
.gif "Project Server Image")
Welcome to your new Business Intelligence Center: you have arrived. By this I mean that the Business Intelligence Center is the most important value center Project Server 2010 inherits from the SharePoint Server Enterprise platform. It may take you a while to comprehend the enormity of the opportunity this provides for you to mine and analyze your Project Server data. The new platform allows you to mash-up your project and portfolio data with line of business systems and a vast array of disparate data sources, especially if you select to deploy the Business Data Connectivity service, also included with SharePoint Enterprise. The tool capabilities presented through combining Excel Services 2010 and PerformancePoint Services 2010, along with all of other conveniences of the SharePoint environment, make business intelligence more accessible to the average business user than ever before.
This becomes the single source of all serious reporting information for your Project Server 2010 environment. The only business analysis that has its own special home resides within the Portfolio Analysis area that supports project portfolio decision making. This specialized feature, previously provided by Microsoft Portfolio Server 2007, is generally not for the average business user. Project Server ships with sample reports and dashboards to form a basic reporting framework upon which you can build your own organization-specific content. You learn more about building Reports and Dashboards in Module 10.
To proceed with your implementation, select the Data Connections link from the Quick Launch menu. On the Data Connections page, select the English (United States) Project Server Data Connections check box. The system displays the Data Connections page shown in the following figure. Hover your mouse pointer to the left of the folder icon for English (United States) or your specific locale and select the check box that appears. Selecting the checkbox automatically selects the Library Tools menu displaying the options on this menu.
Figure 5 - 6: Data Connections Page
.gif "Project Server Image")
On the Menu, click the View Properties button. The system displays the Data Connections dialog shown in the following figure.
Figure 5 - 7: Data Connections Properties dialog
.gif "Project Server Image")
On the Data Connections Properties page, right-click the English (United States) Project Server Data Connections link, and then select Properties. The system displays the Properties dialog shown in the following figure.
Figure 5 - 8: Properties dialog
.gif "Project Server Image")
Notice that the system displays the URL for your Data Connections library, which you need in order to setup a trusted data connection for the library in the steps that follow. On the Properties dialog box, select the Address (URL) value and press Ctrl + C, or right-click the selected text, and then click Copy to harvest the URL. Note that the entire URL is not displayed in the dialog window and you must capture all the text by scrolling if you are using a mouse or click into the URL and press Ctrl + A to select all. Click the Cancel button after you copy the URL to your clipboard. Save the URL to a handy place, such as a new Notepad document, for later use. Then click the Close button on the Data ConnectionsProperties dialog.
With the correct URL of the Data Connections library saved in a handy place, navigate to your SharePoint Central Administration site to setup the trusted connection for Excel Services. From the Central Administration home page, select the Manage service applications link from the Application Management section. The system displays the Service Applications page shown in the following figure.
Figure 5 - 9: Service Applications Page
.gif "Project Server Image")
Select the Excel Services row by clicking on the non-hyperlink portion of the row. (Note that clicking on the Excel Services hyperlink navigates you off the current page.) With the row selected, click the Manage button on the menu or click the Excel Services link. The system displays the Manage Excel Services Application page shown in the following figure.
Figure 5 - 10: Manage Excel Services Application Page
.gif "Manage Excel Services Application")
On the Manage Excel Services Application page, click the Global Settings link. Scroll to the bottom of the page, and in the External Data section enter ProjectServerApplication in the Application ID text box as shown in Figure 5-11.
Warning
You must use "ProjectServerApplication" without the quotes and maintaining the case-sensitivity, as the Secure Store Service application expects this value, and it is embedded in your connection documents, sample reports and templates.
Figure 5 - 11: Enter Application ID
.gif "Site Settings")
After completing your entry, click the OK button to save your data and return to the Manage Excel Services Application page shown previously in Figure 5-10. On the Manage Excel Services Application page, click the Trusted File Locations link. The system displays the Excel Services Application Trusted File Locations page shown in the following figure.
Figure 5 - 12: Excel Services Application Trusted File Locations Page
.gif "Trusted File Locations")
Click the Add Trusted File Location link. The system displays the Excel Services Application Add Trusted File Location page shown in the following figure.
Figure 5 - 13: Excel Services Application Add Trusted File Location details page
.gif "Project Server Image")
Figure 5 - 13: Continued
.gif "Project Server Image")
In the Address field at the top of the page, enter the URL that you copied for your Business Intelligence Center
http://< servername>/<projectsitename>/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)
substituting your server name for <servername> and your Project Web App site name for <projectsitename>.
In the Trust Children section, select the Children trusted check box.
In the Allow External Data section underneath External Data, select the Trusted data connection libraries and embedded option.
In the Warn on Refresh section, clear the Refresh warning enabled check box.
Click the OK button to save your entries. The system redisplays the Excel Services Application Trusted File Locations page, with your new trusted location, shown previously in Figure 5 - 12. Click the Central Administration link at the top of the Quick Launch menu to return to the SharePoint Central Administration home page.
From the SharePoint Central Administration home page, click on the Manage service applications link in the Application Management section. The system displays the Manage service applications page shown previously in Figure 5-9. As you did previously, select the Excel Services row without clicking on a link portion of the row, and then click the Manage button on the menu. The system displays the Manage Excel Services Application page shown previously in Figure 5-10. Click the Trusted Data Connection Libraries link. The system displays the Excel Services Application Trusted Data Connection Libraries page shown in the following figure.
Figure 5 - 14: Excel Services Application Trusted Data Connection Libraries Page
.gif "Project Server Image")
Click the Add Trusted Data Connection Library link. The system displays the Excel Services Application Add Trusted Data Connections Library page shown in the following figure.
Figure 5 - 15: Excel Services Application Add Trusted Data Connection Library Page
.gif "Project Server Image")
In the Address field, type the URL for the data connection library that you harvested previously. It should be in the following format:
http://<ServerName>/<ProjectSiteName>/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)
Click the OK button to save your new trusted connection.
Configure the Secure Store Service Application
Configure the Secure Store Service Application
The Secure Store Service, formerly known by its code name “Geneva,” provides claims-based authentication to the SharePoint environment. The Secure Store Service is the marriage of three Microsoft technologies: 1) Active Directory Federation Services, 2) Windows CardSpace, and 3) Windows Identity Foundation working together to provide a flexible standards-based authentication service that can interact with any business identity system including Active Directory, LDAP-based directories, application-specific identity systems as well as user-centric systems like Microsoft’s LiveID.
The Secure Store Service stores information about users connecting from various systems in its own metadata store. The system accepts and issues security tokens, more commonly referred to simply as “tokens,” that contain information about the rights of, or “assertions” about, various users to access system objects and data. These “assertions” are collectively referred to as “claims.” The Secure Store Service acts as a common security broker that provides a bridge between identity systems, external data stores and service applications running within the SharePoint envelope.
Tip
Microsoft’s best-practice recommendation is to isolate your Secure Store Service deployment as much as possible. If you are deploying within a larger SharePoint farm, you probably planned your architecture by following Microsoft’s best practices for deploying SharePoint Server farms. If you haven’t already read this guidance, you should review the available SharePoint Server 2010 deployment documentation on TechNet. Microsoft’s best-practice recommendations are as follows:
Run the secure store service in its own isolated application pool.
Run the secure store service on its own application server.
Create the secure store database on a different SQL Server than the one hosting your SharePoint Server 2010 databases.
If you are building a small farm to house your Project Server environment on its own server island, then you may not be able to follow these best-practice guidelines, and you may not have the luxury of using multiple SQL Server boxes, but you can isolate these using SQL Server instances. Of course there are licensing impacts to extending your SharePoint Server farm and SQL Server deployments onto additional processors.
Generate a New Encryption Key
The next step is to generate an encryption key for the Secure Store. Because this is the key the system uses to encrypt and decrypt user credentials data housed in the secure store, it deserves careful handling. Be certain to read the following best-practice note before proceeding with this part of the configuration process.
Tip
Before generating a new encryption key, you should always back up the secure store database. In addition, you should back up the Secure Store database immediately after you create it in the previous steps. Now would be the time to do this.
Tip
After completing the initial setup of the Secure Store Service, back up your encryption key, and back it up each time you regenerate it.
Tip
Store the database backup and Encryption Key backup on separate physical backup media. A malicious user must have access to both to compromise the data store.
Warning
When you regenerate a key, the system re-encrypts the database with the new key. If the key refresh fails, you cannot return to the previous state without both a backup of both the Secure Store Service database and its corresponding key backup.
From the SharePoint Central Administration home page, select the Manage service applications link in the Application Management section. From the Manage Service Applications page, select either link for the Secure Store Service application. The system displays the Secure Store Service Application: SecureStoreService page shown in the following figure.
Figure 5 - 16: Secure Store Service Page
.gif "Project Server Image")
Notice that the system alerts you that you must generate a key before you can create a new Secure Store Target Application. To generate a new key, click the Generate New Key button in the Key Management section of the menu. The system displays the Generate New Key page shown in the following figure.
Figure 5 - 17: Generate New Key Page
.gif "Project Server Image")
Enter a passphrase for the encryption key in the Pass Phrase field and confirm it in the Confirm Pass Phrase field. Your pass phrase should consist of upper case and lower case letters as well as one or more numbers and special characters. Click the OK button to submit your pass phrase. After processing momentarily, the system redisplays the Secure Store Service Target Applications page without the stop warning as shown in Figure 5-16. Remain on this page to complete the next procedure.
Create a Secure Store Target Application
In this step, create the Secure Store Target Application. Click the New button in the Manage Target Applications section of the menu. The system displays the Create New Secure Store Target Application page shown in the following figure.
Figure 5 - 18: Create New Secure Store Target Application Page
.gif "Project Server Image")
In the Target Application ID box, type “ProjectServerApplication” and type a display name for your new Target Application. Enter a valid email address in the Contact E-mail field, and select Group from the Target Application Type pick list. The system automatically selects the None option in the Target Application Page URLsection. Click the Next button to confirm your entries. The system displays the Specify the credential fields for your Secure Store Target Application page shown in the following figure.
Figure 5 - 19: Specify Credentials … Page
.gif "Project Server Image")
The page defaults to the fields necessary to authenticate with Windows credentials, which include the Windows User Name and Windows Password fields. Notice that you can determine the display name for the fields that you can select from the Field Type pick lists and that you can select the Masked option or delete the default fields. You might use a different set of fields when creating a Target Application for a different application, but for the Project Server Target Application, you will use the defaults. Click the Next button to continue. The system displays the Specify the membership settings page shown in the following figure.
Figure 5 - 20: Specify the membership settings page
.gif "Project Server Image")
On the Specify the membership settings page, type the username for the Farm Administrator role in the Target Application Administrators field. In the Members field, enter the name of the domain group you created for report viewers. Verify that you entered the correct names by clicking the people icon with the check mark. Click the OK button to continue. The system proceeds to create the Secure Store Target Application. This may take several minutes. When the process completes, the system displays the Target Application page for the Secure Store Service shown in the following figure.
Figure 5 - 21: Target Application page for Secure Store Service
.gif "Project Server Image")
Select the Secure Store Target application that you just created and click the Set: Credentials button on the ribbon. The system displays the Set Credentials for Secure Store Target Application (Group) page shown in the following figure.
Figure 5 - 22: Set Credentials for Secure Store Target Application
.gif "Project Server Image")
Enter the service account identity you want to use for the Secure Store application as well as the password for the account in the appropriate entry fields. Click the OK button to set the credentials for your new target application.
Configure the Cube Building Service
The first step in setting up Analysis services for Project Server is to verify that you have the necessary SQL components installed on each server in your configuration. If you installed to an environment with more than one server, you must install SQL client components on servers running the Project Server service application on your farm. If you installed to a single server or if you used the SharePoint Technologies preparation tool, you may already have these components installed on your system. You must install the SQL Server 2008 Native Client and the SQL Server 2008 Analysis Management Objects (AMO) on the server running Project Server 2010, regardless of whether you are using SQL Server 2005 or SQL Server 2008. Project Server 2010 uses the Analysis Management Objects, a departure from the Project Server 2007 approach which used the SQL Server Analysis Services Decision Support Objects (DSO). You must also install the SQL Server 2008 Native Client on the computer running Analysis Services if this is located on a separate machine.
Obtain the Necessary Software Files
Your first objective is to obtain the necessary components for Microsoft downloads. Go to the primary download site at https://www.microsoft.com/downloads and search for “SQL Server feature pack.” As Microsoft download locations can change, it is always best to begin with a search, which also allows you to obtain the latest available version.
You can download the SQL Server 2008 Native Client here:
https://go.microsoft.com/fwlink/p/?LinkId=123718&clcid=0x409
You can download the SQL Server 2008 Analysis Management Objects (AMO) here:
https://go.microsoft.com/fwlink/p/?LinkId=130655&clcid=0x409
Note
If the above links do not work, remember that the Feature Pack for SQL is available for both standard 32-bit (X86) systems and for 64-bit systems (X64). Select the appropriate X64 files for your server.
Install the SQL Server Native Client
To install the SQL Server Native Client components, follow the directions below:
Double-click the MSI Package that you downloaded for the Microsoft SQL Server Native Client (sqlnclix64.msi). The system displays the SQL Server Native Client Setup screen shown in the following figure.
Figure 5 - 23: SQL Server Native Client
.gif "Project Server Image")
Click the Next button. The system displays the SQL Server Native Client End User License Agreement shown in the following figure.
Figure 5 - 24: SQL Server Native Client EULA page
.gif "Project Server Image")
Read the license agreement. In order to continue you must accept the terms; to accept select the I accept the terms in the license agreement radio button and click the Next button to continue the installation. The system displays the Registration Information page shown in the following figure.
Figure 5 - 25: Registration Information
.gif "Project Server Image")
Complete the information on the Registration Information page and click the Next button to continue. The system displays the Feature Selection page shown in the following figure.
Figure 5 - 26: Feature Selection Page
.gif "Project Server Image")
Select the Client Components and, optionally, the Software Developers Kit (SDK) as well. Note that you can view the Disk space requirements for the selected components by clicking on the Disk Cost button. Click the Next button to continue and the system displays the Ready to Install the Program page shown in the following figure.
Figure 5 - 27: Ready to Install the Program
.gif "Project Server Image")
Click the Install button to continue with the installation. The system displays the Installing Microsoft SQL Server Native Client progress dialog shown in the following.
Figure 5 - 28: Installing Microsoft SQL Server Native Client
.gif "Project Server Image")
The system displays the progress dialog until the installation is complete. When it is complete, the system displays the Completing the Microsoft SQL Server Native Client Setup dialog shown in the following figure.
Figure 5 - 29: Completing the Microsoft SQL Server Native Client
.gif "Project Server Image")
Click the Finish button to close the installation window.
Install the SQL Server Management Objects Collection
Complete the following steps to install the Microsoft SQL Server 2008 Management Objects Collection:
Double click on the MSI file that you downloaded to start the installation. After a brief loading period, the system displays the Welcome to the Microsoft SQL Server Management Objects Collection Setup page shown in the following figure.
Note
The screens and steps for installing SQL components may change slightly with each new release from Microsoft. Most steps remain the same from version to version.
Figure 5 - 30: Object Collections Setup Welcome page
.gif "Project Server Image")
On the Welcome to the Microsoft SQL Server Management Objects Collection Setup page, click the Next button to continue the installation. The system displays the License Agreement page shown in the following figure.
Figure 5 - 31: License Agreement Page
.gif "Project Server Image")
On the License Agreement page, read the license agreement. In order to continue you must accept the terms; to accept select the I accept the terms of this license agreement option and then click the Next button. The system displays the Registration Information page shown in the following figure.
Figure 5 - 32: Registration Information Page
.gif "Project Server Image")
On the Registration Information page, enter your name and company. Click the Next button to continue. The system displays the Ready to Install the Program page shown in the following figure.
Figure 5 - 33: Ready to Install the Program Page
.gif "Project Server Image")
On the Ready to Install the Program page, click the Install button to continue the installation. The system displays the Installing Microsoft SQL Server Management Objects Collection installation progress page shown in the following figure.
Figure 5 - 34: Installation Progress page
.gif "Project Server Image")
After the installation completes, the system displays the Completing the Microsoft SQL Server Management Objects Collection Setup page shown in the following figure. Click the Finish button to complete the installation.
Figure 5 - 35: Completion page
.gif "Project Server Image")
Create a SQL Login for the Service Account Running Analysis Services
The cube building service runs under the OLAP Services account. Therefore, the OLAP Services account also needs datareader rights on the Project Server 2010 reporting database. This process is similar to the steps you followed to create a login for the Report Authors global group earlier in this module. If you are using the Farm Administrator account for all services, this step in not necessary as the Farm Administrator account is already the dbo for the databases. If you do need to create a login and assign the necessary rights, complete the following steps:
Using SQL Server Management Studio, connect to the SQL Server instance containing your Project Server reporting database. Expand the Security folder and right click on the Logins folder and select the New Login menu item as shown in the following figure.
Figure 5 - 36: SQL Server Management Studio New Login
.gif "Project Server Image")
The system displays the Login – New page shown in the following figure.
Figure 5 - 37: SQL Server Login – New Page
.gif "Project Server Image")
Enter the service OLAP service account information in the Login name field and then select the User Mapping page in the Select a page section of the window. The system displays the User Mapping page shown in the following figure.
Figure 5 - 38: User Mapping Page
.gif "Project Server Image")
In the Users mapped to this login section of the upper window, select the checkbox for your Project Server Reporting database. In the Database role membership for <databasename> section of the lower window, select the checkbox for db_datareader and leave the public role selected. Click the OK button to complete your entry.
Implement Optional SQL Server Performance Enhancements
There are two SQL Server database settings that are set to OFF by default, but can cause performance issues in your environment if they are turned ON: 1) AUTO_CLOSE and 2) AUTO_SHRINK.
AUTO_CLOSE
This option is set to OFF by default for both SQL Server 2005 and SQL Server 2008, and in all likelihood you will not have to change it. With the AUTO_CLOSE option set to ON, the database engine closes and shuts down when all processes in the database complete and the last user exits the database. Project Server makes and breaks connections to SQL Server repeatedly, so using the AUTO_CLOSE option forces the system to use a lot of system overhead to repeatedly reopen the databases and should not be used for Project Server databases.
AUTO_SHRINK
This option is set to OFF by default for both SQL Server 2005 and SQL Server 2008. When this option is set to ON, the system marks all of the database’s files for shrinking, which causes SQL Server to initiate a shrink process automatically when more than 25 percent of the file contains unused space. With the option set to ON your production system will suddenly suffer a performance decline when SQL Server decides it is shrink-time.
To verify the status of these settings for your databases, you can use the following queries respectively:
SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')
GO
SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoShrink')
GO
The system returns a value of 0 for OFF or 1 for ON. To view these properties for all of your databases, use the following query:
SET NOCOUNT ON
SELECT [name] AS DatabaseName
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
Order By DatabaseName
AUTO UPDATE STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC
By default, the system sets SQL Server databases to enable AUTO UPDATE STATISTICS. This command can run during core business hours when activity on the server is high, affecting performance. With the introduction of SQL Server 2005, Microsoft introduced a new database property, AUTO_UPDATE_STATISTICS_ASYNC, which configures a database to update statistics asynchronously vs. synchronously. Updating statistics synchronously is the default behavior in both SQL Server 2005 and 2008. In synchronous mode, if a query request triggers an AUTO UPDATE STATISTICS event, the query must wait for the system to update the statistics. Once the system completes updating the statistics, the system executes the query. However, if you enable the AUTO_UPDATE_STATISTICS_ASYNC property, the query executes immediately against the existing statistics. At the same time a background request tells the database engine to update the statistics automatically as soon as possible, without preventing any query requests. When the background operation completes, new query requests begin using the new statistics information. This option affects only AUTO UPDATE STATISTICS events, not manually requested statistic updates or statistic updates scheduled through a SQL maintenance plan. To set these property values using SQL Query Manager use the following queries respectively:
ALTER DATABASE DatabaseName SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE DatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
To change the status of these settings using SQL Server Management Studio, expand the tree in Object Explorer, locate your database, and right click on the database name and select Properties from the popup menu. The system displays the Database Properties page. In the Select a Page pane on the left, select Options. Your Database Properties window should appear similar to the following figure.
Figure 5 - 39: Database Properties Window Options Page Displayed with Default Settings
.gif "Project Server Image")
In the Automatic options section, set your desired values by clicking into the value column and selecting your value from the dropdown list.
Create Additional TempDB Files
Both Project Server 2010 and Microsoft SharePoint Server 2010 make extensive use of TempDB during SQL transactions. To improve performance, create additional TempDB files. To optimize performance, create an additional TempDB file for each processor (core) in the computer running SQL Server. Create the files on a separate partition from other database files.
Verify Project Server Functionality
Now that the installation and initial configuration process is complete, you should verify system functionality and connectivity. If you have not already done so, you must install the Project Professional 2010 client on at least one workstation to complete the work in this module. Make sure that you apply the same Service Pack level to both the client and the server.
Verifying Project Web App Connectivity
Log in to your workstation using the account that you designated as the Project Server Administrator, which is the only account that has access to your Project Web App site at this time. Enter the URL for your new Project Server installation in your browser to verify that your new Project Web App site loads from a system on your network other than the server where it is installed. The system displays your home page as shown in the following figure. Note that the system welcomes you by login name at the top right of the page.
Figure 5 - 40: Project Web App Home page for an administrator
You should see a page that has nothing to report in the Reminders section. Figure 5 - 40 reflects a first-time login to a Project Server instance without any data.
Creating a Login Account in Project Professional 2010
During the installation process, Project Server 2010 creates a single Project Server user, which is an administrator-level account. It uses the Windows account that you specified during the installation process in Module 04; typically this is the Farm Service Account or the installation account that you are using. Because your administrator toolkit includes both Project Professional 2010 and Project Web App, you must be able to connect to Project Server using Project Professional 2010. Before you can use Project Professional 2010 in your Project Server environment, you must create a login account for the Project client application on your local machine.
Note
If you did not specify your own login account during the installation process, you should log in to your workstation using the account you specified as the Project Server Administrator, or first create an account for yourself on the Project Server instance. I cover creating resources and users in Module XX.
You have two options available to you for creating a Project Server login account. You can do this by launching the Microsoft Project Server 2010 Accounts tool under the Office Tools folder in the Office folder from your start menu, or you can launch Project Professional 2010 and complete the following steps:
From Project Professional 2010, click the new File tab and select the Info selection on the left hand navigation menu, and then select the Manage Accounts button in the Project Server Accounts section of the page shown in the following figure.
Figure 5 - 41: Project 2010 Information Page
.gif "Project Server Image")
Note
Notice that the Information Page carries two types of content: 1) Navigation tools for accessing Project Server accounts and the organizer, and 2) Summary information about the current project. If you close the default Project 1 file, the system displays only the navigation buttons and the title becomes simply "Information."
After you click the Manage Accounts button, the system displays the Project Server Accounts dialog shown in the following figure.
Figure 5 - 42: Project Server Accounts dialog
.gif "Project Server Image")
Project Server 2007 users will notice that Microsoft updated this dialog for Project 2010. Click the Add button and the system displays the Account Properties dialog shown in the following figure.
Figure 5 - 43: Account Properties dialog
.gif "Project Server Image")
Enter a friendly name for your new account in the Account Name field and enter a valid URL in the Project Server URL field. You must enter the URL with the http:// or https:// prefix. Select the Set as default account option. Figure 5-43 shows the completed Account Properties dialog for the new account that connects to my organization’s Production Project Server instance.
Click the OK button. If you enter a URL using the http:// protocol rather than the secure https:// protocol, the system displays the warning dialog shown in Figure 5 - 44. Ignore the warning unless your server requires a secure URL. Click the Yes button to continue if you see this warning.
Figure 5 - 44: Security warning dialog
.gif "Project Server Image")
The system redisplays the Project Server Accounts dialog with your new account, as shown in the following figure.
Figure 5 - 45: Project Server Accounts dialog with new account
.gif "Project Server Image")
In the Project Server Accounts dialog, select the Choose an account option. This is particularly important if you connect to multiple Project Server instances and if you want to completely control the connection state.
Tip
MSProjectExperts recommends that you always select the Choose an account option. This provides better control for users who connect to multiple instances and allows for better control of off-line feature use.
Create an additional Project Server login account for each instance of your Project Server 2010 system, such as for test or training Project Server instances, and then click the OK button.
After you create your Project Server login account profiles in Project Professional 2010, exit and then re-launch the software. This is a necessary step because creating your login accounts does not connect you to a Project Server instance. The system displays the Login dialog shown in Figure 5 - 46. Because you selected the Choose an account option in the Project Server Accounts dialog, the system allows you to select the Project Server instance for connection. Had you not chosen this option, the system automatically connects to the instance you set as default.
Figure 5 - 46: Login dialog
.gif "Project Server Image")
The Login dialog pre-selects the account you set as the Default account in the Project Server Accounts dialog. If you wish to connect using a different login account, click the Profile pick list and select a different account as shown in the following figure. Note that the first time you connect to a Project Server instance, the Work Offline option is not available.
Figure 5 - 47: Select a different Profile
.gif "Project Server Image")
Note
You can select the Computer profile to use Project Professional 2010 without connecting to a Project Server instance. This causes the software to function in “desktop only” mode and disables all enterprise features.
Click the OK button to connect to your desired Project Server instance. If the system cannot connect to the selected Project Server instance, it displays the Could not connect to Server dialog shown in the following figure.
Figure 5 - 48: Could not connect to Server warning
.gif "Project Server Image")
If you see the dialog shown in Figure 5-48, click the Cancel button to return to the Login dialog and then click the Cancel button in the Login dialog. At this point, you must troubleshoot the cause of the login problem, which is likely one of the four possible reasons previously shown in the Could not connect to Server warning.
Performing Basic Functional Tests
Now that you have verified basic Project Server connectivity in Project Professional 2010, it is time to confirm that you can exercise basic system input, such as creating, saving, and publishing a project. To verify that you can create and save a project, launch Project Professional 2010 and select the login account that you created in the previous section of this module. Use the Project 1 file that Project opens automatically, or click on the File tab and select the New link from the side menu as shown in the following figure.
Figure 5 - 49: Project Professional with New Project options displayed
.gif "Project Server Image")
In the Available Templates section, click the Blank project link. The system opens the blank project as shown in the following figure.
Figure 5 - 50: New Project
.gif "Project Server Image")
Add a few tasks to your new project. Click File, Save and the system displays the Save to Project Server dialog shown in the following figure.
Note
Note that unless you have selected otherwise, the system defaults all new tasks to “Manually Scheduled,” which is a new feature in Project Server 2010. To learn about Manually Scheduled tasks and the other new features in Microsoft Project Professional 2010, refer to the Ultimate Learning Guide to Microsoft Project 2010: Foundations, ISBN: 978-1-934240-13-7.
Figure 5 - 51: Save to Project Server
.gif "Project Server Image")
Enter a name for your test project in the Name field and then click the Save button. Watch the message in the lower tray of the application frame where the system reports the save status as shown in the following figure. Wait until you see the message “Save completed successfully.”
Figure 5 - 52: Save completed successfully
.gif "Project Server Image")
Next, build a team using enterprise resources in the Enterprise Resource Pool. Select the Resource tab and click the Add Resources button and select the option to Build Team from Enterprise on the menu as shown in the following figure.
Figure 5 - 53: Add resources to project
.gif "Project Server Image")
The system opens the Build Team dialog shown in the following figure.
Figure 5 - 54: Build Team dialog
.gif "Project Server Image")
The dialog should contain only one resource, which is your current login. Select the account representing your current logon on the left, and click the Add button in the center of the dialog to add this resource to the team. Click the OK button to complete the action. In your new project schedule, add the current resource to the test tasks you entered in your project using the Resource Names field as shown in Figure 5 - 55. Note that assigning resources using this method is not a best practice for your users, but it is adequate for testing purposes.
Figure 5 - 55: Tasks with Resources Assigned
.gif "Project Server Image")
Next, click on the Project tab and then click the Project Information button to display the Project Information dialog shown in the following figure.
Figure 5 - 56: Project Information dialog
.gif "Project Server Image")
In the Start field, set the project start date to today, if it is not the default, using the date picker and then click the OK button. Note that you want to use a current start date so that your new task assignments appear in the current period in PWA.
Next, click on the File tab and select the Publish link to publish the project. The system displays the Publish Project dialog shown in the following figure.
Note
Project Professional initiates a save job if you haven’t saved changes to your project plan immediately prior to publishing. You see this in the status tray at the bottom of the Project Professional window in the same place where the system reports the publish job status.
Figure 5 - 57: Publish Project dialog
.gif "Project Server Image")
Accept the defaults in the dialog and click the Publish button to continue. The system announces the publish status and percentage in the Status bar at the bottom of the application window. When the system indicates that the Publish job completed successfully, click File, Close. The system displays the dialog shown in the following figure.
Figure 5 - 58: Check-in dialog
.gif "Project Server Image")
Click the Yes button to check in the project schedule. Launch your Internet Explorer application, if necessary, and navigate to your Project Server home page or refresh this page if you already have it displayed. The page should now look something like the page shown in the following figure. Notice that you now have one or more new tasks assigned to you as highlighted in the figure.
Figure 5 - 59: Project Web App home page after publishing a test project
.gif "Project Server Image")
Click the Tasks link in the My Work section to go to the Tasks page shown in the following figure. You should see the new assignment(s) in the Tasks page.
Figure 5 - 60: Tasks page showing new task assignments
.gif "Project Server Image")
Click the Project Center link from the Quick Launch menu. The system opens the Project Center page shown in the following figure. You should see your test project in this view.
Figure 5 - 61: Project Center Page
.gif "Project Server Image")
On the Project Center page, make sure that your test project is selected in the grid, or select the project by clicking on the row header. You can also select any cell in the row that does not contain a hyperlink. (If you click a cell with a hyperlink, you will navigate away from the page.) In the Navigate section in the menu, click the Project Site button to navigate to your new Project site. The system spawns a new window and displays your new site in the window as shown in the following figure.
Figure 5 - 62: New Project Site
.gif "Project Server Image")
Once you verify that the Project site displays properly, close the Project Site window and return to the Project Web App home page. You have now verified basic Project Server functionality by proving that you can create, save, and publish a project, and that the data is flowing through the system correctly and that the system is creating Project Sites correctly.
Verify Cube Building Capability
Now that you have verified basic functionality for Project Server 2010, you should also verify your ability to build OLAP cubes by configuring your analysis services connection and initiating the first build. To set up the OLAP cube building parameters in the system, navigate to your Project Web App home page and click the Server Settings link in the Quick Launch menu. The system displays the Server Settings page shown in the following figure.
Figure 5 - 63: Server Settings Page
.gif "Project Server Image")
In the Database Administration section of the Server Settings page, click the OLAP Database Management link. The system displays the OLAP Database Management page shown in the following figure.
Figure 5 - 64: OLAP Database Management Page
.gif "Project Server Image")
Notice that Project Server 2010 now supports multiple cubes, which means that you can create cubes for specific audiences, such as departments. You can manage all of your cubes from this page. By default, the system creates a single cube configuration entry with generic names. You must configure this entry to actually build a cube. To configure the build settings for a cube for the first time, click the DatabaseName link below the OLAP Database Name header column. The system displays the OLAP Database Build Settings page shown in the following figure.
Figure 5 - 65: OLAP Database Build Settings
.gif "Project Server Image")
Enter your Analysis Services machine name in the Analysis Services Server field. In the Analysis Services Database to be created field, enter a name for the cube database you want to create (example: <PWAInstanceName>). Note that there are two new sections on this page, Project Department and Resource Department. These allow filtering by department, a new feature in Project Server 2010 that I cover in depth in Module 07. For now ignore these selections, as you must configure your system to use department filtering correctly. Accept the default selection of Use the earliest project start date and the latest project finish date option in the Database Date Range section. For now you can either accept the default selections in the OLAP Database Update Frequency section or deselect the Update Periodically check box, as there is no sense in wasting server resources until you have actual data in the system. Click the Save button to save your cube build settings. The system re-displays the OLAP Database Management page reflecting your changes, as shown in the following figure.
Figure 5 - 66: OLAP Database Management page with saved settings
.gif "Project Server Image")
On the OLAP Database Management page, select the Cube configuration that you just created and click the Build Now button. Notice that the Status column changes to Processing. If you would like to follow the build progress, click the Processing link to open the Build Status window shown in the following figure.
Figure 5 - 67: OLAP Cube Build Status
.gif "Project Server Image")
When the cube build completes successfully, your Build Status page should look something like the following figure.
Figure 5 - 68: Build Status window displaying successful cube build completion
.gif "Project Server Image")
When your initial cube build succeeds, your system is ready to deliver analysis on the portfolio of projects you will soon add to the server. The initial cube build takes practically no time at all because you have only one resource and one project in your database. Expect this process to take much longer as you build up your database of projects and resources. You should revisit the cube settings options after you determine the best time to run the cube building routine, when it does not conflict with other service windows on the server such as daily backups. For now, you have accomplished what you need to verify your configuration; however, you will return to the cube configuration later in the configuration process, after you have configured your system with the custom taxonomic information that makes the information in the cubes interesting and applicable to your users.
Verify Reporting Capability in the BI Center
The final verification step that I suggest you take before proceeding with your Project Server configuration is to verify that your Business Intelligence Center is functioning correctly and you are able to make connections to the sample reports. From Project Web App, select the Business Intelligence link from the My Work section of the Quick Launch menu. The system displays the Business Intelligence Center home page shown in the following figure.
Figure 5 - 69: Business Intelligence Center home page
.gif "Project Server Image")
From the menu on the left, click on the Sample Reports link. The system displays the Sample Reports page shown in the following figure.
Figure 5 - 70: Sample Reports home page
.gif "Project Server Image")
Click the link named English (United States) or the appropriate sample reports link for your installation language. The system displays the sample reports page for the language you choose, in my case English as shown in the following figure.
Figure 5 - 71: Sample Reports for English
.gif "Project Server Image")
Select the SimpleProjectList link to load the Simple Projects List report shown in the following figure. When the system prompts to refresh external data connections, click the Yes button to proceed with displaying the report.
Figure 5 - 72: Simple Projects List report
.gif "Project Server Image")
You should see the test project that you created earlier in this module showing in the report. You can also test the Resource Capacity report which will contain only one resource at this time. All of the other reports will show no results, as no data exists in the system meeting the selection criteria for the other reports. The main point of this exercise is to verify that you configured Excel Services and the Secure Store Service correctly for normal operations.
Congratulations, you now have a fully functioning SharePoint Server 2010 and Project Server 2010 installation. Next, you take on the more difficult and time-consuming task of configuring your Project Server for your organization’s specific requirements. The modules that follow walk you through this process.
Creating Additional Project Server Instances
Creating additional Project Server instances on your farm is quite simple once you have learned how to perform the post installation configuration steps that are the primary subject of this module. Before you create your new Project Server instance, you must consider whether your new instance targets the same audience as your first site, or whether it will serve a different user community. Review this entire topical section before creating an additional instance, to properly prepare yourself and your security environment based on its target audience.
To begin the process of creating an additional Project Server instance, open your SharePoint Central Administration site and click on the Manage service applications link in the Application Management section. The system displays the Manage Service Applications page. Select the Project Server Service Application link and the system displays the Manage Project Web App Sites page shown in the following figure.
Figure 5 - 73: Manage Project Web App Sites
.gif "Project Server Image")
Click the Create Project Web App Site link, just as you did previously on this page to create your first PWA site in Module 4. The system displays the Create Project Web App Site page shown in the following figure.
Figure 5 - 74: Create Project Web App Site page
.gif "Project Server Image")
Notice that I gave the site a new name in the Project Web App Path field. Likewise, you must choose a unique value for this field and then change the database names from the default names to a unique and identifiable name for your system. In this case, I chose to prefix the database names with the server and instance name because my SQL Server instance hosts databases from multiple Project Server farms. These identifiable labels display nicely in SQL Server Management Studio. Click the OK button to create your new site. The system returns to the Manage Project Web App Sites page shown in the following figure. Notice that a line item now shows for your new site and the Status field changes as the site provisioning walks through the following steps:
Waiting for resources
Creating Project Web App Site
Provisioning Databases
Configuring the new Project Web App Site
Provisioned
Figure 5 - 75: Manage Project Web App Sites while provisioning a new site
.gif "Project Server Image")
Configuring Your New Project Server Instance
Once the system completes the new site provisioning, you must repeat the post-configuration steps you completed for your first PWA site, for your new site. To view the specific steps for each of the following, refer back to the detailed instructions earlier in this module.
Add login to Project Server Reporting Database for Domain Global Groups: If you create new domain global groups for a Project Server instance, you must add a login for these groups in SQL Server, repeating the steps you took for your first instance.
Add login to Project Server Reporting Database for Secure Store Account: If your new site is intended for a separate audience, you will want to create a new Secure Store Target Application for the new instance. If your new Target Application will use unique credentials, then you must also create a SQL Login for this account following the same steps you used to do this for the first instance.
Add a Trusted Data Connection Library to Excel Services: You must now add the URL for your new site’s Trusted Data Connection library on the Excel Services Trusted Data Connection Libraries page. The easiest way to accomplish this is to open the existing record, copy the URL and paste it into the Add Trusted Data Connection Library page. Change the portion of the URL that represents the instance name to your new site before saving the record.
Add Trusted File locations to Excel Services: As you did with your first site, you must add the two trusted data connection records to your Excel Services Trusted File Locations library. The easiest way to accomplish this is to harvest the URLs from your first two entries and duplicate the entries, changing the portion of the URL that points to the specific instance.
Create a SQL Login for the OLAP Services account for the new instance reporting database: Just as for your first instance, the account running OLAP services requires access to the reporting database for the new instance in order to generate the OLAP cube.
Implement SQL Server performance settings: Use the same rules you applied to your first instance to your new instance.
Warning
Creating a new instance of Project Server provisions all built-in trusted data connections, templates, and sample reports with the same Secure Store Service ID value "ProjectServerApplication," resulting in all new Project Server instances pointing to the first Secure Store Service Application you created above. In order to segregate Report Readers and Report Authors of various instances, each instance requires its own set of domain global groups and each requires its own Secure Store Target Application; otherwise users automatically gain access to the reporting databases of other instances.
Optionally create new domain global groups for report authors and report viewers: I call this an optional step because you must first determine whether you are accommodating a separate security audience in your new site. If your new site serves a distinctly different audience than your first site, you must create new domain global groups to segregate the reporting user authorities for your instances.
Optionally create a new Secure Store Target Application for the instance: When your new instance serves a separate audience than your first site, you should create a new Secure Store Target Application for your new site following the steps in the Create Secure Store Target Application earlier in this module. You can use any Application ID you want, but you must alter the connection strings for your Data Connections Library and the documents in the Sample Reports and Templates libraries to point to this Application ID using the following procedure.
Point Data Connections to the new Secure Store Target Application
When you elect to create a separate Secure Store Target Application with a distinct Secure Store Service ID value, you must update the existing SSS ID values for all of the Connection Workbooks, Templates and Sample Reports, which always point to the Application ID “ProjectServerApplication.” If you created a new Secure Store Target Application for your new Project Server instance, then you must point your Project Server reporting artifacts at your new Target Application. Before executing the following steps, copy your new Target Application ID to your clipboard to avoid typos, keeping in mind that this data is case-sensitive.
From the Project Web App home page for your new instance, click on the Business Intelligence link on the Quick Launch menu. Click the Data Connections link from the menu on the left and click the English (United States) link or the appropriate link for your system and locale. The system displays the Data Connections page shown in the following figure.
Figure 5 - 76: BI Center Data Connections page (partial)
.gif "Project Server Image")
From the Data Connections page, select the first data connection document link, or hover over the link and select the Checkout option. Once the system completes the checkout operation, select Edit in Microsoft Excel from the same menu. The system launches Excel. Enter your logon credentials if the system prompts. Click the Enable button if you see the warning shown in the following figure.
Figure 5 - 77: Excel Security Warning
.gif "Project Server Image")
After you click the Enable button, the system displays the selected workbook in Excel as shown in the following figure.
ProjServerPostInstallConfig78
.gif "Project Server Image")
Warning
If you do not checkout the document first, it opens in Read-Only mode. To edit the document, click the Edit Workbook button in the warning band below the ribbon menu. You must checkout the document before you can complete the final steps in this procedure.
Select the Data tab and select Connections from the Connections section of the ribbon. The system displays the Workbook Connections dialog shown in the following figure.
Figure 5 - 79: Workbook Connections Dialog
.gif "Project Server Image")
In the Workbook Connections dialog, click the Properties button. The system displays the Connection Properties page. Select the Definition tab to display the connection details as shown in the following figure.
Figure 5 - 80: Connection Properties Page Definition Tab
.gif "Project Server Image")
Click the Authentication Settings button in the Excel Services section of the page. The system displays the Excel Services Authentication Settings dialog shown in the following figure.
Figure 5 - 81: Excel Services Authentication Settings dialog
.gif "Project Server Image")
In the SSS ID field, change the Application ID to the one that you assigned to your new Secure Store Target Application for your new instance. Click the OK button to return to the Connection Properties page shown previously in Figure 5-80. On the Connection Properties page, click the Export Connection File button. The system opens the Excel File Save dialog shown in the following figure.
Figure 5 - 82: Excel File Save dialog
.gif "Project Server Image")
Click the Save button to save the new .odc file to a directory on your local machine. Click the OK button to close the Connection Properties dialog and then click the Close button on the Workbook Connections dialog. Close the workbook, and the system prompts you to save as shown in the following figure.
Figure 5 - 83: Excel Save Prompt
.gif "Project Server Image")
Click the Don’t Save button and the system prompts you to check-in the file as shown in the following figure.
Figure 5 - 84: Prompt to Check-in
.gif "Project Server Image")
Click the No button to leave the connection document checked out. Complete the preceding steps for each of the connection documents in the Data Connections Library until you have created new .odc files containing the new Secure Store Target Application pointer for each connection in the library. After you complete the new file set, use the Upload Document button on the Document tab of the ribbon in the Connections Library to upload your new versions of the connection files and check them in after uploading.
Point Reports and Templates to the new Secure Store Target Application
The process for pointing the Sample Reports and Templates to your new Secure Store Target Application is almost identical to the process you follow to re-point the data connection documents, but does not require the export actions described in the final steps required to save and upload new .odc files. Instead, after you check-out and edit the connection authentication value, you can save the document directly back to the SharePoint document library, making this process slightly less complex.
From the Project Web App home page for your new instance, click on the Business Intelligence link in the Quick Launch menu. Click the Sample Reports link from the menu on the left and click the English (United States) link or the appropriate link for your system and locale. The system displays the Sample Reports page shown in the following figure.
Figure 5 - 85: Sample Reports Page
.gif "Project Server Image")
Hover over the link to the first report, Deliverables and select Check Out from the drop down menu as shown in the following figure.
Figure 5 - 86: Check Out Report
.gif "Project Server Image")
The system displays the dialog shown in the following figure.
Figure 5 - 87: Internet Explorer Prompt
.gif "Project Server Image")
Click the OK button to continue.
Once again, hover over the link to reveal the menu and select the Edit in Microsoft Excel link from the menu. The system opens the sample report in Excel as shown in the following figure.
Figure 5 - 88: Sample Report Open in Excel
.gif "Project Server Image")
Notice the Security Warning above the data grid. Click the Enable Editing button in the warning band to continue. The display changes as shown in the following figure.
Figure 5 - 89: Sample Report after Enabling Editing
.gif "Project Server Image")
Notice that the Security Warning changes. You do not have to refresh the data connection by clicking the Enable Content button for this workbook at this time, as you are about to make a slight change to the connection properties. Click on the Data tab and then click in the Connections link from the Connections section of the Data ribbon. The system displays the Workbook Connections dialog shown in the following figure. Note that this is the same dialog shown in Figure 5 - 79and that the next three steps are identical to changing the properties for the Data Connection documents, but repeated here for your convenience.
Figure 5 - 90: Workbook Connections dialog
.gif "Workbook Connections dialog")
Click the Properties button and the system displays the Connection Properties page shown in the following figure. Select the Definition tab in the center of the page.
Figure 5 - 91: Connection Properties for Deliverables Sample Report
.gif "Connection Properties for Deliverables Sample Rept")
Click the Authentication Settings button in the Excel Services section of the page. The system displays the Excel Services Authentication Settings dialog shown in the following figure.
Figure 5 - 92: Excel Services Authentication Settings dialog
.gif "Excel Services Authentication Settings dialog")
In the SSS ID field, change the Application ID to the one that you assigned to your new Secure Store Target Application for your new instance. Click the OK button to return to the Connection Properties page shown previously in Figure 5-91. Click the OK button on the Connections Properties page. If the system displays the Microsoft Excel Security Notice shown in the following figure, click the OK button to ignore the warning.
Figure 5 - 93: Excel Security Notice
.gif "Excel Security Notice")
The system then displays the Microsoft Excel notice shown in the following figure, to warn you that you are changing the data connection. As you want to do this, click the Yes button to accept the change.
Figure 5 - 94: Connection Change Warning
.gif "Connection Change Warning")
The system returns you to the Workbook Connections dialog shown previously in Figure 5-90, click the Close button to close the page. In the Workbook, click File, Save, to save the workbook. Then use File, Close to close the workbook. Click the Yes button when the system prompts you to check-in the file and add version comments if you desire. You must repeat this process for every Sample Report and Report Template in both the Sample Reports and Templates libraries. Once you have pointed your reporting artifacts to use your new Secure Store Target Application, you can segregate reporting authorities between instances.
You can not only use this technique to segregate authentication authorities between instances, you can also use this technique to segregate authentication authorities within a Project Web App instance. This is a very powerful construct that provides you with enormous flexibility in targeting your reports and available data connections to specific audiences using Excel Services and the Secure Store Service in SharePoint Server 2010.