The Query Builder is a long-standing but little known Web application tool in SAP BusinessObjects. It gets a small mention in the user manual, but other than that, many people don’t know it exists, said David Hudgins, senior systems engineer for Consultancy by Kingfisher, Inc., and a technical advisor for BusinessObjects Expert’s sister site, SAPexperts.com. The tool isn’t new. It’s been in all versions of BusinessObjects since XI 2. It’s understandable that many people don’t know about the tool because unlike most Web applications within the BusinessObjects suite, there are no references to the tool itself anywhere within the Central Management Console or the BI Launch Pad. In addition, there’s no shortcut for people to get to if you work with BusinessObjects XI 3 or new versions. “When they do find it, most admins quickly become frustrated and vow never to use it again,” said Hudgins. That’s because the tool is not particularly user friendly, says Hudgins. However, don’t write this tool off, he said. It’s worth the time and effort to learn how to use the Query Builder because it has the potential to become one of the most powerful tools in an administrator’s arsenal, said Hudgins. Used properly, it can bring you information you won’t find with other BusinessObjects tools. It can also help track inactive users who are tying up a system.    

   

Query Builder Basics 

The Query Builder administration tool allows administrators to query the BusinessObjects Central Management Service (CMS) system repository and pull out detailed information about repository objects. It also allows users to get this information in a format that’s easy to understand. “To ensure data security, the [BusinessObjects] system uses a binary-cryptography-naming system whenever an object is added to the input or output file repository system,” said Hudgins. Instead of names, the system database records the object’s identification code and its actual location. “This means that there is essentially no way for an administrator to figure out where a file is really located or to decipher its real file name based off its code name — that is, unless you can read binary code,” he said. Enter the Query Builder. This function allows administrators to find specific files and determine their exact location along with other key information, such as licensing keys, active users, connections, universes, and server details. “This tool allows you to query the database and pull specific details concerning objects out of the system repository database easily,” says Hudgins. “It’s really designed for the administrators that work with the system day in and day out.” Administrators commonly use the Query Builder when the system runs low on storage and they need to free up space by migrating an existing File Repository Server (FRS) in a multi-FRS environment to a new network server or location, said Hudgins. But before administrators can move the existing FRS, they need to figure out which FRS is currently active. The Query Builder allows administrators to easily locate the active FRS. Another common problem the Query Builder solves is identifying which users are active. If a system limits the number of concurrent users and it’s at the maximum number, running a query allows administrators to see who is currently using the system. Doing so can track down that one person who opened a bunch of reports, used up seven sessions in the process, and then left for lunch with everything open, said Hudgins.

Putting the Query Builder to Use

To access the Query Builder, you first need to launch your Internet browser. Then take the following steps: 1. Point your browser to your SAP Business Intelligence Web Application Server and port number 2. Find the Query Builder at either of the following URLs:https://[servername]:[port]/AdminTools or https://localhost:8080/AdminTools/querybuilder/logonform.jsp 3. Log on to the Query Builder using an administrative or full access user account Then you must enter your query using the SQL Input box or the Query Builder. This may involve a multi-step process depending on the type of information you’re looking for. You might start with an initial query to get the first piece of information and then continue with additional queries until you get the final piece of data. “You may need to do three, four, or five queries before you get the correct information,” says Hudgins. You can reduce the number of needed queries by using a little known yet powerful path mode option. “Path mode enables a user to query the repository based on the CMS folder structure,” said Hudgins. The Query Builder includes a query-building engine that helps you if you’re not familiar with using SQL scripting language, he said. This feature is included on the Tools main page, but you need to understand the repositories in order to use it successfully. A useful resource is a freeware third-party application called Querybuilder that can help you with code. The app builds a smart query for you. Once the query is built, plug it into the Query Builder, and put your results into Microsoft Excel. The ability to move information into Excel is useful because Query Builder cannot export results on its own to Excel or other applications, said Hudgins.  

Using the Query Builder Successfully

  Building a query requires a basic understanding about how the system is set up. For example, if you are looking for all administrator group users, you first need to understand how these users are grouped and linked within the CMS repository, said Hudgins. A simple user query looks like the following: SELECT si_name FROM CI_SystemObjects WHERE Children("si_name='UserGroup-User'", "si_name='Administrators'") In comparison, by enabling Query Builders path mode option, an administrator only needs to know the system path and enter the following query to return the same results: path://SystemObjects/User Groups/Administrators/members[SI_GROUP_MEMBERS] Another simple query that returns all reports currently using a specific universe looks like the following: SELECT * FROM CI_InfoObjects, CI_AppObjects WHERE Parents("si_name='Universe'", "si_name='E-fashion'") In path mode, the query is again simpler: path://InfoObjects/Root Folder/ * [si_kind='Universe' AND si-name=’E-fashion’]   Understanding how Query Builder works provides you with a powerful tool to help you find critical information within your BI system. “With this tool you can find out anything about the system, including information that you cannot find anywhere else in the system,” said Hudgins.