Document a universe using Excel and the Designer SDK

system · June 1, 2010, 7:14pm

Nice catch! Other discussions have pointed to the 1,024 character cell limit, but since it has never impacted me I had not researched the exact reason. Thanks for the research, and a simple solution.

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

system · June 1, 2010, 7:18pm

If you included the SDK as part of your client installation, it should be a simple matter of going into the Tools, References dialog box (from the VB Editor), de-selecting the library that is labeled misssing, and then selecting its XI 3.x counterpart from the list … BusinessObjects Designer 12.0 Object Library.

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

system · June 4, 2010, 3:28pm

Good Morning Folks,
I am in the process of making Data Dictionary based on the existing universe. The excel sheet document has really been helpful to me. The only enhancement I would love to have is to show object universe with their corrsponding table columns. I do not need the select and case statement that comes with coding for object.

So for example:

If universe object is “state ind” and my universe code is "case when state.state_abbreviation = “MN” then 1 else 0 end "…

Here i just want to see :

state_ind => state.state_aabreviation (in the object tab of the sheet)

kurt (BOB member since 2006-11-06)

system · June 5, 2010, 8:54am

Hi kurt,

there is a tool (very similar to Dwaynes one), which has the functionality to report the data lineage.

You can find a brief example here >> http://www.luetzkendorf.eu/download/getsetuniverse/eFashion_v125_20100207_181430.xls
The sheet ‘dependents_eFashion’ contains the lineage analysis for the universe eFashion. For all objects the used tables and columns will be shown. Additionally the objects containing @functions have a reference pointing to the origin objects.

the bob site for the tool >> https://bobj-board.org/t/141886

André Lützkendorf (BOB member since 2009-09-28)

system · June 10, 2010, 9:00pm

Thank oyu so much for your input. I cannot see the “load Universe” option in the excel sheet. I have enabled the macros.

kurt (BOB member since 2006-11-06)

system · June 12, 2010, 8:20am

Hi Kurt. Please use the topic below to post your questions. thanks
https://bobj-board.org/t/141886

André Lützkendorf (BOB member since 2009-09-28)

system · June 23, 2010, 11:36am

RNola, it sounds like you are working on a universe comparison tool. How did it work out? Are you willing to share it? I am currently working on the same, but it’s always nice not to have to reinvent a wheel.

In case I do have to finish my own, and for my own edification, can you (or someone) point me to the API documentation? I’m currently trying to figure out how where in the property structure to find the ID’s, such as ObjectID, etc. Thanks.

JohnBrooking (BOB member since 2009-12-22)

system · June 23, 2010, 3:23pm

Answered my own question; SDK is here. However, having to sort things by ID for comparison seems to be more work than simply running Dwayne’s original code (with the addition of getting the ID) and pulling that into a database for querying, so I’ve decided to go that route instead. I’m not as much of a VBA programmer as I used to be.

But any other advice on comparing universes is still appreciated.

JohnBrooking (BOB member since 2009-12-22)

system · June 29, 2010, 9:31pm

I am getting an issue saying Can’t find project or library. When I try going to the tools menu the references option is greyed out… any ideas?

jshaffe (BOB member since 2008-09-19)

system · July 21, 2010, 4:50am

HI,

What abt for Bo Xi 3.1 …its not working there

pramod_ps123 (BOB member since 2010-07-21)

system · July 21, 2010, 6:45pm

Hi!

I just found this forum when trying to search exporting BO universe into an Excel. The Excel Macro works perfetly for R2 universes. Is there a R3 version available? I need to export my R3 universes to Excel. Thank you so much for your help!

Thanks,
Victoria

vshan (BOB member since 2010-07-21)

system · October 2, 2010, 8:59am

Hi,

When attempting to run the macro I’m getting the following error:

“Compile Error: User-defined type not defined” and Sub ListTables(Tbls As Designer.Tables) gets highlighted in the Module1

Please help!!!

anuragrathor (BOB member since 2010-10-02)

system · October 26, 2010, 4:46pm

All Interested, i had this same error and figure out the solution

Failure in ListObjects()
VBAProject- -2147417851: Automation error
The server threw an exception.

The Errors received are not to do with the VBA, but stem from the default range being restrictive within the objects sheets, meaning an error is returned if large universes are pulled which do not fit. Simply delete the rows e.g. a2;a65536 within the affected sheets. the VBA will then allow the new segment size to fill the abundant space.

i know its an older post, but this is still worthwhile for those searching for this excellent tool, and stumbling during its use.

Regards
gee

pyperg (BOB member since 2008-12-31)

system · October 27, 2010, 11:29am

Thanks

It’s very important document. It’s very help to create a universe documents

Anis_gul (BOB member since 2010-03-05)

