Go The Extra Mile:
How Can You Overcome The Limitations Of Query Builder?

Back to the basics

Query Builder is a tool available in SAP BusinessObjects since Crystal Enterprise 8.5 that allows you to understand what content exists in the CMS (Central Management Server). It is commonly used by SAP BusinessObjects administrators and developers looking for information about their users, reports, and universes.

However, Query Builder has its limitations and you will face situations where Query Builder is not enough. It is a technical tool, and to make queries against the SAP BusinessObjects repository, you need to have the technical knowledge because SAP doesn’t provide any documentation or tutorials on how to create them in the tool, other than online blogs there is no help. You also need to know a language that resembles SQL — without it, you’ll be a little bit stuck! You can find the basic SQL language requests here.

To create proper queries, you need to equally familiarize yourself with these three tables and the content in their rows:

Source: https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/ by Manikandan Elumalai

Querying the relationships between different objects of the tables requires specific Query Builder knowledge. However, there are only three tables and there are vast amounts of information stored in each one, making retrieving the data very difficult.

This is what the Query Builder application looks like in Business Objects and you can access it via this link: http://MyServer:Port/AdminTools

The limitation game

Once you submit your query, results are shown on a web page…but that’s about as far as it goes. With no option to export the results in XLS or CSV format, you can only review the results on your screen, making it hard to leverage and manipulate the output. Neither the request nor the results can be stored. It’s a one-shot process.

The above is an example of a query result. As you can see, there are no simple columns and some data is subdivided making it hard to understand what the formatted data means. This subdivided data is called “property bags” and cannot be queried with Query Builder.

Benefit of the doubt

Despite the technical knowledge needed behind the tool and its constraints, we can’t ignore the fact that it is completely free to use.

As I said above, you need to know the language that resembles SQL in order to carry out queries but fortunately, since the BI 4.2 SP03 release, SAP has put one universe to access the CMS data, which allows you to pass by having to know this technical query-language, be able to see the data in WebI and export the data in Excel if you want. Called the CMS DB Driver, it has the same functionalities as the Query Builder but the advantage is that we can create WebI documents — a great way to format the data in a more understandable way.

However, the CMS DB Driver still remains limited in its usage and is very hard to use. Again, there is no documentation to help you and technical knowledge is still needed given mapping the InfoObjects to a universe can be challenging. It is also confusing the way the levels are sorted in the layers, plus it still doesn’t allow you to fetch data from the Filestore.

Get more with 360Suite

It is clear that Query Builder by itself isn’t enough to be able to really take advantage of your SAP BusinessObjects metadata. Implementing a third-party tool such as 360Suite will give you complimentary access to not only the same data as Query Builder (the System Database) but to both the Auditor and the FRS (file repository server), with the possibility to leverage this data by carrying out impact analyses or analyzing the usage and non-usage of objects within your environment.

A third-party tool will also give you the possibility to carry out complex requests that aren’t possible with Query Builder. For example:

List reports that use a database column,
List universes that reference a database column,
List reports that use a universe object.

To access this information, 360Suite offers two modules: 360View and 360Eyes.

The 360View Drill Down function is a tool that resembles the most to Query Builder in its methodology as it only requests data from the CMS. This tool is also aimed at less technical users because it doesn’t require you to learn the SQL language that is needed with Query Builder. With the graphical interface, users can create requests by using predefined objects and filters, rather than having to type in the technical terms. What makes it more useful than the Query Builder is the fact that the results provide actionable data. For example, you can delete WebI documents or even targeted users per request. You can also retrieve complete documentation on selected users, retrieve the data providers, and prompts of selected documents.

The second module, 360Eyes, allows you to go further and fill the gaps that Query Builder doesn’t. With 360Eyes, you are able to request data from the CMS, Auditor, and Filestore. On a more functional level, we are able to document all objects in your environment, for example, the permissions, users and groups, universes, user connections, and are able to run impact analysis and understand what is used and not used. Any output can also be retrieved by a WebI document. We provide a library of WebI documents in order to efficiently query the metadata it aggregates.

An example of an impact analysis report identifying documents that contain specific Universe objects, SQL expressions, or variables.

An added extra here is that with 360Eyes, you will be able to track inconsistencies between the CMS and FRS. For example, the CMS can contain links toward FRS documents, and this could create various inconsistencies such as:

The link exists but the document in the FRS no longer exists or,
The document exists in the FRS but the link doesn’t exist in the CMS.

These inconsistencies can be extremely frustrating because if the link exists but not the report, it works exactly like on a website when you click on a link and get the “404 error Page Not Found”. On the contrary, if the report exists and not the link, you cannot consult the report and therefore it is taking up valuable space in the machine. Query Builder doesn’t have the means to detect these inconsistencies but 360Eyes can.

Take it a step further

Query Builder allows you to query the whole CMS database but it prevents you from going any further with your metadata. With 360Suite we can help you explore your whole content in a way that allows you to interact with it, providing you with a bigger picture of your content landscape.

BI-on-BI is a discipline that consists of analyzing BI metadata in order to take decisions and actions. 360Suite empowers SAP BusinessObjects professionals by pulling metadata and offering powerful automations.

Don’t let the limitations of Query Builder stop you from going further. Contact us today and one of our experts will help you.