Sep 14 2011

Dagira Universe Compare Tool

Categories:  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. 😎

    Other Related Posts

    Related Links

92 Responses to “Dagira Universe Compare Tool”

  1. Comment by Pedro

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

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

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

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

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

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

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

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

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

  10. Comment by Dave Rathbun

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

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

  12. Comment by Dave Rathbun

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

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

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

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

  16. Comment by Dave Rathbun

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

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

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

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

  20. 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?

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

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

  23. 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”

  24. Comment by Anne

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

    Thank you,
    Anne S.

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

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

  27. 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?

  28. Comment by Rams

    Thank you!!!

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

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

  31. 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… 😳

  32. 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…

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

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

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

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

  37. Comment by M Nemani

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

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

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

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

  41. Comment by Emmanuel Davo

    Will this tool work with BO V4.2 / 14.2.3.2277

    Thanks.

  42. Comment by Dave Rathbun

    It will work on .UNV files only.

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.