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

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

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 2008 Analysis Services with the Microsoft Office Project Server 2007 Cube Building Service.

For information about requirements for SQL Server 2000 Analysis Services, see Requirements for using SQL Server 2000 Analysis Services with the 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.

This article describes the following:

• Install the Decision Support Object (DSO) client components on Project Server application servers   Describes how to correctly install the SQL Server components required on the application server in order to communicate with SQL Server 2008 Analysis Services.

• Configure the Shared Services Provider (SSP) account to access SQL Server 2008 Analysis Services   Describes how to add the SSP account to the OLAP users group and as an administrator on SQL Server 2008 Analysis Services.

• Configure SQL Server 2008 Analysis Services   Describes how to create the repository database through two options:

  • Create the repository database by creating a SQL Server 2008 database

  • Create the repository by using a SQL Server 2000 Microsoft Jet database

• Grant the Analysis Services service account access to the Project Server Reporting database   Describes how to grant permissions to the SQL Server Analysis Services account to access the Reporting database, which is required by the cube building service.

• Requirements for building and viewing cubes in Project Web Access   Describes how to install components that are required for creating and using Data Analysis views that use the OLAP cube data.

• Port requirements for SQL Server 2008 Analysis Services   Describes port requirements for the traffic between the Project Server application server and SQL Server Analysis Services server.

• Port requirement for Office Web Components   Describes port requirements for the Office Web Components when trying to view OLAP cube data by using Data Analysis views over an extranet.

• Error Messages and possible solutions   Describes errors and possible solutions and workaround that can occur when configuring SQL Server 2008 Analysis Services with the Office Project Server 2007 cube building service.

Install the Decision Support Object (DSO) client components on Project Server application servers

Although Office Project Server 2007 can be used with Analysis Services in either SQL Server 2000, 2005, or 2008 versions, it was built primarily to be used with SQL Server 2000 Analysis Services. The Decision Support Objects (DSO) library included with Microsoft SQL Server 2008 enables programs written for Analysis Services in SQL Server 2000 to work with Analysis Services in SQL Server 2008. Office Project Server 2007 uses the SQL Server 2000 Analysis Services DSO library to create and manage OLAP cube data. The ability of SQL Server 2008 Analysis Services to be backwards compatible allows it to use the DSO model required by Office Project Server 2007 for OLAP cube management.

By default, the DSO library is installed by the SQL Server 2008 installation process. However, for the purposes of cube building, the DSO library must be installed on every server on which the Project Server application server is running. Therefore, on any server computer running Project Server application server where Analysis Services is not installed, you need to install the DSO library.

The way to install the DSO library is to get a set of redistributable packages included in the SQL Server 2005 Feature Pack. That set consists of:

1. Microsoft SQL Server Native Client

2. Microsoft SQL Server 2005 Management Objects Collection

3. Microsoft SQL Server 2005 Backward Compatibility Components

Important

Although this article involves SQL Server 2008 Analysis Services, it is important that you download these components from the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288). At this time, do not use the feature packs for SQL Server 2008 to install and download these components, as those files will not allow you to build cubes in SQL Server 2008 Analysis Services with Project Server 2007.

Important

Prior to installing the Microsoft SQL Server 2005 Management Objects from the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288), you must install the Microsoft SQL Server Native Client from this same feature pack collection. It is important to install this component first.

Install the Microsoft SQL Server Native Client

1. Browse to the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288) download page.

2. Click the X86 Package for the Microsoft SQL Server Native Client (sqlncli.msi).

   Note

   Select the X64 Package if you are using the 64-bit version of Office Project Server 2007 on your application server.

3. Click Run to start the download.

4. Click Run to start the installation.

5. On the Welcome to the Microsoft SQL Server Native Client Setup page, click Next.

6. On the License Agreement page, select I accept the terms of this license agreement, and then click Next.

7. On the Registration Information page, enter your name and company. Click Next.

8. On the Feature Selection page, verify that both features (Client Components and SQL Server Native Client SDK) have this option selected: This feature, and all subfeatures, will be installed on local hard drive. Click Next.

9. On the Ready to Modify the Program page, click Install.

10. After the installation is done, click Finish.

Important

Make sure to install the Microsoft SQL Server 2005 Native Client from the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288).

Install the Microsoft SQL Server 2005 Management Objects Collection

