Share via

Step 2: Updating the Data with UserStagingTableUpdate

  • Article

In Step 1, you extended the OLAP cube staging tables to accommodate task issues and risks by using the WSSCubeSetup batch file to create the MSP_CUBE_WSS_RISKS and MSP_CUBE_WSS_ISSUES tables. Step 2 is to populate the new tables with the issues and risks data from Windows SharePoint Server.

The solution starter download includes a sample application that is based on the breakout function template to populate the new dimension tables. See Solution Starter Files for the complete list of files and installation locations for the task issues and risks projects.

The Visual Basic project group OLAPDemoGroup.vbg in the solution starter TaskIssuesRisks directory includes the modified UserStagingTablesUpdate breakout function. Both of the breakout functions are contained in the UserOptionalCode class of the MSPOLAPBREAKOUT project. The sample application OLAPBreakoutTest allows you to call and test UserStagingTablesUpdate and the other breakout function, UserOLAPCubeUpdate (described in Step 3).

To populate the issues and risks staging tables with data:

Note  Each of the three solution starter samples for extending the OLAP cube contain Visual Basic files with the same name but with different code. Be sure you use the files in the TaskIssuesRisks directory for this sample.

  1. Double-click the file OLAPDemoGroup.vbg to open the project group in Visual Basic.

  2. Click the OLAPBreakoutTest project in the Visual Basic Project Explorer pane and, on the Project menu, click References. Make sure the Microsoft ActiveX Data Objects 2.5 Library (or higher) is checked to provide the ADODB objects. The file location is normally [Project Files]\Common Files\system\ado\msado25.tlb.

  3. In the Project Explorer pane, expand the project trees and double-click frmOLAPTest under the OLAPBreakoutTest project.

  4. In the OLAP Breakout Test form object, click the dbConStrTextBox control and open the Properties Window.

  5. Click the Text property of the dbConStrTextBox, and then type in the correct connection string for your server and database names. The following example uses the Microsoft® Office Project Server 2003 sample database that was given the name MPSSampleDatabase during setup of Project Server and the server user name MSProjectServerSampleDBUser.

    Important  You should not store the password for SQL Server in the code. You can type in the connection string password each time you run the test application.

    Provider=sqloledb;Data Source=ServerName;Initial Catalog=MPSSampleDatabase;
   UID=MSProjectServerSampleDBUser;PWD=

    Click Save Project Group on the toolbar.

  6. On the Run menu, click Start (or press F5).

  7. In the DB Server field of the OLAP Breakout Test dialog box, edit the connection string to add your user password for the database.

  8. Click Initialize Connection. You should see the message Connected Successfully to DB.

    If you see the message Error! Unable to initialize, check every parameter of the connection string. The provider must be sqloledb, which is the native OLE DB Provider for SQL Server; it is not an ODBC connection. Check in SQL Server Enterprise Manager for the correct database name, as well as for the correct user name and database access in the Logins node of the Security folder for the correct SQL Server instance. Check also that the user has at least permission to create and modify the database. In the Security folder, double-click the user name in Logins. In the SQL Server Login Properties dialog box, click the Server Roles tab, and then check Database Creators.

  9. When you have a successful connection, click Staging Table Update. You will see the progress in the Immediate Window pane. Following are the results for only two issues entered in one project:

    Found project ID: 132
Retrieving list of Issues
Found project ID: 132
Retrieving list of Issues
Found project ID: 132
Retrieving list of risks
Found project ID: 132
Retrieving list of risks
Populate Fact Tables
Complete Populating Fact Tables

    Note  You must have entered some risks and issues in at least one project in Project Web Access for the database you are using. Otherwise, there is no risks and issues data in Windows SharePoint Server to copy.

Functions for Updating the Staging Tables

After the connection string is established, the key functions for updating the issues and risks staging tables are the following:

  • UserStagingTablesUpdate is the public breakout function that is modified for the task issues and risks solution (and for improvements in tracing and error handling compared to the function that is used in the Pay Period Dimension solution).
  • PrepareWSSSoapCall is a private function that manages most of the work to get the data from SharePoint Server.
  • MakeSoapCall actually gets the risk and issue data from SharePoint Server.
  • PopulateRiskTable and PopulateIssueTable do what their names imply: put the risk and issue data into the staging tables.

UserStagingTablesUpdate

The UserStagingTablesUpdate breakout function simply calls the private function PrepareWSSSoapCall.

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
'[TRY]
    On Error GoTo CaptureError
