The Query Builder

Business Objects contains an application called Query Builder. This application can be used to query the Business Objects Meta Data repository in order to get more details about your the objects created within Business Objects. It can give you answers to questions like “the number of reports in your system”, “a list of users created after a specific date” and “number of reports on top of an universe”. This article gives you more information about how the query builder can be used.

GENERAL

The query builder is a web based application which can be opened via URL:

[notification type=”notification_info_tiny”]http://[server]:[port]/AdminTools/[/notification]

First you need to log in. The results of the queries will only show the objects that you are allowed to see based on your authorization. For that reason it is recommended to login as an administrator.

After logging in, you can start constructing your queries. You can either type in your own query or use the step-by-step guide for assistance. Because the query language is not that difficult (and the step-by-step guide not really useful) I recommend to enter your query directly in the textbox.

Enter the text below in the textbox and hit “Submit query”:

[notification type=”notification_info_tiny”]SELECT TOP 10 * FROM CI_INFOOBJECTS[/notification]

The results will be displayed as HTML table:

Go back to the previous screen by hitting the back button in Internet Explorer. The next session gives you more information for creating your own queries.

CONSTRUCT QUERIES

Queries are created in a language similar to SQL. However, there is not a lot of functions supported (like nesting SQL Statements, the GROUP BY clause or the DISTINCT function). Below the possible functions in Query Builder within the different clauses of a query are described: (SELECT, FROM, WHERE, ORDER BY).

SELECT CLAUSE

The select clause is used to determine which properties will be retrieved from the system. Although there are some properties available for all objects (like, SI_ID, SI_NAME and SI_CUID) most of the properties are object-type specific.

The table below shows the options you have for the select clause:

comma-separated list of object properties To specify which properties needs to be selected
COUNT( property ) Retrieves the number of distinct values of a property
TOP ( int ) specify the maximum number of object to be returned
* To select all of the properties of an object

Examples

  • A list of the Id and Name of all universes

[notification type=”notification_info_tiny”]SELECT SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND = ‘UNIVERSE'[/notification]

  • Total number of Web Intelligence Reports

[notification type=”notification_info_tiny”]SELECT COUNT(SI_ID) FROM CI_INFOOBJECTS WHERE SI_KIND = ‘WEBI'[/notification]

  • A list of 10 Crystal Reports with all properties

[notification type=”notification_info_tiny”]SELECT TOP 10 * FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CRYSTALREPORT'[/notification]

FROM CLAUSE

The FROM clause can be used to specify one or multiple tables that the query needs to run your query on. The table below shows the options you have for the FROM clause:

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.
comma-separated list of tables To select all of the properties of an object

The table below shows per table the object-types (SI_KIND) that are stored:

Table SI_KIND
CI_APPOBJECTS Folder
Universe
CI_INFOOBJECTS Excel
Folder
FullClient
Hyperlink
ObjectPackage
PDF
Powerpoint
Program
Publication
CrystalReport
RTF
Shortcut
TXT
Webi
Word
CI_SYSTEMOBJECTS Calendar
Connection
DiskUnmanaged
Event
Folder
FTP
LicenseKey
Managed
Profile
secEnterprise
secLDAP
secWinAD
secWindowsNT
Server
ServerGroup
SMTP
User
UserGroup

Examples

  • The total number of users in the system

[notification type=”notification_info_tiny”]SELECT COUNT(SI_ID) FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘USER'[/notification]

  • Get all the objects from all tables contain ‘DASH’ somewhere in the name

[notification type=”notification_info_tiny”]SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS WHERE SI_NAME LIKE ‘%DASH%'[/notification]

WHERE CLAUSE (OPTIONAL)

The where clause is used to specify filters for your query. It can be used to restrict the number or result rows. An condition has the following form: [property] [operator] [value]. If you need more than one condition, you can add more and connect them by AND or OR.

The table below shows the operators you can use in the WHERE clause:

= Equal to
!= Not equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
ALL Use ALL to search for objects with properties that have the smallest or largest value among all objects.
(NOT) BETEWEEN … AND… Use BETWEEN to search for property values within a sequential range. It can be used with string values as well as with numeric values.
(NOT) IN Use IN to search for properties that are within a certain set of values.
IS (NOT) NULL Use IS NULL to search for objects that do not have a specific property defined.
(NOT) LIKE Use LIKE to search for values that match a pattern. The pattern can include the following wildcard characters:% » Any string of zero or more characters_ (underscore) » Any single character

[ ] » Any single character within the specified range ([a-f]) or set ([abcdef]).

[^] » Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

There are indexes build on some of the properties in the tables. For having the best performance try to filter on one of these properties:

  • 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_PROGID
  • SI_RECURRING
  • SI_RUID
  • SI_RUNNABLE_OBJECT
  • SI_SCHEDULE_STATUS
  • SI_UPDATE_TS

Examples

  • Get the Universe on which the most Web Intelligence reports are created

[notification type=”notification_info_tiny”]SELECT SI_ID, SI_NAME, SI_WEBI FROM CI_APPOBJECTS WHERE SI_KIND = ‘Universe’ AND SI_WEBI >= ALL SI_WEBI[/notification]

  • Get a list of all reports

[notification type=”notification_info_tiny”]SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND IN (‘Flash’, ‘Webi’, ‘CrystalReport’)[/notification]

  • Get a list of all objects that have a description

[notification type=”notification_info_tiny”]SELECT SI_ID, SI_NAME, SI_DESCRIPTION, SI_KIND FROM CI_INFOOBJECTS WHERE SI_DESCRIPTION IS NOT NULL[/notification]

  • Get a list of objects that contain ‘report’ somewhere in the name

[notification type=”notification_info_tiny”]SELECT SI_NAME, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_DESCRIPTION LIKE ‘%report%'[/notification]

  • Get a list of users that are created after the December 10, 2012

[notification type=”notification_info_tiny”]SELECT SI_ID, SI_NAME, SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘User’ AND SI_CREATION_TIME >= ‘2012.12.10’[/notification]

Note that filtering on datetimes can be done on several ways:

SI_UPDATE_TS > ‘2000.01.11.18:00:00’ for filtering on date and time
SI_UPDATE_TS > ‘2000.01.11’ for filtering on date
SI_UPDATE_TS > ‘2000.01.11.18:00:00’ for filtering on month

ORDER BY CLAUSE (OPTIONAL)

The order by clause is used to specify the order of the query results. You can sort the results ascending or descending on one or multiple columns.

The table below shows the operators you can use in the ORDER BY clause:

ASC Sort the property ascending (default value)
DESC Sort the property descending

Examples

  • Get all Web Intelligence reports and sort them ascending

[notification type=”notification_info_tiny”]Select SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = ‘Webi’ ORDER BY SI_NAME ASC, SI_ID DESC[/notification]

TIPS AND TRICKS

  • The Query Builder shows a TOP 1000 by default (for performance reasons). This can be overwritten by setting your own TOP in the SELECT clause (i.e. SELECT TOP 1500 SI_ID, SI_NAME FROM CI_INFOOBJECTS).
  • The property SI_ANCESTOR can be used to display all the objects in a folder or subfolder (i.e. SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_ANCESTOR = 23 AND SI_KIND = ‘WEBI’)
  • The query builder is a tool that you can use to create ad hoc queries. If you want to store queries and get the query results in MS Excel, you can better use the Business Objects SDK in Visual Basic for Applications (VBA). You do need to have some programming skills, but the documentation of Business Objects is quite good.
This article belongs to
Author
  • Ivo Moor