Sep 14 2011
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.
Download link: Universe Compare Tool Download (360K Zip)
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.
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.
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.
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
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
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
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 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