Dave's Adventures in Business Intelligence

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.

The error was being thrown while processing contexts to get a list of included joins. The first several contexts were processed fine, but one particular context always threw the error mentioned above. I created a very simple program that only tried to traverse the collection of contexts and it threw the same error. (The code is listed at the end of this blog post.) When programs don’t work there are generally only two issues: either the code is wrong or the data is wrong. In this case I was quite certain the code was okay so I started looking at the data.

As I reviewed the context that was causing the error I accidentally “touched” one of the joins, deselecting it from the context. I made sure I restored that join to the context before trying another test run. Imagine my surprise when the “trouble” context was processed successfully on my next test run. Of course the following context still threw the error, but the “data” for the first problem context seemed to have been cleared up. Based on those results I went back into the universe and “touched” every context. I opened each one, deselected and then re-selected a join, and then saved the universe. After doing that, the code ran without throwing any errors while processing the contexts.

I can only assume that something had gone wrong in the way the context was being stored in the universe. I assume that the universe was working okay as far as query generation, but there was something causing problems for the VBA interface. Touching each context cleared that up.

Duplicate Parameters

However that was not the last problem with the test universe provided to me. Once I got beyond the context issue a new error came up while attempting to process the universe parameters. It seemed that somehow in this particular universe each universe parameter had become replicated multiple times! 😯

This was causing a second problem because the name (ANSI92 for example) was used as a unique key to compare the before and after versions of each parameter. I was able to go through and remove many of the duplicate entries, but I decided that I should write some code to address this particular data issue. The code that captures the parameters now captures only the first occurrence of each parameter value and ignores the rest. It could easily be redone to capture the last rather than the first but for now that’s how it works.

2.0.1 Released

Because of the issue found with duplicate parameters I am releasing version 2.0.1 of the script. Please visit the support page to download the updated version. Note that this code update does not include a fix for the context issue as I have not figured out how to address a corrupted context definition with my VBA code yet. If you are experiencing this issue, please try the “touch” process outlined above and see if that addresses your issue.

Note: comments are off for this post to avoid fragmentation of the discussion. Please post any questions about this release on the download page, thanks.

Related Information

Here is the code I wrote to try to help diagnose the issue with contexts. This code simply opens a universe and lists all of the joins assigned to each context. It should work perfectly fine as long as the data is valid.

Sub main()

    Dim boDesignerApp As Designer.Application
    Dim boContexts As Designer.Contexts
    Dim boContext As Designer.Context
    Dim boJoins As Designer.Joins
    Dim boJoin As Designer.Join
    
    Dim boUniv As Designer.Universe
 
    ' Establish a Designer session and log in
    Set boDesignerApp = New Designer.Application
    boDesignerApp.Visible = True
    Call boDesignerApp.LogonDialog
    Set boUniv = boDesignerApp.Universes.Open
    
    Dim strJoinSet As String
    
    Set boContexts = boUniv.Contexts
    
    For Each boContext In boContexts
        strJoinSet = ""
        iRowNum = iRowNum + 1
        Set boJoins = boContext.Joins
        For Each boJoin In boJoins
            strJoinSet = strJoinSet & ", " & boJoin.ID
        Next boJoin
        Debug.Print "Context " & boContext.Name & " has joins " & strJoinSet
        Set boJoins = Nothing
    Next boContext
    Set boContext = Nothing
    
End Sub