The Business Objects Query Builder Guide

A helpful book if you use the Query Builder a lot.

I don’t analyze blog stats very often, so it came as a bit of a surprise that the article about the SAP BusinessObjects Query Builder 4.0 is the sixth most popular on my blog. The Query Builder is crude and— generally speaking— so is my language when I’m required to use it. But the Query Builder can sometimes provide deeper insight into what’s going on than the Central Management Console (CMC) administration tool is willing to tell you.

The reason Query Builder exists is due to the cryptic nature of the SAP BusinessObjects system database, sometimes called the CMS database or repository. If you look at the database schema (BI 4.0/4.1 shown), you’ll see tables with the following names:

  • CMS_ALIASES7
  • CMS_CCFRONTIER7
  • CMS_FRONTIER7
  • CMS_IDNUMBERS7
  • CMS_INFOOBJECTS7
  • CMS_LOCKS7
  • CMS_RELATIONS7
  • CMS_ROOTFRONTIER7
  • CMS_SESSIONS7
  • CMS_VERSIONINFO

Sadly, by design these tables are very difficult- if not impossible- to query without resorting to SDK programming. The first thing to learn about Query Builder is that you don’t use it to query the actual tables in the database. Query Builder is a thin veneer atop the SDK, so you’ll instead build queries on these fictional tables:

  • CI_AppObjects
  • CI_InfoObjects
  • CI_SystemObjects

A quick Google search will turn up some simpler queries on these tables. But for more serious inquiries, you’ll want to get a copy of Julian Romeo’s The Business Objects Query Builder Guide. The latest version 1.3 that includes BI 4.0 updates was published February 1, 2012 and my electronic receipt says I purchased mine just 5 days later. The book explains how to use the step-by-step query “wizard”, write SQL in the Query Builder, and use Relationship and Path Queries. The book is easy to read and contains a lot of examples.

The book is a good value at $29 and an even better value if you convince your BI Manager to put it on the corporate credit card.

Disclosure of Material Connection: I purchased this book with my own funds. It was not a free review copy. I was not required to write a positive review. The opinions I have expressed are my own. Some of the links in the post above are “affiliate links.” This means if you click on the link and purchase the item, I will receive an affiliate commission. Regardless, I only recommend products or services I use personally and believe will add value to my readers.I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: “Guides Concerning the Use of Endorsements and Testimonials in Advertising.”

SAP BusinessObjects Query Builder 4.0

Administrators will appreciate that the Query Builder is still lurking about in SAP BusinessObjects BI 4.0.

SAP BusinessObjects Business Intelligence 4.0 went into General Availability (GA) on September 16, 2011. Although there are numerous administration improvements in the Central Management Console (CMC), fans of the less refined yet useful Query Builder will be thrilled that it remains in BI 4.0, despite its disappearance from the Windows Start Menu and its new location. You can find the BI 4.0 Query Builder at http://[hostname]:[portnumber]/AdminTools/querybuilder/logonform.jsp, or http://localhost:8080/AdminTools/querybuilder/logonform.jsp if you’re using the default Apache Tomcat web application server.

UPDATE: Query Builder lives on in SAP BusinessObjects Business Intelligence 4.1 at the same location.

Query Builder provides a crude but effective means of querying data stored in the CMS database. Using a rudimentary SQL interface, you can query data that is exposed through the SAP BusinessObjects SDK into virtual “tables”. Your database administrator won’t find these imaginary tables- CI_APPOBJECTS, CI_INFOOBJECTS, CI_SYSTEMOBJECTS- in the CMS data structure. See my related article, Queries to identify universes and their universe connections.

I asked Eric Vallo, chief architect at EV Technologies, whether the Query Builder was still relevant to SAP BusinessObjects administrators. He replied that:

The Query Builder gives good, quick insights in small chunks, to high level data. You can gain quick access to users in groups, reports in folders, and much more. The bad news is, there is no easy way to automate or more granularly report on this information.

Several add-on tools have cropped up to overcome Query Builder’s limitations, and EV Technologies’ Sherlock is one of them. Here’s Eric Vallo again:

Technologies such as Sherlock automate the acquisition of this data to remove limits on performance, row counts, and drill down many levels deeper into the hierarchy of the API to make this information easier to report on. Further, this analysis can be extended in more detail into the semantic layer, detailed report construction, and usage analysis.

As an example of using the Query Builder, the Administering Servers course for XI R2 contained an activity using the Query Builder to determine which of multiple Input or Output File Repositories was active. Sadly, the activity was removed from the XI 3.0/3.1 course. Here’s the original query for the Input File Repository Server (iFRS).

SELECT TOP 1 SI_NAME, SI_SERVER_DESCRIPTOR, SI_SERVER_IS_ALIVE, SI_STATUS_CHECK_TS
FROM CI_SYSTEMOBJECTS
WHERE SI_PROGID='CrystalEnterprise.Server'
AND SI_SERVER_KIND='fileserver'
AND SI_SERVER_IS_ALIVE=1
AND SI_NAME LIKE 'Input%'
ORDER BY SI_SERVER_DESCRIPTOR

It requires a slight modification to continue to work in BI 4.0.

SELECT TOP 1 SI_NAME, SI_SERVER_DESCRIPTOR, SI_SERVER_IS_ALIVE, SI_STATUS_CHECK_TS
FROM CI_SYSTEMOBJECTS
WHERE SI_PROGID='CrystalEnterprise.Server'
AND SI_SERVER_KIND='fileserver'
AND SI_SERVER_IS_ALIVE=1
AND SI_NAME LIKE '%Input%'
ORDER BY SI_SERVER_DESCRIPTOR

Of course, you can modify the query to SI_NAME LIKE ‘%Output%’ to examine the Output File Repository servers (oFRS) instead.

And here are the results. I still wish they could be easily exported to Microsoft Excel.

Resources

And what about you?  Glad that the Query Builder is still alive?  What are some of your “favorite” Query Builder queries?

Queries to identify universes and their universe connections

Query Builder connections for troubleshooting universe connections.

The Query Builder can be used to analyze universe connections. I recently used the Query Builder to assist in how many universes (and which ones) were using a specific connection. Using the information, I was able to update and republish universes to use the “preferred” universe connection. Once all universes were updated, the Query Builder clearly showed that the “non preferred” universe connections were no longer in use and could be safely deleted using the Central Management Console (CMC).

First, determine the SI_ID of the connection.

SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_CONNUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=’MetaData.DataConnection’
— AND SI_NAME = ‘DW’

ORDER BY SI_NAME

Then, use the list of IDs in the SI_CONNUNIVERSE collection to build a second query to inquire about the universes that use that connection.

SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND=’Universe’
AND SI_ID IN (692,670,633,634,699)

The Query Builder can also identify unused universe connections. Using the first query above, look for connections where the SI_CONNUNIVERSE has a single attribute of SI_TOTAL with a value of 0. For example, no universes are using the test only connection.

SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_CONNUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=’MetaData.DataConnection’
AND SI_NAME = ‘test only’

ORDER BY SI_NAME

Accordingly, the test only connection can be safely deleted from the Central Management Console (CMC).

Resources

If you’ve found a useful resource regarding the Query Builder, feel free to post a comment to share with others.