Extending the Cube

It is essential for any business to have some sort of insight into the health of its organization, including the health of its projects. Microsoft provides a tool capable of performing complex analysis of the data stored in a Project Server database. This analysis is supplied in a reporting service called the Portfolio Analyzer online analytical processing (OLAP) cube.

OLAP Cube Basics

The OLAP cube is a logical construct that brings together multiple dimensions of data and measures of that data that are organized to be displayed by the reporting service. This data is based on a fact table that resides in the project database itself.

Dimensions are used to summarize and display the data in the fact tables in a hierarchical manner, such as the work performed in given time periods. Each dimension can be visualized as one side of a three-dimensional cube (hence the name). However, a cube construct could contain just one dimension or many more than three.

The Data Warehouse

The data warehouse is a relational database that stores the underlying data used to build the cube. This is where all the facts, dimensions, and verified data values are stored for quick access.

The Purpose of Measures

Measures are numerical summarizations that use some given formula for the cube to display data within and across dimensions. Measures can be simple numerical values but also provide the capability to have calculated fields similar to formulated values in Excel. A simple example of a measure would be the count of all products sold in a given month or their equivalent dollar amount.

NOTE

Although most measures are simple summations, complex formulas can be used as calculated fields as well.

Overview of Fact Tables

The fact table is an independent table stored in the relational data warehouse that contains all the values used in the cube. The fact table contains at least one column as a key and one column as a value used for measures, or a fact.

A simple example would be a resource's name and a month as key columns and the amount of work that resource performed that month as a fact.

The fact table must contain rows that represent the lowest level of detail you would use for a measure. In other words, you would not use a fact table row to store aggregates of values.

Structure of Dimension Tables

The fact tables contain the data at the lowest level of detail, such as the amount of work performed by each resource. The dimension tables are used to store the hierarchical summaries of this data. This may be the work a particular resource performed in each month. One row in the dimension table corresponds to one leaf member for the dimension.

Dimension tables must contain at least one primary key column used to reference key columns in fact tables, such as resource unique identifiers. Although this primary key value must be unique in the dimension table, it may reference multiple rows in the corresponding fact table.

The dimension table also may have columns that represent parent members. In the resource table example given in the preceding section of this chapter, a parent member may be a department.

NOTE

Multiple types of dimensions can be in a cube, such as key, time, and shared dimensions. References to the various types of dimensions can be found in the online documentation.

Description of the Analysis Server

At the core of Analysis Services is the analysis server. This server is the link between the reports you see in PWA and the data warehouse in the database. It is the entity responsible for extracting information from the data warehouse and constructing the cube itself.

The Portfolio Analyzer to Display Cube Data

The Portfolio Analyzer in PWA uses components in all three tiers of the Project Server application:

• Database tier SQL Analysis Services uses data in a relational database to generate the cube.

• Middle tier Fact and dimension tables are created based on data in other tables in the Project Server database (or beyond).

• Client tier PivotTables and charts are used to display the cube data to the user in PWA.

The PivotTables and charts in PWA connect to the OLAP database through OLE, which then connects to Analysis Services on the server. The Analysis Services fetches the information from the database for the PivotTables and charts to display.

The Structure of the Default Portfolio Analyzer Cube

The out-of-the-box cube built with Project Server is labeled MSP_PORTFOLIO_ANALYZER. This cube is actually a virtual cube that contains two cubes: MSP_ASSN_FACT and MSP_RES_AVAIL_FACT. All the fact and dimension tables for these cubes reside in the same database read by Project Server.

TIP

The default cube fact and dimension tables in the Project Server database is a good, although complex reference for how information is stored in the data warehouse.

The two cubes are combined under one virtual cube so that the reports can more easily reference data from both cubes to display information side by side.

The MSP_ASSN_FACT cube contains assignment and timephased data for all enterprise projects. This cube contains data such as standard time, project versions, and resource status. It also contains dimensions for resources as well as enterprise project and resource outline codes.

NOTE

Generally, multivalued outline codes as a dimension show up incorrectly as No Value on the final report. This is due to the inability of the web control to pick what category to place the data under.

The MSP_RES_AVAIL_FACT cube contains all resource availability and calendar information for all resources. This includes the standard time and resource dimensions, as well as dimensions for defined resource outline codes.

Cube Extension Build Process

The cube building process is generally kicked off in two wayseither on a scheduled basis or manually. Both are done in the Admin section of PWA under the Manage Enterprise Features tab.

When the cube build is kicked off, its first step is to create all staging tables (the data warehouse) referenced by the cube. Its second step is to build the actual cube structure, including all dimensions and measures. Project Server provides a method to insert functions to perform custom actions after each of these two steps, as shown in Figure 29.1. This is done by creating a custom library named MSPOLAPBREAKOUT.dll.

Figure 29.1. The MSPOLAPBREAKOUT object can be called to perform custom actions in the cube build process.

PWA then links the virtual cube to the staging tables, building all dimensions and measures referenced by the cube.

Overview of the Cube Build Breakout Object

