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?

Author: Dallas Marks

I am a business intelligence architect, author, and trainer. I help organizations harness the power of analytics, primarily with SAP BusinessObjects products. An active blogger, SAP Mentor and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting about business intelligence.

7 thoughts on “SAP BusinessObjects Query Builder 4.0”

Leave a Reply