Wednesday, February 18, 2009

Configuring the Office Project Server 2007 Cube Building Service

Can't get your cube to build in your shiney new environment? Well, don't dispair, this is actually a common problem! It's a pretty easy fix, but you will need a SQL 2000 disk, to finish this proceedure, so hopefully, you have one laying around. Yes, I said SQL 2000 (x86 or x64), that wasn't a misprint or fat finger! I listed these steps in the order they need to be done. So, sit back, relax...and here we go!

Security Requirements
The Application Server (PSI Server) Queue service will be running using the SSP credentials, entered when setting up the SSP instance. The Analysis Services service, used to build the cubes, should be running under the same account or an account running that would need to have read access to the Reporting database.
If multiple PWA instances are created and their cubes are built on the same Analysis Services machine, the recommendation is for administrators to create a single windows account that will run the Analysis Services service, as follow:


  1. Go to the machine where Analysis Services is installed
  2. Go to the Computer Management window
  3. Right click on ‘My Computer’ and select Manage
  4. Select Services under ‘Services and Applications’
  5. Select the Analysis Services (OLAP) service
  6. Right-click on it and select Properties
  7. Select the Log On tab and enter the credentials that will have read access to the Reporting database of all of the PWA instances
The same account used to run the Analysis Services service will need to have read access to all of the PWA instances that plan to build their cubes on that Analysis Service.

Application Server
This applies to all of the WFEs that are running Microsoft Office Project Server 2007. But, it would be a good idea to do this on all WFEs, so if one of your Microsoft Office Project Server 2007 servers go down, you can easly spin up another one.

The Application Server must have the Analysis Services DSO (Decision Support Object) component installed.
To install the Decision Support Objects for Analysis Services


  1. Run the SQL Server Installation Wizard.
  2. On the End User License Agreement page, read the license agreement and then select the check box to accept the licensing terms and conditions. Click Next.
  3. On the SQL Server Component Update page, setup installs software required for SQL Server 2005. To begin the component update process, click Install. To continue after the update completes, click Finish.
  4. On the Welcome page, click Next.
  5. If you will build the cubes in Analysis Services 2005, this component can be obtained by running the installation setup for Analysis Service, and selecting the Client Component to be installed.

If the cubes will be built on Analysis Services 2005, you need to install the following components:

  1. Microsoft SQL Server Native Client (sqlncli.msi)
  2. Microsoft SQL Server 2005 Management Objects Collection (sqlserver2005_xmo.msi)
  3. Microsoft SQL Server 2005 Backward Compatibility Components (SQLServer2005_BC.msi)
  4. Microsoft SQL Server 2005 Microsoft ADOMD.NET (SQLServer2005_ADOMD.msi)
These components can be downloaded from the Feature Pack for Microsoft SQL Server 2005 – February 2007 release, available here: http://www.microsoft.com/Downloads/details.aspx?familyid=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en

Analysis Services
If running Analysis Services 2005, the following setup steps are required:


  1. Add the user account running the Queue service at the Application Server to the OLAP Admin Group
  2. Give administrative access to the OLAP Admin Group to the “\bin” folder
If running Analysis Services 2005 (Service Pack 1 is required), you need to setup a repository for DSO to write to. If you have migrated the Analysis Services 2000 repository to the Analysis Services 2005 server, no additional steps are required.
The instructions below detail how to migrate the repository or create a new one:


  1. On the server with Analysis Services 2005 installed, go to \Program Files\Microsoft SQL Server\MSSQL.2\OLAP
  2. Create a directory called ‘DSO9’
  3. Share it as MSOLAPREPOSITORY$
  4. Give the SQLServer2005MSOLAPUser$MSSQLSERVER administrative access to the share
  5. Copy the file msmdrep.mdb into the DSO9 directory previously created. This file can be found in the Analysis Services 2000, and by copying it to this directory you will be migrating it to Analysis Services 2005. If you don’t have an Analysis Services 2000 server, you should create a new (empty) .mdb file and add it to the DSO9 directory (you should use the name above or edit the section below).
  6. Navigate to the \Program Files\Microsoft SQL Server\MSSQL.x\Config directory
  7. Edit msmdsrv.ini file and replace DSO section with the following (make sure to change the text in red to your specific environment variables):

<DSO>
<RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=\\<MACHINENAME>\MSOLAPRepository$\msmdrep.mdb;
Persist Security Info=False
</RemoteRepositoryConnectionString>
<RepositoryConnectionString>
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=x:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\DSO9\msmdrep.mdb;
Persist Security Info=False
</RepositoryConnectionString>
<RemoteLocksDirectory>
\\<MACHINENAME>\MSOLAPRepository$
</RemoteLocksDirectory>
<LocksDirectory>
x:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\DSO9
</LocksDirectory>
</DSO>

Internet Explorerer Settings
Make sure that the URL to your almost complete Microsoft Office Project Server 2007 PWA site is in the Trusted Zone of your Internet Security. You will then need to follow the final steps to be able to setup and build the cube.

  1. Open Internet Explorer, click Tools, and then click Internet Options.
  2. Click the Security tab, click the zone that you use to connect to the Office Project Server 2007, and then click Custom Level.
  3. Under Access data sources across domains, select Enable.

Note: If you set the security setting to Prompt, you cannot use the Office Web Components when you create a data analysis view.

No comments:

Post a Comment