Step 1: Extending the Staging Tables

Article
09/12/2006

The first step in adding a dimension to the Portfolio Analyzer OLAP cube is to extend the staging tables so they can hold the additional data. To add a new pay period dimension, you first add the PayPeriodName and PayPeriodLink tables to the Microsoft Office Project Server 2003 database. Then, using the Microsoft Visual Basic code available from the Microsoft Download Center, you can run the breakout function, UserStagingTablesUpdate, to populate the tables with information from the database.

To create the new database tables:

You can manually create the tables in the Project Server database using the SQL Server Enterprise Manager. Or, you can use SQL Query Analyzer to run the script ExtendOLAPTables.sql that is included in the solution starter download. Be sure to select the correct database in SQL Query Analyzer before you run the script.

The ExtendOLAPTables.sql script first deletes PayPeriodName and PayPeriodLink tables after it checks whether the tables exist and if you have ownership permission. For example:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PayPeriodName]') 
      and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PayPeriodName]
GO

The script then creates the tables and fields, as follows:

CREATE TABLE [dbo].[PayPeriodLink] (
    [TIME_ID] [int] NULL ,
    [PP_ID] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PayPeriodName] (
    [PP_ID] [int] NULL ,
    [PP_NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

To populate the new tables with data:

The solution starter download includes a sample application that uses the breakout function template to populate the new dimension table for a pay period. After you install the Portfolio Analyzer OLAP Extensions Solution Starter sample code, the Visual Basic project MSPOLAPBREAKOUT contains the UserStagingTablesUpdate function. Use the sample application OLAPBreakoutTest to help you call and test the UserStagingTablesUpdate breakout function.

Note The following procedure shows how to test the breakout function. To have the breakout functions automatically run every time Portfolio Analyzer updates the cube, you must compile the MSPOLAPBREAKOUT project and register MSPOLAPBREAKOUT.dll on the Project Server computer. For a discussion of what you would need to do to install multiple OLAP extensions, see Implementations. The extensions are separate in this solution starter.

Keep SQL Server Enterprise Manager open while performing the following procedure, and then refresh the view to see the table changes.

In Visual Basic 6.0, open the project group OLAPDemoGroup.vbg in the solution starter's PayPeriodDimensions directory.
In the Visual Basic Project Explorer, expand the project trees and right-click the UserOptionalCode class module under MSPOLAPBREAKOUT project. Click View Code.
Place a breakpoint in the UserStagingTablesUpdate function. To insert a breakpoint, click anywhere in the UserStagingTablesUpdate function definition and click in the left breakpoint bar or press F9. You will see all four lines of the function definition highlighted, with the breakpoint symbol in the left bar.
```
Public Function UserStagingTablesUpdate(ByVal o_dbConnection As ADODB.Connection, _
                                        ByVal l_DBType As Long, _
                                        ByRef l_errnum As Long, _
                                        ByRef s_errdesc As String) As Long
```
On the Run menu, click Start (or press F5).
In the DB Connection String field of the OLAP BreakoutTest dialog box, change the Data Source, Initial Catalog, and UID parameters to the corresponding values for your Project Server database. For running multiple tests, modify the connection string in the code. In the following example, Project Server is installed on the computer named ServerName, the extension uses the sample database that was given the name MPSSampleDatabase during setup of Project Server, and the server user name is MSProjectServerSampleDBUser:
```
Provider=sqloledb;Data Source=ServerName;Initial Catalog=MPSSampleDatabase;
   UID=MSProjectServerSampleDBUser;PWD=
```
Important You should not store the database password in code.
In the OLAP Breakout Test dialog box, enter the OLAP server name in the OLAP Server field. For example, if SQL Server Analysis Services is installed on the same computer as Project Server, you would enter ServerName.
Enter the OLAP database name in the OLAP DB field. For example, if SQL Server Analysis Manager shows MPS_Sample_Cube is the top-level name of the OLAP cube database generated by Portfolio Manager, enter MPS_Sample_Cube.
Click Initialize Connection to create the database connection.
Click OLAP Breakout 1 to run the UserStagingTablesUpdate function.

This function populates the PayPeriodName table with the pay period names in the PP_NAME column, and it creates the time period ID (TP_ID) and pay period ID (PP_ID) values in the PayPeriodLink table in the Project Server database.

The code should stop running at the breakpoint you inserted in the UserStagingTablesUpdate function. Step through the function if you want, to see how the tables are updated (on the Debug menu, click Step Into, or press F8), and then check the table data in SQL Server Enterprise Manager as you continue to step through the function. Press F5 when you want to run the rest of the UserStagingTablesUpdate function.

You are now finished with Step 1 of adding a dimension to the cube. You have extended the OLAP cube staging tables to accommodate a pay period dimension and populated the tables with pay period data.

The next step Step 2: Updating the OLAP Cube.

Share via

Step 1: Extending the Staging Tables

Additional resources