'/==================== BODY ========================    
    Trace ("Called UserStagingTablesUpdate")
    
    UserStagingTablesUpdate = 0
    
    If (PrepareWSSSoapCall(o_dbConnection)) Then
        UserStagingTablesUpdate = 0
        Trace ("Exit UserStagingTablesUpdate OK.")
    Else
        UserStagingTablesUpdate = 1
        Trace ("Exit UserStagingTablesUpdate with error.")
    End If
    
'\==================================================/
    GoTo CleanUp
'[CATCH]
CaptureError:
    ErrorHandler_Catch "UserOptionalCode.cls", "UserStagingTablesUpdate"
    l_errnum = Err.Number
    s_errdesc = Err.Description
    UserStagingTablesUpdate = 1     ' although it could be any non-zero value
                                   ' to indicate an error
'[FINALLY]
CleanUp:
    ErrorHandler_Finally False
End Function

PrepareWSSSoapCall

The PrepareWSSSoapCall function has the following definition, and does several jobs.

Private Function PrepareWSSSoapCall(ByRef dbConnection As ADODB.Connection) As Boolean

  • Get the SharePoint Server name and port number from the Project Server database and a list of all projects that have risks and issues.

    sSQLCommand = "  SELECT " & _
                  "  WP.WPROJ_STS_SUBWEB_NAME," & _
                  "  WS.WSTS_SERVER_NAME," & _
                  "  WS.WSTS_SERVER_WEB_PORT," & _
                  "  WS.WSTS_MANAGED_PATH," & _
                  "  OBJ.WOBJ_PROJ_ID," & _
                  "  OBJ.WOBJ_LIST_NAME," & _
                  "  OBJ.WOBJ_TYPE" & _
                  "  From " & _
                  "     MSP_WEB_PROJECTS WP " & _
                  "     inner join MSP_WEB_OBJECTS obj on OBJ.WOBJ_PROJ_ID = WP.WPROJ_ID " & _
                  "  AND (obj.wobj_type = 5 or obj.wobj_type =4) " & _
                  "     INNER JOIN MSP_WEB_STS_SERVERS WS ON " & _
                  "             WP.WSTS_SERVER_ID = WS.WSTS_SERVER_ID"

    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = dbConnection
    oCmd.CommandText = sSQLCommand
    oCmd.CommandType = adCmdText
    Set oRs = oCmd.Execute

    The adCmdText constant is a member of CommandTypeEnum in the ADODB library. The obj.wobj_type 4 is an issue, and obj.wobj_type 5 is a risk.

  • For each project that has risks or issues, PrepareWSSSoapCall constructs a SOAP call for SharePoint Server and sends that to MakeSoapCall.

    Do While Not oRs.EOF
        wssProjID = oRs!WOBJ_PROJ_ID
        Trace ("found project ID: " & wssProjID)
        Debug.Print "Found project ID: " & wssProjID

        'URL for SOAP call includes server name, port and the project id
        sPostURL = "http://" & oRs!WSTS_SERVER_NAME & ":" & _
                   oRs!WSTS_SERVER_WEB_PORT & "/" & _
                   oRs!WSTS_MANAGED_PATH & "/" & _
                   oRs!WPROJ_STS_SUBWEB_NAME & "/_vti_bin/lists.asmx"

        MakeSoapCall sPostURL, oRs!WOBJ_LIST_NAME, oRs!WOBJ_TYPE, riskList(), issueList(), & _
                   riskLength, issueLength, wssProjID

        'Move to the next record
        oRs.MoveNext
    Loop

  • Finally, PrepareWSSSoapCall calls the functions to store the riskList and issueList array data in the Project Server staging tables.

    populateRiskTable riskList, dbConnection.ConnectionString, riskLength
    populateIssueTable issueList, dbConnection.ConnectionString, issueLength

MakeSoapCall

The MakeSoapCall function fills the riskList and issueList arrays with the risks and issues stored in SharePoint Server and also sets riskLength and issueLength with the number of items in the arrays. The function definition is:

Private Function MakeSoapCall(sPostURL As String, _
                              listGUID As String, _
                              listType As Integer, _
                              oListRisk() As objRisk, _
                              oListIssue() As objIssue, _
                              riskLength As Integer, _
                              issueLength As Integer, _
                              wobjProjID As String)

MakeSoapCall builds an XML request in sRequest, depending on whether listType is 4 for issues or 5 for risks. See the download for the full code sample.

