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.
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.
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:
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.
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.
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."
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):
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.
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.
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.
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):
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: