I’ve got a universe designer who needs to update object descriptions in their universe for about 3,000 objects. Does anyone know of a way to automate this if the designer creates a flat file with the object name, id, and description? Could this be done using a strategy?
The designer doesn’t want to have cut and paste 3000 descriptions into the universe. I suppose the unsupported option would be to use a SQL UPDATE statement on the appropriate repository table.
I have an Excel-based utility for this purpose. It contains two VBA macros that manipulate the Designer object model (no direct repository updates). The first macro “dumps” the current class name, object name, and description in columns A,B,C and repeats the object name and description in columns D,E. Then columns D,E can be edited to a “new” object name and description. The second macro makes the changes to the universe. It’s not “bullet proof” by any means, but I’ll share the code if you’re interested.
The Excel utility can be downloaded here. Open the file, press Alt-F8 and you should see two macros. Run GetInfo first. You will be asked to log into Designer, and open a universe. The current class, object, and description will be loaded into the spreadsheet. Make changes to the columns in blue. Then run the MakeChanges macro. You will again be asked to log into Designer and open a universe. There is no “hourglass” so be sure the macro is finished. Designer will be left open with the updated universe UNSAVED, to give you a chance to review the updates.
I will see about getting this added to the Code Samples forum later.
[Edit: Utility has been placed in the Code Samples forum here.]
Was wondering if there has been any reported issues with this code. It was mentioned that it’s not ‘bullet proof’, so I was wondering if there have been any reported cases of it having/causing problems?
I have had no reported problems … either from my own usage (a couple times a month), or from any other users. The “not bullet proof” is mostly in reference to not having any error handling included. If any errors occur, you will see them as they occur. As with most code samples posted here on BOB, they are intended as learning tools … not intended to be “commercial” quality (whatever that means). In any case, it should be worth at least as much as you’re paying for it. 8)
I have used it quite a bit and the only problem that I have found is that the macro doesn’t close Designer after it fetches the data from the universe, so there is a hidden Designer session that I have to manually kill. I have also made the following changes to the macro:
I added a message box that says “Done” when it is done. It is not obvious when it is done.
I added a second tab in the Excel spreadsheet for printing out a listing. The tab it generates now is for updating and is not formatted for printing.
I’ve actually made a couple of improvements to my own version (closing Designer, cursor management, status messages). I’ll make a note to “clean it up” (maybe even add generic error handling) and repost it here … in all my spare time of course :?
I’ll check back often for your latest / greatest version! If a subject matter expert happens to delete rows, is there an adverse effect on the mass update? Just wondering how diligent I need to be, as far as checking their changes goes…
You’re quite welcome. As far as deleting rows, it should have no impact. The second step is purely update. The utility does not attempt to add or delete objects whatsoever.
Dwayne this utility is great. One question. Can it be amended to pick up new classes and objects that you enter into the spreadsheet. Bear in mind I know very little about the Designer object library, but point me in the right direction and I’ll figure it out.
If I understand your question, you want to have new classes and objects added to a universe by simply adding them to the Excel sheet, correct? Can it be amended? Certainly! Easily? I think not. You would have to expand the utility to cover ALL of the properties of an object, plus include logic to determine if a class / object is new. At that point you’re almost duplicating Designer itself. To make it worth that effort, you’d have to have a HUGE project going. You would probably be better off buying Data Integrator, since it can create universes based on metadata.
[quote:45a6515ddd="Dennis W. Disney "] I don’t show hidden objects
[/quote]
Hi - I was wondering if you would be willing to share how you modified the spreadsheet so that it doesn’t include the hidden objects. This would be a real nice function to have.
Private Sub GetObjectInfo(Clss, RowNum As Long)
Dim Cls As Designer.Class
Dim Obj As Designer.Object
For Each Cls In Clss
For Each Obj In Cls.Objects
If Obj.Show = True Then '<<===add this line
RowNum = RowNum + 1
Wksht.Cells(RowNum, 1) = Cls.Name
Wksht.Cells(RowNum, 2) = Obj.Name
Wksht.Cells(RowNum, 3) = Obj.Description
End If '<<===add this line
Next Obj
If Cls.Classes.Count > 0 Then
Call GetObjectInfo(Cls.Classes, RowNum)
End If
Next Cls
End Sub
I have yet another question about this. The objects that print in the excel spreadsheet are not in the order they are in the universe(this is a linked universe). Is it possible in some way to order the objects that are coming in in the spreadsheet? I have written an oracle query and I know that they need to be ordered by class_id, object_id and Obj_position.
I don’t think so. There are hidden Class.Id and Object.Id properties that you can use, but I can’t find anything in the object model related to position.
(BOB member since 2002-06-11)
(BOB member since 2002-08-08)
(BOB member since 2004-07-01)