Extending OLAP Cube Services in Microsoft Project Server
Extending OLAP Cube Services in Microsoft Project Server
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
This article provides an overview of the process used to build the Portfolio Analyzer online analytical processing (OLAP) cube in Microsoft® Project Server, and its architecture. In addition, it explains how to extend the cube building process to either add data to the cube, such as a new dimension, or to set up security in the cube. Finally, this article describes how to customize the user interface in Microsoft Project Web Access to allow you to create Portfolio Analyzer views that point to a different OLAP cube from the one generated by Microsoft Project Server.
The Portfolio Analyzer OLAP cube is a powerful reporting and analysis feature in Microsoft Project Server that allows you to perform complex analysis of project data. This article describes how you can extend and customize the Portfolio Analyzer OLAP cube to meet the needs of your organization using Visual Basic 6.0, SQL Server Enterprise Manager, and SQL Server Analysis Manager.
In this article, we explain the technologies involved, the architecture of the Microsoft Project Server Portfolio Analyzer, the cube generation process, and the extensibility model for the OLAP cube.
In addition, you can use the procedures in this article to extend the Microsoft Project Server OLAP cube by adding a pay period dimension to the cube. Using the Visual Basic code included in the download, you can run a function that extends the staging tables by populating the new tables with pay period data. You can run another function to edit the structure of the virtual cube. Then, you can regenerate the cube with the new staging table data.
Finally, there are sections at the end of this article that describe how you can add security to the cube, how you can find information on automating this same process with Decision Support Objects (DSO), and how you can troubleshoot the cube building process.
Extending the OLAP Cube
There are tools for extending the OLAP cube that are built into the user interface of Microsoft Project Server Portfolio Analyzer. For example, you can insert calculated fields, variance fields, and special chart types by using the Microsoft Office XP Web Components in the Portfolio Analyzer user interface. However, you may want to extend the cube in other ways.
There are many reasons why you might want to extend data in the cube. For example, you may want to grow the data by extending the measures that are already in the cube. You can extend the OLAP cube in Microsoft Project Server by extending the fact table so that it contains more information; for example, you may want to include non-project time or costs. You can also want to capture non-project data, such as non-project time or costs that are in the Microsoft Project Server database but may not already be in the cube.
You may also want to grow the information in the cube by adding a new dimension for analyzing the data in a different way; for example, you might want to add a dimension for a fiscal year or a pay period.
You may also want to extend cube security by limiting access to the cube based on the user�s security role. See the section "Adding Security to the OLAP Cube" in this article for more information about cube security.
You may also be familiar with the user interface provided for building Portfolio Analyzer views on the Admin center of Microsoft Project Web Access using Microsoft Office XP Web Components. This article describes how you can use this user interface, but point to a different cube altogether. For example, if you have generated a sales cube in addition to the Portfolio Analyzer view, this article explains how to present reports to other users against the sales cube in Microsoft Project Web Access.
This article assumes you have a familiarity with SQL Server 2000 Enterprise Manager, SQL Server 2000 Analysis Services, OLAP, SQL Server Analysis Manager, DSO, the Microsoft Project Server database, Microsoft Project Web Access administration, Visual Basic 6.0, and some basic scripting.
You can learn more about the Microsoft Project Server database from the Microsoft Project 2002 Resource Kit Database Reference Tools.
In addition, the MSDN library contains more information about Installing SQL Server 2000 Analysis Services.
SQL Server 2000 Analysis Services includes three main components: OLAP, Analysis Manager, and DSO. OLAP is a technology for providing fast access to data warehouse data by creating multidimensional cubes from information in the data warehouse fact and dimension tables. The MSDN library contains more information about Data Warehousing and OLAP. Analysis Manager is the user interface for SQL Server Analysis Server, which is similar to SQL Server Enterprise Manager. DSO is an application programming interface (API) to SQL Server Analysis Server that is used to build and manage cubes. The MSDN library also contains more information about Decision Support Objects.
Portfolio Analyzer Architecture
It is important to understand the key pieces of the Portfolio Analyzer architecture to be able to extend the OLAP cube.
Microsoft Project 2002 supports OLAP through the Portfolio Analyzer feature. Portfolio Analyzer uses components in all three tiers of Microsoft Project: database, middle, and client. The database tier uses SQL Server Analysis Services to create an OLAP cube. The middle tier uses the Portfolio Analyzer cube generation service to create a set of fact and dimension tables (used by SQL Server Analysis Services), based on data in a number of tables in the Microsoft Project Server database. The client tier (Microsoft Project Web Access) uses the Microsoft Office Web PivotTable® and PivotChart® controls bound to the OLAP cube.
For more information, see the Microsoft Project 2002 Enterprise Project Management Architecture Guide.
The architecture of Microsoft Project Server Portfolio Analyzer is illustrated in Figure 1. This is a high-level architecture diagram of how the Office XP Web Controls connect to the Portfolio Analyzer cube built by Microsoft Project Server. In this diagram, the client is Microsoft Internet Explorer, and the server is actually the machine where SQL Server Analysis Services is installed, which may or may not be the same machine where Microsoft Project Server is installed.
Figure 1. Portfolio Analyzer architecture
The first thing you may notice is that the OLAP cube, or the MSP_PORTFOLIO_ANALYZER cube, is actually a virtual cube made up of two cubes: the MSP_ASSN_FACT cube and the MSP_RES_AVAIL_FACT cube. These cubes reside in the cube database specified by the administrator in Microsoft Project Web Access.
The MSP_ASSN_FACT cube contains the bulk of the data. For example, this cube includes assignment information that has been extracted from projects. The fact table of the MSP_ASSN_FACT cube contains timephased assignment data for all enterprise projects. The cube contains the standard time, project, project version, and resource dimensions, along with dimensions for any enterprise project outline codes and enterprise resource outline codes defined in Microsoft Project Server.
The MSP_RES_AVAIL_FACT cube contains resource availability information and calendar information for resources. The fact table of the MSP_RES_AVAIL_FACT cube contains mainly timephased resource availability data for all resources in the enterprise resource pool. The cube contains the standard time and resources dimensions, as well as dimensions for any enterprise resource outline codes defined in Microsoft Project Server.
The two cubes are combined and presented through a virtual cube named MSP_PORTFOLIO_ANALYZER, so that the assignment information can be seen side-by-side with resource availability information by the end users who browse the OLAP cube through the Portfolio Analyzer user interface in Microsoft Project Web Access.
Cube Building Architecture
Microsoft Project Server builds the OLAP cube on the Analysis Server computer, and Office Web Controls bind directly to the OLAP cube. Microsoft Project Server components do not form a middle tier between the Office Web Controls and the OLAP cube. Therefore, any security needs to be applied directly to the OLAP cube that Microsoft Project Server has created.
When a cube is built with Microsoft Project Server, no default security settings are applied. It is recommended that you apply security to the cube; see the Microsoft Project Server Installation Guide for more information. SQL Server Analysis Services allows security to be applied at a very detailed level, and Microsoft Project Server maintains any existing security roles when cubes are updated. For more information about security, see the "Adding Security to the OLAP Cube" section of this article.
Generating the Cube
**You can build the OLAP cube in Microsoft Project Server in one of two ways: first, there is an administrative setting in Microsoft Project Web Access where the administrator can configure the cube settings so that the cube is generated on a regular basis. For example, you can generate the cube weekly or nightly.
Second, you can generate the OLAP cube manually in Microsoft Project Web Access.
To update the OLAP cube manually:
- Click Admin.
- On the Administration overview page, click Manage enterprise features.
- Under Enterprise options, click Update resource tables and OLAP cube.
- Under Update frequency, click Update only when specified, and then click Update Now.
The cube building service is housed in the dynamic link library (DLL) file Projolap.dll. This file resides on the Microsoft Project Server computer, where the processing takes place. There are actually two steps required to generate the OLAP cube. Figure 2 shows the architecture overview in detail.
Figure 2. Overview of OLAP cube architecture
First, Microsoft Project Server creates the data warehouse by creating the MSP_CUBE staging tables. When the cube building process is started, data is gathered from the Microsoft Project Server database and put into a designated set of tables referred to as the data warehouse. These tables all have the prefix MSP_CUBE in the Microsoft Project Server database, and they serve as a set of staging tables for building the OLAP cube. The schema of the staging tables has been optimized so that an OLAP cube can easily be generated. Figure 3 shows the first step in the cube building process.
Figure 3. Cube building 1: creating the data warehouse
After the staging tables have been built, the cube building service (Projolap.dll) starts the second step, which generates the cube from the staging tables of the data warehouse. When the cube generation process is complete, the two cubes and the virtual cube in the Analysis Server database specified by the user in the Microsoft Project Web Access Admin center will have been built. Figure 4 shows the second step in the cube building process.
Figure 4. Cube building 2: generating the virtual cube
Extensibility Model for the OLAP Cube
Microsoft Project Server provides a simple extensibility model that supports two breakout points during the process of cube generation where you can insert your own custom code. Figure 5 shows the location of the two breakout points in the cube building process.
Figure 5. Extending the cube with breakout functions
The first breakout point occurs after you have created the staging tables. Using the code in this article, you can use the function UserStagingTablesUpdate to add data to the staging tables.
The second breakout point occurs after Microsoft Project Server has generated the cube, which is where you can use the UserOLAPUpdate function. At this point, you can add security roles to the cube or programmatically process extensions performed at the first breakout point, and then process the cube again.
Extending the Cube by Adding a New Pay Period Dimension
This section describes the cube extension process in three steps:
- Extending the staging tables by adding a new pay period dimension
- Updating the OLAP cube with the new data
- Adding the new dimension to the virtual cube
Before you start, you may want to familiarize yourself with the structure of the Microsoft Project Server database. In SQL Server Enterprise Manager, open the Microsoft Project Server database. The staging tables have the MSP_CUBE prefix. The fact tables are named MSP_CUBE_ASSN_FACT and MSP_CUBE_RES_AVAIL_FACT. All other MSP_CUBE tables are the dimension tables. For more information about the Microsoft Project Server database, see the file PjSvrDB.htm in the \Docs\1033 folder on the Microsoft Project Server installation CD, or in the C:\Program Files\Microsoft Project Server\1033 folder (1033 is the locale ID [LCID] for U.S. English; the folder for localized versions will vary. For example, the LCID for Japanese is 1041).
Step 1: Extending the Staging Tables
The following example adds a new pay period dimension to your OLAP cube. To add a new dimension, you must add new tables to the staging tables. First, you add two new tables to the Microsoft Project Server database. Then, using the Visual Basic code available from the Microsoft Download Center, you can run the first breakout function, UserStagingTablesUpdate, to populate the tables with information from the database.
To create the new database tables:
- In SQL Server Enterprise Manager, open the Microsoft Project Server database.
- Click Tables to view the available tables.
- Create a new table named PayPeriodName with two fields: PP_ID and PP_NAME.
Data Type = int
Data Type = nvarchar
Length = 4
Length = 255
Allow Nulls = checked
Allow Nulls = checked
- Create a new table named PayPeriodLink with two columns: TIME_ID and PP_ID.
Data Type = int
Data Type = int
Length = 4
Length = 4
Allow Nulls = checked
Allow Nulls = checked
A sample application has been included that uses the breakout template to populate the new dimension table for a pay period. After you download ExtendOLAP.exe, open the Visual Basic project OLAPDemoGroup.vbg. The project MSPOLAPBREAKOUT contains the UserStagingTablesUpdate function. To see the application work, there is a sample OLAPBreakoutTest form to help you call and test the breakout functions to make sure they work properly.
You are now ready to use Visual Basic 6.0 to run the first breakout function located in the download for this article. Keep SQL Server Enterprise Manager open while performing the next procedure.
To populate the new tables with data:
- In Visual Basic 6.0, open the Visual Basic project OLAPDemoGroup.vbg, which is part of the ExtendOLAP.exe download.
Alternatively, double-click OLAPDemoGroup.vbg
to open the project in Visual Basic 6.0.
- Right-click the UserOptionalCode Class Module under MSPOLAPBREAKOUT, and click View Code.
- Place a breakpoint in the UserStagingTablesUpdate function.
- To insert a breakpoint, highlight all four lines of the
- function and press F9.
- On the Run menu, click Start.
- In the DB Connection String field of the OLAP BreakoutTest dialog box, change the Data Source, Initial Catalog, UID, and PWD parameters to the corresponding values for your Microsoft Project Server database.
- In the OLAP Server field of the OLAP Breakout Test dialog box, enter the OLAP Server name.
- In the OLAP DB field of the OLAP Breakout Test dialog box, enter the OLAP database name.
- Click Initialize 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 Microsoft Project Server database. The code should stop running at the breakpoint you inserted in the
- function. You are now finished with the first step.
Step 2: Updating the OLAP Cube
Now that you�ve extended the staging tables and created the database connection, you�re ready to add the pay period dimension to your OLAP cube. This article shows you how to update the OLAP cube manually; however, you can also update the OLAP cube programmatically. Once you are familiar with the steps involved, you can use DSO to update and regenerate the virtual cube programmatically.
If you are creating a new dimension, it will not be overwritten when the OLAP cube is rebuilt using this procedure. However, if you are modifying an existing dimension, you will have to do this programmatically by using the UserOLAPUpdate function in the download. Otherwise, the dimension will be overwritten each time.
The following steps describe the update process using the SQL Server Analysis Manager user interface. To open SQL Server Analysis Manager, click Start, point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Analysis Manager.
To add the new tables to the virtual cube:
- In SQL Server Analysis Manager, open your Analysis Server, which is listed under the Analysis Servers tree.
- Click your server name, click
- PS Extended Cube
- , and then click
- to expand the tree that contains the two Fact cubes (MSP_ASSN_FACT and MSP_RES_AVAIL_FACT) and the virtual cube (MSP_PORTFOLIO_ANALYZER).
- Right-click the MSP_ASSN_FACT cube, and click Edit.
- Right-click Dimensions, and click New Dimension to start the Dimension Wizard.
- In the Dimension Wizard, click Next, select Snowflake Schema: Multiple, related dimension tables, and click Next.
- In the Available tables pane, scroll down and double-click both the PayPeriodLink table and the PayPeriodName table to add them to the Selected tables pane, and then click Next.
- Verify that the PP_ID fields in both tables are joined, and click Next.
- Double-click PP_NAME to move the PP_NAME column from the Available columns pane to the Dimension levels pane, and click Next.
- Click Next on the Specify the member key columns page, click Next again without selecting any advanced options, and then type a Dimension name (for example, Pay Period) to allow the wizard to finish.
Now that you�ve created the new Pay Period dimension, you�ll need to link it to the fact table. You can recognize the MSP_CUBE_ASSN_FACT table by its yellow color in the user interface, and the Dimension tables dbo.PayPeriodLink and dbo.PayPeriodName are blue. If you do not see these three tables when you click Dimensions, scroll down and drag the tables to the top of the right pane.
To link the Pay Period dimension to the MSP_CUBE_ASSN_FACT table and process the cube:
- Click Dimensions under the MSP_ASSN_FACT table in the left pane to display the fact tables and dimension tables in the right pane.
- Click the TIME_ID column in the MSP_CUBE_ASSN_FACT table and drag it to the TIME_ID column in the dbo.PayPeriodLink table to create the link.
- Click Save.
- Click OK in the message box that asks whether you want to continue.
- Right-click MSP_ASSN_FACT, and click Process Cube.
- Click Yes when prompted to save.
- If the Storage Design Wizard does not start, on the
- menu, click
- Design Storage
- Click Next to begin, click Next to replace the existing aggregations, click Next to select the multidimensional OLAP (MOLAP) type of data storage, and then click Start.
- Once all the aggregations have been designed, click Next.
- Click Finish to create the cube.
Step 3: Adding the New Dimension to the Virtual Cube
The final process is to add the new dimension to your virtual cube. This procedure is also done in SQL Server Analysis Manager.
To add the new dimension to the virtual cube and process the cube:
- In SQL Server Analysis Manager, right-click MSP_PORTFOLIO_ANALYZER, and click Edit.
- Right-click MSP_PORTFOLIO_ANALYZER, and click Edit Structure to start the Edit Structure Wizard.
- Click Next, click Next again, and on the Available dimensions page of the wizard, double-click Pay Period to add the dimension to your list of selected dimensions in the right-hand pane.
- Click Next to create the cube.
Extending the Cube Building Process
The Visual Basic 6.0 project OLAPDemoGroup.vbg has a class that exposes two public functions: UserStagingTablesUpdate and UserOLAPUpdate. The following procedure allows you to add custom code to the two breakout functions, and then regenerate the dynamic link library (DLL).
To regenerate a customized Msolapbreakout.dll:
- Open the Visual Basic 6.0 template project named Msolapbreakout.vbp in the C:\OLAP Breakout folder.
- Insert code into each of these functions as appropriate.
- Compile the DLL and name it Msolapbreakout.dll.
- Register this DLL on the Microsoft Project Server computer.
Another way you may want to extend the OLAP cube is by including task data in the OLAP cube. To view task data in the cube in a work breakdown structure (WBS), you will need to insert task codes into assignments, since task enterprise codes are not supported in the OLAP cube. You can do this by creating a task enterprise code named WBS with a structure that is representative of your WBS. You can then create a resource outline code that shares the lookup data with the resource data. Finally, you can create and run a VBA macro that copies task codes into the assignments as you save a file. The task WBS will appear through the resource outline code in the OLAP cube.
Adding Security to the OLAP Cube
You can use SQL Server Analysis Manager to add security features to the OLAP cube. You can do this programmatically by using DSO. Use the following procedure to manage security roles by modifying the database roles.
To add security roles by dimension:
- In SQL Server Analysis Manager, open the Analysis Server, which is listed under the Analysis Servers tree.
- Click the server name, click the name of the cube database, right-click
- Database Roles
- , and then click
- Manage Roles
- Click New to create a new database role.
- Type a name for the role in the Role name field, and select Client in the Enforce on field.
- Click the Cubes tab, and select the cube name where you wish to define the database role.
- Click the Dimensions tab, select the Custom option in the Rule column, and define the custom settings in the Custom Settings column.
- For example, you can restrict access to certain projects or certain locales, based on roles, by selecting or clearing items in the
- pane of the
- Custom Dimension Security
- dialog box.
- When you finish creating the new database role, click OK, and then click Save to save the roles with the cube.
Troubleshooting OLAP Cube Generation
There are three common issues with OLAP cube generation. For more information, see the Microsoft Project Server Installation Guide, Pjsvr10.chm, or the Microsoft Project Server 2002 Support Center.
- Ensure that the service packs you�re using for both SQL Server and SQL Server Analysis Server are the same versions. It is recommended that you use Service Pack 2. There are separate service packs for SQL Server and Analysis Server; they must be in alignment, and they must be installed separately. The latest service packs are available from the SQL Server Support Center.
- Ensure that you migrate the OLAP repository to SQL Server. For more information, see the Microsoft Project Server Installation Guide, Pjsvr10.chm, in the \Docs\1033 folder on the Microsoft Project Server installation CD, or in the C:\Program Files\Microsoft Project Server\1033 folder (1033 is the locale ID [LCID] for U.S. English; the folder for localized versions will vary. For example, the LCID for Japanese is 1041).
- Ensure that the account you�re running in Microsoft Project Server OLAP cube components Component Object Model Plus (COM+) Applications is a member of the OLAP Administrators group. This is necessary because the user account that grants permissions to build the cube must be an OLAP administrator. In other words, to run the DLL on the OLAP analysis server, you need to be an OLAP administrator.