Skip to Content
Author's profile photo Manikandan Elumalai

BusinessObjects Query builder – Basics

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

/wp-content/uploads/2013/06/11_232171.jpg

/wp-content/uploads/2013/06/22_232190.jpg

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    

Assigned Tags

      138 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Hafeez Rasheed
      Hafeez Rasheed

      Hi Mani,

      I need to find ithe list of universes where ,a specific TABLE is being used.

      Is it possible in Query builder?

       

      Regds,

      Samson

      Author's profile photo Bryant Birr
      Bryant Birr

       

      Is there a way to easily export data from the Query Builder?

      Author's profile photo Denis Konovalov
      Denis Konovalov

       

      there isn't, the tool is not built for exporting, however CMS DB driver now allows you to build universes on top of repository and report of them.

      Author's profile photo Mahendra Reddy
      Mahendra Reddy

      Hi Bryant-

       

      There is one tool called BI clever. Use the below link to easily export the data.

      http://biclever.com/blog/bi-on-bi/

       

      Author's profile photo Chip Smithson
      Chip Smithson

      These query builder posts have been extremely beneficial after being thrown into the SAP BOBJ admin world. I am curious if it is possible to filter on child objects. As an example SI_EXTENDED_PROCESSINFO.SI_PROMPT_DEFAULT_VALUES

       

      SI_EXTENDED_PROCESSINFO

      > SI_PROMPT_DEFAULT_VALUES