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
Invaluable tool, Dave. Any chance of updating it for BO 4?
It will probably work for BI 4 simply by switching the library reference, but know that it will only work on .UNV files. There is no SDK support for the Information Design Tool (IDT) .UNX files.
I too was receiving the Run Time error ‘-2147417851 ( 80010105)’ on Contexts. My solution was to open each context and “Check” it. I did not have to remove and add any joins. I guess asking Designer to check the context cleans up the inconsistency that this fantastic tool doesn’t like.
Kudos Dave for creating and maintaining this tool!!
Hi Dave,
I am facing the same issue Tommy reported here a year back.
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 in FillWorksheet sub-routine
Going to the debug excel highlights this row “Set boDesignerApp = New Designer.Application”
I do have reference to Designer 12.0 Objects library.
My Installation Details:
BO 3.1 SP1
Windows 7
MS Excel 2010
I went through the forums and downloaded and installed hotfix Windows6.1-KB983246-v4-x86.msu, did not solve the issue. Any help is greatly appreciated.
Tool does not work with Windows7/Office 2010; geeting 430 runtime error. Please let me know how to solve this issue.
You may need to review your security settings. Also I believe someone else suggested making sure to save the macro with a .XLSM (macro) file type.
Please save the file as .XLSM to enable macro execution, and you may also need to check your security settings. I will be testing this shortly and post a fix if this does not do it. Thanks for the input.
Hey Dave,
I ran it on the same universe for both before and after (I’m not really looking for comparison, just documentation of a universe) and it’s not detecting whether a join is outer. There are several outer joins in the universe but the “Outer” column of the “BeforeJoins” and “AfterJoins” tabs is blank. So I ran it again with two other different universes and that column stayed blank. I would think the issue is with getting the value in the following line of ExtractJoins(), which looks alright but I’m not familiar enough with VB or the BO API to say definitively:
xlSheet.Cells(iRowNum, 8) = aJoinTypes(boJoin.OuterJoin)What database are you using? I am quite certain that it works as expected on Teradata. Is the join in question a complex join, where the outer join checkbox is grayed out, or is the checkbox marked as expected?
[Edit] I ran my latest code, and you’re correct. It does not properly capture the outer join setting. I’ll look into it and post an update / fix in the main page. Thanks.
Bug fix posted here and will upload the downloadable zip file shortly. Thanks for the bug report!
Hi Dave,
Any updates on error mentioned for error..run-time error ‘430?: Class does not support automation or does not support expected interface.
I have even changed the library files to point to BO 4.0 Library files.
My Installation Details:
BO 4.0 SP04
Windows 7
MS Excel 2010
Thanks,
Chitra
On Windows 7 I had to use “run as administrator” on the Designer application in order to overcome this issue.
Dave,
I am looking for the Macro which can list out the restricted objects in the restriction sets.I am doing object level security I have 40 resriction sets and each set needs to restrict atleast 100 objects.My business wants to see list of restricted objects for each restriction set.If you know the solution please let me know.
I am not aware of anything like this. I haven’t every looked, and don’t know if this is even available in the Designer SDK.
Hi Dave,
I can’t add “BusinessObjects 6.5 Object library” to VBA code. Although i pointed to its correct location under Business Objects installation folder, but it not added 🙁
I have Windows XP, Microsoft Excel 2007, and BO 6.5.1
Please advice.
I don’t think this code will be backwards compatible with the libraries from 6.5.
Hi Dave
As described in your post I have Microsoft Scripting Runtime in C:\WINDOWS\system32\scrrun.dll folder
and Business Objects 12.0 Object library
When I run this script , It does not work for me and I am getting error :
Run Time error ‘430’
Class does not support automation or does not support expected interface
PS : I have excel 2010 and BOXI 3.1 client on my machine
Any idea what’s wrong ?
Thanks,
Saurav
The “class does not support automation” error is generally related to operating system permissions. If you run Designer at least one time as Administrator it sometimes clears that up.
I discovered a bug in the compare code today when I tried to process a universe with contexts with zero joins. It threw an error during the process to capture the joins for the context. Here’s an easy fix. Open the code in the VB editor. Find this:
For Each boContext In boContexts strJoinSet = "" iRowNum = iRowNum + 1 Set boJoins = boContext.Joins For Each boJoin In boJoins strJoinSet = strJoinSet & ", " & boJoin.ID Next boJoinReplace it with this:
For Each boContext In boContexts strJoinSet = "" iRowNum = iRowNum + 1 Set boJoins = boContext.Joins If boJoins.Count > 0 Then For Each boJoin In boJoins strJoinSet = strJoinSet & ", " & boJoin.ID Next boJoin End IfBasically all it does it do a quick check to make sure the context has at least one join before trying to extract the information.
Hi Dave,
I have the same “Server threw an exception” error as described in the previous posts. I tried “touching” the joins in the contexts, as described, but it did not resolve the issue.
I have this to suggest, though:
While trying to resolve the same kind of error with another macro, I “fixed” the issue by having the description of the joins and contexts being not empty….. I seems as if there are no description in the context or join, it fails to get the description correctly.
Maybe it it the same issue here?
Hi Dave,
As everyone experiencing same issue on “Runtime error ‘-2147417851 (80010105)’: Automation Error The server threw an exception”, I do see the same issue on context side.
I’m scare to touch context because I don’t no where to start from. Can you please help on how to resolve context issue, and also I have linked universes (around 6) to the main universe.
Hello Dave,
Dwayne referred me to your site. Thanks for creating such a useful tool.
Questions:
1. I ran your tool to compare 2 universes: The After Conditions tab shows a new condition was added in the after universe, but it did not show up in my Change Log. What would be the reason that it is not captured in my change log? I did get the “Done” dialog box so it executed successfully
2. I would like to use this tool to compare universe in XI3.1 against the upgraded BI4.1 universe – Do you have a version of this tool that will allow login’s into Designer and Universe Design Tool?
Thank you.
Hi Dave,
I’m getting the below when try to run this code. Do you know how it needs to be fixed?
“MISSING: Business Objects Designer 12.0 Object Library”
Hi,
This is very good tool. Thank you for sharing. it saved my testing time a lot.
Thank you,
Anne S.
Hello Dave,
I am a big fan of your blog and work.
We use this comparison tool a lot , recently we migrated to BO4.1 and this tool stopped working.
Is there any change this tool is available for Bo4.1 ?
Regards,
Kedar
Hi Dave , Thanks for this great tool however it runs successfully but there are no changes in the change log tab.I have deleted one class in my Before universe and that same class is available in the after universe. In (befoe and after)Class tab and (befoe and after)object tab the count is coming correct.
Please advice.
I’m getting the 430 Automation error too. Ran Designer as Admin, Ran Excel as Admin. No luck. Any other thoughts?
Thank you!!!
Hi Dave,
Is there same tool for BO 4.1 Universe Comparision? It works perfectly fine with 3.1 universes but throws an exception for BO 4.1
Thanks
Pawan
I have not tested for 4.1, but it should work fine as-is. You will need to update the library reference so that it uses the proper version. If you have already updated the references and you’re still getting an error, please try it on different 4.1 universes and see if you continue to get errors. I’ve seen universes where contexts get some sort of corruption in them; there is a post or comment somewhere prior to this that discusses that issue and how to fix it.
Kathy, it has been a while since I had to deal with this. I don’t remember the exact sequence that solved it, but it’s a Windows permissions issue. Once it’s fixed, it’s fixed, and that’s why I don’t remember it… 😳
Amrendra, I’m behind on answering comments, are you still having this issue? If it’s possible, could you send me the XLS that contains the results of the two universe scans? I don’t need the universes themselves, just the scan results if possible…
Kedar, I have access to BI 4.1 in a sandbox environment here at work. I will test the macro against that environment and see what happens. I didn’t really anticipate issues between 4.0 and 4.1 other than making sure you have updated to the proper library references.
Hi, Dana, it means that you don’t have the expected Business Objects libraries installed on your computer. You need to have Designer installed on your system in order to use this tool, and it has to be the expected version.
Bee, I don’t currently have a tool to compare across versions (3.x to 4.x). What I would suggest is that you simply open / save the 3.x universe in 4.x but keep it only locally. This tool requires only a local copy of the universe. Then you can run against the 3.x and 4.x version using only 4.x Designer to process the universes. That’s how I have done it for myself.
Madu, “touching” the context is as simple as opening the context, pick a join (any join!) and remove it from the context and then add it right back without closing the context window. Essentially you are not changing anything, you’re just getting Designer to “rewrite” the context definition and that has always fixed the issue for me. It’s a very safe operation.
I am getting error “Can’t find project or library”
There are several libraries required to run this. Most are standard Microsoft, but you must also have the Designer application installed on your system.
Initially when I used this tool on a 4.x version , it was not able to use the 4.x designer libraries (though I manually selected the 14.0 libraries). I have seen posts above with similar issues – so thought I should post the way I got it working.I reinstalled 3.x on my machine and then it picked up the required libraries. It still uses 3.x designer to open the 4.x universe. But my purpose is served – I can compare 4.1 universes now.
This is an amazing tool Dave. Appreciate the amount of time you have saved for all of us by sharing it !
Our primary environment is still 4.0, which for me works fine. I have not tested 4.1. We’re in the process of moving to 4.2 and I will soon test on that version. Thanks for the feedback.
Will this tool work with BO V4.2 / 14.2.3.2277
Thanks.
It will work on .UNV files only.