Dave's Adventures in Business Intelligence

The full client applications (Business Objects aka Desktop Intelligence) have had VBA (Visual Basic for Applications) for quite some time. The initial release of 4.x included a scripting language that was “like” VBA but was not quite the same. When 5.x was introduced they switched to Microsoft VBA. I have written more than a few VBA utilities over the years, some of which are published on the Integra Solutions library page. Today I want to share a utility that I put together to help migrate universes in a Teradata environment. I should point out that this utility does not require that you use Teradata; it can be used in any database environment where you need to do a mass-update to the owner or schema name.

Executive Summary

This utility is designed to automate the update of the schema or owner name in a universe. The host application is Microsoft Excel since the Universe Designer application cannot be a VBA host. You enter the “From” and “To” schema names into cells in the XLS and run the macro. At completion you will have a universe where the schema (or owner) has been updated.

Explaining the Problem

When I build a connection to Teradata, I do not connect to a database. I connect to a server. That server could be the host for more than one database or schema. That means that for my universe I have to fully qualify the tables as owner.tablename rather than referencing the tablename by itself. This presents a problem because my schema names change during the migration process. And most migration managers frown on editing code during the migration process. 🙂

In this particular case the schema name is a short abbreviation of the project (I will use PROJ for this blog post) followed by a letter that designates the purpose of the schema. For example, _D is the development schema, _S is the System Test schema, and _P is for production. A table that is named PROJ_D.tablename in development will become PROJ_P.tablename in production. That’s a code change.

First Solution

If you have a simple universe you can highlight all of the tables, right-click and select the “rename table” option from the mouse menu. With this technique you can update the owner for every single table in the universe in one step. But what if you have aliases as well as regular tables? In that case, you have to selectively highlight all of the tables (ignoring the aliases) and rename only the source tables. The aliases will inherit the change.

The easiest way I have found to do this is to use the table listing on the “List Mode” panel that you can show on the top of the screen. That way all of the tables with the same owner are listed together, and rather than use the graphical structure to rename my tables I can use a list. This works well, and solves the problem.

Until you introduce Derived Tables into your universe. 🙂

Second Solution

When you use a derived table those tables don’t really have owner names since they exist only in the universe. But they do reference tables in the database, and as discussed above, they will include the schema or owner name. In order to migrate derived tables correctly, I had to open each one and update the SQL manually. This was a problem.

The main issue is that I was changing the code during a migration. That means that a bug could be introduced. And introducing bugs during a migration (after system testing has been completed) is not a popular result. 🙂

So what I did to solve this issue at first was to create an Excel spreadsheet that contained the SQL code required for each derived table. I created formulas where the schema name was a reference to a single cell in the sheet. To migrate a universe I would open this XLS, update the schema name in one place, and see that schema name updated in each different SQL statement. It becomes a simple copy / paste operation during the migration rather than an “edit” operation. This was certainly safer, because the code itself was verified and the only thing that was being updated was the schema name. But there were still problems.

For example, at one point we had someone new join our team, and their video resolution was smaller than mine. What does the video setting have to do with this? Smart question. 😆 The issue was on my screen I would see seven derived table definitions. On their screen, because it was smaller, they only saw five. That meant when they processed the instructions in the spreadsheet they only updated five of the seven derived tables, with the net result being we got code in production that was not working.

Automate the Process

So now, finally, the VBA solution. The advantage of VBA (or any programming language) is that it’s going to do exactly what you tell it to do. It won’t do half of the derived tables and skip the rest. It won’t accidentally miss a table during the selection process. If you set up a program loop and tell it to process every table, that’s just what it will do.

The Code

The code is contained within a host application since Designer cannot host the code itself. I selected Excel as my host which is quite common. Many of the utilities on BOB’s Downloads do the same. I will detail the code here, and the entire file is presented as an attachment at the end of this post. Disclaimer: This code should be considered “beta” quality at this time. This means it is “use at your own risk” and make backups and all that good stuff.

Getting Started
First there are some basic settings and declarations that appear at the top of every Business Objects utility that I write.

Option Explicit                 ' Require variables to be declared before being used
Option Base 1                   ' Start array indexes at 1 as I think better that way

Dim boDesignerApp As Designer.Application
Dim boUniv As Designer.Universe
Dim xlSheet As Excel.Worksheet

Next there is the main routine. In most cases my main routines are very short, often (as in this case) there is nothing in them except for function or subroutine calls. This means that the main routine reads more like a process flow and less like actual code. I think it’s easier to manage that way.

Sub Main()
    
    Set xlSheet = Worksheets(1)
    Call UpdateSchema
    MsgBox ("Done")
    
End Sub

