Sep 14 2011

Dagira Universe Compare Tool

Categories: General Dave Rathbun @ 3:53 pm

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.
  • 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. 8-)

    Other Related Posts

    Related Links

90 Responses to “Dagira Universe Compare Tool”

  1. Comment by Yoav

    Works Great!

    Thanks for sharing this great tool!

    Regards

    Yoav

  2. Comment by Dave Rathbun

    Hi, Yoav, thanks for the report of a successful run.

  3. Comment by JB

    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

  4. Comment by Dave Rathbun

    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.

  5. Comment by Tommy

    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?

  6. Comment by Dave Rathbun

    Hi, Tommy, I’m assuming you have Designer installed on your system? What version?

  7. Comment by Ray

    Hi Dave,

    Can you please post what changes need to be made to have your tool work for XIR2 universes?

    Thanks,
    Ray

  8. Comment by PG

    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 boTables

    of the private function
    Private Function ListTables

  9. Comment by Tommy

    I 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.

  10. Comment by Dave Rathbun

    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?

  11. Comment by Ray

    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.

  12. Comment by Smriti

    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.

  13. Comment by tommy

    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.

  14. Comment by Matt

    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 boJoins in the ExtractContexts(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 boJoins in the ExtractContextDetails subroutine. Commenting out both subroutines allows it to finish running.

    I’m probably setting up something wrong, thanks for your help.

  15. Comment by Dave Rathbun

    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.

  16. Comment by mike

    Hi Dave,
    I’m also looking for an XIr2 version of the compare tool. Has that been made available? Thanks!

  17. Comment by Dave Rathbun

    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. :)

  18. Comment by Leszek

    Hi Dave,

    I think the issue with Runtime error ‘-2147417851 (80010105)’: Automation Error The server threw an exception on some ForEach boX in boXs happens 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 a boJoins.Count() on the collection, it gives you 5, but valid indexes when I try to use boJoins.Item(n) are for example 1,2,3,4 and i.e. 7, not 5. Thus, ForEach fails as it happens to get null/nothing when expecting valid collection element.

    Happens for me on every universe I’ve tried :-(

  19. Comment by Dave Rathbun

    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.

  20. Comment by Ray

    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 exception

    All our universes have contexts and some of them are linked universes.

  21. Comment by Dave Rathbun

    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.

  22. Comment by Leszek

    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…

  23. Comment by Matt

    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

  24. Comment by Dave Rathbun

    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.

  25. Comment by Dave Rathbun

    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.

  26. Comment by Ton Bunnik

    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.

  27. Comment by Dave Rathbun

    Ton, thank you for the feedback. I assume you were able to go “touch” the contexts and get past the issue then?

  28. Comment by Ton Bunnik

    Thanks Dave , yes the ‘touch’ solved it.

  29. Comment by Linda Caron

    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

  30. Comment by Dave Rathbun

    Hi, Linda, can you send me a copy of your universe for debugging purposes?

  31. Comment by Leszek

    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.

  32. Comment by praveen

    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

  33. Comment by Dave Rathbun

    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.

  34. Comment by Dave Rathbun

    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.

  35. Comment by praveen

    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

  36. Comment by Dave Rathbun

    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.

  37. Comment by Alan

    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

  38. Comment by James Halligan

    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

  39. Comment by Andrew Poulter

    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 For
    This 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

  40. Comment by Dave Rathbun

    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.

  41. Comment by Arun Gurumurthi

    Great tool ………

    Is there a tool or way to map this to all the reports which are refrenced by the universe, objects …

  42. Comment by Dave Rathbun

    There are some 3rd party tools that do this. There are also some items in the “BOB’s Downloads” area that you might check out.

  43. Comment by Paul Sims

    This is great but it took me a huge amount of time to find a way around the run-time error “‘430?: Class does not support automation or does not support expected interface” that I was getting when I was trying to run this on my Windows 2008 R2 Server with Excel 2007 and XI3.1 SP4. The solution was simple in the end. I needed to run Excel as Administrator, i.e. right click on Excel and select Run as Administrator. Hopefully someone having this problem won’t have to spend the amount of time I did trying to resolve it.

  44. Comment by Rajesh

    I am getting an error msg, when I am trying to run the utility; I am using Business Objects X1.3.1 and oracle 11 g. Please take a look at the below where I am getting an error msg. Thanks for your help in advance.

    Run Time error ‘-2147417851 ( 80010105)’:

    Automation error
    The server threw an excepotion

    ‘ This function takes a Tables argument and returns a comma delimited
    ‘ string containing a list of all of the tables included in the array

    Private Function ListTables(ByRef intTblCount, boTables)
    Dim tbl As Designer.Table
    Dim sTables As String

    If intTblCount > 0 Then
    For Each tbl In boTables
    sTables = sTables & “, ” & tbl.Name
    Next tbl
    sTables = Mid(sTables, 3)
    Else
    sTables = “No Tables”
    End If

    ‘ Remove the first comma from the list of tables
    ListTables = sTables

    End Function

  45. Comment by Dave Rathbun

    I suggest trying it on different universes and see if it behaves the same way. Also check to see which object (or objects) are failing; do they have table assignments? You’re going to have to do some debugging on your own since I don’t have your universe. :)

  46. Comment by Rajesh

    Thanks for your reply Dave, actually the other universes are working fine, i am only having a issue in that universe, can you please take a look at the universe, if yes, can you please provide me your email? Thanks for your help.

  47. Comment by Dave Rathbun

    I’m afraid I don’t have time for the next several weeks. What I have seen so far when the “server through an exception” message comes up is there is something that is partially corrupted in the universe. For example there was an issue with contexts that was addressed earlier in the comments. If you step through the code while it’s trying to process the tables, then perhaps you can identify which table is causing the fault and investigate from there.

  48. Comment by Bobby

    Is there anyway that we can compare ‘n’ number of universes in one go and spill out the results in Change log(Excel). Your help is really valued and appreciated. Thanks.

  49. Comment by Dave Rathbun

    Comparing two universes is complicated enough! :) As I’m sure you’ve noticed, there is a worksheet for each universe component in both “before” and “after” versions. To compare “n” universes would require “n” versions of each worksheet. Those worksheets could be generated automatically, of course. But where the challenge comes in would be the comparison algorithms. Comparing two universes means compare A to B. Comparing three universes would increase the complexity quite a bit (A to B, B to C, and A to C) and four could be even worse (A to B, A to C, A to D, B to C, B to D, and C to D). If I had four iterations of a universe (A, B, C, and D) I would simply run the utility comparing A to D and ignore B and C.

    I’ve never needed to do this, and I don’t think I will try to rewrite the code to support this.

  50. Comment by Bobby

    Dave..Thanks for the reply..Well, I believe I haven’t explained it in a proper way. Let me put in this way. I just need to automate the tool so that it can compare different set of universes at a time i.e I want to compare A to B and C to D and so on..(A and B are same universes but residing in Development and Prod Servers).
    I would like to compare ‘n’(A-B, C-D, …) DIFFERENT set of universes at a time. May be I can make use of a batch script(Unix) to call this tool providing all the credentials in the script itself. So that it need not authenticate each and every time. Its just a thought..Don’t know whether I am correct or not. Correct me if am wrong. Any help would be really great. Thanks.

  51. Comment by Pedro

    Invaluable tool, Dave. Any chance of updating it for BO 4?

  52. Comment by Dave Rathbun

    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.

  53. Comment by Larry

    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!!

  54. Comment by Reji Sebastian

    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.

  55. Comment by Reji Sebastian

    Tool does not work with Windows7/Office 2010; geeting 430 runtime error. Please let me know how to solve this issue.

  56. Comment by Dave Rathbun

    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.

  57. Comment by Dave Rathbun

    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.

  58. Comment by Pedro

    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)

  59. Comment by Dave Rathbun

    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.

  60. Comment by Dave Rathbun

    Bug fix posted here and will upload the downloadable zip file shortly. Thanks for the bug report!

  61. Comment by Chitra Keluskar

    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

  62. Comment by Dave Rathbun

    On Windows 7 I had to use “run as administrator” on the Designer application in order to overcome this issue.

  63. Comment by Ravi

    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.

  64. Comment by Dave Rathbun

    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.

  65. Comment by biPlanet

    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.

  66. Comment by Dave Rathbun

    I don’t think this code will be backwards compatible with the libraries from 6.5.

  67. Comment by Saurav

    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

  68. Comment by Dave Rathbun

    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.

  69. Comment by Dave Rathbun

    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 boJoin

    Replace 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 If

    Basically all it does it do a quick check to make sure the context has at least one join before trying to extract the information.

  70. Comment by Philippe Chayer

    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?

  71. Comment by Madu

    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.

  72. Comment by Bee Tuz

    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.

  73. Comment by Dana

    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”

  74. Comment by Anne

    Hi,
    This is very good tool. Thank you for sharing. it saved my testing time a lot.

    Thank you,
    Anne S.

  75. Comment by kedar

    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

  76. Comment by Amrendra

    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.

  77. Comment by Kathy Olsen

    I’m getting the 430 Automation error too. Ran Designer as Admin, Ran Excel as Admin. No luck. Any other thoughts?

  78. Comment by Rams

    Thank you!!!

  79. Comment by Pawan

    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

  80. Comment by Dave Rathbun

    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.

  81. Comment by Dave Rathbun

    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… :oops:

  82. Comment by Dave Rathbun

    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…

  83. Comment by Dave Rathbun

    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.

  84. Comment by Dave Rathbun

    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.

  85. Comment by Dave Rathbun

    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.

  86. Comment by Dave Rathbun

    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.

  87. Comment by M Nemani

    I am getting error “Can’t find project or library”

  88. Comment by Dave Rathbun

    There are several libraries required to run this. Most are standard Microsoft, but you must also have the Designer application installed on your system.

  89. Comment by Shirisha

    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 !

  90. Comment by Dave Rathbun

    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.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

     **         

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.