Mar 06 2008

Using the Designer SDK to Ease Migrations

Categories: Universe Design, VBA Tools Dave Rathbun @ 6:31 pm

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

        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.

25 Responses to “Using the Designer SDK to Ease Migrations”

  1. Comment by nod

    Thanks for that Dave, I’ve encountered plenty of projects where they introduce a new “standard” when we are halfway through, and suddenly we need to change schemas or owners.

  2. Comment by sanj

    I’ve got 50 universes to migrate onto XI. However they all have the inherited problem of a missing table owner. I am therefore using this tool to change from a “blank” schem name and get the following runtime error of “Table already exists. (UNV0028).”

  3. Comment by Dave Rathbun

    Hm. I have never actually tried to run it with a blank (empty) table name for the source. I’m not sure that would work, since the string replacement logic needs to find something to match before a replacement takes place. I suspect it is trying to rename the table right back to the original name, thus, the error.

  4. Comment by ann

    How to create conditions in BO designer? and what is the purpose of that?

  5. Comment by Dave Rathbun

    Hi, ann, it seems that your question doesn’t really have anything to do with this post. If you have general questions that are not related to the subject of a post, I would like to suggest that you try posting them on BOB instead, thanks.

  6. Comment by cs

    Hi Dave,

    I tried to use this utility for my universe. It works fine to change owner names in derived tables. But I have some complex joins (around 147), I dont see the owner name changes applied there. Do we have any workaround for this? Thanks well in advance.

    Regards,
    CS

  7. Comment by Dave Rathbun

    Hi, cs, and welcome to my blog. Joins should be referencing the table names by table “ID” and therefore should automatically be updated. There is no attempt by this script to update joins, as it should not be needed. I have never run into this issue.

    If you can define more what you mean by “complex join” and perhaps I can try to reproduce the issue and determine a fix.

  8. Comment by cs

    Thanks Dave.
    I have complex joins conditions where in the comparison I have In Clause and subquery (basically self join to restrict rows of a table – where clause)
    e.g.
    FINANCEBVDB_TS3.BV_BOOKINGS.BOOKINGS_PROCESS_DATE IN (SELECT CALENDAR_DATE FROM FINANCEBVDB_DV6.BV_FISCAL_DAY_TO_YEAR WHERE (CURRENT_FISCAL_QTR_FLAG =’N’ AND CALENDAR_DATE <=DATE
    AND
    EXISTS (SELECT 1
    FROM FINANCEBVDB_DV6.BV_IAM_USR_ROLE_BIZ_ATTR_MAP A JOIN FINANCEBVDB_DV6.BV_IAM_USER B
    ON A.IAM_USER_KEY=B.IAM_USER_KEY
    WHERE B.CEC_ID=@Variable(’BOUSER’) AND BUSINESS_DATA_ATTRIBUTE_NAME ‘TIME~CQ’)
    )
    OR
    EXISTS (SELECT 1
    FROM FINANCEBVDB_DV6.BV_IAM_USR_ROLE_BIZ_ATTR_MAP A JOIN FINANCEBVDB_DV6.BV_IAM_USER B
    ON A.IAM_USER_KEY=B.IAM_USER_KEY
    WHERE B.CEC_ID=@Variable(’BOUSER’) AND BUSINESS_DATA_ATTRIBUTE_NAME = ‘TIME~CQ’)
    )

  9. Comment by cs

    Also, I have filter conditions that have subquery, which doesnt get the owner name change after running the macro.

  10. Comment by Dave Rathbun

    Ah, in that case the table referenced in your join (sub-query) doesn’t even have to appear in the universe. It’s not going to get updated when a table is renamed. What would have to happen is that this macro would need to process every join expression as well as every table name in order to catch those.

    I believe it’s certainly possible, but I’m not going to have time to play with the idea right now.

  11. Comment by Dave Rathbun

    cs, if you’re still out there :) I have a fix for you. It turned out to be quite simple. Open the macro code and add the following lines:

        Dim join As Designer.join
        For Each join In boUniv.Joins
            join.Expression = str_replace(join.Expression, sSchemaFrom, sSchemaTo)
        Next join

    These lines should be added immediately after the code that updates the table names… meaning right after these few lines of code:

                If (sTableFrom <> sTableTo) Then
                    tbl.Name = sTableTo
                End If
            End If
        Next tbl

    Once you do that, your complex joins should also get updated. I finally had a need to create a complex join (with a sub-query) and so I had to figure out how to update the code. I will attach an updated version to this post when I get the chance, but the code changes are quite simple.

  12. Comment by CS

    Thanks Dave.

    Fallen into another issue of updating restrictions (SQL Where clause in restriction), for owner name change.

    I tried to look into macro code, didnt find any function related to restriction. Did you ever come across this?

    CS.

  13. Comment by Dave Rathbun

    Nope, as a general rule I don’t use the SQL Where clause. What you would have to do is add another loop that goes through the entire universe, checks each object and its where clause, and update if needed.

  14. Comment by CS

    Sorry for the confusion. I meant to say is…. I have data/row level security which is implemented through access restrictions. These restrictions have a condition to restrict the rows. Whenever I change owner name in universe, I observed that none of the restrictions inherit the owner name change. I have as many as 51 such restrictions and time consuming to change owner name manually.

    If you can suggest something, it will be a great help.

    Thanks much.

    CS.

  15. Comment by Dave Rathbun

    Ah. That clarifies and complicates things. :) Access restrictions are not stored in the universe, they are stored directly in the repository database. As such, they’re not exposed to the SDK in the same way. I’m not sure they’re even available for manipulation at all.

  16. Comment by CS

    :-(
    Anyways, Thanks a lot for the response. It explains why the code doesn’t change restriction SQL.

  17. Comment by André

    Hi, I need to copy one dimension from one class to another class in my universe. The “Add” method in the Class Object doesn’t work (it expectates a String as Name).

    Any help would be appreciate.

    Thanks

  18. Comment by Dave Rathbun

    Hi, AndrĂ©, and welcome to my blog. This script doesn’t do anything to manipulate objects in the universe. If you have a question unrelated to the blog post, I ask that you please post it on BOB where you will get more attention. Thanks.

  19. Comment by Fusion

    Hi Dave,
    Great solution. I could change the schema name using the VBA macro but I ran into some issues.
    I am using version XI 3.1 SP2. My derived table uses subquery but the tables specified in the sub-query are not joined to any of the tables in the designer.

    When I run the VBA macro you’ve provided, it changes the schema name. Perfect!
    But the problem I am running into is when I run the integrity check after opening the designer, it does not recognize the join with the derived table. If I open up the join and parse the join, it is okay. The integrity check does not result in error.

    I had to change the universe parameter to point it to the production database before I run the integrity check.

  20. Comment by Dave Rathbun

    Yes, we change the connection first, that way when the schema name is updated in the derived tables it passes the parse, and the structure is present. Without doing that the columns of the derived table don’t exist. Thanks for pointing that out.

  21. Comment by Fusion

    Thanks Dave

  22. Comment by Fusion

    Hi Dave,
    Is it necessary to include tables that is referenced by a derived table in the universe schema?
    I have tables a, b, and c that is being used in derived table SQL FROM clause.

    Thanks

  23. Comment by Dave Rathbun

    Tables referenced only in derived table definitions do not have to exist in the universe schema. It’s not the same as with an alias, where you have to include the source table in order to be able to reference it via an alias.

  24. Comment by Dave Rathbun

    We have had a BI4 development environment up and running for some time. We recently created our Q/A environment for BI4. If you are using this utility against BI4 you will need to log in to the code and update the project references from the Business Objects Designer Library 12.0 to Business Objects Designer Library 14.0 instead. That way it will open BI 4 universe designer rather than 3.x. Note that this utility will not work for Information Design Tool created .UNX files.

  25. Comment by ijot

    There’s something I don’t get : is there no other way to configure somewhere a variable to specify under which owner the tables/views are located?

    I often heard that default database on odbc config would do the trick.

    thanks

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.