Sep 14 2011
Dagira Universe Compare Tool
At the 2010 SAP BusinessObjects User Conference (also known as SBOUC) I did a presentation titled, “Don’t Lose Control: Change Management Strategies for Universe Designers.” The slides from this presentation are available as a PDF download on my conference presentations page or via a link at the end of this page. This page contains a link to download the utility as well as documentation and (soon to come) answers to frequently asked questions. Please post all support questions using the comment form for this page.
The current version of this utility is 2.0.1 as of October 25th, 2011.
Release Notes
Download link: Universe Compare Tool Download (360K Zip)
Installing
There is no installation routine for this utility. It is written using Visual Basic for Applications (VBA) and is hosted in Microsoft Excel. You may have to adjust the security settings on your computer in order to be allowed to run this macro. You will also need to have Business Objects Designer installed on your computer, and you will also need the Microsoft Scripting Runtime library, available from Microsoft.com. Please read about the system requirements as well as support plans for this utility for full details. Here is a screenshot showing the references that I have included for the macro to run. This list is specific to XI 3.x; some small changes need to be made for XI R2 which will be detailed later.
Worksheet List
There are a number of worksheets in this utility. Do not remove or rename any of these sheets or the macro will likely fail. Sheets are listed here and described here.
- Copyright
This sheet contains a copy of the GNU GPL v2 license. This is a required component for any code released under that license and therefore is included. It may be hidden but not removed from the workbook. If distributed, the sheet must remain visible. - Config
This sheet contains several items that may be customized by the user, and other information that should remain exactly as it is. Any items that may be customized are shaded green. More details will be provided below. - Read Me
This sheet contains some basic instructions and notes about how to use the macro. - Change Log
This is the destination for the output of the macro. It will contain a list of changes found after the compare process has been completed. The “auto filter” option has been turned on to provide an easy way to filter down to specific items. This option may be turned off at your option without impacting the functionality of the code. - Before [...] and After [...]
There are “before” and “after” sheets for each element of the universe that is analyzed. Note that not every sheet will be filled, based on the options activated on the “Config” sheet. As downloaded, the macro will compare classes, objects, condition objects, joins, tables, contexts, and universe parameters. It will not compare table structures (columns), hierarchies, hierarchy dimensions, or incompatible objects. - What is GPL software?
- System Requirements and Support Plans for Universe Compare Tool
- Universe Compare Tool – How It Came To Be
- Presentation slides (Adobe PDF format) for Don’t Lose Control: Change Management Strategies for Universe Designers (298K Zip)
- Universe Compare Tool Download (359K Zip)
- GNU GPL v2 License
Configuration
The configuration tab has a number of cells with a green background. These are items that you may change in order to customize the behavior of the macro. The first set of green cells is in column “C” on the configuration worksheet. These values should be either Y or N (capital letters only) and they are used to determine which components of the universe to compare. For example, when you downloaded the macro the setting for Hierarchies was N. If you want to compare before and after values for custom hierarchies, change this to Y and rerun the macro.
The second set of green cells found in column “N” is optional. Some databases such as Teradata use a fully qualified table name in the format of schema.table or owner.table and others do not. If the owner / schema is part of your universe, and if this value changes from one environment to the next, you should enter your owner or schema names here. There is a routine in the macro that removes the schema name(s) from the tables and objects so that DEV.TABLE_NAME is not detected as a change from PROD.TABLE_NAME. The list of schema names is used as input for the function that removes the extra text from wherever it might be found before a compare operation is done.
If provided, the schema is removed from any item with a “Y” in the “Has Schema” (column J) in the configuration worksheet. For example, an object ID does not have a schema, but the object select or where clause might. It is not recommended that you update this column but you may if you find it necessary.
The rest of the configuration worksheet should not be edited, however some details are provided below.
Column A – List of components of a universe
Column B – I don’t remember what this is; need to go back and look
Column C – Y / N flag that determines whether this component will be compared or not
Column D – If present, contains the column of the ‘parent’ information. For example, an object belongs to a class, and a column belongs to a table.
Column E – Left blank
Column F – Attribute name, used to classify which characteristics of that attribute will be compared, for example Objects
Column G – Column header for the characteristic, for example objects have Object ID, Object Name, etc.
Column H – Y / N flag that determines whether this characteristic is a key for this attribute or not, for example Object ID is a key, Object Name is not
Column I – Y / N flag that determines whether this element should be compared or not. By definition a key will not be compared because it will always be the same
Column J – Y / N flag that shows whether this characteristic might have a database schema or not (more details above)
Column K – Single character field that denotes the type of data, N = Number, S = String, B = Boolean
Column L – This is the number of the output column where this information will be logged
Column M – Left blank
Column N – List of schema names (more details above)
All further columns in the worksheet are blank.
Support
Support for this utility will be provided only here on my blog, and only via the comment form for this page. As time allows I will create a FAQ document and publish it as well. For now, this is it. 
Other Related Posts
Related Links
Works Great!
Thanks for sharing this great tool!
Regards
Yoav
Hi, Yoav, thanks for the report of a successful run.
Hi Dave,
Thanks for coming up with this great utility. I downloaded the tool and ran it but it threw me an error:
“Run-time error ‘-2147417851 (80010105)’:
Automation error
The server threw an exception.”
I am using this tool on Win XP Pro PC. BO version is XI 3.1 (SP3) on Win Server 2003 SP2.
Any suggestions?
Thank You
Any idea where you were in the code when the error was thrown? If you go into the VBA editor (Alt-F11 is the shortcut key when you’re in Excel) and then select “Debug” and “Compile” do you get any errors? Unfortunately I don’t have any more ideas for you than that right now, given the generic nature of your error.
when I try to run this excel file I get a run time error saying
run-time error ‘430′: Class does not support automation or does not support expected interface
Going to the debug excel highlights this row “Set boDesignerApp = New Designer.Application” Ln80, Col5
I know nothing of VBA so I have no idea how to move on from this, any suggestions or help?
Hi, Tommy, I’m assuming you have Designer installed on your system? What version?
Hi Dave,
Can you please post what changes need to be made to have your tool work for XIR2 universes?
Thanks,
Ray
Hi Dave,
thanks for the tool.
However I am getting the same error as mentioned by JB above. when I click on debug its highlighting
For Each tbl In boTablesof the private function
Private Function ListTablesI have designer BO XI R3.1 SP4 installed. And the MS Office is 2007. I installed BO directly under C-drive – which as something I had to change in the downloaded file as well.
Ray, there are a few changes but not many. I plan to post an XIR2 version after this weekend which will save you the effort.
Tommy, it should not matter what drive BusinessObjects is installed on. There’s nothing that references a drive or install path anywhere. When you open the VBA Editor (Alt-F11) and click “Tools” and then “References” you should have the Excel library selected, the Microsoft Scripting Library, and BusinessObjects 12.0 Object Library. The library references a path, but I thought it would pick up the new path for you automatically. Perhaps that’s what you are referring to? If you click “Debug” and then “Compile Project” does it tell you everything is okay?
Ray, there are a few changes but not many. I plan to post an XIR2 version after this weekend which will save you the effort.
That would be great. Thanks Dave.
Hi Dave,
We had to revert back few changes made in the universe this release and was wondering how to identify them all.
This utility is just what we needed. Thank You.
Hi David, sorry to say that once I press “debug” and then selecting “DEBUG and Compile VBA” I do not get any error messages, it just goes into a loop and does not want to move forward.
and yes, it is the BusinessObjects 12.0 Object Library I need to redefine from D- to instead be my C-drive.
David,
I’m hitting an issue “Runtime error ‘-2147417851 (80010105)’: Automation Error The server threw an exception.”
It is failing at the line
For Each boJoin In boJoinsin theExtractContexts(sSheet, boContexts)subroutine.This is occurring on any universe i run it on — it never asks for the second UNV file because it can’t seem to process the first. When i comment out the ExtractContexts subroutine, it breaks again at
boJoinsin the ExtractContextDetails subroutine. Commenting out both subroutines allows it to finish running.I’m probably setting up something wrong, thanks for your help.
Matt, I’m assuming that your universe(s) do have contexts? Would you be able to email me a copy of one of your universes so I can see if it’s a universe issue rather than an environmental issue? If so, let me know, and I will contact you at the email address associated with your blog comments.
Hi Dave,
I’m also looking for an XIr2 version of the compare tool. Has that been made available? Thanks!
We had a busy soccer weekend, among other things, so no I have not yet uploaded the XI R2 version. The basic changes are to establish a reference to the older library (the version as downloaded points to the 3.x library files) and to remove a couple of references that are not present in XI R2 such as database delegated measures. If anyone feels bold, the steps would be to redo the reference, use the Debug + Compile Project menu to see what fails, and remove those bits of code.
Hi Dave,
I think the issue with
Runtime error ‘-2147417851 (80010105)’: Automation Error The server threw an exceptionon someForEach boX in boXshappens because sometimes the collecion returned by BO has holes inside, i.e. we have 5 elements, but they are not one after the other – if you do aboJoins.Count()on the collection, it gives you 5, but valid indexes when I try to useboJoins.Item(n)are for example 1,2,3,4 and i.e. 7, not 5. Thus,ForEachfails as it happens to get null/nothing when expecting valid collection element.Happens for me on every universe I’ve tried
Leszek, thanks for the suggestion. The point of the “foreach” code though is that you don’t need to reference items by index. I can just about guarantee that the universes I use are in the same state as yours, in that the item numbers are not sequential. The code doesn’t expect them to be anyway.
Dave, I was able to establish a reference to the XIR2 library and remove the non-XIR2 references and successfully compiled the code. But as Leszek and Matt have mentioned, I also ran in to this error:
Runtime error ‘-2147417851 (80010105)’: Automation Error The server threw an exceptionAll our universes have contexts and some of them are linked universes.
I have never tested linked universes, so that’s something that could cause an issue. Contexts are definitely not a problem. I’ve run the script on a universe with nearly 10,000 objects and over 1,000 contexts.
I think I need someone to be able to send me a universe that is causing the error for testing so I can verify whether it’s environmental (something about the computer it’s installed on) or universe related.
Hi Dave,
The error for me tend to happen after several successful “turns” of the ForEach loop, usually near the end – i.e. after 38 of 41 contexts, boContext object becomes null/Nothing for no apparent reason – and if I check the collection then, this somehow happens on the item that has non-sequential indexing issue.
Maybe VBA version is an important thing – I use Office 2010, and it just implements ForEach using in fact the indexes on the backend side? Cannot really share my issue-causing universes without a lot of bureaucracy here…
Dave,
I can send you my Universe that is giving me the issue. Can you email me at the email address I submitted for this posting? I appreciate your help.
-Matt
Matt, thanks for providing a universe for me to test. Here’s what I have found out. First I tried to run my script on the universe exactly as I would run it on any other universe. I wanted to see if in my environment the script would run okay, which would point to some sort of configuration issue. I got the same error that you reported.
Next I created a smaller test script that only tried to extract the contexts from a universe. It failed at the same place. The first several contexts came through fine, but one particular context was a problem. I opened that context to see if there was anything visibly wrong but could not see anything. However, I accidentally touched a join which turned it off, so I clicked it again to turn it back on again. As an interesting bonus, that context no longer failed during the extraction process but the next context on the list did instead.
I then decided to open each context and turn a join “off” and then back “on” again. After doing that, the script processed the contexts just fine. Based on this, I’m assuming that there was something wrong with the definition of some of the contexts or at least the way they’re stored within the universe. Obviously whatever was wrong with the context was not wrong enough to cause the universe to fail, but it did cause problems with my script. Touching each context seemed to reset whatever was a problem, allowing the script to run through the context extraction portion just fine.
There was also an issue with duplicate parameter values which I am addressing with a code update to be posted shortly.
Version 2.0.1 has been released to address duplicate parameter values. I’ve also published a blog post describing the context issue, but to be clear there is no coded fix for this in place yet.
Dave thanks a lot for this excellent and extremely usefull tool !.
I have developed a HUGE Universe which is used by 7 Universities/High Schools. (5000 objects, 362 tables and 8 contexts: that’s what I call HUGE). The only issue I have encounterd using your tool is the one already mentioned (the context ’store’ bug in Designer).
Your UCT saves me an awful lot of time compared to the manual process I used to keep track of changes in subsequent releases.
Ton, thank you for the feedback. I assume you were able to go “touch” the contexts and get past the issue then?
Thanks Dave , yes the ‘touch’ solved it.
Dave,
I am consistently getting the error mentioned by PG on 10/13 in the comments above. I did not see an answer for that issue. If I skip over this routine, it will finish the “befores” and “afters” but does not do the comparison.
The debugger shows that it stops on:
Private Function ListTables on the line:
For Each tbl In boTables
I think that boTables is not being created because if I put a “watch” on it, it is empty.
I am using Designer 3.1 sp4.
Thanks for any help you can provide, Linda
Hi, Linda, can you send me a copy of your universe for debugging purposes?
Dave,
I’ve added simple “On error resume next”/”On error goto 0″ workaround to the parts of code listing joins in contexts, and it allows me to run the tool also on universes with “broken” contexts – however, it can be seen that the list of joins are not really ok sometimes, they miss a lot of joins, so exact comparison is not 100% correct.
However, usually for contexts that are already broken they are broken on bot versions the same way, and the ones recently changed in one of versions are stored correctly (not causing any errors), so in fact it shows the contexts as modified on the change log, which might be good enough for some uses.
Hi,
I wanted to test or compare only the incompatability set in the universes and also the forced joins implemented in the universes.
please can you suggest me
Interesting note: I have another script that I used to manage contexts in a very large universe. It’s designed to drop and recreate contexts based on a set of rules that I have programmed into the code. The last time I tried to run it, I got the same style of error. After opening the universe and “touching” each context as described in the comments above, the script worked.
In this particular universe, there are a lot of contexts (over one thousand). The issue appeared after I deleted a couple of joins from the universe. During my debugging, I found out that a context thought it had 18 joins when in fact it only had 15. After touching the context it thought it only had 15. So I’m wondering if at least in this case the fact that there were so many contexts and so many joins that the process of deleting the joins just didn’t work cleanly.
In any case, the technique outlined here worked for me in that scenario as well.
praveen, there is code in place to compare the incompatibility settings but it’s turned off by default. When I ran it against one of my larger universes it blew out the row limit on Excel. If you have a small universe, you can follow the instructions in the “read me” to activate the compare for that element.
Forced joins… I assume you are asking about stub joins? They will be processed along with all of the other joins in the universe. I’m not sure what your question is there.
Hi,
I changed the config sheet Incompatibles part to Y.
and after the tool is run but could not find any part regarding the incompatability.
could you please suggest the sheet name.
the tool is very nicely built thanks so much…
Regards
Praveen
There are two steps. First you have to collect the information, that’s done by adjusting the value in column C. Next you have to specify which attributes you want to compare, which is done in column I. Columns F through L contain information that is used to know what all of the attributes are for each element being collected, and then which of those attributes can be compared across two versions of a universe. All of the incompatibility information is marked as “N” for compare options.
To be very honest, I have not done any testing on the comparison of incompatibility settings, so it may or may not work.
Dave,
I have a universe that takes a very long time to open just thru Designer itself (reasons for that are a discussion that is out of scope here). When opening this universe in the compare tool, it takes so long that Excel begins to complain with an error that says “Microsoft Excel is waiting on another application to complete an OLE action”.
I was able to fix this by turning off the alerts temporarily while the universe was opening. This was accomplished by adding the following code around the Universes.Open call in the FillWorksheet subroutine:
‘ Save current state of DisplayAlerts
Dim bDisplayAlerts As Boolean
bDisplayAlerts = Application.DisplayAlerts
‘ Temporarily turn off alerts
Application.DisplayAlerts = False
Set boUniv = boDesignerApp.Universes.Open
boDesignerApp.Visible = False
‘ Reset alerts to previous state
Application.DisplayAlerts = bDisplayAlerts
I don’t know if this is valuable for anyone else or not, but thought I’d pass it along just in case.
Thanks,
Alan
Hi Dave,
we are experiencing the “Runtime error ‘-2147417851 (80010105)’: Automation Error The server threw an exception” as well.
This tool could be very helpful in getting to the bottom of an issue at a client site. Client suddenly started to see the error message “Keydate Properties – Operation is not valid due to the current state of the object. (Error: INF)” when refreshing reports from Infoview (refreshing in edit mode works fine). We have an old copy of the universe from a few months back and we do not experience this issue with this older copy, hence the need to compare universes.
I’m not convinced that it is the changes to the universe that are the issue – the simplest of reports fail in the latest copy of the universe. The universe is either corrupted(??) or has simply become too big . Old version 2MB – new version 2.3MB
James
Hi Dave,
great Utility, great way to compare two universes. Thank you for making it available.
A few points after using it on our universes:
ExtractClass function
Testing initial value of iRowNum. This should be changed to:
If (iRowNum < 2) Then ' not blank row!Range(sSheet).ClearContents
End If
ExtractObjects function
Assumes a valid SELECT for each object, which is dangerous if a universe is out of date and the underlying table has been removed, renamed etc. This results in the error reported by others during the ListTables routine because boObj.Tables can be Nothing. For our universes I checked the obObj.Select property as follows:
If Left(boObj.Select, 8 ) = "!Unknown" Then Exit ForThis avoided the problem.
I am also getting the Automation error reported by others but this pops up already in the ExtractObjects routine. Still digging for the cause of this. With 2000+ objects, its a bit of a chore to step through!
Andrew
Thanks for the suggestions, Andrew. Any / all feedback is appreciated.
I’m curious as to your first suggestion though. All of the “extract” functions use the same logic. It’s designed to look at the row number being passed in, and if it’s on the initial pass through the function it clears out the existing data. Since some of the extraction routines are recursive the row count seemed to be an easy way to check to see if I was on the first iteration. You suggested changing <= 2 to < 2 but didn’t say why, and all of the other extraction routines use the same logic and you didn’t suggest changing those. Can you shed some additional light on this?
I like the suggestion for checking for invalid selects, good call on that one. Thanks.