Extending the Cube

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.

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]

[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]

[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.