1. Browse to the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288) download page.

2. Click the X86 Package for the Microsoft SQL Server 2005 Management Objects Collection (SQLServer2005_XMO.msi).

   Note

   Select the X64 Package if you are using the 64-bit version of Office Project Server 2007 on your application server.

3. Click Run to start the download.

4. Click Run to start the installation.

5. On the Welcome to the Install Wizard for Microsoft SQL Server Management Objects Collection page, click Next.

6. On the License Agreement page, select I accept the terms of this license agreement, and then click Next.

7. On the Registration Information page, enter your name and company. Click Next.

8. On the Ready to Modify the Program page, click Install.

9. After the installation is done, click Finish.

Install the Microsoft SQL Server 2005 Backward Compatibility Components

1. Browse to the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288) download page.

2. Click the X86 Package for the Microsoft SQL Server 2005 Backward Compatibility Components (SQLServer2005_BC.msi).

   Note

   Select the X64 Package if you are using the 64-bit version of Office Project Server 2007 on your application server.

3. Click Run to start the download.

4. Click Run to start the installation.

5. On the Welcome to the Microsoft to the Install Wizard for Microsoft SQL Server 2005 Backward compatibility page, click Next.

6. On the License Agreement page, select I accept the terms of this license agreement, and then click Next.

7. On the Registration Information page, enter your name and company. Click Next.

8. On the Feature Selection page, verify that the DSO component has this option selected: This feature, and all subfeatures, will be installed on local hard drive. Click Next.

9. On the Ready to Modify the Program page, click Install.

10. After the installation is done, click Finish.

Configure the SSP account to access SQL Server 2008 Analysis Services

Prior to configuring SQL Server 2008 Analysis Services, you must configure the Shared Services Provider (SSP) account to have the correct permissions to access it. (The SSP account is the security account for the application pool that you are using.)

To do this, you must:

• Add the SSP account to an OLAP users local group on the computer hosting SQL Server 2008 Analysis Services.

• Add the SSP account as an administrator on the SQL Server 2008 Analysis Services instance.

To begin with, determine the SSP account, as follows.

Determine the SSP account

1. On the SharePoint Central Administration Web site, in the Quick Launch, click Shared Services Administration.

2. On the Manage this Farm's Shared Services page, from the drop-down list for the Shared Services Provider you are using, click Edit Properties.

3. On the Edit Shared Services Provider page, in the SSP Services Credential section, note the account name in the Username field. This is the SSP account.

Add the SSP account to the OLAP users group

When you install SQL Server 2008 Analysis Services, a local group is created on the server that allows users to access it. This group is named SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER. You must add the SSP account to this group.

Note

In SQL Server 2000 Analysis Services, this group is similar to the OLAP Administrators group. However, in the group used for SQL Server 2008 Analysis Services, Administrative privileges are not automatically assigned to members.

Add the SSP account to the OLAP users local group

1. Click the Start menu, point to All Programs, point to Administrative Tools, and then click Computer Management.

2. On the Computer Management page, in the left pane under System Tools, expand Local Users and Groups. Click the Groups folder.

3. In the right pane, under the Name list, double-click SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER.

   Note

   <SERVERNAME> represents the name of the computer.

4. On the SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER properties page, click Add.

5. On the Select Users, Computers, or Groups page, go to the Enter the object names to select section and add the name of the SSP account. Click Check Name to verify that the account exists.

6. Click OK.

Configure the SSP account to have administrative permissions in SQL Server 2008 Analysis Services

You must also add the SSP account as an administrator in SQL Server 2008 Analysis Services in order to have permissions to create databases. In SQL Server 2000 Analysis Services, this automatically occurs when a user is added to the OLAP administrators group. Similarly to SQL Server 2005 Analysis Services, the user must be added manually as a member to the Server Administrator role in SQL Server 2008 Analysis Services.

Members of the Server Administrator role within an instance of Microsoft SQL Server 2008 Analysis Services have unrestricted access to all Analysis Services objects and data in that instance. A member of the Server Administrator role can add Microsoft Windows users and groups to the Analysis Services server role. A user must be a member of the Server Administrator role to perform any server-wide task, such as creating a database, modifying server properties, or launching a trace (other than for processing events).

