Altek Solutions Business Intelligence Blog

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:

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.

Related Posts:

1. What’s in a Name? Your guide to SAP BusinessObjects 4.0 Product Names.