in System Administration

Using Query Builder to Explore Your BusinessObjects Repository

A little known and little discussed tool included in the SAP BusinessObjects suite is something called Query Builder.  This simple webapp can be used to query your BusinessObjects repository to get all kinds of information not readily available in other places such as the Central Management Console.

The BusinessObjects repository is a database that contains all the information about the reports, universes, and security that make up your deployment.  Unfortunately, the data contained in this repository is stored in a binary format, so you can’t query it with conventional SQL tools.  That’s where 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.

A Quick Tour of 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 begin to construct your query.  There are three “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 Enteprise applications. For example, the InfoView and Desktop Intelligence objects are stored in this table.

A query can be as simple as this:

SELECT * FROM CI_INFOOBJECTS

This will return the details for all of the “InfoObjects” in your repository — all documents, folders, and other content.  You can filter this list using a WHERE clause just like you would in SQL. Using some of the basic properties, you can refine your query.

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
returns all WebIntelligence documents

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE ‘Monthly%’
returns all content starting with the word “Monthly”

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_NAME LIKE ‘Monthly%’ AND SI_RUNNABLE_OBJECT=1
returns a list of WebIntelligence documents that have a name starting with the word “Monthly” and are scheduled

As you can see, you can quickly tailor the query to find the specific details you’re looking for.  You could query CI_APPOBJECTS for a list of all your universes that were changed in the past month, or CI_SYSTEMOBJECTS to get a list of BusinessObjects users that have an e-mail address containing a given domain.  This information can answer questions you have about your own deployment, and can even be leveraged using the BusinessObjects SDKs to provide even more value.

Learn More About Query Builder

That’s it for the quick tour of this tool, but to really appreciate it you will need to consult the documentation.  A complete guide to the BusinessObjects Query language is buried within the Enterprise .NET SDK Developers’ Guide, available on SAP’s Help Portal.  There are two sections of interest in this guide — under SDK Fundamentals, the section “How do I use the query language to retrieve objects from the CMS repository?” explains the basics on how to use this tool, and under Reference, the section “Query language properties” lists all the properties you can query.

Free Web Intelligence Best Practices Guide

We put together a Best Practices Guide for Web Intelligence Development that includes over 20 pages of tips and techniques for developing business-ready reports. Plus we'll show you the common functions that have a negative impact on performance, how to create interactive reports, the best ways to standardize look-and-feel, and much more!

GET YOUR FREE COPY OF THE GUIDE TODAY!

Powered by ConvertKit

Write a Comment

Comment

  1. I’ve been trying to find an answer to “Norm’s” question posted on Nov 4, 2012… Can one use Crystal Reports to query CI_SystemObjects table? I’d like to schedule the report to send to me at a certain time daily, which is the only reason to do so…

    • @jay I don’t know of any way to do this. The only other way that I know to query the repository tables would be with the SDK.

  2. Hi Ryan,

    I don’t suppose you know of a way to get security information out of the repository using Query Builder, or any other tool for that matter? I want a list of folders and the security settings for each folder e.g. which user/group and which access level.

    Thanks in advance 🙂

    • @Christine As far as I know, the Query Builder doesn’t expose security information. You would have to use the SDK to achieve what you’re trying to do. There is a section on Security in the Java SDK Developer Guide.

  3. Ryan,

    Do the same 3 tables “CI_InfoObjects, CI_AppoObjects and CI_SystemObject” that are in BOXI3 are they the same in BOXI4.0?. I can not find anything on this.

    Thanks, Bob

Webmentions

  • Batch Promotions with LCMCLI | Altek Solutions Business Intelligence Blog March 31, 2014

    […] over the suggested 100 object limit for LCM on the web. It’s well worth the effort to learn the BI query language and save yourself the […]