Export (0) Print
Expand All

Requirements for using SQL Server 2000 Analysis Services with the Project Server 2007 Cube Building Service

Office 2007

Updated: April 23, 2009

 

Topic Last Modified: 2009-04-16

For cube building, you can use SQL Server 2000 Analysis Services, SQL Server 2005 Analysis Services, or SQL Server 2008 Analysis Services. This article describes requirements for using SQL Server 2000 Analysis Services with the Microsoft Office Project Server 2007 Cube Building Service.

For information about requirements for SQL Server 2005 Analysis Services, see Requirements for using SQL Server 2005 Analysis Services with the Project Server 2007 Cube Building Service.

For information about requirements for SQL Server 2008 Analysis Services, see Requirements for using SQL Server 2008 Analysis Services with the Project Server 2007 Cube Building Service.

In order for SQL Server 2000 Analysis Services to function correctly with the Office Project Server 2007 Cube Building Service, you must apply SQL Server 2000 Service Pack 4 for Analysis Services.

If you are using the SQL Server 2000 database component as your database server, you must apply Service Pack 4 to the database component as well. Both the SQL Server 2000 database component and SQL Server 2000 Analysis Services must be at that same service pack level.

You can check the service pack version for both the SQL Server 2000 database component and SQL Server 2000 Analysis Services by doing the following.

Verify the SQL Server 2000 service pack version
  1. From the Start menu, select Control Panel.

  2. Double-click Add or Remove Programs.

  3. Click the product on which you would like to check the version (either Microsoft SQL Server 2000 or Microsoft SQL Server 2000 Analysis Services).

  4. Click Click here for support information.

  5. Note the version number. The version for Service Pack 4 is 8.00.2039.

To apply Service Pack 4 to SQL Server 2000 Analysis Services, you can use the following procedure.

