BusinessObjects Board

Document a universe using Excel and the Designer SDK

Hi,
I have checked the object but it have no problem.
Laurent


coraziari :fr: (BOB member since 2007-11-26)

Dwayne, many thanks for another fine tool :smiley:

I’ve managed to adapt my copy to list the SQL for each of our derived tables.
It took a little doing, because for some reason (my VBA being incredibly rusty, no doubt being one!), I kept getting “Out of memory” errors trying to place the SQLOfDerivedTable into a single Excel cell, even though none of my derived table SQL statements exceeded the ~32,000 character limit for Excel cell contents.
(I used the “Left(Tbl.SQLOfDerivedTable, 1024)” and that worked fine :? )

Anyway, I ended up splitting the SQL into an array and outputting each element of the array onto a separate line. Works for me 8)

Thanks again,
Pete


Peter Hughes :uk: (BOB member since 2005-11-21)

Can the SQL of a derived table be extracted?


johnghirst :uk: (BOB member since 2008-02-19)

Yes John.

I just added a sub (code below) and added a worksheet called “Derived Tables”. Then just call the sub from within the main bunch of subroutine calls.

Sub ListDerivedTables(Tbls As Designer.Tables)
    Dim Tbl As Designer.Table
    Dim Rng As Excel.Range
    Dim RowNum As Long
    Dim DTSQL As Variant
    Dim i As Integer
    
    On Error GoTo ErrorHandler

    Application.StatusBar = "Documenting derived tables..."
    Set Rng = Sheets("Derived Tables").Cells
    RowNum = 1
    For Each Tbl In Tbls
        If Tbl.IsDerived Then
            RowNum = RowNum + 1
            ' split the string
            DTSQL = Split(Tbl.SqlOfDerivedTable, vbCrLf)
            Rng(RowNum, 1) = Tbl.Name
            Rng(RowNum, 2) = Len(Tbl.SqlOfDerivedTable)
            Rng(RowNum, 3) = LenB(Tbl.SqlOfDerivedTable)
            i = 0
            While i < UBound(DTSQL) + 1
                Rng(RowNum + i, 4) = DTSQL(i)
                i = i + 1
            Wend
            RowNum = RowNum + i
        End If
    Next Tbl

CleanUp:
    Set Tbl = Nothing
    Exit Sub

ErrorHandler:
    MsgBox Err.Source &amp; " - " &amp; Err.Number &amp; ":  " &amp; Err.Description, _
        vbCritical, "Failure in ListDerivedTables()"
    Resume CleanUp

End Sub

Disclaimer: My VBA is a little rusty, so most of the above was “cobbled together” :wink:

EDIT: Thought I better offer a bit of ‘code explanation’ …
Basically the derived table SQL is “split” based on vbCrLf (end of line characters), so if your derived table SQL doesn’t contain any, this may not be a suitable solution for you. Then the name of the derived table is printed in column 1, whilst the length in char and bytes of the SQL is placed in columns 2 and 3 respectively (if you don’t want/need these then comment out, or remove, the lines that do a “Len” and “LenB”). Next, each “line” of the derived table SQL is placed into its own cell in column 4. If I remember correctly, the “while” loop ensures that all the lines in the SQL for a single derived table are output before printing the name of the next derived table.

HTH,
Pete


Peter Hughes :uk: (BOB member since 2005-11-21)

Hi Dwayne,

I have tested and used your tool: Document a Universe (XIr2).xls > Excellent!!! and very useful!!! I have used to document a big universe (> 6000 objects, > 400 tables , etc…) > macro has runned during 10 minutes, and result is OK!

Do you know if it’s possible to have a similar tool to document a WebIntelligence XI R2 report? To extract : Name of report, Name of Dataprovider, Universe used, Object used in data provider?

Thanks in advance for your feedback

Olivier


OlivBO38 :fr: (BOB member since 2008-07-09)

I’m glad you found it helpful!

I’ve not used it before, but another member provided this utility … WID Scanner.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Interesting utility!! Dwayne.

