Welcome to part 15 in a series of posts which describe how to create a Microsoft Office Project Server 2007 Virtual PC image for demos and development work. Last time, we looked at how to create an instance of Project Server, including the Project Web Access site. This time, we’ll look at how to configure the Project Server OLAP cube building service inside the virtual machine.
Part 15: Configuring the Cube Building Service
Download the Microsoft SQL Server Native Client:
Filename: sqlncli.msi
URL: http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/sqlncli.msi
Copy the sqlncli.msi file into the VPC image and execute the file; when the Welcome to the Microsoft SQL Server Native Client Setup dialog box appears, click the Next button:
In the Program Maintenance dialog box, select the Modify option, then click the Next button:
In the Feature Selection dialog box, verify that both features (Client Components and SQL Server Native Client SDK) are selected, then click the Next button:
In the Ready to Modify the Program dialog box, click the Install button:
After the installation has completed, click the Finish button:
Download the Microsoft SQL Server 2005 Management Objects Collection:
Filename: SQLServer2005_XMO.msi
URL: http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/SQLServer2005_XMO.msi
Copy the SQLServer2005_XMO.msi file into the VPC image and execute the file; when the Welcome to the Install Wizard for Microsoft SQL Server Management Objects Collection dialog box appears, click the Next button:
Read the license agreement very carefully, select the I accept option, then click the Next button:
In the Registration Information dialog box, enter your Name and Company, then click the Next button:
In the Ready to Install the Program dialog box, click the Install button:
After the software installation has completed, click the Finish button:
Download the Microsoft SQL Server 2005 Backward Compatibility Components:
Filename: SQLServer2005_BC.msi
URL: http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/SQLServer2005_BC.msi
Copy the SQLServer2005_BC.msi file into the VPC image and execute the file; when the Welcome to the Microsoft SQL Server 2005 Backward compatibility Setup dialog box appears, click the Next button:
In the Program Maintenance dialog box, select the Modify option, then click the Next button:
In the Feature Selection dialog box, verify that all of the features are selected, then click the Next button:
In the Ready to Modify the Program dialog box, click the Install button:
After the software installation has completed, click the Finish button:
Open Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP folder, select File > New > Folder, and create a folder named DSO9:
Select the new DSO9 folder that was created inside the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP folder, then select File > Sharing and Security:
Under the Sharing tab of the DSO9 Properties dialog box, select the Share this folder option, then change the Share name to MSOLAPREPOSITORY$:
Under the Security tab of the DSO9 Properties dialog box, in the Group or user names list, select SQLServer2005MSOLAPUser$SERVERNAME$MSSQLSERVER (SQLServer2005MSOLAPUser$VPC02$MSSQLSERVER, in this case), select the Allow checkbox next to the Full Control item in the Permissions list, then click the OK button:
Download a blank SQL Server Analysis Services repository file:
Filename: msmdrep.mdb
URL: http://www.pptspaces.com/msprojectreporterblog/Shared%20Files/msmdrep.zip
Copy the msmdrep.zip file into the VPC image, extract the msmdrep.mdb file contained within, and place it into the new DSO9 folder:
Select Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio; in the Connect to Server dialog box, select Analysis Services from the Server type drop-down box, enter or select the server name in the Server name drop-down area (VPC02, in this case), then click the Connect button:
Right-click the Analysis Server name in the left pane (VPC02, in this case), then select Properties from the drop-down list:
Select the Show Advanced (All) Properties checkbox, located near the bottom of the Analysis Services Properties dialog box:
A larger, more complete properties list will be shown; widen the Name column in the list by dragging the column width handle in the header row, then scroll down to the section where the DSO properties are listed:
Locate the DSO\LocksDirectory property in the Name column, then enter C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9 into the Value column on the same row:
Locate the DSO\ RemoteLocksDirectory property in the Name column, then enter C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9 into the Value column on the same row:
Locate the DSO\ RemoteRepositoryConnectionString property in the Name column, then click the selection button in the Value column on the same row:
In the Connection Manager dialog box, select Microsoft Jet 4.0 OLE DB Provider from the Provider drop-down list, then click the OK button:
After returning to the Connection Manager dialog box, click the Browse button next to the Database file name text area:
In the Select Microsoft Access Database File dialog box, browse for the msmdrep.mdb file, which is located in the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9 folder, then click the Open button:
After returning to the Connection Manager dialog box, remove the User name (Admin, in this case), then click the Test Connection button:
The Test connection succeeded message should be displayed; click the OK button to acknowledge the message, then click the OK button to close the Connection Manager dialog box:
After returning to the Analysis Server Properties dialog box, locate the DSO\ RepositoryConnectionString property in the Name column, then click the selection button in the Value column on the same row:
In the Connection Manager dialog box, select Microsoft Jet 4.0 OLE DB Provider from the Provider drop-down list, then click the OK button:
After returning to the Connection Manager dialog box, click the Browse button next to the Database file name text area:
In the Select Microsoft Access Database File dialog box, browse for the msmdrep.mdb file, which is located in the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9 folder, then click the Open button:
After returning to the Connection Manager dialog box, remove the User name (Admin, in this case), then click the Test Connection button:
The Test connection succeeded message should be displayed; click the OK button to acknowledge the message, then click the OK button to close the Connection Manager dialog box:
After returning to the Analysis Server Properties dialog box, click the OK button:
Open Internet Explorer, navigate to the PWA home page located at http://SERVERNAME/pwa (http://vpc02/pwa, in this case), then click the Server Settings link in the left Quick Launch menu:
On the Server Settings page, click the Build Settings link, located below the Cube heading:
On the Cube Build Settings page, enter the following information, then click the Save and Build Now button, located at the bottom of the page:
Analysis Services Server: SERVERNAME (VPC02, in this case)
Analysis Services Database to be created: ProjectServer
In the Project OLAP cube settings have been saved message box, click the OK button:
Immediately after saving the new cube build settings, the Build Status should be Cube build request message has been added to the Project Server queue; you may scroll to the bottom of the page and click the Refresh Status button periodically, or simply wait for the page to refresh automatically when the OLAP cube build process has completed:
After the OLAP cube has been built successfully, the Build Status should be Cube build request completed successfully:
You should now have a fully-functional Project Server OLAP cube building service.
In Part 16, we’ll look at how to install and configure Project Professional 2007.