When PWA builds a cube, it also checks for a registered dynamic link library (DLL) called MSPOLAPBREAKOUT. When this library is exposed over the component object model (COM), Project Server calls two methods involved in building the cube. One method sets up the staging tables used by the cube; the other sets up the cube itself. Refer to Figure 29.1 for an idea of where in the build process these methods are called.

Microsoft provides a Solution Starter that contains example cube extension code and the cube extension SDK. This Solution Starter is available from the Microsoft download center under the name "Microsoft Office Project Server 2003: Portfolio Analyzer OLAP Extensions."

NOTE

Although the Solution Starter that Microsoft provides is written in Visual Basic 6.0, the extension can be written in any language that can produce an ActiveX COM DLL, such as the .NET Framework. Make sure that the ActiveX class is labeled UserOptionalCode and that the proper methods are exposed.

To tell Project Server to break out to the MSPOLAPBREAKOUT code, you must register the DLL on the same computer. You can easily register a VB 6.0 created library by executing the following (specifying the full path of the DLL):

 Regsvr32.exe .\MSPOLAPBREAKOUT.DLL 

This can also be done in .NET by using the RegAsm.exe utility, provided by the framework.

Extending Staging Tables Using the Breakout

The UserStagingTablesUpdate method of the breakout COM allows you to execute custom code for filling staging tables during a cube build. This provides a foundation for your cube extension because this is where the data warehouse the cube will reference is populated. Note that the staging tables should be built separately from this process, such as through SQL calls or through Enterprise Manager.

[View full width]
 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 

Two input parameters are passed: the connection to the data warehouse and the database type. Although an ADO object is passed, you should open a new connection to the database by using the connection string property of the m_dbConnection object. The database type can be ignored because it will always be a SQL server.

Two output parameters are given: an error number and error description. These provide essential bits of the information for debugging the cube extension build.

The functions should return a nonzero value on an error, which halts the rest of the cube build process.

Populate the staging tables using any SQL database connection library of your choice. The ADODB library is a logical choice because it is already used as one of the input parameters. See the cube extension Solution Starter for examples and the SDK for more information.

TIP

You are not limited to the Project Server tables for sources of information to be included in the cube. A common data source for cube information is WSS. Microsoft's Solution Starter kit provides an example of a risk cube and how to retrieve that information using WSS SOAP calls.

Building the Cube Structure with the Breakout Object

The UserOLAPUpdate method of the breakout object allows you to build your cube extension that relies on previously populated staging tables. This is where you can use Microsoft Decision Support Objects (DSO) to create objects such as measures, dimensions, and cubes themselves.

[View full width]
 Public Function UserOLAPUpdate(ByVal sOLAPServerName As String, ByVal sOLAPDatabaseName As  String, ByVal o_dbConnection As ADODB.Connection, ByVal l_DBType As Long, ByRef l_errnum  As Long, ByRef s_errdesc As String) As Long 

The UserOLAPUpdate function has two additional input parameters than the staging tables update function, a server name and a database name. The server name is the name of the OLAP server machine, and the database name is the OLAP database name on that server (these values are both set in PWA's Admin page).

At the end of this function you kick off the actual cube build process for the custom cubes you've set up. The process is a command that is part of an MDStore object (the DSO object that represents a cube):

 dsoCube.Process 

CAUTION

In Microsoft's Solution Starter, the custom cube is always deleted before being built in the UserOLAPUpdate function. This should be avoided because doing so erases any custom security settings or any changes such as calculated fields.

MSDN provides a comprehensive reference for all the DSO objects. This reference combined with the Solution Starter should provide a good guide for building your custom cube. It may also help to first build the cube in SQL Analysis Manager to provide a plan before coding.

Debugging the Cube Extension

Debugging the cube extension build can be tedious. Elegant error handling within the breakout class itself is important to offer the debugger some sort of insight as to what is happening. Verbose event message reporting is recommended.

The Solution Starter kit provided by Microsoft includes a handy debugging application (along with the code) that you can use to test your custom MSPOLAPBREAKOUT library, as shown in Figure 29.2. You provide a connection string, the OLAP server name, and the OLAP cube name, and it runs the two exposed functions on the registered MSPOLAPBREAKOUT library.

Figure 29.2. The Solution Starter kit provides a handy application to debug your custom cube extension.

The following list contains some common things to check while debugging a cube building problem:

• Make sure that the service packs for SQL Server and Analysis Server are the same versions. These must be applied separately from one another.

• Make sure that the OLAP repository was migrated to SQL Server (as opposed to an Access database) according to the Microsoft Project Server Installation Guide.

• Make sure that the account running in OLAP COM+ applications is an OLAP administrator.

• Make sure that your Project Server service account has access to the OLAP server and the particular cube, as well as the user viewing the data from PWA.

• Check that the cube name and OLAP server settings are correct in PWA's OLAP admin page, as well as the settings for a particular Portfolio Analyzer view.

• Use the cube extension test program to verify that your breakout functions are working properly.