Works perfectly. Thank you.
dopple (BOB member since 2009-06-19)
Works perfectly. Thank you.
dopple (BOB member since 2009-06-19)
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 & " - " & Err.Number & ": " & 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)
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)
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)
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)
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)
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:
lguser (BOB member since 2011-01-24)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
Wonderful!
Thank you so much Dave. You Rock!
singh (BOB member since 2005-09-26)