SAP BusinessObjects Query Builder 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.

 

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?

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni 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.

7 thoughts on “SAP BusinessObjects Query Builder 4.0

  1. Thanks for mentioning my name here Dallas. much appreciated. You were one of my favorites in my early days of Businessobjects learning and I am still following you and your blogs.

    1. Manikandan,

      Thanks writing those SCN articles about Query Builder. The whole community benefits when we take a few minutes to share what we’ve learned.

      Sincerely,
      Dallas

Comments are closed.