The first line of code is a shortcut and would not be in the final code. I use it so I have a reference to the first sheet in the workbook where the schema names appear. I generally try to provide all of my input variables via excel cells rather than requiring a user to answer a prompt (because I’m lazy) or editing the code (because of the potential for mistakes).

Next, the function used to update the schema names. The “Private” declaration means that you cannot run this macro function directly. It can only be called from other code.

Private Sub UpdateSchema()

The next lines are declarations for variables that I need. The Designer reference is going to give me a handle for the application, and the tbl item will point to the collection of tables in my universe once it is open.

    Dim tbl As Designer.Table
    Dim sSchemaFrom As String
    Dim sSchemaTo As String

Those lines of code retrieve the “from” and “to” schema from the appropriate cells in the worksheet. Minor point: if you use this technique, I strongly suggest that you use named ranges rather than absolute cell references like “A1” as it makes the code much easier to maintain.

    sSchemaFrom = xlSheet.Range("SchemaFrom").Value
    sSchemaTo = xlSheet.Range("SchemaTo").Value

Next, log in to Designer. The code to do this is different depending on whether you’re using versions 6.5 or earlier or XI. Both syntaxes are shown here.

    ' Establish a Designer session and log in
    Set boDesignerApp = New Designer.Application
    boDesignerApp.Visible = True
    
    ' Use this method instead for older versions
    ' Call boDesignerApp.LoginAs
    Call boDesignerApp.LogonDialog

Next, open the universe. Since I don’t specify a universe name, I will be able to interact with the standard “File – Open” dialog box provided by Designer.

    Set boUniv = boDesignerApp.Universes.Open

After opening the universe it is time to process the tables. This next block of code loops through each table in the Tables collection. There is an attribute called IsAlias that is true if the table is an alias rather than a true source table. If that flag is true, then there is no processing done on that table. The “debug.print” command sends output to the debug window; a user will not see that information.

    boDesignerApp.Visible = False
    
    For Each tbl In boUniv.Tables
        If tbl.IsAlias Then
            Debug.Print tbl.Name & " is alias"

Next, if the table is a derived table I have to update the schema name in the SQL. There is an called IsDerived that will be true if the table is a derived table. If it is true then the attribute SqlOfDerivedTable contains the SQL code.

One note: if the update fails the derived table will still exist but it will be in an invalid state. In this case what I have found is that a universe integrity check will fail in catastrophic fashion. 😆

        ElseIf tbl.IsDerived Then
            ' Update derived table SQL here
            Debug.Print tbl.Name & " is derived"
            tbl.SqlOfDerivedTable = str_replace(tbl.SqlOfDerivedTable, sSchemaFrom, sSchemaTo)

Finally, if it’s a normal table then the Name attribute contains the full name, which includes the schema. I update the name attribute then loop to the next table in the collection.

        Else
            ' Update table owner
            Debug.Print tbl.Name & " is table"
            tbl.Name = str_replace(tbl.Name, sSchemaFrom, sSchemaTo)
        End If
    Next tbl

Finally, save the universe and exit.

    boUniv.Save
    
    ' Release the Designer app and recover memory
    boDesignerApp.Quit
    Set boDesignerApp = Nothing

End Sub

This code is fairly simple, once you know which attributes (they are actually called “properties”) are available. The last bit of code is the str_replace() function. There is no function named str_replace() in VBA by default, but there is a Replace() function. So why did I create something brand new, if all I am going to do is call a built-in function? It’s a good question.

There are many advantages to creating a function wrapper. First, if I need to port my code to a different version of VBA there might not be a Replace() function. By creating my own function call I can write a replacement function without touching the rest of the code. The more important advantage, however, is that I can put extra code inside my function. Here is what my str_replace() function looks like right now:

Private Function str_replace(ByRef sSql As String, sFrom As String, sTo As String) As String

    sSql = Replace(sSql, sFrom, sTo, 1, -1)
    str_replace = sSql
    
End Function

As you can see, there’s not much going on other than a basic replacement of text. But what I thought about doing is writing a log to the Excel worksheet showing the “before” and “after” state of each table that gets processed in the loop. That will help create an audit trail and make debugging easier as well. At the moment that code does not exist. But by creating a wrapper for the Replace() function I can do that.

I use this technique all the time in various programming languages, not just VBA. It has served me well in the past.

So that’s the end of the code. What, you thought there was more? 😉

Conclusion

I’ve done only very preliminary testing for this utility so far, but it seems to work just fine. I expect that it will really help my current client with their Teradata universe migrations.

Downloads

The following utility is released under the GPL or Gnu Public License. This means that you have the freedom to do whatever you like with the code but it must retain my copyright. There are no licensing costs associated with use in a corporate or any other environment. No warranty is expressed or implied.

• Dagira Schema Change Utility