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:
Log on as an Administrator to get full access to all the repository objects.
Query Builder Objects
There are 3 main objects:
- CI_INFOOBJECTS: Covers folders, reports and other content consumed by the end user
- CI_SYSTEMOBJECTS: Covers servers, connections, users, and user groups.
- 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
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
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
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'
- 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