Introduction
Query Builder is one of the essential and interesting tools in BusinessObjects. Using Query builder one can easily query the BusinessObjects repository and get the required information which cannot be found even in CMC.
To explain more in detail, BusinessObjects 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. This is the place where Query Builder comes in to picture where in which this is the one and only door step through which we can query the metadata stored in the repository.
Querying repository
The repository information is stored in the form of InfoObject and the CMS reads the InfoObjects from the virtual tables. Below are the BusinessObjects metadata virtual tables and their associated objects.
| Virtual table | Table description | Table Objects |
|---|---|---|
| CI_SYSTEMOBJECTS | Contains InfoObjects that the BI Platform uses | User, User Group, Server, Server Group,Folder, Connection, Calendar,Event |
| CI_APPOBJECTS | Holds InfoObjects that are used by documents | Universe |
| CI_INFOOBJECTS | Contains InfoObjects that are consumed by the end user | WebI, Crystal Report, FullClient, PowerPoint, Pdf, Excel, Word, Rtf, Txt,Program, Shortcut |
Accessing Query Builder
Query Builder can be found at the below URL http://MyServer:Port/AdminTools
Make sure you are using a user credentials that is part of Administrator user group in order to gain access to all the repository objects. Please note using Query Builder we can only query the information stored in the CMS database not from the File repository files.
Simple queries to use against the repository
To list all the Users
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'
To list all the Universes
SELECT * FROM CI_APPOBJECTS WHERE SI_KIND='UNIVERSE'
To list all the WebI reports
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI'
Query Builder Blog series
Basics
BusinessObjects Query builder - Basics
BusinessObjects Query builder – Best practices & Usability
Sample Queries
BusinessObjects Query builder queries
BusinessObjects Query builder queries - Part II
BusinessObjects Query builder queries - Part III
BusinessObjects Query builder queries - Part IV
BusinessObjects Query builder – Exploring Visualization Objects
BusinessObjects Query builder – Exploring Monitoring Objects
BusinessObjects Query builder - Exploring Lumira & Design studio Objects
Use cases
BusinessObjects Environment assessment using Query builder
BusinessObjects Environment Cleanup using Query builder
BusinessObjects Query builder – What's New in BI 4.0
Bluesky