Apply SQL Server 2000 Service Pack 4 to the Analysis Services component
  1. Go to the Microsoft SQL Server 2000 Service Pack 4 download page (http://go.microsoft.com/fwlink/?LinkId=86989).

  2. Download the Analysis Services Components (download file: SQL2000.AS-KB884525-SP4-x86.EXE).

  3. When you run the download, you are prompted to select a location for saving the file. After specifying the location, click Next. The files will be extracted to this location.

  4. Click Finished.

  5. Browse to the location you specified on the server and run \msolap\install\Setup.exe.

  6. Follow the directions that appear to complete the installation of the service pack.

If you are using SQL Server 2000 Analysis Services with Office Project Server 2007, you need to install the Analysis Services DSO (Decision Support Objects) component on your Project Server application servers. You also need to install SQL Server 2000 Service Pack 4 for Analysis Services on the DSO component as well.

NoteNote:
If SQL Server 2000 Analysis Services is installed on the Project Server application server (for example, a single-computer installation), you do not need to install DSO on that server.
Install SQL Server 2000 Analysis Services Decision Support Objects and Service Pack 4
  1. On the Project Server 2007 application server, insert the SQL Server 2000 CD into the CD-ROM drive, or connect to a network installation point.

  2. If you are installing from the SQL Server 2000 CD with AutoPlay enabled, click SQL Server 2000 Components in the SQL Server 2000 Setup program. Otherwise, on the SQL Server CD (or network installation point), browse for and run the file named Autorun.exe.

  3. On the Install Components page, click Install Analysis Services.

  4. Read and accept the end-user license agreement.

  5. In the Analysis Services Setup program, on the Select Components page, clear all the components except Decision Support Objects and Client components, and then follow the wizard to install DSO.

  6. Go to the Microsoft SQL Server 2000 Service Pack 4 download page (http://go.microsoft.com/fwlink/?LinkId=86989).

  7. Download the Analysis Services Components (download file: SQL2000.AS-KB884525-SP4-x86.EXE).

  8. When you run the download, you are prompted to select a location for saving the file. After specifying the location, click Next. The files will be extracted to this location.

  9. Click Finished.

  10. Browse to the location you specified on the server and run \msolap\install\Setup.exe.

  11. Follow the directions that appear to complete the installation of the service pack.

When you install and configure Office Project Server 2007, the Windows user account responsible for running the Microsoft Project Server Queue service is automatically designated at the time that the Shared Services Provider is created. This account must be added as a member of the OLAP Administrators group.

To find out which Windows user account is designated as the Queue service account, go to the Project Server application server and do the following procedure.

Determine the Microsoft Project Server Queue service account
  1. From the Start menu, click Programs, click Administrative Tools, and then click Services.

  2. In the Services Name list, double-click Microsoft Office Project Server Queue Service.

  3. On properties page, click the Log On tab. Note the Windows user account that the service is running under. You will need to add this account to the OLAP Administrators group in a later step.

  4. Click Cancel to exit the menu.

You will then need to add the Microsoft Project Server Queue service account to the OLAP Administrators group on the computer on which SQL Server 2000 Analysis Services is running.

Add the Queue Service account to the OLAP Administrators group
  1. From the Start menu, click Programs, click Administrative Tools, and then click Computer Management.

  2. In Computer Management, expand Local Users and Groups and then click the Groups folder.

  3. Double-click the OLAP Administrators group to open the properties page.

  4. In the properties page, click Add.

  5. On the Select Users, Computers, or Groups page, in the Enter Object Names to select box, enter the Windows user account under which the Queue Service is running. Click OK.

  6. In the OLAP Administrators property page, click OK.

The OLAP Administrators group needs to have permissions to all SQL Server 2000 Analysis Services components. You may need to explicitly grant the OLAP Administrators group full control on the \bin subdirectory of the Installation directory for SQL Services 2000 Analysis Services. The default location of this directory is:

C:\Program Files\Microsoft Analysis Services\Bin

Each server running Microsoft SQL Server 2000 Analysis Services has a repository to store metadata for the objects of the computer running Analysis Services (for example, cubes and dimensions). By default, this repository is a Microsoft Jet database (.mdb) on the server on which Analysis Services is installed. Our recommendation for better scalability is that you migrate the repository to a Microsoft SQL Server 2000 database.

NoteNote:
You can only migrate the repository to a SQL Server database. The Analysis Services 2005 management application does not support migration of the repository file. However, migrating the repository before you upgrade or obtaining a pre-defined repository database will allow you to have the repository hosted in a SQL Server database.
Migrate the repository
  1. In SQL Server, create a new database and name it Analysis Services Repository.

  2. Under Security, right-click Logins and then click New Login.

  3. For the name of the login, click the ellipses (...) and select the OLAP Administrators group for this computer.

  4. On the Database Access tab, select the Analysis Services Repository database.

  5. Under Permit in Database Role, select db_owner.

  6. Log in to the computer running Analysis Services 2000 by using a user account that has permissions that are equivalent to either the Administrators group or OLAP Administrators group, and start Analysis Manager.

  7. In the left pane, expand Analysis Servers.

  8. Right-click the name of your server, and then click Migrate Repository to start the Migrate Repository Wizard. Migrate the repository to the database that you created in step 1.

    NoteNote:
    We recommend that you choose the Analysis Services native format when migrating the repository.
  9. After you migrate the repository to a SQL Server database, you can safely delete the old repository. By default, this database is \Program Files\Microsoft Analysis Services\Bin\msmdrep.mdb.

Because Project Server 2007 uses the enhanced capabilities of SQL Server integrated security, you must give the Analysis Services service account explicit access to the Project Server 2007 Reporting database in each Project Server instance. Default SQL Server 2000 installations use the Windows system account to run the Analysis Services service. This must be changed to an account that can access SQL Server. A domain account is required if SQL Server is hosted on a separate server.

In Office Project Server 2007, the account running the SQL Server Analysis Services service (MSSQLServerOLAPService) is granted direct access to the Project Server Reporting database in SQL Enterprise Manager.

In order to access the Project Server Reporting Database for the Cube Generation Service, the minimum permission required is the DB_DataReader role. You must grant this permission to the SQL Server Analysis Services service account.

Grant permissions to the SQL Server Analysis Services service account to access the Project Server Reporting database
  1. From the Start menu, select Programs, select Microsoft SQL Server, and then click Enterprise Manager.

  2. In Enterprise Manager, expand Microsoft SQL Servers, SQL Server group, and the server.

  3. Expand the Security folder, right-click Logins, and then click New Login.

  4. On the properties page, in the General tab, enter the Windows Authenticated account for the user running the SQL Server Analysis Services service.

  5. Click the Database Access tab.

  6. Enable Permit for the Project Server Reporting database(s).

  7. In the Permit in Database Role section, enable db_datareader permissions for the Project Server Reporting database(s).

  8. Click OK.

Cube data is seen through a "Data Analysis View" in Project Web Access. Some ActiveX components are required for creating and using these Data Analysis views.

Users are prompted to download the ActiveX components to their computer when they first build a Data Analysis view or when they attempt to use such a view. The Microsoft Office Web Components are a collection of ActiveX components that allows Project Web Access users to use PivotTable and Chart components to access OLAP cube data. Make sure that your users are allowed to download these components to their computers, or else preinstall the components for your users. For more information, see Office XP Tool: Web Components (http://go.microsoft.com/fwlink/?LinkId=87125&clcid=0x409).

Traffic between your Project Server application server and your SQL Server Analysis Services server occurs when a Project Web Access user builds a cube database . Project Web Access clients building Data Analysis views or viewing Project Cube data in Data Analysis views are communicating directly with your SQL Server Analysis Services server. Make sure that any firewalls allow for this traffic, especially when you have extranet users.

The default instance of SQL Server Analysis Services normally listens on port 2383. If you are using named instances in SQL Server 2005 Analysis Services, then the SQL Server Browser service needs to be running on the server to give clients who are accessing it a port for the named instance. The SQL Browser service is normally on port 2382.

Named instances of Analysis Services will have other dynamically allocated ports. These can be discovered by looking in the configuration file for SQL Server Analysis Services.

Determine the port number SQL Server Analysis Services Named Instance
  1. On the computer on which SQL Server 2005 Analysis Services is running, open the msmdredir.ini file in a text editor. The default location is C:\Program files\Microsoft SQL Server\90\Shared\ASConfig.

  2. In the <Instances> section of the msmdredir.ini file, note the text. For example:

    <Instances>
         <Instance>
             <Name>AS2005</Name>
             <Port>1259</Port>
         </Instance>
     </Instances>
    

    In this example, the AS2005 instance is listening on port 1259.

If you have extranet users, it is also important to note that Office Web Components require port 2725 to allow a direct connection to SQL Server 2005 Analysis Services. Even though access is enabled over port 80 via HTTPS, if port 2725 is not available, the dynamic OLAP reports will not be able to access data from Analysis Services.

There are several scenarios related to the Analysis Services account that need to be taken into consideration:

  • Organizations may have multiple Project Web Access instances on the farm. The information described in this article applies to each instance. The same Analysis Services account must be granted DB_DataReader Role permissions to each instance's Reporting database in order to start the Cube Building Service.

  • The Windows logon account used to start the SQL Server 2000 Analysis Services service may be an account where the password will expire. When this occurs, the service will not run again until the password is reset, which could cause down time. A best practice would be to run the service using a dedicated Windows Account where the password does not expire.

  • Depending on how SQL Server 2000 and SQL Server 2000 Analysis Services were installed, the MSSQLServerOLAPService may be running with a local system account. Because the Analysis Services service account needs access to the Reporting database, there are two options to use for the service account in order for the service to access the database for cube generation:

    1. Change the logon access for the service to a Windows user account with a password that never expires. Or simply be aware that you must change the password for the service whenever the account password is changed.

    2. Add the Domain\MachineName$ as an account in Enterprise Manager with DB_DataReader role permissions to the Project Server Reporting database.

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 .

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft