Troubleshooting – Business Intelligence Features

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.

Architecture

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. 

Setup

The setup steps for the Business Intelligence features can be found here.  https://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.

Frequently Asked Questions

Why isn’t my workbook rendering on the web?

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.

• One Time Setup Items
  • Has the Excel Services Service Application and Proxy Connection been created?
  • Is Secure Store Service Application and Proxy Connection been created?
  • Is Excel Services Service Application configured?
  • Is the folder for the data connections in a trusted location within the Excel Service Application?
  • Is the folder for the reports in a trusted location within the Excel Service Application?
  • Is the Secure Store Service Service Application configured?
• For each Target Application Profile
  • Has the Secure Store Target Application Profile for the reports configured?
  • Is the user authorized or belongs to a group that is authorized to use the particular SSS Target Application profile?
  • Does the SSS Target Application have credentials set?
  • Does the SSS Target Application credentials have DB_DataReader rights to the Reporting Database?
• For each workbook
  • Is the SSS ID value in the Office Data Connection filled in?
  • Does the SSS ID in the Excel workbook match the ID of the Application Profile in SSS? 

Why hasn’t my data appeared yet?

• If the data is sourced from the Reporting database, it could be that the Reporting Publish job has not yet completed.  Since these jobs are queued, you need to ensure the jobs has completed before you will see the data.
• If the data is sourced from an OLAP database, you won’t see the data until the OLAP database is refreshed.
• If these two items aren’t the case, there may be an issue with the query itself.  

Technorati Tags: Microsoft Business Intelligence,Microsoft Project Server 2010,SQL Server Setup

Comments

• Anonymous
  January 01, 2003
  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? Best regards, Brian

• Anonymous
  January 01, 2003
  Hi Sharon - not sure what you mean by Dashboard File - could you give a little more information on exactly which products you are using? Thanks, Brian

• Anonymous
  April 28, 2011
  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? thanks!!

• Anonymous
  August 21, 2011
  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.

• Anonymous
  December 26, 2012
  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 tariq.guroo@gmail.com.