BusinessObjects Board

Querying Repository in 6.5

building semantic layer
#1

Using 6.5.1 and need to query the repository to determine what tables are associated with a given report. The report may have multiple data providers from which I would want to see the distinct tables amongst all. Providing me with the SQL would be preferable. Any ideas?

ronpadz :us: (BOB member since 2005-09-14)

#2

SQL used in a report is not stored separately in the repository tables, but is contained within the document itself. Documents are stored as BLOBs in the repository, so the information is “buried” and not able to be queried directly.

You could see if you can use this utility as an alternative.

Anita Craig :us: (BOB member since 2002-06-17)

#3

Anita-
Thank you for responding but I think you mis-read/misunderstood my question. I am not seeking SQL used in a report. There are repository tables with metadata about reports and their data providers, universes and their objects. These tables can be queried to analyze the metadata. I am seeking a query that relates these repository tables. I am not seeking the SQL from the reports themselves.

That said, thanks for turning me on to this cool utility. I may be able to use it for documentation purposes.

Ron

ronpadz :us: (BOB member since 2005-09-14)

#4

The best you can do from the repository is determine which universes are used in the data provider. You will have no idea if a specific table within that universe is used without using some other means, such as the utility Anita linked you to.

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

#5

I did not misread your question. You are incorrect in your assumptions about what’s available in the repository for documents.

There are not multiple repository tables with metadata about reports and their data providers/objects. There is one table OBJ_M_DOCUMENTS which has a pointer to the BLOBs stored in the document domain, and a few definition items about the document (name, size, owner, repoID, …).

There is a single table which makes up the document domain, OBJ_X_DOCUMENTS, which has the binary content of the document stored as BLOB. Which is what I essentially said at first.

Anita Craig :us: (BOB member since 2002-06-17)

#6

Thanks again Anita. I must have misunderstood your response. When you said “SQL used in a report is not stored separately in the repository tables” I thought you were telling me about the SQL used in a report. :roll_eyes: My bad. I appreciate all your help. Diggin’ the utility you turned me on to. :smiley:

Dave-
Thank you for the succinct response.

ronpadz :us: (BOB member since 2005-09-14)

#7

Hi Anita,

Thanks a lot for sharing your view. Just one confusion, this OBJ_M_OBJECT table is situated in which domain, security domain or document domain?

and can you please explain that one as well?1) if full client is not installed in the local system am I able to open infoview but not ZABO? is that conception correct?
2)while opening, ZABO is taking help of some system resource. what they are?

Thanks,
Aakash

aakash (BOB member since 2006-11-16)

#8

OBJ_M_OBJECT is part of the Security domain. OBJ_X_OBJECT is the document domain.

ZABO does get installed on the local machine if you allow it. It is just like the full-client without any database connectivity. If you install the client from the CD, you can use it in 2-tier mode or in ZABO (3-tier) mode.

What do you want to know about ZABO resources? On the server? or client? ZABO uses both.

Steve Krandel :us: (BOB member since 2002-06-25)

#9

Thank you Steve.

Just tell me both OBJ_M_OBJECT and OBJ_X_OBJECT tables contains what kind of data, and other details.

and for ZABO resource I want to know both on client and server

Thnanks

aakash (BOB member since 2006-11-16)

#10

M_document holds the names of the docs. Just look at the table yourself.

X_document holds the binary file that is the actual report. It will be the biggest table in the repository.

I don’t know what you want to know about ZABO. Read the deployment guide it’s there.

It displays the reports that are run by the server. The server makes the database call and passes the resultset back to the client.

Steve Krandel :us: (BOB member since 2002-06-25)

#11

A few simple queries using Toad, PL SQL, Query Analyser or any other SQL editor will show you the contents of the tables…

The only way to really get to know the repository is to read up and then fire off a few queries…basically do a bit of work yourself… :wink:

Mak 1 :uk: (BOB member since 2005-01-06)

#12

Mak

Can you please explain a little bit , where to throw the query? means which database and schema? i m lilttle confused. is it on the database and schema on which the universe is connected?

aakash (BOB member since 2006-11-16)

#13

We can’t tell you that. It’s one of the repository tables. You have to know where the repository is. It’s unlikely in the same place that an application universe is.

You’ll have to look in Supervisor to see where you database is: Tools->Repository.

Steve Krandel :us: (BOB member since 2002-06-25)

#14

The installation CDs used to have some terse documentation on what tables comprised each of the domains – and a subset of information about what was in the various repository tables. It was far from complete.

IF you go to BusinessObjects Support, you can navigate and try to find documentation for the Repository for your version. I know for a fact that version 6.5 – under “Security and Administration” – there is a download “The BusinessObjects Repository Reference Guide”.

Anita Craig :us: (BOB member since 2002-06-17)

#15

Hello,

I have question: I need report with report name and sql for this report, but I use WebIntelligence 6/all my reports are in WebIntelligence/. Can I do this? Is this possible? :oops:

elica.and :bulgaria: (BOB member since 2006-04-02)

#16

[quote:413ae31684=“elica.and”]Hello,

I have question: I need report with report name and sql for this report, but I use WebIntelligence 6/all my reports are in WebIntelligence/. Can I do this? Is this possible? :oops:
[/quote]

I don’t think it is possible with WebI reports, but its do-able with Full Client reports. :yesnod:

BO_Chief :us: (BOB member since 2004-06-06)

#17

Hi Experts,

I have a few questions.

1)can i create variables on thin client report?
2) reports viewes in web i , zabo and info view are called tin client report?
3)can thin client report be opened without using a browser? if yes how?
4)when do we see cube 1 , cube 2 for same data provider?
5)what is th role of data access folder generated while downloding through ZABO?

Thanks in advance

aakash (BOB member since 2006-11-16)

#18

Please post your questions in a new thread under an appropriate forum. These questions are not about the Semantic layer.

Steve Krandel :us: (BOB member since 2002-06-25)