Dave's Adventures in Business Intelligence

Hi folks… a few days ago I posted an update via a comment on my universe compare tool page regarding a small bug. It’s not a huge deal, otherwise I am sure someone would have reported it by now. If for some reason you had a context that had zero joins then the program would not properly detect that and move on without generating an error. I posted what turns out to be a partial fix in the comment area.

While validating that change, I discovered that there are in fact two places where the code has to be updated in order to properly detect an empty context. I also found out that the process was not really finding changes in class properties at all! Oops. 😳 In order to handle the recursive nature of classes (class can contain sub-classes which can contain sub-classes and so on) the output row number for the target worksheet is passed as an argument. It seems that the first row was then cleared out, leaving the actual change log data to start on row 3 rather than row 2. (Row 1 is the column headers.)

Later on when the “detect changes” algorithm is called, it looks for a non-empty value in row 2. Given that this was never true for the class extract, changes in classes were never properly documented. The reason I suspect nobody called this out as a bug before is because the object change detection process was already capturing changes in class names, so at least that part was there. But if there were changes in class help text or visibility attributes or other class properties they were not being captured.

Given that there are several changes coming, I will be posting an update to the downloadable code within the next week or so.

As of today I don’t have any time to even make plans to create a similar version of this utility for .UNX universes created via the Information Design Tool.

Many years ago when I first started working with Teradata we had a challenge. The DBA team had defined a standard where each table was tagged to indicate the purpose of the table. Specifically, and development table would be called PROJ_D.TABLE_NAME and the equivalent table in production was called PROJ_P.TABLE_NAME. Why do this? In a Teradata connection I connect to a server, not to a schema (or instance as Oracle would call it). One of the DBA strategies was to use the QA or “system test” hardware as a preliminary DR (disaster recovery) platform. That means they keep a copy of the tables in PROJ_S and the same table exists as PROJ_P on the same server. In order to have specific queries sent to the database I had to include the schema name (or prefix) on every table name in my universe. During DR testing I could reset my schema from PROJ_S to PROJ_P without moving the universe (it’s still on the QA server) and now I would be using the production tables.

While this did provide the advantage of being specific, it also presented a problem during migrations because I had to change my code. I first wrote about this back in 2008 when I shared a utility that I wrote to make this process easier. (Read the details in the Using the Designer SDK to Ease Migrations post.)

With the advent of BI4 the new challenge is that we don’t (yet) have an SDK. At the same time we have become a much more mature Teradata shop. Our DBA team recently introduced a new strategy that eliminates the issue altogether, meaning I don’t have to worry about either of the issues listed above.

New View Layer

Our standard for reporting says that our tools (Business Objects included) never access the source tables. As a result, the naming convention I described above was used on all of the views that I used in my universe. Assume we have views called PROJ_D.TABLE1, PROJ_D.TABLE2, and PROJ_D.TABLE3. In the “old days” I would have to update each of these tables when I want to migrate my universe out of the development environment. Our new strategy seems simple on the surface, probably because it is :), but it solves both issues.

For each and every table that I am using we have now created a “semantic layer” view on top of the base view (which is on top of the base table). So for each of the above tables I know have this:

create view SEM_PROJ.TABLE1
as
select * from PROJ_D.TABLE1

The “PROJ” portion of the schema related to the project, so it remains as part of the view name. The “SEM” prefix is of course short for “semantic layer” and indicates that this view is used by some sort of external tool. What is missing from the new view name is the location tag (either _D or _S or _P for production). This seems like a very simple solution (and it is) but it took a while for us to get here. We have created the semantic layer views for one project so far, and it’s a real pleasure to be able to migrate a universe without touching it anymore. 😎 I anticipate we’ll be using this strategy for all of our Teradata projects from this point forward. Obviously the select clause changes in each environment, but finally I have a consistent view name in all environments.

When I have to reset my universe due to a disaster recovery exercise, it’s now up to the DBA team to re-point the semantic layer views to the proper schema. When I migrate the universe I no longer have to touch anything, except to perhaps change a connection. It’s a much cleaner process, and no longer requires me to be concerned about waiting for the full SDK to become available in BI4.

A user recently commented that outer join settings were not being properly captured by my universe compare tool. It turns out there is a very simple fix. If you have previously downloaded a copy of the tool, simply open the VBA editor and find this section of code:

aColTypes(dsCharacterColumn) = "Character"
aColTypes(dsDateColumn) = "Date"
aColTypes(dsNullColumn) = "Null"
aColTypes(dsNumericColumn) = "Numeric"
aColTypes(dsTextColumn) = "Text"
aColTypes(dsUnknownColumn) = "Unknown"

Immediately after that (or after a blank line if you prefer) add these new lines of code:

aJoinTypes(dsFullOuter) = "Full Outer"
aJoinTypes(dsNoOuter) = "No Outer"
aJoinTypes(dsOuterLeft) = "Outer Left"
aJoinTypes(dsOuterRight) = "Outer Right"

That is the missing piece that was causing the compare tool to skip recording the outer join types.

I will update the downloadable version and upload it shortly. For now, this will fix the bug.

There have been a couple of issues discovered with my universe compare tool. One has been fixed in the code, and the other appears to be a universe issue which I have not been able to decide how to approach. An update to version 2.0.1 has been posted, so please download this updated version if you have been experiencing issues.