Add the SSP account as a Server Administrator role member in SQL Server 2008 Analysis Services

1. Open SQL Server Management Studio. In the Connect to Server window, connect to the instance of SQL Server 2008 Analysis Services that you are using.

2. In Microsoft SQL Server Management Studio, in Object Explorer, right-click your SQL Server 2008 Analysis Services instance name, and then click Properties.

3. On the Analysis Services Properties page, in the Select a page pane, click Security. NT Users and Groups that are members of the server role will appear in a list.

4. Click Add. In the Select Users or Groups page, go to the Enter the object names to select field and enter the name of the SSP account that you are adding to the server role. Click Check Name to verify that the account exists.

5. Click OK.

Configure SQL Server 2008 Analysis Services

After installing the DSO client on Project Server application servers, you must create the repository database and then configure SQL Server 2008 Analysis Services to enable access to it. You can create the repository database in either of two ways:

• Create the repository in a SQL Server 2008 database

• Create the repository by using a SQL Server 2000 Microsoft Jet database

Option 1: Create the repository database by creating a SQL Server 2008 database

It is possible to create the repository database in SQL Server 2008 to use in SQL Server 2008 Analysis Services. The following set of procedures creates the repository database and runs a SQL script to create the required database schema.

Create the repository database in SQL Server 2008

1. Open SQL Server Management Studio. For Server Type, select the Database Engine, and then click Connect.

2. In Microsoft SQL Server Management Studio, right-click the Databases folder, and then click New Database.

3. On the New Database page, for Database Name type Analysis Services Repository. Click OK.

4. In the Object Explorer list, expand the Security folder. Right-click Logins and then click New Login.

5. In the Login Name section, click Search.

6. On the Select Users or Groups page, click Object Type.

7. On the Object Type page, select Groups, and then click OK.

8. On the Select Users or Groups page, go to the Enter the object name to select box and enter the local OLAP users group for this computer. Click OK.

9. On the Select a page list, click User Mappings. In the Users mapped to this login list, select Analysis Services Repository.

10. In the Database role membership for list for the repository database, select db_owner. Click OK.

11. In Microsoft SQL Server Management Studio, expand the Databases folder and right-click Analysis Services Repository. Click New Query.

