How to get the whole list of BEx queries used for all our Webi reports in 4.1 BICS connection

[Former Member]

We have  a good number of Webi reports on 4.1 BICS connection which are created on top of a single BEx query or multiple BEx queries. We would like to know what are the bex queries preferably with connections for all these Webi reports.

Do we have some AdminTools query anyone is aware of to find out the list or some other way instead of opening each report and getting the Query names individually?

I tried to identify via Promotion Manager-> Manage Dependecies for the Webi reports, it just showed the OLAP Connection names but not the BEx query?

Thanks for helping.

[mike_howles4 · Accepted solution]

There's probably a much better way to do this, but here's a quick and dirty way to get an initial dump:

SELECT SI_NAME, SI_WEBI_DOC_PROPERTIES FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' AND SI_WEBI_DOC_PROPERTIES LIKE '%Q00%'

This is assuming you have some sort of naming standard for your queries such as AB_ABC_C01_Q001 or whatever.  You can adapt the LIKE clause to get to something closer that would meet your naming conventions perhaps.

Like I said, this isn't probably the best way but will give you a dump of the fields that contain the DSNAME that should give you the BEx Queries involved.

Hope this helps.

[Former Member · Accepted solution]

Which is this tool you mentioned - QueryBuilder?

[Former Member · Accepted solution]

Hi Shabir,

Query Builder is a web application that allows us to obtain metadata from the system database by querying virtual tables using SQL like syntax.

Virtual Tables

CI_INFOOBJECTS

Contains portal content.

CI_APPOBJECTS

Other objects stored in the repository but managed through client-server tools or add-ons to the system.

CI_SYSTEMOBJECTS

Contains system objects required for the system to function and that are displayed and managed in the user interface.

Sample Query

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi'

The URL in BI 4.x is: http://localhost:8080/AdminTools/ (replace localhost:8080 with your server name and port number).

[Former Member]

There are quite a few White papers available on Query Buildder (http://<System>:<PORT>/AdminTools)

just Replace CmcApp or InfoViewApp with AdminTools.

Here is one of the good links I've seen in the past.

[Former Member]

Hi Tilak,

SI_WEBI_DOC_PROPERTIES is the property bag that contains the Web Intelligence data provider details. The SI_WEBI_DOC_PROPERTIES is structured in an XML format, look out for the following tags:

DPNAME = Name of the Web Intelligence data provider (i.e. name of the data provider tab in query panel).

DSNAME = Name of the actual data source (i.e. name of the BEx query).

Note: SI_DOC_COMMON_CONNECTION will tell you which OLAP connection the report is connected to.

If you simply need a list of all BEx queries used (regardless of relationship to report), then simply pull out all the DSNAME tag using Notepad++ or Excel. If you need the Webi > BEx relationship it would probably be easier to write some Java SDK

[rama_shankar3]

Hi Tilak,

You can run a query like the following in QueryBuilder, you would get the

ID of the BICS connection used in the webi report:

SELECT SI_NAME, SI_DOC_COMMON_CONNECTION FROM CI_INFOOBJECTS

Then, you can use the ID of the BICS connection for your inventory purposes.

Hope this helps.

Regards,

Rama

[Former Member]

Thanks Ram, unfortunately  that query  didnot help.

I got the below outpu from Query Builder. Not sure how can use this to identify the source BEx query name and it's connection.

Properties
SI_DOC_COMMON_CONNECTION	1 31193 2 35840 SI_TOTAL 2
SI_NAME	XXXXXXXXXXXXXXX

[Former Member]

Hi Mishra

Can I ask you if you were able to get th BEx query name (associated with a connection or universe) from the Query Builder?

I would appreciate your help on this.

Thanks in advance.

Regards

A.Santos