Condividi tramite

Troubleshooting Business Intelligence Features in Project Server 2010

  • Articolo

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.

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.

Project Server BI Architecture

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.