12. In the Query Editor, enter the following text:

    CREATE TABLE [dbo].[OlapObjects] (

                [ID] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

                [ParentID] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

                [ObjectName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

                [ClassType] [int] NOT NULL ,

                [ObjectDefinition] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

                [LastUpdated] [datetime] NULL ,

                [Changed] [bit] NULL ,

                [Version] [int] NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Server] (

                [ObjectDefinition] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

13. Execute the script. The script will create the database schema required for the repository.

Edit the Analysis Services DSO configuration

You need to configure the Analysis Services DSO properties so that the repository database is configured to be used from DSO in SQL Server 2008 Analysis Services. Changes made during this process will be reflected in the Analysis Services configuration file (Msmdsrv.ini).

Configure Analysis Services to use a SQL Server repository database

1. In SQL Server 2008, start SQL Server Management Studio. On the Connect to Server window, select Analysis Services, and then click Connect.

2. In SQL Server Management Studio, in the Object Explorer pane, right-click the Analysis Services name, and then choose Properties.

3. On the Analysis Services Properties page, in the Select a page section select General. Select Show Advanced (All) Properties.

4. Select DSO\RepositoryConnectionString from the Name list.

   1. Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.

   2. On the Connection Manager page, in the Provider list, select Native OLE DB\SQL Native Client.

   3. In the Server Name list, select the server on which the repository database is located.

   4. In the Logon to the server field, enter the account information to connect to the server.

   5. In the Connect to database section, select Select or enter a database name and enter the name of the repository file.

   6. Click OK.

5. Select DSO\RemoteRepositoryConnectionString from the Name list.

   1. Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.

   2. On the Connection Manager page, in the Provider list, select Native OLE DB\SQL Native Client.

   3. In the Server Name list, select the server on which the repository database is located.

   4. In the Logon to the server field, enter the account information to connect to the server.

   5. In the Connect to database section, select Select or enter a database name and enter the name of the repository file.

   6. Click OK.

6. On the Analysis Server Properties page, click OK.

Option 2: Create the repository by using a SQL Server 2000 Microsoft Jet database

In SQL Server 2000 Analysis Services, the repository file is a Microsoft Jet database (.mdb) that could easily be migrated to a SQL Server 2000 Analysis Services repository database. SQL Server 2008 Analysis Services does not include the repository file. However, the Microsoft Jet database that comes with SQL Server 2000 Analysis Services can still be used in SQL Server 2008 Analysis Services as the repository database. This provides another option to create the repository database.

This approach requires the following steps to be performed on the computer on which SQL Server 2008 Analysis Services is running:

1. Create the shared folder for the repository.

2. Download the repository file to the remote administration shared folder.

3. Edit the Analysis Services configuration file.

Create a shared folder for the repository

You must create a shared folder in SQL Server 2008 Analysis Services where the repository will be located. You also must make the shared folder accessible to accounts that will need to access it.

Create the shared folder

1. In Windows Explorer, browse to the folder containing the Analysis Services installation. By default, it is located at:

   C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLServer\OLAP

2. In this folder, create a new folder and name it DSO9.

3. Right-click the DSO9 folder, and then click Sharing.

4. On the Sharing and Security page, on the Sharing tab, click Advanced Sharing. On the Advanced Sharing page, select Share this folder. In the Share Name field, type MSOLAPRepository$ as the share name for the folder.

5. Click Permissions. On the Share Permissions tab, in the Group or user names list, click Add. On the Select User, Computers, or Groups page, add the SQLServerMSASUser$<Servername>$MSSQLServer account from the local computer. Click OK.

6. In the Group or user names list, select the SQLServerMSASUser$<Servername>$MSSQLServer account. In the Permissions for SQLServerMSASUser$<Servername>$MSSQLServer box, select Allow next to Full Control. Click OK.

   Note

   For security reasons, you will want to remove the Everyone group from the Group or user names list.

7. On the Security tab, in the Group or user names list, click Edit. Click Add, and on the Select Users, Computers, or Groups page add the SQLServerMSASUser$SERVERNAME$MSSQLSERVER account from the local computer. Click OK.

8. From the Group or user names list, select SQLServerMSASUser$SERVERNAME$MSSQLSERVER. Select the Allow check box next to the Full Control item in the Permissions list, and then click OK.

9. On the Security tab, click Edit. Click Add, and on the Select Users, Computers, or Group page add the account running the Project Server Queue service on the Project Server application server. Click OK.

   Note

     To verify this account, on the Project Server application server, click the Start menu, click Programs, click Administrative Tools, and then click Services. Double-click Microsoft Office Project Server Queue Service and note the account on the Log On tab.

10. From the Group or user names list, select the account you just added. Select the Allow check box next to the Full Control item in the Permissions list, and then click OK.

11. On the DSO9 Properties page, click OK.

Copy the repository file to the remote administration share

DSO needs to have full access to a copy of the SQL Server 2000 Analysis Services repository (Msmdrep.mdb) for which the DSO application is being used. You can use the repository that is included with SQL Server 2000 Analysis Services. The location of the sample repository file depends on the installation path for the instance of SQL Server 2000 Analysis Services, but it is typically located as follows: C:\Program Files\Microsoft Analysis Services\ Bin.

Note

If you do not have the SQL Server 2000 repository file, click here (https://go.microsoft.com/fwlink/?LinkId=87082&clcid=0x409) to download it.

After obtaining a copy of the Msmdrep.mdb file, copy it to the remote administration share (the DSO9 folder) that you created earlier.

Edit the Analysis Services DSO configuration

You need to configure the Analysis Services DSO properties so that the repository is configured to be used from DSO in SQL Server 2008 Analysis Services. Changes made during this process will be reflected in the Analysis Services configuration file (Msmdsrv.ini).

Configure Analysis Services to use a SQL Server repository file

1. In SQL Server 2008, start SQL Server Management Studio. In the Connect to Server window, select Analysis Services, and then click Connect.

2. In SQL Server Management Studio, in the Object Explorer pane, right-click the Analysis Services name, and then choose Properties.

3. On the Analysis Services Properties page, in the Select a page section, select General. Select Show Advanced (All) Properties.

4. Select DSO\RepositoryConnectionString from the Name list.

   1. Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.

   2. On the Connection Manager page, in the Provider list, select Microsoft Jet 4.0 OLE DB Provider and click OK.

   3. In the Database file name section, click Browse. In the Select Microsoft Access Database File window, locate the repository file and click Open. This action enters the path of the repository file in the Database file name box.

   4. In the Logon to the database field, enter the account information for the database. Click OK.

5. Select DSO\RemoteRepositoryConnectionString from the Name list.

   1. Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.

   2. On the Connection Manager page, in the Provider list, select Microsoft Jet 4.0 OLE DB Provider.

   3. In the Database file name section, click Browse. In the Select Microsoft Access Database File window, locate the repository file and click Open. This action enters the path of the repository file in the Database file name box.

   4. In the Logon to the database field, enter the account information for the database. Click OK.

6. On the Analysis Server Properties page, click OK.

Grant the Analysis Services service account access to the Project Server Reporting database

In Office Project Server 2007, the account running the SQL Server Analysis Services service needs to be granted direct access to the Reporting database in SQL Server Management Studio.

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 account to access the Reporting database

1. In SQL Server 2008, start SQL Server Management Studio. On the Connect to Server window, select database engine, and then click Connect.

2. In Management Studio, expand the Security folder, right-click Logins, and then click New Login.

3. On the General page, enter the Windows authenticated account for the user running the SQL Server Analysis Services server.

4. In the Select a page list, click User Mapping.

5. In the Database list, select the Project Server Reporting database (the default name is Project Server_Reporting). Select the corresponding Map check box.

6. In the Database role membership for the Project Server Reporting database section, select db_datareader.

7. Click OK.

Requirements for building and viewing cubes in Project Web Access

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

• Microsoft Office Web Components   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 install the components for your users. For more information, see Office XP Tool: Web Components (https://go.microsoft.com/fwlink/?LinkId=87125\&clcid=0x409).

• Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider   This component allows your users who are accessing cube data to query data stored within SQL Server 2008 Analysis Services. This component can be downloaded from the Microsoft SQL Server 2008 Feature Pack, August 2008 download page (https://go.microsoft.com/fwlink/?LinkID=133802\&clcid=0x409).

  Note

  If you are using SQL Server 2000 Analysis Services, this component is not required.

  Install the Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

  1. Browse to the Microsoft SQL Server 2008 Feature Pack, August 2008 download page (https://go.microsoft.com/fwlink/?LinkID=133802\&clcid=0x409).

  2. Click the X86 Package for the Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider (SQLServer2008_ASOLEDB10.msi).

     Note

     Select X64 Package if your client operating system is a 64-bit version.

  3. Click Run to start the download.

  4. Click Run to start the installation.

  5. On the Welcome to the Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider page, click Next.

  6. On the License Agreement page, select I accept the terms of this license agreement, and then click Next.

  7. On the Registration Information page, enter your name and company. Click Next.

  8. On the Ready to Install the Program page, click Install.

  9. After the installation is done, click Finish.

• Enable Access data sources across domains in Internet Explorer Additionally, if the Access data sources across domains security setting in Internet Explorer is set to Disable, you must change this setting to Enable for the given security zone that you use to connect to Project Web Access.

  Enable the "Access data sources across domains" security setting in Internet Explorer

  1. In Internet Explorer, click Tools, and then click Internet Options.

  2. Click the Security tab, click the zone that you use to connect to Office Project Server 2007, and then click Custom Level.

  3. Under Access data sources across domains, select Enable.

Port requirements for SQL Server 2008 Analysis Services

Traffic flows between your Project Server application server and your SQL Server Analysis Services server 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 is normally listening on port 2383. If you are using named instances in SQL Server 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 Server Browser 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 the SQL Server Browser.

Determine SQL Server Analysis Services Named Instance port number

1. On the computer on which SQL Server 2008 Analysis Services is running, open the Msmdredir.ini file in a text editor. The default location is C:\Progarm files\Microsoft SQL Server\90\Shared\ASConfig.

   Note

   If you are using the 64-bit version of SQL Server 2008 Analysis Services, the default location is C:\Program files (c86)\Microsoft SQL Server\90\Shared\ASConfig.

2. In the <Instances> section of the Msmdredir.ini file, note the text. Here is an example:

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

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

Note

As an alternative, you can also verify the port being used by checking the Analysis Services instances properties page and checking the Port value.

Port requirement for Office Web Components

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 are not able to access the Analysis Services data.

Analysis Services considerations

There are several things about 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 2008 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 2008 and SQL Server 2008 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.

Error messages and possible solutions

Error Message 1

Symptom:

When building a cube, the following error is received: Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: The Application Server needs to have Analysis Services DSO Component installed.

Solution:

More than likely you are missing the Backwards Compatibility Components from the Decision Support Objects components. See the "Install the DSO client components on Project Server application servers" section of this article and install any that you may be missing.

Error Message 2

Symptom:

When building a cube, the following error is received: Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server PetKrebbsSQL08. Error: Cannot connect to the repository. Analysis server: PetKrebbsSQL08 Error: Provider cannot be found. It may not be properly installed.

Solution:

More than likely you are missing the Native Client of the Decision Support Objects components. It is also possible that you have the incorrect version. See the "Install the DSO client components on Project Server application servers" section of this article and install any that you may be missing.

Error Message 3

Symptom:

When building a cube, the following error is received: Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server PetKrebbsSQL08. Error: ActiveX component can't create object.

Solution:

More than likely you are missing the Management Objects Collection of the Decision Support Objects components. It is also possible that you have the incorrect version. See the "Install the DSO client components on Project Server application servers" section of this article and install any that you may be missing.

Error Message 4

Symptom:

When building a cube, the following error is received: Failed to connect to the Analysis Services server PetKrebbsSQL08. Error: Cannot connect to Analysis Services version '10.0.1600.22' (or Analysis Services version '10.0.1763.0' if you have loaded the Cumulative Update 1 for SQL Server 2008).

Solution:

You are not using the correct version of the Analysis Management Objects. Make sure to install the files from the Feature Pack for Microsoft SQL Server 2005 - December 2008 (https://go.microsoft.com/fwlink/?LinkId=142288) or later.

Error Message 5

Symptom

When building a cube, the following error is received: Failed to create OLAP cubes. Error: Failure Analysis Services session, with the following error: Failed to connect to Analysis Services server srv-sql2008.intranet.opt. Error: Your permissions on the server computer do not entitle you to administer this Analysis server.

Solution:

Create an empty MSOLAP.upd file in the SQL Server 2008 Analysis Services installation directory. You will have to grant access to the directory that contains the file and then point to it through SQL Server Analysis Services advanced properties.

To create and grant access to the MSOLAP.upd file

1. In Windows Explorer, browse to the folder that contains the Analysis Services installation. By default, it is located at:

   C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLServer\OLAP

2. In this folder, create a new folder and name it DSO9, if this name does not already exist.

3. Right-click the DSO9 folder, and then click Sharing.

4. On the Sharing and Security page, on the Sharing tab, click Advanced Sharing. On the Advanced Sharing page, select Share this folder. In the Share Name field, type MSOLAPRepository$ as the share name for the folder.

5. Click Permissions. On the Share Permissions tab, in the Group or user names list, click Add. On the Select User, Computers, or Groups page, add the SQLServerMSASUser$<Servername>$MSSQLServer account from the local computer. Click OK.

6. In the Group or user names list, select the SQLServerMSASUser$<Servername>$MSSQLServer account. In the Permissions for SQLServerMSASUser$<Servername>$MSSQLServer box, select Allow next to Full Control. Click OK.

7. In Notepad, create an empty file and name it MSOLAP.upd. Save the file to the DSO9 folder.

8. In SQL Server 2008, start SQL Server Management Studio. In the Connect to Server window, select Analysis Services, and then click Connect.

9. In SQL Server Management Studio, in the Object Explorer pane, right-click the Analysis Services name, and then choose Properties.

10. On the Analysis Services Properties page, in the Select a page section, select General. Select Show Advanced (All) Properties.

11. Select DSO\RemoteLocksDirectory from the Name column.

12. In the Value field for DSO\RemoteLocksDirectory, enter the path to the DSO9 directory (for example, \\Servername\MSOLAPRepository$).

13. Click OK.

After you complete the procedure, restart SQL Server 2008 Analysis Services and attempt to rebuild the cube. Also make sure to verify that the Microsoft SQL Server 2008 Native Client is installed on all Office Project Server 2007 application servers.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

• Cube Building Service in Office Project Server 2007

See the full list of available books at Downloadable content for Project Server 2007.