Query Builder is one of the powerful administrative tools in Business Objects and it’s used to query your BO repository to get all kinds of information which is not even available in CMS. It is available only in BO XI R2 and adobe versions.
BO repository has set of tables to store BI information such as Universes, reports, Users, schedules, security that make up your deployment, etc. Repository tables are encrypted in such a way that it can’t query with conventional SQL tools. This is the place where Query Builder tool comes in and get those Repository Tables metadata information. Query Builder is a simple GUI tool which enables the user to run queries against the BO repository (also known as the CMS InfoStore).
The CMS repository stores metadata in the form of InfoObjects. All the contents of BO Enterprise system can be classified into two types: metadata and the actual file. For example, if there is a Crystal Report document in the BOE system, the metadata (including Report name, type, ID, CUID, path, etc.) is stored as an InfoObject in the CMS Repository. The Crystal Report document itself (i.e. the .rpt file) is stored as a file on the File Repository Server (FRS).
Note: To access your Query Builder, use the below URL link in the Web browser.
CMS Repository Structure
The CMS metadata is physically stored on a database, but we browse the InfoObjects from virtual tables. To give a clear picture, let’s begin with physical structure at the database level.
Physical Database Tables
There are 6 tables on the database level to store the metadata.
CMS_VersionInfo – Contains the current version of BO Enterprise.
CMS_InfoObjects – Important table in the repository and each row in this table stores a single InfoObject. The table contains the following columns:
ObjectID, ParentID, TypeID, OwnerID, Version, LastModifyTime, ScheduleStatus, NextRunTime, CRC, Properties, SI_GUID, SI_CUID, SIRUID, SI_INSTANCE_OBJECT, SI_PLUGIN_OBJECT, SI_TABLE, SI_HIDDEN_OBJECT, SI_NAMEDUSER, SI_RECURRING, SI_RUNNABLE_OBJECT, SI_PSS_SERVICE_ID, ObjName_TR, SI_KEYWORD, SI_KEYWORD_TR, LOV_KEY.
CMS_Aliases6 – This table maps the user alias to the corresponding user ID. A user has an alias for each security domain in which they are members. For example, a user may have both a Win AD alias and an LDAP alias. Regardless of the number of aliases a user may have, in the BI Platform each user has only one user ID. The map is stored in a separate table to enable fast logins.
CMS_IdNumbers6 – The CMS uses this table to generate unique Object IDs and Type IDs. It has only two rows: an Object ID row and a Type ID row. The CMSs in a cluster use this table when generating unique ID numbers. GUIDs, RUIDs and CUID are generated with an algorithm that does not use the database.
CMS_Relationships6 – Relationship tables are used to store the relations between InfoObjects. Each row in the table stores one edge in the relation. For example, the relation between a Web Intelligence document and a Universe would be stored in a row in the WebI – Universe Relation table. Each relationship table has these columns:
Parent Object ID,Child Object ID,Relationship InfoObject ID ( this Default InfoObject “DFO” describes the properties of the link between the two objects.), member, version, ordinal, data. Relationship tables are defined by default objects.
CMS_LOCKS6 – This is auxiliary table of CMS_RELATIONS6
Virtual Tables in BO Repository
The repository information is stored in the form of InfoObject and CMS can read repository tables from virtual tables. Which are described detailed manner as given below.
Contains User, User Group, Server, Server Group ,Folder, Sub Folder, Connection, Calendar, Event information
Contains Webi, Crystal Report, Full Client, PowerPoint, Pdf, Excel, Word, Rtf, Txt, Program, Shortcut information
Contains Universe information
Note: When you log in to Query Builder, Log on as Administrator, so that you will get complete (full) access to the repository objects.
Query Builder Limitations:
Query Builder has limitations and below is the details.
- Need to write the query manually in the Query Builder or you can use the step-by-step wizard to build the query which is too simplistic.
- You should be aware the complete Repository table Names, objects and what kind of information it holds on.
- Need to write several queries to get the required result. For example, If you want the objects in the folder, first you have to write a query to get the Folder ID, then use that ID and get the object in that particular folder.
- Query Builder returns the result set by individual tables (Structure of the Result) and you have to copy past each & every tables and made your required format.
- Cannot execute / run multiple queries in parallel.
- The Default limit for returning objects would be 1000 objects. In order to get more than 1000 objects we need to use ‘Top N’ function before the column listing in the query. For ex. SELECT Top 2000 * FROM CI_INFOOBJECTS
- Query Builder won’t allow Sub queries.
- The order of columns selected in the SELECT clause has no impact in the result returned and in its own order and order cannot be changed.
Query Builder Login:
Most wanted / Beneficial Query Builder BO Query
To get BO Repository Information
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4
Sample Result Set for above Query
Find Cluster Unique Identifiers that uniquely identify an InfoObject (CUID) for a specified report and this CUID is useful for document linking. You can find the CUID information in the Properties of each report in CMC & Infoview.
SELECT SI_CUID FROM CI_INFOOBJECTS WHERE SI_NAME = ‘SALES REPORT’
Find the List of users fro a particular Group (ex – USA is the Group Name here)
SELECT SI_USERFULLNAME FROM CI_SYSTEMOBJECTS WHERE CHILDREN(“si_name = ‘usergroup-user'”, “si_name = ‘USA'”)
Find the report list for a particular Folder.
SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER=’9565′
Find complete details about users.
SELECT SI_KIND, SI_NAME, SI_USERFULLNAME, SI_CREATION_TIME, SI_ALIASES, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘User’
Find Repository server details.
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘SERVER’ AND SI_NAME LIKE ‘%FILEREPOSITORY%’
Daily Scheduled / Recurring report Details
SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_STATUS !=8 AND SI_RECURRING = 1
Retrieve Public Folders other then 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’
Get the List of Webi reports along report details.
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’
Get Full Client (Deski reports) reports details.
SELECT SI_ID, SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM CI_INFOOBJECTS
WHERE SI_KIND = ‘FULLCLIENT’
Get the Report list which are using more than one universes.
SELECT SI_ID, SI_KIND, SI_NAME FROM CI_INFOOBJECTS WHERE SI_UNIVERSE.SI_TOTAL>1
Get complete status of the Events (name, location and file information)
SELECT SI_ID, SI_NAME, SI_FEATURES FROM CI_SYSTEMOBJECTS WHERE SI_KIND= ‘Event’
Complete Details about the Scheduled report which are based on Events
SELECT SI_NAME, SI_SCHEDULEINFO FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0
To Get non scheduled Deski reports.
SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER
WHERE SI_KIND = ‘FULLCLIENT’ AND SI_CHILDREN = 0 AND SI_SCHEDULEINFO.SI_SCHED_NOW = 0
Get complete details of the reports from a particular Universe
SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”,”SI_NAME =’SALES'”)
Get particular report information
SELECT SI_DOC_SENDER, SI_NAME,SI_DESCRIPTION, SI_FULLCLIENT_PROMPTS, SI_TOTAL, SI_FULLCLIENTDATAPROVIDERS FROM CI_INFOOBJECTS WHERE SI_NAME=’Sales Report’
Complete User Groups information of your BO system.
Select SI_ID, SI_ALIASES, SI_DESCRIPTION, SI_NAME, SI_USERGROUPS, SI_GROUP_MEMBERS from CI_SYSTEMOBJECTS where si_kind = ‘UserGroup’
Get complete set of users, folders(including personal folder), Categories and Users inbox.
SELECT SI_PARENTID,SI_NAME,SI_KIND FROM CI_INFOOBJECTS
Users Inbox – SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=48
Categories – SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45
User Inbox, Favorites and Personal Folder ID information.
SELECT SI_ID, SI_NAME, SI_INBOX, SI_FAVORITES_FOLDER, SI_PERSONALCATEGORY
FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’
Get the user login count for a particular day to the system
SELECT count (SI_NAME) FROM CI_SYSTEMOBJECTS
WHERE SI_LASTLOGONTIME> ’2013.02.14.00.00.01′ AND SI_KIND = ‘Connection’
Latest Accessed Documents and alerts for the user.
SELECT SI_ID, SI_NAME, SI_RECENT_DOCUMENTS, SI_RECEIVED_ALERTNOTIFICATIONS
FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’
Get Complete Calendar details.
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND= ‘Calendar’
Complete status of each user
SELECT SI_ID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_LASTLOGONTIME, SI_FAILEDLOGONCOUNT, SI_NAMEDUSER FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’
Get Scheduled report complete details (report name, Description, scheduled frequency and etc) for a particular user.
SELECT * FROM CI_INFOOBJECTS WHERE SI_OWNER = ‘BOUSER1’ AND SI_RECURRING = 1
For more details about the each & every Objects in the repository tables, please check the SAP SDK link.