Document a universe using Excel and the Designer SDK

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)

system · February 4, 2011, 6:52pm

I wrote the following post a couple of weeks ago and was hoping to stir your interest. Any reconsideration on modifying the excel sheet to open more than one universe, or better yet, possibly import multiple universes from repository?? If not, any suggestions?

Semantic Layer/ Universe Designer
Posted: 24 Jan 2011 17:02
Post subject: Document ALL universes

I am asking if anyone has added and/or modified code in the Document a universe downloads to enable the documentation of ALL universes.

The Document a Universe download is very helpful BUT we need to document ALL universes for the following reasons:

We manage a couple hundred of universes. When substituting an old field and/or table for a new field and/or table, it would be very helpful to obtain a list of all universes currently using the old field/table.
We are currently implementing a metadata repository and have been tasked with listing all universe tables, field names, and field descriptions (as entered into the object description).
Upper management is now requesting various metrics from Bus. Obj. as a whole, as well as per project/application we need # users( which we can get from a different BOB download, thank you!) # universes, # distinct tables, # distinct fields.
To help maintain/document the assignment of Oracle Roles and Privileges to a new Oracle ID.
As a cross reference to see what tables exist in what universes. When adding a table to a new universe, sometimes we may need to identify the join properties from an existing universe. Also, when converting existing reporting systems into the warehouse, it is helpful for documentation purposes to list what distinct tables we currently have as compared to what we need.
We currently are migrating from 6.5 to XI 3. In 6.5 I was able to run sql scripts off the universe repository tables to obtain answers to all of the above. Now Im lost

lguser (BOB member since 2011-01-24)

system · February 4, 2011, 9:38pm

I would submit that you need a different tool for this. Instead of capturing the elements of a universe into Excel, you should work on something that captures the same elements into MS Access, or even a “real” database if you don’t want to use Access. That would give you far more flexibility.

There are also after market tools (meaning not free) that provide this sort of documentation already.

Dave Rathbun (BOB member since 2002-06-06)

system · February 7, 2011, 8:19pm

Any suggestions on which tools? A couple of tools that we have looked at do not go into the universe documentation detail that this excel sheet does.
So then I thought we could possibly get this data into oracle tables, but I am not sure the best way to do that. I am interested in any suggestions for this solution as well.

lguser (BOB member since 2011-01-24)

system · June 10, 2011, 6:53am

Hi All,

I am getting the following error when i am trying to document the universe.

Failure in ListObjects()

VBAProject- -2147417851: Automation error
The server threw an exception.

I found this is because of following scenario:

There are some hidden objects in my universe which doesnt referrence to any of the tables. The select clause of the hidden object looks like this “unknown.column”. The utility is documneting properly but it throws error when the utility come across the objects which doesnt have any referrence to DB tables. Can any one please suggest if the code need any modification to overcome the above problem.

Thanks

Ashie (BOB member since 2008-09-17)

system · June 28, 2011, 12:01pm

Hi,

I want to identify “Do not generate SQL before running” checked object (query over write) in my universe.

how to get or which column i can get this information.

Thanks,
Mahu

mahudes (BOB member since 2006-03-06)

system · July 18, 2011, 4:41pm

We’ve also seen the ‘yyyymmddhhmmss’ error under a few circumstances.

We’re on XIr3.1 FP1.7.

Through some testing today I found a method that works for us that might work for you, although a little long winded.

Log into Designer as Administrator and import the universe from the respository (don’t open the file location on the local/network disk).

Save the universe to a handy location like your desktop (not your usual universe file location, I have no idea if this could mess up your environment)

Log into Designer using your usual user account for Designer and open the file as read only (this step may not be required but I never went back to check)

You should now be able to open the universe using the tool using your usual Designer credentials.

I hope that works for you and anyone else seeing this problem.

norty303 (BOB member since 2003-03-19)

system · August 23, 2011, 9:21pm

Is there a way I can change some values in the excel sheet and write it back to the universe? for example If i needed to uncheck useassort for all objects in the universe, can I change the values in the excel sheet for all the objects and write it back to the universe

Bo_Bhai (BOB member since 2003-07-11)

system · August 30, 2011, 9:13am

getting error as class does not support automation or interface, as soon as clicked on document universe button or run macro, any idea.

lahca_ac (BOB member since 2010-01-18)

system · September 4, 2011, 7:52pm

any clue pls or if thr’s any other macro for 3.1, do let m know.

Thanks

lahca_ac (BOB member since 2010-01-18)

system · September 7, 2011, 10:44pm

Hi Dave,

I am very new to Excel Macro, Is there a way, I can capture the Source Information Tab details (found in the object properties) in this Document a universe spreadsheet please or a way to capture the unique Object IDs?
I am using BO XiR3 version.

Thank you,
singh

singh (BOB member since 2005-09-26)

system · September 8, 2011, 1:36pm

Object ID values can be captured, and I’m fairly certain the notes on how to do that have already been posted here. It involves exposing the hidden elements of the model but I don’t immediately remember the exact steps. The source information is not, as far as I remember, available.

Dave Rathbun (BOB member since 2002-06-06)

system · September 8, 2011, 1:44pm

Hi Dave,

thank you for your reply, Would the hidden elements be on the “Document a Universe” spreadsheet or something to do with the Designer SDK, I have Designer level access, not sure, if this requires Admin level access, I have not worked with SDKs before… :?

Thank you Sir…

singh (BOB member since 2005-09-26)

system · September 8, 2011, 8:52pm

In the VBA editor, you press F2 to invoke the object browser, then right-click and select “Show hidden members.” After that the hidden attributes like the object ID will be visible. That way you can reference them in the code.

Dave Rathbun (BOB member since 2002-06-06)

system · September 9, 2011, 1:59pm

Wonderful!

Thank you so much Dave. You Rock!

singh (BOB member since 2005-09-26)