Using SAP Business Objects Query Builder - Perficient Blogs
Blog
  • Topics
  • Industries
  • Partners

Explore

Topics

Industries

Partners

Using SAP Business Objects Query Builder

Business Objects has a CMS repository which stores all information about reports and universes. Since the data is stored in a binary format in database, then we could not query directly to get all these information.

Beside SDK (which need you know java or .net development tools),   Business Objects also provides a tool named Query Builder, which gives you a simply way to get most information.

Here is Query Builder URL:  http://[server]:[port]/AdminTools/, and use Administrator account to log on to get full access to all the repository objects

Below are tables you could query:

Table Name Description
CI_INFOOBJECTS Contains objects that are often used by users, such as folders and reports.
CI_SYSTEMOBJECTS Contains objects that are often used by administrator, includes internal system objects, such as servers, events, and user groups.
CI_APPOBJECTS Contains objects that represent Business Objects Enterprise applications, such as universe, universe folder.

Below are columns that frequently used from the above tables

Column Name Description
SI_ID Identifies each Info Object instance uniquely in the database. Value could be reassigned to another if one is deleted.
SI_NAME Name of the Info Object instance.
SI_KIND Identifies each row by a particular Info Object extended class type.
SI_KIND for CI_INFO OBJECTS includes Webi, Pdf, Excel, Folder, etc.
SI_KIND for CI_APPOBJECTS includes Universe, Universe Folder, etc.
SI_KIND for CI_SYSTEMOBJECTS includes User, UserGroup, etc.
SI_OWNERID User ID of the owner
SI_CHILDREN Number of children for the InfoObject
SI_UNIVERSE Universes used by the document, there might be a list of universes’ SI_ID attached to the property if multiple universes used in one document;
SI_WEBI Webi reports used by the document, there might be a list of reports’ SI_ID attached to the property if multiple reports used in one document
SI_PARENTID Identifies the Info Object instance that operates in a parent relationship to the current Info Object. Typically, a report that is configured to be scheduled is a parent, and each report that is copied and stored when scheduled will view the source report as its parent.

Below are some queries:

  • Get all universes
    SELECT * FROM CI_APPOBJECTS WHERE SI_KIND =’UNIVERSE’
  • Get all users
    SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’
  • Get all report recurring instances
    SELECT * FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_STATUS =9 AND  SI_KIND!=’PROGRAM’
  • Get all ETL BOE recurring scheduler
    SELECT * FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_STATUS =9 AND SI_KIND=’PROGRAM’

Example: How to get all reports name using one universe
1. Get all reports SI_ID by report name
SELECT SI_WEBI FROM CI_APPOBJECTS WHERE SI_NAME =’REPORT_NAME’
2. Get report name by SI_ID list
SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_ID IN (1234, 2345)

 

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up