View OLAP Cube data
Updated: April 23, 2009
By allowing stakeholders in an organization to access your Project Server 2007 OLAP cube data, you enable them to identify critical information across the entire organization, such as how projects compare to each other, in addition to specific project details. Available features such as charts, filters, spreadsheets, and pivot tables allow for sophisticated reporting on and analysis of project and resource data.
Project Web Access users who are stakeholders in an organization, such as executives, project managers, and administrators, can use Data Analysis views to sort, filter, or change data; expand and collapse detail views; work with pivot table lists; and chart the results of their changes. This functionality is provided through Microsoft Office Web Components. These components are a collection of ActiveX controls designed to let users publish fully interactive worksheets, charts, PivotTable reports, and databases to the Web.
Data Analysis views were known as Portfolio Analyzer views in Microsoft Office Project Server 2003.
You can also use Microsoft Excel to view Project cube data. This option does not require you to be a Project Web Access user. After importing the data to Excel, use Excel to create reports using charts and pivot tables. You can also modify data in the spreadsheet to create "what-if?" scenarios to see how it affects project output. For example, you can change billable hours for a resource to see how other values change as a result. Additionally, if you have Microsoft Office SharePoint Server 2007, you publish your report to Office SharePoint Server 2007, and then allow your users to interact with the report by using Excel Services in Microsoft Office SharePoint Server 2007 in a Web browser.
You can also view your OLAP cube data through SQL Server Reporting Services, but this approach is not be covered in this article. For more information, see SQL Server Reporting Services (SSRS) (http://go.microsoft.com/fwlink/?LinkId=89402&clcid=0x409).
The following are required to create a Data Analysis View:
The Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider must be installed on the computer on which you are building the view. (This requirement applies only if you are using SQL Server 2005 Analysis Services.) For more information, see Requirements for using SQL Server 2005 Analysis Services with the Project Server 2007 Cube Building Service.
The computer on which you are creating the Data Analysis view must be able to download the required ActiveX controls that contain the Office Web Components, or the components should already be installed. To facilitate installation of the controls, you should temporarily do the following in Internet Explorer:
Add the Project Web Access site as a trusted site.
Clear the option to Block Pop Ups.
Enable Access data source across domains. You can change this setting through Internet Options by configuring the Trusted Sites Zone security settings.
Verify that your users who are viewing the Project cube data through Data Analysis views have access to SQL Server Analysis Services, especially when they are accessing it through a firewall. Make sure that any firewalls allow for this traffic.
In instances where your Data Analysis View users are connecting to an extranet through HTTPS, even though access is enabled over port 80 you must also open TCP port 2725 in the organizational firewall. Doing so allows Office Web Components (OWC) to have a direct connection to the Analysis Services server.
The following are required to connect to Project data with Microsoft Office Excel 2007:
Read access to the Project Server Reporting database in SQL Server.
Microsoft Office Excel 2007 installed on your computer.
To view OLAP cube data, you can perform the following procedures:
Download this book
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for Project Server 2007.