Context Issue

The first issue was rather generic. The message was Automation error: The server threw an exception. It seemed to happen while the code was looping through a collection of joins or contexts for some universes, although it had never happened to me during my usage. (Of course, isn’t that always the case?) One user was able to send me a copy of their universe for my testing and I was able to recreate the error which is always the first step towards a resolution. Continue reading “Dagira Change Log Utility 2.0.1 Released”

The long-awaited release of my universe compare tool has finally happened. 🙂

Get it here.

Please post any support questions on the release page, and not on this blog post. The page will remain a permanent link on the top of my blog, while this post will eventually roll off of the front page. I hope that the utility proves to be useful, and that the wait was worth it. 😎

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… Continue reading “Universe Compare Tool – How It Came To Be”

This post details the system requirements and support plans for my universe compare tool which will be posted for downloading later this week. Comments are off for this post; any questions should be posted in the release topic, once it’s available.

System Requirements

Continue reading “System Requirements and Support Plans For Universe Compare Tool”

WordPress (the software that powers this blog) is released under the GNU GPL license. So is phpBB, the board software used to run BOB. The essential purpose of the GPL license is to provide software authors the rights of copyright and to provide software users with the freedom to do whatever they want to with the software. There is a FAQ provided by the FSF (Free Software Foundation) that addresses many questions related to the GPL but I would like to call attention only to a few specific items.

I want to get credit for my work. I want people to know what I wrote. Can I still get credit if I use the GPL?
You can certainly get credit for the work. Part of releasing a program under the GPL is writing a copyright notice in your own name (assuming you are the copyright holder). The GPL requires all copies to carry an appropriate copyright notice.

Simply put, this means that even though I am releasing code under the GPL I still retain the copyright to the code. Releasing it under the GPL ensures that anyone who downloads the code for use has the right to do so, and that those rights cannot be removed by someone else. If someone were to download my code and modify or improve it in some way, then the modified / improved version must also be released under the GPL, so that everyone can benefit.

If I add a module to a GPL-covered program, do I have to use the GPL as the license for my module?
The GPL says that the whole combined program has to be released under the GPL. So your module has to be available for use under the GPL.

That restates what I was saying earlier. By releasing my code under the GPL it protects everyone. Someone could take my program and turn around and try to sell it, but anyone who buys a copy – even if there are improvements – has the right to then distribute the software for free. Code based on GPL software must be licensed under the GPL, which grants the user the right to decide what to do with it, not the owner of the copyright.

What does this have to do with anything? 🙂 I just finished a project where I reworked a very large universe. During that project I used my universe comparison tool quite extensively, and I think the final testing is done. I will be posting a copy of the VBA code for download next week here on my blog. The software will include the GNU GPL license so that anyone is free to use it in any way they see fit.

Keep in mind that – unfortunately – there is already an expiration date on the software, as the BI 4.0 Information Design Tool does not initially ship with an SDK, and even when it does start to provide one it will likely be in java rather than visual basic. So enjoy it while it lasts. 😎

I have had a number of questions or requests related to the release date for my universe compare tool that I demonstrated at the SAP BusinessObjects User Conference last month in Orlando. I am happy to report that I have almost completed the “digging out from under” process that occurs every year after the conference season, and hope to be able to finalize the initial release. All I have to do is remove the logic that checks for object incompatibilities (related to Aggregate Navigation) as large universes contain too much data to fit in a standard (old version) spreadsheet.

Once that is complete, I will release the initial version here on my blog. I anticipate at this time that I will be able to do this early in December. Thanks for your patience.

One of the more interesting (and disappointing) tidbits I got from the folks talking about the new semantic layer (the Information Design Tool) coming in 4.0 is that there won’t be an SDK in the initial release. There may be one coming later, but it will potentially be java based rather than VBA. That means that my VBA experience is going to be less useful, and tools like my Schema Change utility and the soon to be posted Universe Change Log script that I showed at the 2010 BusinessObjects User Conference will soon not only be obsolete but we won’t have a mechanism to replace them. 😯

While on the subject of the SDK… I was asked multiple times when I would be posting my script from the conference. I discovered a few days before the presentation was due that there is a problem with joins when switching from one database to another. For example, we are in the process of switching one of our larger datamarts from DB2 to Teradata. I am going to write a more detailed blog post on that shortly, but let me say that my universe compare script was very useful during the process. But during the compare process I found that this join:

table1.table_1_id = table2.table_2_id

Got changed to this:

table2.table_2_id = table1.table_1_id

Of course both joins are functionally the same. But to my script they showed up as three different changes. Table 1 was changed from “table1” to “table2” while Table 2 was changed in reverse. And of course the join logic was reversed as shown above. So while this was not really a change, my script was recognizing three unique differences between the two universes, all related to that one join. I had not encountered this when running against two different versions of the same universe pointing to the same database, which is the expected use of the tool. I created it primarily to compare the DEV and PROD versions of the same universe. But I am trying to think about a way to update the code so that inverted or reversed joins are not detected as changes. If I can’t come up with something in the next few weeks, I will go ahead and post the code as I demonstrated it at the conference last week.