Dim sRequest    'part of the XML command
    Dim xmlhttp     'XML response
    Dim xmlDoc As MSXML2.DOMDocument 'XML object to hold the response
    Dim objNodeList As MSXML2.IXMLDOMNodeList       'contains the list of issues/risks returned by SharePoint Server
    Dim oNamedNodeMap As MSXML2.IXMLDOMNamedNodeMap 'contains the attributes for a issues/risk
    . . .
    
    If listType = 5 Then
        Debug.Print "Retrieving list of risks"
        sRequest = . . .    
    ElseIf listType = 4 Then
        Debug.Print "Retrieving list of Issues"
        sRequest = . . .     
    End If 
    
    'receiving the response  
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
    xmlhttp.Open "POST", sPostURL, False
    xmlhttp.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    xmlhttp.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetListItems"
    xmlhttp.send sRequest
 
    'load the response
    xmlDoc.loadXML xmlhttp.responseXML.XML

MakeSoapCall finally checks the XML document for errors, and loads the oListRisk and oListIssue arrays with the data.

Populate the Risks and Issues Staging Tables

The PopulateRiskTable and PopulateIssueTable functions are similar, for example:

Private Function PopulateRiskTable(oArrayRisk() As objIssue, _
                                    conString As String, _
                                    riskLength As Integer) _
                                    As Boolean

PopulateRiskTable first builds a SQL command to insert all of the data fields into the Project Server risks staging table, and then clears the table. MakeSoapCall has stored all of the current risk data from SharePoint Services in oArrayRisk.

Dim dbConnection As New ADODB.Connection
    Dim sSQLInsert As String
    Dim sSQLSelect As String
    Dim sSQLCommand As String
    Dim oCmd As ADODB.Command
    
    Set dbConnection = New ADODB.Connection
    dbConnection.ConnectionString = conString
    dbConnection.Open
    populateRiskTable = False 'Assume failure
        
    sSQLCommand = "INSERT INTO MSP_CUBE_WSS_RISKS (ows_Title" &_
                                         ",ows_Author" & _
                                         ",ows_listName" & _
                                         ",ows_Status" & _
                                         ",ows_StatusActive" & _
                                         ",ows_StatusPostponed" & _
                                         ",ows_StatusClosed" & _
                                         ",ows_Category" & _
                                         ",ows_Category1" & _
                                         ",ows_Category2" & _
                                         ",ows_Category3" & _
                                         ",ows_Probability" & _
                                         ",ows_Cost" & _
                                         ",ows_Exposure)" & _
                                         " Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"                                         
    Set oCmd = New ADODB.Command
    
    'Clear the risks table
    oCmd.ActiveConnection = dbConnection
    oCmd.CommandText = "DELETE FROM MSP_CUBE_WSS_RISKS"
    oCmd.CommandType = adCmdText
    Call oCmd.Execute
        
    oCmd.CommandText = sSQLCommand

PopulateRiskTable then creates each of the risk data parameters and appends them to the ADODB command object, loops through oArrayRisk to load the parameters into the command object, and executes the command to save the data in the staging table for each risk.

Dim oParam As Parameter
    Set oParam = oCmd.CreateParameter("ows_Title", adVarWChar, adParamInput, 256, "")
    oCmd.Parameters.Append oParam
    . . .
    Set oParam = oCmd.CreateParameter("ows_StatusActive", adInteger, adParamInput)
    oCmd.Parameters.Append oParam
    . . .
    
    Dim x As Integer 'count through the list of risks
    For x = 0 To riskLength - 1          
        oCmd("ows_Title") = oArrayRisk(x).ows_Title
        . . .
        oCmd("ows_StatusActive") = ProcessStatus(oArrayRisk(x).ows_Status, "active")
        . . .
        oCmd.ActiveConnection = dbConnection
        oCmd.CommandText = sSQLCommand
        oCmd.CommandType = adCmdText
        Call oCmd.Execute
    Next x

ProcessStatus and similar minor functions provide the logic to determine the value of fields such as ows_StatusActive. See the download for the full source code.

You are now finished with step two in adding task issues and risks to the cube. You have extended the OLAP cube staging tables to accommodate the new dimensions and populated the tables with task issue and risk data from Windows SharePoint Server that was entered in the extended Project Web Access pages.

The third step is updating the OLAP cube in Analysis Server with the new data from the staging tables. See Step 3: Adding Issues and Risks with UserOLAPCubeUpdate.