Dave's Adventures in Business Intelligence

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 post. I said I would post a download link last week; I didn’t yet. 😳 It took longer than I expected to write up the documentation that I felt was required prior to the release. Part of that documentation has been extracted and published as this post instead as I don’t expect everyone will care or need to know about the background as to how this utility came about. However, it was important to me, so I wrote it. 🙂

Which brings up the philosophical question: if a blogger writes a post and nobody reads it, does it make the sound of one hand clapping? Or something like that…

Background and Purpose

Business Objects universes have always presented a challenge for people who want to track code changes and understand differences between versions. Universe information is stored in a binary file (.unv format) and is not readable by normal means. However, starting with Business Objects 4.0 the software included a basic scripting language (SBL) which allowed us to create some additional functionality. Starting with Business Objects 5.0 they switched to Visual Basic for Applications (VBA) provided by Microsoft. Both Desktop Intelligence and Designer had their inner workings exposed via a new software development kit (SDK).

Up until Business Objects XI the universe was stored in a set of relational tables in the repository. With a little research, it was not too hard to figure out where the various components of a universe were stored, and since everything was in relational tables many clients wrote universes and reports against this data. In some cases I even saw some folks that had created ways to check for differences and dependencies. However, when XI was released all of those utilities became worthless because the universe was no longer broken apart and stored in relational tables. We could no longer run reports that would show the content and structure of our universes.

Some years back Dwayne Hoffpauir released a VBA utility that provided a way to document a universe by extracting the various components into Microsoft Excel. This was in many ways the first step towards building a compare utility. Some enterprising folks were able to run the program against two different universes, then use third-party tools to identify the differences between the two workbooks. However, Dwayne’s utility was not really designed for this use, and in my opinion the third-party compare tools left much to be desired since they didn’t really “get” universes. For these and other reasons I decided to write my own utility that could be used to compare two universes.

First Release

The first version of this utility was completed in 2006 and was used in a Business Objects 6.5 project. This project required all programmers to check in a log of changes made before any code could be promoted. No matter how hard I protested that a universe wasn’t really code they would not relax this requirement. I thought about Dwayne’s utility, and used the concepts to create my own universe extract. The main difference was that mine was designed to extract classes and objects from two universes and then compare the results, all within an Excel environment. This first release had a lot of hard-coded elements and was not extendable as it was only designed to compare SQL differences between objects and predefined conditions. That meant that it could not easily be extended to compare joins, contexts, hierarchies, or any other universe element. It was successful as a proof of concept, but not viable as a long-term solution, therefore I did not publish this code.

Second Release

By 2008 I had completely rewritten the code. It was much more modular and included code to extract and compare classes, objects, tables, and joins. At this point I discovered that my method of comparing the “before” and “after” sets was not efficient enough to handle large universes. I was using Excel worksheets to contain the extracted data and trying to compare universe elements via their assigned ID. At first I tried to store each element in the row based on the assigned ID, but that was not very effective because there is no guarantee that ID’s will be sequential. The concept was sound: in order to look up an element in the “after” set in order to compare to the equivalent item in the “before” set I only had to check the row in the proper worksheet. While this was an effective way for matching elements that had a match, it was not an efficient way to track items that were new (existed only in the “after” list) or removed (existed only in the “before” list.)

Next I tried to create arrays to track the before and after sets, but while many universe elements have a system-generated key (objects, tables, and joins) many others do not (hierarchies, contexts, and others). That’s why this release only compared the four elements mentioned above; all of those elements have a system-assigned numeric key that could be used as an array index. Contexts and other elements didn’t have a numeric key so I had left them out for now. Of course it was important to be able to compare these elements so I started drawing on my experience with programming in other languages for inspiration.

In perl or PHP I can create arrays that do not have requirement of a numeric value for the array index. I started looking for a similar concept for VBA, and after some research I found the Microsoft Scripting Runtime library. This library allowed me to create arrays in VBA with a non-numeric key. In the previous version of the script I had written a specific routine to extract each component of a universe into a specific worksheet, then I had written another specific routine designed to compare the “before” and “after” sheets for that specific component. After rewriting this portion of the code to use the scripting library I was able to create a single subroutine that would do all of the compare operations. Ultimately I had plans to rewrite the extraction routines and replace all of them with a generic routine but I never got around to that.

Third Release and Conference Demo

In 2010 I finally had completed the parts of the code that I wanted to have ready in order to release it for others to use. I demonstrated it at the SBOUC. During the demonstration I took a risk. 😉 I asked for three suggestions from the audience as to what I should change in the “after” version of the universe, made those changes, crossed my fingers, and hoped that the script would capture everything. It did, and I felt good about the result and was fully intending to release it last fall.

Unfortunately I discovered that there were still some issues with the code when I returned back to work from the conference. Some universes were large enough that the list of elements (specifically the list of incompatible objects) was too large for the 65K row limit of the Excel host. One more tweak was required. The workbook already included a worksheet called “Config” that I intended to hide to prevent accidental edits. I left this sheet visible and added a list of universe elements and a Y/N flag. If the flag is set to “N” for a specific universe component, then that element will be ignored during the compare operation.

Now, here in 2011 I am finally releasing the code. 🙂 When? It could be any day now…