Share via

Step 1: Extending the Staging Tables

  • Article

After the task issues and risks template is installed on Microsoft® Windows® SharePoint™ Server, you can implement the OLAP extension for task issues and risks. As in the solution starter sample Adding a Pay Period Dimension, the first step in adding dimensions for issues and risks to the Portfolio Analyzer cube is to extend the staging tables. The easiest way to add the MSP_CUBE_WSS_RISKS and MSP_CUBE_WSS_ISSUES tables to the Microsoft® Office Project Server 2003 database is with the script provided in the solution starter download.

To create the new database tables:

The WSSCubeSetup.bat script (in the TaskEarnedValue\Utils subdirectory) uses the SQL Server osql.exe utility to add the staging tables with a Transact-SQL script; it is not necessary to use SQL Query Analyzer. Following are the two key lines in WSSCubeSetup.bat:

set EV_ProjectDatabaseName=MPSSampleDatabase
...             
OSQL -E  -d "%EV_ProjectDatabaseName%" -i createWSSCubesTable.txt

  1. Edit the WSSCubeSetup.bat file to set the EV_ProjectDatabaseName variable to the correct name of your database. The example uses the Project Server sample database that was named MPSSampleDatabase during Project Server setup.

  2. Execute the WSSCubeSetup.bat script. The file createWSSCubesTable.txt contains the SQL script that creates the tables.

    The SQL script first deletes MSP_CUBE_WSS_RISKS and MSP_CUBE_WSS_ISSUES tables after it checks whether the tables exist and if you have ownership permission. The script then creates the empty tables and fields, similar to the SQL script for the first solution starter sample (see the topic Step 1: Extending the Staging Tables in the section Adding a Pay Period Dimension).

The next step for the OLAP extension for task issues and risks is Step 2: Updating the Data with UserStagingTableUpdate to add the risks and issues data from Windows SharePoint Services to the staging tables.