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.
I have MS Project 2010. I am setting up a project dashboard and company-wide resource pool. When I import projects into the dashboard file, some projects are imported with the project name. However, some projects include the entire path of where the file is stored along with the name.
How do I fix this without manually changing it?
Hi Sharon - not sure what you mean by Dashboard File - could you give a little more information on exactly which products you are using?
I have a number of custom fields. I have successfully added some of the custom fields to the relevant ODC files. Other custom fields, I can't add. Please help me figure out why.
Hi Valerie - are these custom fields that you can access in the appropriate reporting database tables or cubes? Perhaps you are trying to access fields that are not available in the cubes or tables you are connecting to. Is there a pattern to which work and which don't?
I had setup a Project Server 2010 on SharePoint Server 2010. I changed the name of the server and updated SharePoint configuration. Everything is working fine other than the Business Intelligence Reports, whenever i try to open a report it throws an error saying that Microsoft Excel cannot open the file and it the file path it is showing the old server name instead of the new name. What could be the issue. Please assist and provide a solution and contact me on email@example.com.