Query Designer in Eclipse

How to successfully use the Query Designer in Eclipse

We are very happy to present to you our first guest author: Jürgen Noe. He is Managing Partner of Jürgen Noe Consulting UG (haftungsbeschränkt) in Mannheim (www.juergen-noe-consulting.de). His article is about – as is his new book – Query Designer in Eclipse. Many thanks again Jürgen – and now enjoy the read!

 

Along with support for HANA, the introduction to SAP BW 7.4 also brought a silent interface revolution. Up until this SAP BW release, support in terms of software development environments (SDE) had been limited to the SAP GUI. But with the development of the HANA database, the Hasso Plattner Institute relied on the Eclipse as a SDE from the get-go. This now gives developers of SAP applications two relevant development environments. When it comes to, for example, developing HANA database objects HANA Studio is the go-to environment, while traditional ABAP applications still required the SAP GUI.

But SAP had another surprise in store for us: What started out with support for the HANA platform only was eventually expanded with other tools for the development and customization of applications on HANA in Eclipse.

One of the tools here is BW-MT, short for BW Modelling Tools, which allows developers to completely move their typical BW customizing tasks to Eclipse. The creation of InfoProviders, really the entire ETL (extraction, transformation, load) process can now be carried out from start to finish in BW-MT.

The logical consequence was to recreate the central tool for creating BW queries in the Modelling Tools as well. This renders the good old-fashioned Query Designer as a standalone application within the context of the Business Explorer Suite (BEx) obsolete with all releases starting from SAP BW version 7.4.

A quick start to the Query Designer in Eclipse

Against this background, I wrote a book to describe the new functionalities offered by the Query Designer in Eclipse. The book titled “Schnelleinstieg in den Query Designer in Eclipse” and published by Espresso Tutorials in September of 2017 is available in German only.

Query Designer

Click here to purchase the book.

I would like to take this opportunity and use the following paragraphs to outline the book for you:

The book starts out with some basic information about SAP BW and Eclipse in general. In the Eclipse section of the book, I provide a short explanation of how Eclipse is structured and break down essential terms such as plug-in, view, and perspectives. Experienced Eclipse users can skip this chapter.

The third chapter summarizes the BW Modelling Tools. I explain how to call well-known transactions such as the Data Warehouse Workbench in Eclipse and how to create data flows, accompanied by an in-depth description of central views such as the Project Explorer view and the InfoProvider view.

 

Given the central role that the Project Explorer plays in Eclipse, the book includes a detailed walk-through of how to create a new project and work with it. After that, I will explain how to navigate to the InfoProvider view, which is shown in the following figure 1, in Project Explorer:

Infoprovider View

Figure 0.1 InfoProvider view

This view allows you to create global, reusable objects such as restricted or calculated key figures, structures, filters, but also variables. You can find them under the Reusable Components folder in figure 1.

Chapter four then features a detailed description and many screenshots of how to create the different reusable elements and an overview of the various setting options along with their impact. The ability to create reusable components from a central location is one of the reasons why I think switching from the old BEx version to the new Query Designer in Eclipse is worth your while. Gone are the times when you had to click your way through multiple windows in the BEx Query Designer in order to, for example, create a formula variable. What’s more, I also noticed major improvements in navigation and usability.

There is yet another area where BW-MT demonstrates its full strength: It has never been easier to jump from one object to another, change it, and view the changes in the initial object right away. Here’s an example: You realize that you need an additional key figure in the query. It used to be that you first had to create it in the DataWarehouse Workbench, add and assign it in the MultiProvider, and restart the Query Designer for it to register the change before you could insert it into the query. Now, you no longer have to deal with the inconvenience of having to jump back and forth between different tools and transactions. With BW-MT all that changes are the views in Eclipse! You simply switch from the Query Designer view to the master data view, where you create your key figure, and go on to the InfoProvider view to add it to your data model in the MultiProvider. Once you have saved it, you can switch right back to the Query Designer view.

And you can do all of this in parallel in a single tool, using multiple windows, however you see fit!

With Eclipse, you can view the changes to the MultiProvider right away. And even if not, simply hit refresh to have your new key figure available in your query. It has never been so easy!

A detailed look at the query properties

Surely, you are now asking yourself how the Query Designer view that allows you to create, change and delete queries looks like. You can find the answer to this in figure 2:

Query Filter

Figure 0.2 Query definition (filter)

As you can see, the query definition is spread across multiple tabs. The General tab allows you to configure general query properties such as how to display repeated key values and much more.

Figure 2 shows the definition of a query filter. As with the BEx Query Designer, the fundamental query structure with columns, rows, and free characteristics stays the same. You can define this structure in the Sheet Definition tab. All of these configurations are carried out using the context menu, which lets you access all relevant functions in the respective views.

The Conditions tab allows you to specify conditions such as show me all datasets with revenues of more than 1 million euros.

Use the Exceptions tab to define any exceptions. These exceptions allow you to color code rows, columns or individual cells to highlight outliers or special circumstances.

I’m very fond of the Dependency Structure tab, which provides you with an overview of any other queries in which the variables used in query at hand are also used.

The Runtime Properties tab lets you configure the performance properties of the query, for example whether to use the delta cache process and many other properties that you are already familiar with from the transaction RSRT.

Chapter five of the book includes many screenshots and examples that serve to explain the various options provided by the different tabs and their respective impact.

So, what does the query result look like?

Once you have created you query, you will want to test and execute it. With BW-MT, the query result is presented in a separate view, as shown in figure 3.

Result

Figure 0.3: Query result view

You can navigate the query results freely, apply filters, add drilldowns, delete, just like you did in the past. Once again, you will find everything you need in this view, there is no longer the need to have a JAVA server installed to produce web output or to switch to the BEx Analyzer to create Excel output.

For more complex queries, you may need two structures:
In the old BEx Query Designer, you had to work with the cell editor. The cell editor was completely overhauled with the new Query Designer and now includes useful options such as copy & paste. It also eliminates any annoying roundtrips to the server to check the entries, which makes working with the cell editor that much faster. Take a look at the cell editor in figure 4:

Cell editor

Figure 0.4 Cell editor

Last but not least: the variables

The last item on our list are the variables that add dynamic to your queries. The sixth chapter takes a closer look at variables and uses screenshots and simple examples to demonstrate how to create all typical variables.

The advantages of the new Query Designer in Eclipse:

  • A modern, user-friendly and future-proof interface
  • Any existing BEx Query Designer functions can also be found in the new Query Designer in Eclipse
  • Seamless integration of BW data modelling in a single tool

My conclusion is a wholehearted recommendation to switch to the Query Designer in Eclipse along with BW-MT. It has never been so easy to create and test entire BW data models and queries. To me, the Query Designer in Eclipse is a big step towards the future!

Author
Jürgen Noe Managing Partner Jürgen Noe Consulting UG (limited liability)
Phone: +49 (0) 621 72963337
Email: juergen.noe@juergen-noe-consulting.de