Adding joins to contexts via updates to repository

system · November 12, 2003, 11:18pm

Hi,

I’ve got a universe that has alot of contexts, I need to add to most of these contexts a new join. Rather than doing it manually in Designer has anyone managed to achieve the same thing by updating the repository directly ?

Cheers
Rich

Rich (BOB member since 2002-10-04)

system · November 13, 2003, 12:23am

I wouldn’t recommend adding directly to the repository. Someone can correct me if I’m wrong, but I believe you need to let the system assign an object key so that it can refresh between all tables. Even if the system did allow you update and you missed just one reference point you could break a lot of reports when another context is added later.

For what my 2 cents is worth.

Have a great day!

MayhewM (BOB member since 2003-10-22)

system · November 13, 2003, 12:13pm

I can only agree with Michael. I know this can be a pain but its the safe way to do it. What sort of numbers are you talking anyway?

Nick Daniels (BOB member since 2002-08-15)

system · November 13, 2003, 12:37pm

The universe has got 93 contexts at present (think record for context per universe on this forum is 300 plus or something)

I know its not idea but it works and at the moment I dont have time to re-build the whole universe which would be the best solution.

Rich (BOB member since 2002-10-04)

system · November 13, 2003, 2:53pm

I will chime in and agree that direct repository update would be a bad idea. However, if you are comfortable with the SDK, it can be automated that way. I had one occasion where I added a new dimension table, then because of volume (and the increased chance for error), I automated the adding of the joins themselves as well as adding the joins to contexts.

The benefit of course is that by using the Designer SDK, it can be both automated and supported. If this is an option for you, I can try to dig up that code.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · November 13, 2003, 3:46pm

Hi Hoffpauir

Looks like a novel way of getting things done, would you mind sharing the source code

thanks

gowthaman (BOB member since 2002-08-15)

system · November 13, 2003, 8:29pm

Cheers for the advice unfortantly we don’t have the SDK licience. Oh well.
its a one off with this universe anyway and when I get more time its going to be rewritten. So I’m going grin and bare :x it and add things manually for the time being

Rich (BOB member since 2002-10-04)

system · November 13, 2003, 10:03pm

OK, I found what I had written and simplified it trememdously. It is an Excel-based utility and can be found here. I hope someone finds it useful (Rich, let your conscience be your guide), and if you have any problems or other feedback, post it here.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · September 14, 2006, 9:26am

Hi
I found this script interesting for to dublicate large contexts.
Why do I need to dublicate ?
For example, there is context with a lot of dimensions and facts F1 - granted amount.
But then I need LAST granted amount in period.
Therefore I need to create alias F2 for F1 with self join (subquery filters last in period).

My plan was to GetLists from universe, rename all F1 contexts joins to F2 and then AddJoinToContext all joins to new F2 context.

But, Joins&Contexts throws me “-2147417851 (80010105)
Automation error
The server threw an exception”

No idea where’s problem

BO 5.1.6, MS excell 2000

thanks

miganos (BOB member since 2004-02-06)

system · November 16, 2008, 3:30pm

oops…posted in old topic…

peternolan9:

Hi All,
I put this post over here…

Writing code to automatically implement joins

And was pointed back to here…I have downloaded and looked at the code to add joins to a BO universe…and that’s fine…I understand that code…

What we are going to be doing is buiding worksheets that not only update the BO Universes as we see necessary but we are also building them so that they load the data into an sql server database and can be reported on separately from BO as part of our metadata dictionary…we will be able to report on the object being used on a report and link it all the way back to the source field and what was done to it inside the ETL subsystem as well…full data heritage…something I have been wanting for 15 years or so…

Hence, as useful as this tool is, I am still looking to see if anyone else here can give me a tip on what might be wrong in the code below…I am still using an old version of BO…This code works for one universe but not for another…I can’t figure out how to get a more details message or how to diagnose why one universe works and another does not…

Any ideas people have would be greatly appreciated…

Thanks in Advance…
        For index1 = 0 To numUpdateDataModelJoinRows - 1
            If myDataModelJoinsArray(index1).FoundInUniverse = False Then
                do_nothing1 = 0

                JoinExpressionToAdd = myDataModelJoinsArray(index1).LeftTable + "." + myDataModelJoinsArray(index1).LeftTableColumnName + "=" + myDataModelJoinsArray(index1).RightTable + "." + myDataModelJoinsArray(index1).RightTableColumnName

                JoinExpressionToAdd = JoinExpressionToAdd.ToUpper

                MyNewJoin = Univ.Joins.Add(JoinExpressionToAdd)

                NumberBOJoinsAdded = NumberBOJoinsAdded + 1

                Select Case myDataModelJoinsArray(index1).Cardinality.ToUpper
                    Case "ONETOONE"
                        MyNewJoin.Cardinality = Designer.DsCardinality.dsOneToOneCardinality
                    Case "ONETOMANY"
                        MyNewJoin.Cardinality = Designer.DsCardinality.dsOneToManyCardinality
                    Case "MANYTOONE"
                        MyNewJoin.Cardinality = Designer.DsCardinality.dsManyToOneCardinality
                    Case "MANYTOMANY"
                        MyNewJoin.Cardinality = Designer.DsCardinality.dsManyToManyCardinality
                End Select

            End If
        Next

peternolan9 (BOB member since 2003-02-01)

system · February 12, 2009, 7:47am

The following functions can be used to add contexts into the universe.
Context names has to be loaded in Sheet “Contexts” in the column 3


Sub AddContexts()

    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, 3) = ""
        Call Univ.Contexts.Add(Rng(RowNum, 3))
        RowNum = RowNum + 1
    Loop

End Sub

smartpandian (BOB member since 2009-01-27)

system · February 12, 2009, 2:31pm

That code looks very familiar … Add joins to universe / joins to contexts using Designer SDK

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · February 17, 2009, 9:59pm

Dwayne, do you have an updated version that works with XIr2?

When I try to run the macro, I get an error. When I go into VBA in Excel and call up the References panel, it says:

“Missing: BO designer 5.1 object library”.

So I tried un-checking this library and using the Designer 11.5 library, but then there’s an error “The LoginAs method has been deprecated.”

dnewton (BOB member since 2004-01-30)

system · February 17, 2009, 11:14pm

@dnewton:

Can you use LogonDialog instead of LoginAs… ?
That is the only method that always create issues … in XI.

BO_Chief (BOB member since 2004-06-06)

system · February 19, 2009, 2:38pm

BO_Chief’s guidance is correct. Use .LogonDialog instead of .LoginAs.

Dwayne Hoffpauir (BOB member since 2002-09-19)