Step 3: Adding Issues and Risks with UserOLAPCubeUpdate

Article
09/12/2006

In Step 2, you used the UserStagingTablesUpdate breakout function to copy the task issues and risks data from Microsoft® Windows® SharePoint™ Server to Microsoft® Office Project Server 2003. In Step 3, the UserOLAPCubeUpdate breakout function copies data from the MSP_CUBE_WSS_RISKS**** and MSP_CUBE_WSS_ISSUES**** OLAP staging tables and programmatically creates the MSP_ISSUES and MSP_RISKS cubes in Analysis Server for the Portfolio Analyzer views. UserOLAPCubeUpdate communicates with Analysis Services using the Decision Support Objects (DSO) COM component.

You will use the same OLAP Breakout Test sample application to test UserOLAPCubeUpdate that you used in Step 2. You have already entered the Project Server database connection string, tested the connection, and updated the staging tables in the OLAP Breakout Test form.

To add issues and risks data and build the cube:

The following steps assume you have already built the default Portfolio Analyzer cube using the Project Server sample database and named it MSP_Sample_Cube. Substitute your own server, database, and cube names where appropriate.

In the SQL Server Analysis Manager, expand the tree under Analysis Servers in the left pane, and then click the server name where Portfolio Analyzer stores the OLAP cube. Note the server name and the top-level cube database name for the Portfolio Analyzer cube. Expand the Cubes node and you will see the MSP_PORTFOLIO_ANALYZER virtual cube, and the MSP_ASSN_FACT and MSP_RES_AVAIL_FACT cubes.
In the TaskIssuesRisks directory of the installed Portfolio Analysis OLAP Extensions Solution Starter, double-click the file OLAPDemoGroup.vbg to open the project group in Visual Basic.
Click the OLAPBreakoutTest project in the Visual Basic Project Explorer pane and, on the Project menu, click References. Make sure that Microsoft Decision Support Objects is checked to provide access to the DSO object library for programming SQL Server Analysis Services. The file location is normally [Project Files]\Common Files\Microsoft Shared\DSO\msmdddo80.dll.
In the Project Explorer pane, expand the project trees and double-click frmOLAPTest under the OLAPBreakoutTest project.
Click the txtOLAPServerTextBox control and open the Properties Window. Click the Text property and type in the server name where Analysis Services is installed.
Click the txtOLAPDBTextBox control, click the Text property, and then type in the OLAP database name of the cube. The cube name is the top-level cube as shown in Analysis Manager, for example, MSP_Sample_Cube. Click Save Project Group on the toolbar.
In the Run menu, click Start (or press F5).
In the OLAP Breakout Test dialog box, click Cube Building. In the Immediate window you should see the debug print output as follows:
```
Building Cube
Process cube complete
```
An OLAPBreakoutTest message box then shows Successfully completed OLAP Breakout function 2.
In Analysis Manager click the Refresh button on the toolbar, and you should see MSP_ISSUES and MSP_RISKS in the Cubes folder.

If you see error messages or the risks and issues cubes aren't built, see the topic Troubleshooting OLAP Cube Generation.

Functions for Building the Cube

The key functions for building the issues and risks cubes are the following:

UserOLAPUpdate is the public breakout function that starts the process of building the task issues and risks cubes.
BuildCube is a private function that does most of the work to build the cubes.

UserOLAPUpdate

When you click Cube Building in the OLAP Breakout Test dialog box, the button's click event handler (Command4_Click) instantiates a UserOptionalCode object and calls UserOLAPUpdate with the OLAP server name, OLAP database name, and staging table connection string that you entered in the dialog's text boxes, along with the other required parameters (the 0 parameter specifies the type of database is SQL Server, and the lerr and serr parameters return the error code and error string). Following is the main test code in the button's click event handler:

Dim oBreakout As Object
    Dim z As Long
    Dim lerr As Long
    Dim serr As String
    
    Set oBreakout = CreateObject("MSPOLAPBREAKOUT.UserOptionalCode")
    
    If (Not oBreakout Is Nothing) Then
        z = oBreakout.UserOLAPUpdate(txtOLAPServer, txtOLAPDB, m_dbextract, 0, lerr, serr)
    End If
    
    If serr <> "" Then
        MsgBox "Error! Unable to complete OLAP Breakout 2", vbCritical
        Exit Sub
    Else
        MsgBox "Successfully completed OLAP Breakout function 2", vbInformation + vbOKOnly
    End If
    . . .

The UserOLAPUpdate breakout function essentially does the following jobs:

Use the DSO server object to connect with Analysis Server and create a DSO database object.
Call DeleteCube to delete the MSP_RISKS and MSP_ISSUES cubes if they exist. DeleteCube essentially has one line:
```
    dsoDB.MDStores.Remove (sCubeName)
```
However, DeleteCube is made into a separate function to allow more detailed error tracing.

An alternate approach could be to validate the cubes if they exist and delete them only if they are invalid; the implementation does not use that approach.
Call BuildCube to create new risks and issues cubes.

Note The following code does not show all of the trace or error handling statements included in the code download.

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
                               
   Dim dsoServer As New DSO.Server
   Dim dsoDB As DSO.MDStore
   Dim dsoCube As DSO.MDStore
   Dim dsoIssueCube As DSO.MDStore
   
   dsoServer.Connect sOLAPServerName
   Set dsoDB = dsoServer.MDStores(sOLAPDatabaseName)
   
   If (dsoDB Is Nothing) Then
      Err.Raise 1
   End If
   
   Set dsoCube = dsoDB.MDStores("MSP_RISKS")
   Set dsoIssueCube = dsoDB.MDStores("MSP_ISSUES")

   If (Not dsoIssueCube Is Nothing) Then
       Call DeleteCube(dsoDB, "MSP_ISSUES")
   End If
   
   If (Not dsoCube Is Nothing) Then
       Call DeleteCube(dsoDB, "MSP_RISKS")
   End If
   
   Debug.Print "Building Cube"

   If (BuildCube(dsoDB, o_dbConnection)) Then
       UserOLAPUpdate = 0
       Trace ("Exit UserOLAPUpdate OK.")
   Else
       UserOLAPUpdate = 1
       Trace ("Exit UserOLAPUpdate with error.")
   End If
   . . .
