Business Objects Query Builder

Query Builder is a web application to query Business Objects repository. It provides information which can’t be found in Central Management Console (CMC).

BusinessObjects deployment repository made up of set of tables to hold the information about the BI content such as universes, reports, users, schedules, etc. These tables are encrypted in such a way that the information stored in these tables cannot be readable using conventional SQL query tools.

Query Builder gives the opportunity for the Admin/developer to query the metadata stored in the repository.

Why you may ask, because users, auditors may ask some questions with data not ready available. Like what reports were updated since start of the year, categorise reports by kind, number and names of recurring reports etc

How to access Query Builder

To access the Query Builder, in the Internet browser of your choice type the following URL:

http://[BO SERVER]:[PORT]/AdminTools/querybuilder/ie.jsp

Access Query Builder screen

 

Log on as an Administrator to get full access to all the repository objects.

 

Query Business Object repository

 

Query Builder Objects

 There are 3 main objects:

  1. CI_INFOOBJECTS: Covers folders, reports and other content consumed by the end user
  2. CI_SYSTEMOBJECTS: Covers servers, connections, users, and user groups.
  3. CI_APPOBJECTS: Covers BusinessObjects Enterprise applications including InfoView, Desktop Intelligence etc and also objects used by documents such as Universes

 

Query Bulider Functions and operators

  • COUNT – Retrieves the number of distinct values of a property
  • TOP – specifies the maximum number of object to be returned

Operators inlcude: =,!=, >, >=, <, <= , IN, LIKE, BETWEEN, ALL

 

Simple queries Object type

1. CI_INFOOBJECTS

BO Repository System Information

 SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4

Full Client and webi reports in the repository starting with the word “Audit” which are scheduled

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND IN ('WEBI' ,'FULLCLIENT') AND SI_NAME LIKE ‘Audit%’ AND SI_RUNNABLE_OBJECT=1

Reports updated since 1th July 2013

SELECT SI_NAME, SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' AND SI_UPDATE_TS > '2013.07.01'

All Full Client reports that use a specific universe

SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'Universe Name'")

List of all the public folders (Non System Folders)

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=23 AND SI_NAME!='REPORT CONVERSION TOOL'  AND SI_NAME!= 'ADMINISTRATION TOOLS' AND SI_NAME!= 'AUDITOR'

List all the WebI reports with prompts

SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS, SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_PROMPTS FROM CI_INFOOBJECTS WHERE  SI_KIND = 'WEBI' and SI_INSTANCE = 0 and SI_PROCESSINFO.SI_HAS_PROMPTS=1

Which Reports Use my_Universe

SELECT SI_ID, SI_NAME, SI_UNIVERSE FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Universe Name'")
and SI_KIND = 'Webi' and si_instance = 0

List of available categories

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45

 

2. CI_APPOBJECTS

Total Count of univeses in the deployemnt

SELECT COUNT (SI_ID) FROM CI_APPOBJECTS WHERE SI_KIND = 'UNIVERSE'

List the Universes associated with a particular Data connection

Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where CHILDREN("SI_NAME='DataConnection-Universe'","SI_NAME='efashion-webi'")

List of all your universes updated since some date

SELECT SI_NAME, SI_REVISIONNUM, SI_UPDATE_TS   FROM CI_APPOBJECTS WHERE SI_KIND='UNIVERSE' AND SI_UPDATE_TS > '2013.07.01'

Count of reports per Universe

SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS WHERE SI_KIND='Universe' AND SI_WEBI.SI_TOTAL > 0

 

3. CI_SYSTEMOBJECTS

Business Objects File Repository Server Information

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'SERVER' AND SI_NAME LIKE '%FILEREPOSITORY%'

List all the servers and their status with server category

SELECT SI_NAME, SI_SERVER_IS_ALIVE, SI_DISABLED, SI_SERVER_KIND, SI_FRIENDLY_NAME, SI_EXPECTED_RUN_STATE FROM CI_SYSTEMOBJECTS WHERE SI_KIND='SERVER'

         Possible values for SI_EXPECTED_RUN_STATE object

  • -1:  Server is currently in an invalid state due to a configuration error.
  • 0:  The expected state of the server is stopped.
  • 1:  The expected state of the server is running.
  • 2:  The expected state of the server is restarting.
  • 3:  The server is not being managed by the Server Intelligence Agent.
  • 4:  The expected state of the server is immediate shutdown.

Users in specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

List all the Users created between a date ranges

SELECT SI_ID,SI_NAME,  SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' AND SI_CREATION_TIME BETWEEN '2013.11.01' AND '2013.11.30'

 Users member of more number of groups

SELECT SI_ID, SI_NAME, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'USER' AND SI_USERGROUPS >= ALL SI_USERGROUPS

List of available Calendars 

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PARENTID=22

Count total number of connection on a particular day

SELECT COUNT (SI_CREATION_TIME) FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME > '2013.10.15.00.00.01' AND SI_KIND = 'Connection'

Total number of unique users logged in to the system on a particular day

SELECT COUNT(SI_NAME)  FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME> '2013.10.15.00.00.01' AND SI_KIND = 'Connection'

 

NOTE:

  • Default limit for returning objects is 1000 objects. In order to get more than 1000 objects use ‘Top N’ function before the column listing in the query.
  • No sub queries allowed
  • The order of columns in the SELECT clause has no impact as the results will be rendered in its own order

Leave a Reply

Your email address will not be published. Required fields are marked *