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.

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, now in release 2.0,  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 these could be easily exported to Microsoft Excel.

Learn more about EV Technologies and Sherlock 2.0 at SaveTheCMS.com.

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