End Function

BuildCube

The BuildCube function requires at least one issue and one risk to build the cubes.

Private Function BuildCube(ByRef dsoDB As DSO.MDStore, ByRef dbConnection As ADODB.Connection) As Boolean

   On Error GoTo CaptureError
   
   Dim dsoCube As DSO.MDStore
   Dim dsoIssueCube As DSO.MDStore
   Dim issueTablename As String
   Dim riskTablename As String
   Dim oRs As Recordset
   Dim oCmd As ADODB.Command
   Dim sSQLCommand As String
   
   BuildCube = False  'Assume failure 
   issueTablename = "MSP_CUBE_WSS_ISSUES"
   riskTablename = "MSP_CUBE_WSS_RISKS"
   
   sSQLCommand = "select TOP 1 * from dbo." & issueTablename
   Set oCmd = New ADODB.Command
   oCmd.ActiveConnection = dbConnection
   oCmd.CommandText = sSQLCommand
   oCmd.CommandType = adCmdText
   Set oRs = oCmd.Execute
    
   ' Check if issue source tables are empty, then exit.
   If oRs.EOF Then
       Error ("Issue table is empty, unable to build cube.")
       GoTo CaptureError
   End If
       
   sSQLCommand = "select TOP 1 * from dbo." & riskTablename
   oCmd.CommandText = sSQLCommand
   Set oRs = oCmd.Execute
    
   ' Check if risk source tables are empty, then exit.
   If oRs.EOF Then
       Error ("Risk table is empty, unable to build cube.")
       GoTo CaptureError
   End If
       
   ' Add the new cubes to the database
   Set dsoCube = dsoDB.MDStores.AddNew("MSP_RISKS")
   Set dsoIssueCube = dsoDB.MDStores.AddNew("MSP_ISSUES")

In BuildCube you then add the data source for both cubes, which is the same as the OLAP database source.

dsoCube.DataSources.AddNew (dsoDB.DataSources(1).Name)
   dsoIssueCube.DataSources.AddNew (dsoDB.DataSources(1).Name)

Set the Fact tables for the cubes to the staging tables in Project Server (MSP_CUBE_WSS_RISKS and MSP_CUBE_WSS_ISSUES) and set the estimated rows to a number greater than you expect.

dsoCube.SourceTable = Chr$(34) & riskTablename & Chr$(34)
   dsoIssueCube.SourceTable = Chr$(34) & issueTablename & Chr$(34)
   dsoCube.EstimatedRows = 10000
   dsoIssueCube.EstimatedRows = 10000

Now add the shared dimensions, status dimensions, measures, and calculated members that you want in the OLAP cube. The code download uses separate functions for each of these jobs to centralize repeated actions and provide specific error tracing. See the download for the implementation of the functions AddSharedDimension, AddStatusDimension, AddTaskMeasure, and CreateAverageCalculatedMember.

Join statements are appended to the strings strJoin and strIssueJoin, which are passed by reference to the functions AddSharedDimension and AddStatusDimension. The constants aggSum and aggCount are members of the AggregateTypes enumeration in the DSO library. For example:

Dim strJoin As String   'Join string for the Risks fact table and dimensions
   Dim strIssueJoin As String   'Join string for the Issues fact table and dimensions
   
   strJoin = ""
   strIssueJoin = ""
   
   Call AddSharedDimension(dsoDB, dsoCube, strJoin, "Project List", _
             "(""MSP_CUBE_WSS_RISKS"".""Proj_ID""=.""MSP_CUBE_PROJECTS"".""PROJ_UID"" )")
    . . .
   Call AddStatusDimension(dsoDB, dsoCube, strJoin, 1, "Status", riskTablename)
    . . .
   Call AddTaskMeasure(dsoIssueCube, "ows_StatusActive", "Active Status", issueTablename, aggSum)
    . . .
   Call AddTaskMeasure(dsoCube, "ows_Probability", "Probability Count", riskTablename, aggCount)
    . . .
   CreateAverageCalculatedMember dsoCube, "Probability"
    . . .

Note See the Glossary for definitions of the OLAP meaning of the terms dimension, shared dimension, measure, and member. Extensive information on programming with DSO is available in SQL Server Books Online.

Finally, you join the fact tables and dimensions, and then update and process the cubes. The trace statements and sections that do cleanup and set BuildCube to False in an error condition are not shown here.

dsoCube.JoinClause = strJoin
   dsoIssueCube.JoinClause = strIssueJoin
      
   dsoCube.Update
   dsoIssueCube.Update
      
   dsoCube.Process
   dsoIssueCube.Process
   
   Debug.Print "Process cube complete"

   BuildCube = True
    . . .
End Function

You are now finished with Step 3, creating and testing the UserOLAPCubeUpdate function for adding risk and issue data to the cube. Next is Step 4: Building the Cube for Task Issues and Risks.

Share via

Step 3: Adding Issues and Risks with UserOLAPCubeUpdate

Functions for Building the Cube

UserOLAPUpdate

BuildCube

Additional resources