Originally posted by Treb Gatte over on the Project Administration Blog. I’m reposting it here to make sure everyone sees it:
The Project Server 2010 Business Intelligence feature utilizes the support of Excel Services, Secure Store Service, PerformancePoint Services and SQL Server. A thorough understanding of how these features fit together is necessary to get the most from the feature. This post will provide that overview and provide troubleshooting questions for common issues.
Our Business Intelligence features leverage Excel Services as the base functionality since most people use Excel to visualize data and it’s a tool that many people already know how to use.
There are four core components to this solution.
Excel client. The Excel Client is used to author and publish new reports. This solution will work with Excel 2007 SP2 or later.
Office Data Connections. Office Data Connections(ODC) are used to store the connection information, the SQL Query and the Secure Store Target Application ID. External ODCs are used to allow you to manage data connection and query information externally to the reports that consume the data. These two components together are the deliverables from the report author.
When you provision a new Project Web Application site or when you create a new OLAP database, ODCs and attached templates will be automatically generated in the Business Intelligence Center.
Excel Services. Excel Services provides rendering and interactivity support on the web. This service enables the user to share reports easily with others. It also enables a user to filter the data in a report dynamically to meet a particular need.
Secure Store. Secure Store is a SharePoint service used to store credentials in a Target Application Profile. These profiles help avoid double hop authentication situations and provide control around who has access to what data for a given Target Application Profile. In SharePoint Server 2007, this service was known as Single Sign-On service or SSO.
The diagram above illustrates the interactions between the four components. The arrows denote what information is passed between the components and in what direction.
Lastly, PerformancePoint is called out above as it is used to create the Business Intelligence Center as it is their service that provides this infrastructure. It isn’t used for the core reporting features. However, you can easily develop PerformancePoint reports over Project Server data.
The setup steps for the Business Intelligence features can be found here. http://technet.microsoft.com/en-us/library/ee662106(office.14).aspx
Please note, if you are using Active Directory(AD), you can set up a AD Group for Report Authors such that you only have to create one SQL Login for the group. The membership of the group is then maintained outside of SQL Server.
Also, you must set up a SQL Login with db_datareader rights for the credentials used in the Secure Store Target Application ID. If you have created the AD group for the authors above and it matches the security needed to service reports, you can simply add the Target Application ID credentials to the Report Author’s AD group instead of creating a new SQL Login.
This can be due to a number of reasons. Here is a list of items to verify. The steps to do each of these items are listed in the setup link above.