Nov 13 2007
Universe “diff” Tool
I have been working on a “diff” tool for universes. I started with Dwayne’s fine work on his Universe Documentation Tool but have taken it in a different direction. I prompt for two universes (in sequence, not at the same time), import the structure into an Excel file, and then use some arrays and indexing strategies to compare one universe to another universe. I use this tool to generate a “diff” or change log report before migrating a universe from Development to Test and on to Production.
With my current version the utility relies on the object_id being unchanged. If you drop an object and create a new one with the same name, it will show up on the audit as Object A (Deleted) and Object B (Added) on the final report. Because I am using the object_id I can capture all sorts of changes, including a move to a new class, SQL updates, and even object name changes. This also means that my tool will work exactly the same way that the reporting tools work… in that if you delete and then recreate an object they will not have the same object_id value, and therefore could cause issues.
At the moment the tool seems to be fairly reliable as far as capturing changes for objects of all sorts (both regular objects and condition objects). I have started to add change detection for joins and also for derived tables. I never intended to add those, so as is often the case I will have to go back and rewrite some of the code in order to make that process more efficient. That is where you come in. 🙂
Since I will be potentially rewriting some of the code I am looking for feedback on a couple of ideas. First, would this tool be useful to you? And if so, what sort of information would you like to see included? Right now the output is displayed in Excel and I make use of the AutoFilter option to be able to work through the list and see what has changed and where. The current report (as mentioned) only shows objects and predefined conditions. I am adding joins and derived tables. I don’t intend to add a table list, as if you have added a table it really doesn’t matter unless you have also added joins or objects from that table. So I really don’t care if a new table exists. 🙂
I am not sure about contexts; I guess they should probably be included. Thoughts?
Anything else that you think is important?
Right now in order to run this utility you have to have Microsoft Excel and the Universe Designer application installed. You have to have access to the universes that you want to compare.
Dave,
How about
— Contexts
— Table Aliases
— Standalone tables which are not joined with other tables if exists
— Objects – (Object Security Level, Aggregation, Object is an LOV etc).
— Class – Class/sub class/description help
Thanks.
Remember, this isn’t a documentation tool, it’s a “differences analysis” tool. At this point I don’t really care if a table is stand-alone or not… I just want to know if it’s new or if it already existed. The same thing for joins.
All of the object details (including attributes like aggregation, has a LOV, and so on) are already processed in the existing code. So I am ahead of you there. 🙂 I include classes as an attribute of objects, but at this point not by themselves. The attributes that I might track for classes include the class position, its parent, whether it’s visible or not, and the class description. Those are all good points.
One of the useful warning messages that this tool might be able to deliver is something along the lines of… “You have added the following new joins but they don’t appear to be in any contexts” or something like that. This message would only be generated, of course, if you have at least one context.
Dave,
I don’t know how it is better, but I one used Dwayne’s (Document a Universe) code, Documented my universe and saved all the excel sheets in separate excel files, and there are free excel compare softwares… like ExcelCompare, ZCompare.. with which we can find the difference between the two universes…
Actually, at my client the universes have been modified at Offshore Development Center(ODC) and at the same time at the client site.. and that is how I found his utility helping me more than what I thought. I am not sure howz your going to be 😉
I think Joins and derived tables are about all that I worry about, aside from the objects.
How about making hidden objects italic or something, so they look different. After a while, they tend to build up since you never know what people have done with them in ad/hoc reports.
Ahmar, the main difference that I see is that by keeping everything in Excel and doing my own “diff” analysis I can provide details in a format that is easier to use. Using a compare tool to compare Excel sheets is certainly a viable option. I just like mine better. 🙂
nod, the idea of making hidden objects italic is interesting. I already capture the “Visible” attribute for the objects (and therefore capture changes from visible to invisible… or back). I can filter on it using the awesome Auto Filter option in Excel.
Dave,
I’m in need of, and about to redevelop, exactly the same tool. It is for internal work only, not for distribution. Would you be interested in sharing what you have?
Thanks
Mike
Hey Dave, I just thought of something else – would it matter which versions of the universe you are comparing? I’m currently transitioning from 6.5 to XIr2, and since clients can overrule a “code freeze” I am making changes in both universes during the freeze. Would your tool be able to compare the two versions? Do you need a beta tester??? 🙂
Yes, and no.
Yes, in that each universe is opened and downloaded to excel in a separate process. No, in that the library that I currently have linked is only the XI library. You could use it to compare universes from two different versions of Business Objects as long as the script was set to do it that way.
I have versions for both 6.x and XI. The only code that would be different would be the code to log in.
Mike: (earlier commenter) my code is not yet at a point where I can share it. You can download the code that I used as a starting point from BOB here if you are interested. The utility published there will allow you to download an entire Universe into Excel. Then you can either write a compare routine, or I am told that there are some standard tools that would let you compare XLS files for differences.
Hi Dave,
I’d like use this tool to compare 6.5 vs XI universes. Could you please let me know from where I can download this tool?
Thanks,
It is not available for download at this time. I have a bug of some sort in the routine that extracts the list of tables from the universe and on some universes it causes the entire script to fail. It’s still very much a work in progress at this time.
There are a few impact analysis tools that I think are necessary to ease the work of BO developers/architects, this is one of them. I definitely believe that contexts should be part of the diff analysis to ensure that a join hasn’t been dropped, like those pesky self-joins that don’t higlight very well when you select the context in List View to ensure you’ve selected properly.
Hi, Joel, I recognize your name from BOB, welcome. 🙂 I have added contexts to the list of supported items, mostly for the exact reason that you mention. Thanks for your input.
How do you extract
object_ids?bodessdk.chm does not mention this…
[I built up an Excel tool for copying universes and manipulating their connections, to tailor for different consumers. Involved is also a diff of targeted and actual repository content w.r.t. my bunch of tailored universes.]
I’d like to develop a tool that builds up a universe from a textual representation (maybe XML). Will it be possible to control the
object_id?You have to tell Excel to open up the hidden elements of the object model. For example, open the Object Browser in your VBA editor. Right-click on one of the attributes and select “Show Hidden Members” to reveal some additional attributes of the object model. The Object class has a hidden property named ID that you can use to retrieve the object_id from the universe information.
However this property is strictly read-only, to the best of my knowledge you cannot control it.
Dave, Any updates on this tool? – would be really interested in test driving it.
Unfortunately, nothing new to report. I haven’t made much progress beyond realizing that certain parts of the code need to be rewritten in order to support a wider range of attributes (joins, contexts, and so on). It’s on the list of things to do, but likely won’t get much attention for at least another couple of months.
Projects that I do in my free time generally have development cycles that are fairly long, if only because there are so many different projects I have going at any given point in time and a fixed amount of free time. 🙂
Hi Dave,
I am a very big fan of your blog. It gives me lot of valuable information. I am just curious – have you completed the coding of the universe diff tool? it would be a great tool to have.
Thanks,
Priyanka
No, development has not progressed beyond where it was when I first made this post. The code I have is functional… but only to a certain point. It’s not anywhere close to being ready to share at this time.
Dave –
Thanks for the tip on how to find the hidden stuff (object id, join id, class id, etc). You just saved me a lot of work !!!!!
Deb Franke
Hi, Deb, thanks for taking the time to leave a comment. 😎
Dave-
This is exactually what I am looking for, but for some reason I cannot find the download link. What am I not seeing?
Brian
There is no download link at this time as the utility has not been completed. Thanks for your interest.
Hello Dave, Do you have any update on the utility?
No update at the moment, other than to say it’s basically where it was months ago. I have submitted an idea for a presentation at the fall conference related to this. If the abstract gets accepted, then finishing this utility will become a higher priority. Thanks for your comment, and your interest.
Hi Dave,
Do you not think SAP BO will give us the functionality to do this with Meta Data Manager…one day?
And in theory, LifeCycle Manager should give use these answers too? I mean it is the tool recommended for migrations and version control, so how hard can it be for SAP BO to add a little bit of extra functionality like comparing unv’s?
Hello Dave,
Thank you, Thank you, thanks you that will be great addition to BO Admins.
Already signed up for your class!
Releted to universe topic, we do expirince other issues with universes:
BO Repository has several different universes with same shortname, and as you know some WEBI reports linked to universes via Shortname.
Is any way the short name of universe can be updated in CMS database and then a feature to update the shortname in the link between WEBI and universe?!
Thank you,
Alex
Dave,
Do you have any update on when the this tool will be available to download?
This tool will be a great help for everyone.
Thanks
Corey
Before the end of the year. 🙂 It should be sooner, but October was a really busy month where I attended two different conferences, and November is a busy month catching up from October. I need to make a few tweaks (or remove the code altogether since the aggregate objects part does not work) and then I can post it. Thanks for your interest, and your patience.