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 Enterprise Solutions. For example, the InfoView and Desktop Intelligence objects
are stored in this table.
Following columns are the frequently used from the above repository tables
| Column |
Description |
| 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.
Sample Queries
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.
SI_CUID
SI_GUID
SI_HIDDEN_OBJECT
SI_ID
SI_INSTANCE_OBJECT
SI_KIND
SI_NAME
SI_NAMEDUSER
SI_NEXTRUNTIME
SI_OWNERID
SI_PARENTID
SI_PLUGIN_OBJECT
SI_RECURRING
SI_RUID
SI_RUNNABLE_OBJECT
SI_SCHEDULE_STATUS
SI_UPDATE_TS
SI_INSTANCE
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!
