Update universe definition from database table

[system]

Author: Reema Gupta, Sr. Datawarehouse Specialist, Delinea Corp

Further discussion on this code sample should take place in this topic.

Author Notes:

The purpose of this code is to update the descriptions of the objects in the universe from a database table which contains the universe objects “Select” and “Description” fields.

In this example, the table object_desc has two columns:
object_name: contains the select value of the objects in the universe
desc1 : contains the description of the above object

This macro requires the inclusion of following references in the project:
Microsoft ActiveX DataObjects 2.0 Library
BusinessObjects Designer 5.1 Object Library

Before running the code:
1) Change the userid, pwd for universe login
2) Change the userid and password for reading the table data source
3) Use appropriate table name and field name where applicable

Sub UpdateUniverse()

    Dim cnn1 As ADODB.Connection 'Create an ADO Connection Object
    Dim rs As ADODB.Recordset 'Create an ADO recordset Object
    Dim strCnn As String 'Create a string to hold the connection information
    Dim i As Integer
    Dim ObjDes As New Designer.Application
    Dim ObjUniverse As Designer.Universe
    Dim cls As Class
    Dim j As Integer
    
    'Initialize connection string
    strCnn = "DSN=dsn where above table exists;Uid=userid;Pwd=password;"
    Set cnn1 = New ADODB.Connection
    cnn1.Open strCnn 'Open the Connection
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenForwardOnly
    rs.Open "select object_name,desc1 from object_desc", cnn1
    rs.MoveFirst 'moves currentRecord pointer to first record
    
    Call ObjDes.LoginAs("uid of universe login", "password of univ login", False)
    Set ObjUniverse = ObjDes.Universes.Open("universe name")
    For i = 1 To ObjUniverse.Classes.Count
        For j = 1 To ObjUniverse.Classes.Item(i).Objects.Count
            rs.MoveFirst 'moves currentRecord pointer to first record
            Do While Not rs.EOF
                If UCase(rs.Fields("object_name").Value) = _
                UCase(ObjUniverse.Classes.Item(i).Objects.Item(j).Select) Then
                'above statement compares each object select vale with the table's
                'field and wherever it matches it sets the objects description accordingly
                    ObjUniverse.Classes.Item(i).Objects.Item(j).Description = _
                    rs.Fields("desc1").Value
                'MsgBox (rs.Fields("object_name").Value & " " & rs.Fields("desc1").Value)
                End If
                rs.MoveNext 'moves currentRecord pointer to next record
            Loop
        Next j
    Next i

    rs.Close 'close recordset when done
    Set rs = Nothing
    cnn1.Close 'close connection when done
    Set cnn1 = Nothing
    ObjUniverse.Save
    ObjUniverse.Close
    
End Sub

---

BOB Downloads (BOB member since 2003-05-05)