Business Objects Query Builder is a quickly accessible backdoor to the BO Repository that requires no SDK development but can return pretty much anything an SDK query might. The BusinessObjects repository is a database that contains all the information about the reports, universes, and security that make up your deployment. Unfortunately, the data contained in this repository is stored in a binary format, so you can’t query it with conventional SQL tools. That’s where Query Builder comes in. Using queries that are very similar to SQL, you can tap the information hidden away there. Since the repository is what drives the entire BusinessObjects system, there is a lot to explore.
Accessing Query Builder
To access the Query Builder, point your web browser to your BusinessObjects server. Query Builder can be found at the following URL: http://[server]:[port]/AdminTools/.
Log on as an Administrator to get full access to all the repository objects. From here you can start writing your query. There are three Info objects tables that you can query:
• CI_INFOOBJECTS
Contains objects that are often used to build the user desktop, such as favorites folders and reports.
• CI_SYSTEMOBJECTS
Contains objects that are often used to build the admin desktop and internal system objects, such as servers, connections, users, and user groups.
• CI_APPOBJECTS
Contains objects that represent BusinessObjects Enteprise applications. For example, the InfoView and Desktop Intelligence objects are stored in this table.
Following columns are the frequently used from the above repository tables
Relationship between InfoObjects
CMS InfoObjects are organized into hierarchies based on the relationship between them. The hierarchy could be based on folder based or user group.
From above diagram, the InfoObjects relate to each other not only by folder hierarchy, they may have other relationships. For example, the SI_OWNERID is the property to identify the ownership from the user to the document.
Sample Queries
SELECT * FROM CI_INFOOBJECTS
This will return the details for all the “InfoObjects” in your repository — all documents, folders, and other content. You can filter this list using a WHERE clause.
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’CrystalReport’
will returns all Crystal reports documents
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
will returns all Web Intelligence documents
SELECT * FROM CI_APPOBJECTS WHERE SI_KIND='Universe'
Will returns all Users in the BOE Repository
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’User’
will returns all Users in the BOE Repository
Accessing Query Builder
To access the Query Builder, point your web browser to your BusinessObjects server. Query Builder can be found at the following URL: http://[server]:[port]/AdminTools/.
Log on as an Administrator to get full access to all the repository objects. From here you can start writing your query. There are three Info objects tables that you can query:
• CI_INFOOBJECTS
Contains objects that are often used to build the user desktop, such as favorites folders and reports.
• CI_SYSTEMOBJECTS
Contains objects that are often used to build the admin desktop and internal system objects, such as servers, connections, users, and user groups.
• CI_APPOBJECTS
Contains objects that represent BusinessObjects Enteprise applications. For example, the InfoView and Desktop Intelligence objects are stored in this table.
Following columns are the frequently used from the above repository tables
Relationship between InfoObjects
CMS InfoObjects are organized into hierarchies based on the relationship between them. The hierarchy could be based on folder based or user group.
From above diagram, the InfoObjects relate to each other not only by folder hierarchy, they may have other relationships. For example, the SI_OWNERID is the property to identify the ownership from the user to the document.
Sample Queries
SELECT * FROM CI_INFOOBJECTS
This will return the details for all the “InfoObjects” in your repository — all documents, folders, and other content. You can filter this list using a WHERE clause.
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’CrystalReport’
will returns all Crystal reports documents
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
will returns all Web Intelligence documents
SELECT * FROM CI_APPOBJECTS WHERE SI_KIND='Universe'
Will returns all Users in the BOE Repository
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’User’
will returns all Users in the BOE Repository
