I’ve had the same problem. It was caused by an object where de syntax wasn’t correct. In my case it was !unknown.description. I deleted the object and the excelsheet works as it should work…
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)
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 & " - " & Err.Number & ": " & 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”
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.
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?
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?
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 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.
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
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.