BusinessObjects Board

Identify Universes/Reports using certain tables and columns

general discussion
#1

Hi,

I have two questions.

  1. I need to identify the universes which use certain tables and columns.
    I fired the following SQL in BO repository:
    SELECT
    UNV_UNIVERSE.UNI_FILENAME,
    UNV_UNIVERSE.UNI_LONGNAME,
    UNV_TABLE.TAB_NAME,
    UNV_COLUMNS.COLUMN_NAME
    FROM UNV_UNIVERSE, UNV_TABLE,UNV_COLUMNS
    WHERE
    UNV_UNIVERSE.UNIVERSE_ID = UNV_TABLE.UNIVERSE_ID
    AND UNV_TABLE.TABLE_ID = UNV_COLUMNS.TABLE_ID

But as ‘UNV_COLUMNS’ table is empty, no data is returned. My question is is this table supposed to be empty in BO repository?
Is there any other way to find out this (identify the universes which use certain tables and columns)?

  1. I want to identify the reports which use certain tables and columns. Is it possible to retrieve this information from Bo Repository? (If so, then please provide the SQL query for that).
    Is there any other way to identify the reports which use certain tables and columns?

Your urgent response is awaited.
I am using BO 6.5

Thanks & Regards,
ram_rpb

ram_rpb (BOB member since 2010-01-25)

Universe Information : BO 6.5
#2

The 6.x repository contains information to link reports and data provider sources (such as universes) but will not tell you which tables or columns or objects are being used.

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

Thanks Dave for your reply.

Could any one kindly help me with the following questions please?

How can I find the following information BO repository?

  1. Which report is using which universe.
  2. Identify the reports which use certain tables and columns. (If possible)

(Note: As per the BO Documentation, OBJ_X_DOCUMENTS
This is the only table in the document domain. It stores the binary content of all
documents sent to the repository (through the user actions Publish to corporate
documents, Send to users or Send to Broadcast Agent). The document contents
are stored as BLOBs (Binary Large OBjects) stored in slices.)

ram_rpb (BOB member since 2010-01-25)

#4

The OBJ_M_DOCUMENTS table will link to the table OBJ_M_DOCATVAR which contains a list of data providers. However, it’s only there by name, so you can’t know if a document is pointing to DEV or PROD versions of the universe if they’re stored in different domains in the same respository.

It will not tell you which columns are used, or which tables. Only which universe.

You don’t use the OBJ_X_DOCUMENTS table at all.

Note that all of this changes on the XI platform.

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

Hi Dave,

Do you know if it is possible to find out (in XI 3.1 SP2) the list of objects from a universe that are getting used in reports?
Our universes now contain too many objects and we suspect a large chunk of them are not getting used. So what we are looking to do is, find out the objects that ARE being used in some or the other report and remove the rest of them to make the universe managable.
Please let me know.

Thanks,
Buddy

Buddy :india: (BOB member since 2006-08-04)

#6

Not possible at this moment. Not out of the box anyway. Perhaps there’s a third party application that can.

wahey :netherlands: (BOB member since 2007-05-31)