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_DESCRIPTORIt 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_DESCRIPTOROf 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
- Manikandan Elumalai has a series of articles on the SAP Community Network (SCN) about writing Query Builder queries with many examples (see part one, part two, part three, and part four) added June 10, 2013
- Read my review of Julian Romeo’s ebook, The Business Objects Query Builder Guide, added October 18, 2013
- BusinessObjects Enterprise SDK – Relationship Queries (from Ted Ueda on the SAP Community)
- Repository Tables in XI from the BusinessObjects Board (BOB)
- SAP KB 1895241 How to list all reports present in Users Inbox and Personal Folder using Query Builder
And what about you? Glad that the Query Builder is still alive? What are some of your “favorite” Query Builder queries?
hi,
may i know the audit tables and fields details?
thanks in adv,
mahu
Mahu,
These questions are best asked on the BusinessObjects Board or forums on the SAP Service Marketplace. The SAP BusinessObjects Business Intelligence 4.0 audit tables are described in the Business Intelligence Platform Administrator Guide which can be downloaded from the SAP Help Portal.
Great post Dallas!
Hope you don’t mind, we added a link to one our Crystal Reports search FAQs for more information on this little known but useful query tool.
Cheers.
FinditEZ, thanks for the link!
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.
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