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
). However, a cube construct could contain just one dimension or many more than three.
Although most measures are simple summations, complex formulas can be used as calculated fields as well.
Overview of Fact Tables
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
and the amount of work that resource performed that month as a fact.
The fact table must contain rows that represent the
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
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
. In the resource table example given in the
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
of Analysis Services is the analysis server. This server is the link between the
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
in all three tiers of the Project Server application:
SQL Analysis Services uses data in a relational database to generate the cube.
Fact and dimension tables are created based on data in other tables in the Project Server database (or beyond).
are used to display the cube data to the
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
. This cube is actually a virtual cube that contains two cubes:
. 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.
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.
, 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.
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
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
When PWA builds a cube, it also checks for a registered dynamic link library (DLL) called
. 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
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
and that the proper methods are exposed.
To tell Project Server to break out to the
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
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
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
. 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
a new connection to the database by using the connection string property of the
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
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
[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
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
object (the DSO object that represents a cube):
In Microsoft's Solution Starter, the custom cube is always deleted before being built in the
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
. 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
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
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
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
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.