system · November 24, 2010, 11:41am

Works perfectly. Thank you.

dopple (BOB member since 2009-06-19)

system · November 24, 2010, 2:17pm

Just to add, I have amended this to populate the control tab with the metadata from the parameters window (universe name, date created etc).

Note: You will have to move the command button to the other side of the worksheet.

Add this into the module

Sub ListMetaData(Author As String, Comments As String, Connection As String, CreationDate As String, _
                    CurrentOwner As String, Description As String, FullName As String, _
                    ModificationDate As String, Modifier As String, Name As String, _
                    Params As Designer.Parameters)
    Dim Rng As Excel.Range
    Dim RowNum As Long
    Dim Param As Designer.Parameter

    Application.StatusBar = "Documenting Metadata..."
    Set Rng = Sheets("Control Sheet").Cells
    RowNum = 5
    
    Rng(RowNum, 1) = "Universe Name"
    Rng(RowNum, 2) = Name
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Description"
    Rng(RowNum, 2) = Description
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Connection"
    Rng(RowNum, 2) = Connection
    RowNum = RowNum + 1

    Rng(RowNum, 1) = "Created By"
    Rng(RowNum, 2) = Author
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Created On"
    Rng(RowNum, 2) = CreationDate
    Rng(RowNum, 2).NumberFormat = "[$-809]dd mmmm yyyy;@"
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Local Path"
    Rng(RowNum, 2) = FullName
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Current Owner"
    Rng(RowNum, 2) = CurrentOwner
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Modified By"
    Rng(RowNum, 2) = Modifier
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Modified On"
    Rng(RowNum, 2) = ModificationDate
    Rng(RowNum, 2).NumberFormat = "[$-809]dd mmmm yyyy;@"
    RowNum = RowNum + 1
    
    Rng(RowNum, 1) = "Comments"
    Rng(RowNum, 2) = Comments
    RowNum = RowNum + 1
    
    RowNum = RowNum + 1
    Rng(RowNum, 1) = "PARAMETERS"
    RowNum = RowNum + 1
    
    For Each Param In Params
        RowNum = RowNum + 1
        Rng(RowNum, 1) = Param.Name
        Rng(RowNum, 2) = Param.Value
    Next Param

CleanUp:
    Set Param = Nothing
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Source &amp; " - " &amp; Err.Number &amp; ":  " &amp; Err.Description, _
        vbCritical, "Failure in ListMetaData()"
    Resume CleanUp
    
End Sub

And then add this line into the DocumentUniverse() subroutine where the rest of the list procedures are being called.

    Call ListMetaData(Univ.Author, Univ.Comments, Univ.Connection, Univ.CreationDate, _
                        Univ.CurrentOwner, Univ.Description, Univ.FullName, Univ.ModificationDate, _
                        Univ.Modifier, Univ.Name, Univ.Parameters)

dopple (BOB member since 2009-06-19)

system · November 24, 2010, 5:20pm

This is excellent, i was getting around to this and you’ve beaten me to it.

Many thanks!

This really does complete the picture

One extra thing i did include in the Documents Objects detail sheet was the qualification, ie. Dimension/Measure/Detail.

i just added this into the VBA;

Rng(RowNum, 21) = Replace(Replace(Replace(Obj.Qualification, 1, “Dimension”), 3, “Measure”), 2, “Detail”)

Hope it helps

One Question; do you know if the “Revision” can be added to this Universe Perameters meta data??

Thanks

pyperg (BOB member since 2008-12-31)

system · November 30, 2010, 8:04pm

I am running XI R3 and after I select the universe file I get a popup that says “Designer” and in the box: yyyymmddhhmmss. Can’t get it to run.
Help?
Thanks,
Kathy

2spots (BOB member since 2008-07-29)

system · January 4, 2011, 8:04pm

I am continuing to get this error when running this tool in Excel 2007 SP2. Error in GetUniverse. Automation Error. The Server threw an exception.

I have gotten this to run twice, but it has failed over 200 times. I have tried everything listed here, including removing all rows except headers, removing all cell locking, etc. Any other suggestions? This is going to make me pull my hair out and I don’t have that much left!

Thanks.

Dave

ddefazio (BOB member since 2010-11-23)

system · February 3, 2011, 7:19pm

Hi,
I can run this tool and document my universe. But the challenge I see is that, certain objects with very long multiple line descriptions are hashed out in Excel.

I changed the line to get the long descriptions.
Rng(RowNum, 4) = Left(Obj.Description, 1024)

But still no luck.

Sorry, My bad. I just adjusted the cell formatting in Excel to General and it went away.

Never Mind.

Thanks.

BIDeveloper (BOB member since 2009-08-19)