It worked wonders. :+1:
Now, I just need to make a few modifications to complete the Universe Design Document required by my boss.

Thank You.

-SaiSravan


SaiSravan :india: (BOB member since 2008-10-20)

Hi,

When i tried to run XIR2 macro, igot error"VBAProject-430: Class doesn’t not support Automation or does not support expected interface" how to resolve this?

Regards,
GGS


Arasu (BOB member since 2008-10-24)

Excel 2007 by chance?


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

This is awesome, exactly what I needed. BOB forums rock yet again!


river-wind (BOB member since 2007-07-24)

What a wonderful tool, have been looking for this for a while. Is there any way we could do the opposite, i.e add objects, classes in the Excel file, upload to BO universe, ie partially creating a BO universe via Excel?

thanks,
plexi 8)


pleximax :norway: (BOB member since 2004-08-20)

Possible? Yes … see Create Class and Objects Automatically.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Thanks for this excellent thread. Helped as a reference/starting point for a Universe re-engineering work (from Oracle to SQL server + breaking down a large universe into small manageable universes), having 2300 objects, approx 400 table, 500 join, 250 conditions, having Oracle specific syntaxes all over.

Thanks a lot for support, new contributions and improvements to previous posts.

-Guna


vigi_guna :australia: (BOB member since 2008-07-09)

Dwayne:

I have downloaded the “Document-a-Universe” for XIR2. I am currently using XI R3.

When I clicked “Document a Universe” button from the excel, logged onto the CMS Server and selected the universe.
Once it processed and returned the error message

Buffer overrun detected
Program: $PATH\designer.exe
A buffer overrun ahs been detected which has corrupted the program’s internal state. The program cannot safely continue execution and must now be terminated.

Is this error message normal or I need to configure anything before executing this program. I am using the XI R3 under XP environment.

The program still downloads the information in the tabs and it appends the information with existing universe information. Is it normal operation ?.
Please confirm.
Thank You
Ramesh Vasudevan


rameshbalajiv (BOB member since 2008-03-25)

Now that is a new one. I’ve not seen that error before. I suspect something corrupt on your workstation, but there is one thing to try. Could you run the utility on a very small, simple universe, and see if it generates the same error? The utility is recursive (calls itself as it moves down the folder tree), but if the error persists on a small universe, it eliminates the possibility that you’ve run out of memory.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Hi:

Now that is a new one. I’ve not seen that error before. I suspect something corrupt on your workstation, but there is one thing to try. Could you run the utility on a very small, simple universe, and see if it generates the same error?

I tried small to medium size universes, got same error message every time.

In fact, I downloaded other utilities like WD Scanner, got the same error message once it completes the execution.

The utility is recursive (calls itself as it moves down the folder tree), but if the error persists on a small universe, it eliminates the possibility that you’ve run out of memory.

I have 4 GB of memory on my box. I will try investigate further again.

Regards
Ramesh Vasudevan


rameshbalajiv (BOB member since 2008-03-25)

… and the WID scanner doesn’t even use Designer (I don’t think). Sounds more and more like a local workstation issue, I’m afraid.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Hi,

when i am working with to dowload a universe as excel document of XIR2 universe the following error was coming. please can u give me a solution to resolve this problem.

( VBAProject - 430 : class does not support automation or does not support expected interface )

regards
sekhar
[/u]


bsekhar1980 (BOB member since 2009-01-16)

Hi Dwayne

I get the same error as well

VBAProject - 430 : class does not support automation or does not support expected interface

Using Excel -2007 and on another PC Excel 2003. Get the same error both ways.

The library is fine, it does use The BOXI one. The compiler highlights the first line of code
Sub DocumentUniverse()

How do I fix this?


meenal (BOB member since 2006-03-08)

Hi,

When i tried to Document a universe.

i am getting the following error

VBAProject -430 : Class does not support Automation or does not support expected interface.

universe version : XIR2

Please help me on this…


vickys (BOB member since 2007-11-08)