BusinessObjects Board

Universe Documentation best practices

building semantic layer
#1

What are the best practices for universe documentation? Thoughts.

Flame (BOB member since 2004-09-21)

#2

Have you looked at the PDF of the universe you can export from designer? It gives you all the details about the universe. You can control what gets printed in the file from Tools\Options\Print PDF. Attached is a picture of the options.
option.gif

bonniebee :us: (BOB member since 2007-02-27)

#3

Hi bonniebee, thanks for your reply. I’m aware of this. I was looking for something along the lines of a document analysis.

Flame (BOB member since 2004-09-21)

#4

That’s a bit confusing. Are you wanting to document a universe, or analyze individual documents (that may use a universe)?

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

#5

One thing I would suggest for documentation of the Universe is to take the Mass Update utility from HERE and with a little tweaking you can extract the definitions of a universe’s derived tables.
I am working on a Universe that is getting its data from an OLTP so there are many derived tables that need to be included in the documentation.

chris_c :us: (BOB member since 2006-01-10)

#6

That would be a great start, but you might find this utility … Document a universe using Excel and the Designer SDK to be more thorough. It does not currently do derived tables, however.

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

#7

I had not seen that one, pretty slick! 8) 8)

I’ll be adding that one to the toolbox.

Thanks :mrgreen:

If I get a chance to add the derived table bit I’ll be sure to share the changes.

chris_c :us: (BOB member since 2006-01-10)

#8

Chris - I thought I had shown this to you? I’ve used this on a few of my universes, and yes it’s slick.

ngosz :us: (BOB member since 2003-09-25)

#9

I may have forgotten. :oops:

Things get crazy and I get brain leaks :crazy_face:

chris_c :us: (BOB member since 2006-01-10)

#10

It appears that there is nothing separate in the SDK for derived tables. They are listed just like any other table, with columns, etc. The existing utilities do cover derived tables.

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

#11

Here is the code I have to print the SQL from derived tables in a text file
I would not describe this as distributable code but the key is the table.isderived method and the table.SQLofDerivedTable

Private Sub Docinfo()
    Dim Tbl As Table
    Dim TBLtxt As String
    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LogonDialog
    On Error Resume Next
    Set Univ = DesignerApp.Universes.Open
    If Err.Number = 41 Then ' cant open the universe for some reason
        MsgBox "Unable to open universe"
        GoTo closeDesigner
    End If
    Open "F:\BO Utility\" & Univ.Name & "UniverseDerivedtables.txt" For Output As #1
    For Each Tbl In Univ.Tables
       If Tbl.IsDerived Then
          Debug.Print Tbl.SqlOfDerivedTable
          TBLtxt = "/**************Dervived Table Definition**************" & vbCrLf & Tbl.Name & vbCrLf & "******************************************/" & vbCrLf & Tbl.SqlOfDerivedTable & vbCrLf & "/******************************************/"
          Print #1, TBLtxt
       End If
    
    Next Tbl
    Close #1
    On Error GoTo 0
    
closeDesigner:
    DesignerApp.Quit
    Set DesignerApp = Nothing


End Sub

Each table prints something like this…
It is a Garbage in Garbage out sort of thing, so if you write messy sql you’ll get messy sql back

/**************Dervived Table Definition**************
DRV_PGM_PLNG_PERD_PTR
******************************************/
SELECT
    pgm_plng_perd_id,
    pgm_plng_attr_val_txt as PRICE_TIER
FROM 
    K_AS01.ast_pgm_plng_perd_attr
WHERE
    pgm_plng_attr_cde = 'PTR'
/******************************************/

chris_c :us: (BOB member since 2006-01-10)

#12

Thanks guys. This was helpful and slick. 8)

Flame (BOB member since 2004-09-21)