Business Objects Query Builder
Hope you had some time to read on my previous blog on CMS Repository tables. As discussed, the CMS repository is a database that contains all the information about the reports, universes and security that make up your deployment. As the data contained in this repository is stored in a binary format, you cannot query it with conventional SQL tools. That’s where Business Objects 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. Query Builder requires no SDK development but can return pretty much anything an SDK query might return.
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:
Contains objects that are often used to build the user desktop, such as favorites folders and reports.
Contains objects that are often used to build the admin desktop and internal system objects, such as servers, connections, users, and user groups.
Contains objects that represent BusinessObjects Enterprise 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
|SI_ID||Identifies each InfoObject instance uniquely in the database. But, this is not a primary key. If the instance is deleted, the value may later be reassigned to a new instance.|
|SI_NAME||Name of the InfoObject instance.|
|SI_KIND||Identifies each row by a particular InfoObject extended class type.
SI_KIND for CI_INFOOBJECTS includes Webi, Pdf, Excel, Folder, FullClient, FavoritesFolder, Inbox, PersonalCategory, Shortcut, MyInfoView
SI_KIND for CI_APPOBJECTS includes Universe, Universe Folder, MetaData.DataConnection,ReportConvTool, WebIntelligence, Discussions, InfoView, CMC, busobjReporter, Designer, AdHoc
SI_KIND for CI_SYSTEMOBJECTS includes User, UserGroup,Connection,secWinAD, secLDAP, secWindowsNT
|SI_OWNERID||User ID of the owner|
|SI_OWNER||User name of the owner|
|SI_CHILDREN||Number of children for the Infoobject|
|SI_CUID||CUIDs are Cluster Unique Identifiers that uniquely identify an InfoObject, within a given cluster and also identify replicas or copies of an object across multiple CMS clusters. Because CUIDs are moderately lengthy strings they are less efficient to use and slower to query for.|
|SI_UNIVERSE||Universes used by the document, there might be multiple universes used in one document; you may see a list of universes’ SI_ID attached to the property.|
|SI_PARENTID||Identifies the InfoObject instance that operates in a parent relationship to the current InfoObject. 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.|
|SI_INSTANCE||Identifies whether the item that is stored in the database row is an InfoObject that was created through scheduling (such as a nightly report) and is therefore an ‘instance‘.|
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.
SELECT * FROM CI_INFOOBJECTS
Returns the details for all the ‘InfoObjects’ (documents, folders, and other content) in your repository; you can filter this list using a WHERE clause.
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’CrystalReport’
Returns all ‘Crystal Reports’.
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
Returns all ‘Web Intelligence documents’.
SELECT * FROM CI_APPOBJECTS WHERE SI_KIND=’Universe’
Returns all ‘Universes’ in the BOE Repository.
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’User’
Returns all Users in the BOE Repository.
Improving Query Performance in Query Builder
1. For improved performance use the below Indexed properties in query’s WHERE clause wherever required.
2. Order of the above properties in WHERE clause also improves the Query performance as the Query Builder processes queries from top to bottom and left to right. So the selection criteria should be ordered from the most restrictive to the least restrictive.
For example, SI_NAME = ‘Test Report’ should be placed before SI_KIND = ‘WebI’ in the query.
I will discuss on few more queries in the next blog that will be followed by the File Repository Server details.
Happy blogging! Have a good year ahead!