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:
|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
|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)