Rediscovering the convenience of ‘search’ with Query Builder

Introduction

SAP (and BW) landscapes are often large and extensive. If you found this webpage, that won’t come as a shock to you :-). The surprise will be the amount of useful tools that exist to lend a hand in finding what you need and make sense of the chaos.

In the next few pararaphs I’ll give you a brief overview of one those. A ‘Where Used’ tool to assist you in finding particular Queries or WebI reports along with a bunch of useful information.

Maybe you’re looking to update documentation, you need to find all queries used in a particular report or maybe you’re assessing the impact of a changing a particular query.

There’s a lot of benefit a small tool like this can offer. These next 5 minutes of reading might save you hours of frowning and irritation in the future.

Let’s get to it.

Metadata

The Query Builder is the tool we’ll be looking at. It helps you find metadata of a specific query. What’s metadata again? It’s data that provides information about other data, but not the content of the data itself.

The name of the query you should know. But you’ll get to know all kinds of other insights like:

  • How much is it used
  • Where is it used
  • When was it last changed
  • Does it use prompts

These kinds of things we call metadata and are retrievable through the Query Builder.

The Query Builder is accessible in the same way you access the BO Launchpad or CMC Launchpad. Only adjust the URL-part and set /AdminTools/ instead of /BOE/BI (or /BOE/CMC)

Example

http://cubisbo:8080/BOE/BI  –>  http://cubisbo:8080/AdminTools/

You’ll end up on log-in page where you have to use your credentials and start the system of your choice (DEV/QUA/PROD).

Step-by-step

Once you are logged in, you can use SQL to create a systemwide search.

The below screen shows you not only the option to enter manual SQL. (You also have the option to build a query statement by filling in specific parameters, should you want it.)

(Query Builder start screen, multiple options available)

Let’s define an example of a search.

(Snippet of code to provide an example search)

The last blacked out part is where the Query technical name should be:

–> LIKE ‘%QUERY TECHNICAL NAME%’

Sidenote: if you need to find the technical name of the query, you can look for it in the Bex Query Designer tool (or In SAP Hana Studio/Eclipse for newer versions of BW)

(Screenshot from the Bex Query Designer, the place to find the technical name)

All done.

Let’s check out our results.

(Result of the SQL code search)



As you can see, a lot of interesting details have now revealed themselves.

  • SI_NAME

gives you the Webi report name (Title)

  • SI_WEBI_DOC_PROPERTIES

informs you on the query(ies) source of the Webi and by consequence the selected query

  • Technical ID’s (ID, CUID, File Name) of the Webi report are available in the following objectsSI_ID, SI_CUID, SI_PATH, SI_FILE1

Untangle WebI reports

A different, equally interesting, usecase with this tool could be to list all queries used in a single WebI report.

Some reports have a lot of queries as a source and it might be good to keep track of those.

We create a new SQL statement:

(SQL statement to lookup all queries in a single WebI report)


We run this statement through the engine and get the following result:

(Result of our second SQL statement, looking for all queries of a single report)

Now we have a complete view of the WebIntelligence report.

We get very detailed information, we see all data providers in the report (be they queries, Excel files, …). Is the report refresh-on-open or scheduled, are there prompts setup. Even the system-language of the WebI report is setup in is shown.

Wrapping up

Two short but insightful examples to complete our brief introduction to the Query Builder. We can use it to generate queries, but in this article we found another functionality: the “search engine”.

We saw how to enter a short SQL statement and get lots of information about our query, WebI report and DataProvider.

In short, the Query Builder is a great way to get easy access to useful info.

One small line of code, one giant piece of information.

Written by

Fabian Magnabosco