Add joins to universe / joins to contexts using Designer SDK

[system]

Author: Dwayne Hoffpauir, EDS Corporation

Further discussion on this utility should take place in this topic.

Author Notes:

This utility provides a high volume method for adding joins to a universe, and adding joins to contexts, without writing directly to the repository. It uses Excel as the VBA host and data entry tool, and makes the appropriate calls to the Designer application. The complete utility can be downloaded from the link above.

Usage is as follows: Open the Excel workbook, press Alt-F8 and you should see three macros. Run GetLists first. You will be asked to log into Designer, and open a universe. The GetLists macro “dumps” the current tables (just FYI), joins, and contexts on three separate tabs. This “dump” is to give a starting point for syntax and such.

If you would like to add joins, on the Joins tab make changes as needed. All of the original information would need to be removed, since it would fail while attempting to add the duplicate joins. Now press Alt-F8 and run the AddJoins macro. You will again be asked to log into Designer and open a universe. Designer will be left open with the updated universe UNSAVED, to give you a chance to review the updates. Save the universe and close Designer when your review is complete.

If you would like to add joins to contexts, on the Contexts tab make changes as needed. It would be a good idea to copy the joins themselves from Joins tab to be sure they match. Again, all of the original information would need to be removed, since it would fail while attempting to add the duplicate joins to the contexts. Now press Alt-F8 and run the AddJoinsToContext macro. Once again, you will be asked to log into Designer and open a universe. Designer will again be left open with the updated universe UNSAVED, to give you a chance to review the updates.

A copy of the code is below. If you choose to copy the code into your own workbook, you will need to create a reference to the Designer Object Library first (Tools, References from the VB Editor).

Option Explicit                 'require variables to be declared before being used
Option Compare Text             'use case insensitive comparison

Sub GetLists()

    Dim DesignerApp As Designer.Application
    Dim Univ As Designer.Universe
    Dim Tbl As Designer.Table
    Dim Jn As Designer.Join
    Dim Cont As Designer.Context
    Dim Rng As Excel.Range
    Dim RowNumTables As Long, RowNumJoins As Long, RowNumContexts As Long
    Dim CurrentApp As String

    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LoginAs

    RowNumTables = 1: RowNumJoins = 1: RowNumContexts = 1
    Set Univ = DesignerApp.Universes.Open

    'get list of tables / aliases
    Set Rng = Sheets("Tables").Cells
    For Each Tbl In Univ.Tables
        RowNumTables = RowNumTables + 1
        Rng(RowNumTables, 1) = Tbl.Name
        If Tbl.IsAlias Then
            Rng(RowNumTables, 2) = Tbl.OriginalTable
        End If
    Next Tbl

    'get list of joins
    Set Rng = Sheets("Joins").Cells
    For Each Jn In Univ.Joins
        RowNumJoins = RowNumJoins + 1
        Rng(RowNumJoins, 1) = Jn.Expression
        Rng(RowNumJoins, 2) = Jn.ShortCut
        Rng(RowNumJoins, 3) = Jn.Cardinality
        Rng(RowNumJoins, 4) = Jn.OuterJoin
    Next Jn

    'get contexts
    Set Rng = Sheets("Contexts").Cells
    For Each Cont In Univ.Contexts
        For Each Jn In Cont.Joins
            RowNumContexts = RowNumContexts + 1
            Rng(RowNumContexts, 1) = Cont.Name
            Rng(RowNumContexts, 2) = Jn.Expression
        Next Jn
    Next Cont

    DesignerApp.Quit
    Set DesignerApp = Nothing

End Sub

Sub AddJoins()

    Dim DesignerApp As Designer.Application
    Dim Univ As Designer.Universe
    Dim Jn As Designer.Join
    Dim Rng As Excel.Range
    Dim RowNum As Long

    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LoginAs

    Set Rng = Sheets("Joins").Cells
    RowNum = 2

    Set Univ = DesignerApp.Universes.Open
    Do Until Rng(RowNum, 1) = ""
        Set Jn = Univ.Joins.Add(Rng(RowNum, 1))
        Jn.ShortCut = Rng(RowNum, 2)
        Jn.Cardinality = Rng(RowNum, 3)
        Jn.OuterJoin = Rng(RowNum, 4)
        RowNum = RowNum + 1
    Loop

End Sub

Sub AddJoinToContext()

    Dim DesignerApp As Designer.Application
    Dim Univ As Designer.Universe
    Dim Jn As Designer.Join
    Dim Cont As Designer.Context
    Dim Rng As Excel.Range
    Dim RowNum As Long

    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LoginAs

    Set Rng = Sheets("Contexts").Cells
    RowNum = 2

    Set Univ = DesignerApp.Universes.Open
    Do Until Rng(RowNum, 1) = ""
        For Each Cont In Univ.Contexts
            If Cont.Name = Rng(RowNum, 1) Then
                Call Cont.Joins.Add(Rng(RowNum, 2))
            End If
        Next Cont
        RowNum = RowNum + 1
    Loop

End Sub

JoinsContexts.zip (14.0 KB)

---

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

[system]

this is not working its showing an error does this requires any reference as i have downloaded the above excel

Please help

Regards
Arif

---

idarif2007 (BOB member since 2010-06-03)

[system]

Hi

does this work for BOXIR3.1 SP3 by any chance ?

regards, david

---

david.poulton (BOB member since 2008-05-30)

[system]

I came across these the other day on an American Disney site, but did not know that they were available here in the U.K!

---

rocknggirls (BOB member since 2012-01-17)

[system]

Hi

i need some help on this issue, do you know if this utility works or not for R2 or R3 ?

david

---

david.poulton (BOB member since 2008-05-30)