Eclipse Based Query Modeling in BW4HANA Step by Step

The purpose of this document is to provide an overview on the new and improved eclipse based BW query modeling in BW/4HANA system and how this compares from the traditional Bex reporting.

Overview

SAP has been constantly evolving with its new innovations in terms of new application upgrades or be it with a completely new application itself as a next-generation solution with better features, integrations, performance and so on.

With BW/4HANA coming in as a next-generation data warehousing solution, SAP has finally put a full stop to very old and good Bex Query designer giving way to a whole modern, user-friendly, eclipse based BW Query modeling. The new terminology in BW/4HANA is called as BW Query.

Until recent SAP BW on HANA versions from BW7.4, BW query existed but was optional. However, from BW 7.5 on HANA SP4, the BW Query eclipse based has become mandatory which means we no longer use the Bex query designer.

BW Query provides a new, improved, well integration of BW data modeling in one single tool. Which means it is one stop place to create BW data models and Queries. For example, in the earlier version, to change query properties, you would have had to log into the back-end system with transaction codes like RSRT. But with new eclipse based query modeling, all this is achieved on a single platform. It is easy to jump from one object to another, view, change whether it Is modeling or query will be done in a single tool.

You can view the query result directly using the drill downs, filters etc., without the need to have a JAVA server for web-based querying or to switch to excel based Bex analyzer tool. What more, these queries can be accessed by other visualizations tools, for example, SAP Lumira to create more sophisticated dashboard representations.

Step by Step to create a BW Query

Let us go through each step on how to create a BW query and to have a look and feel of the eclipse based query modeling layouts and options all in one tool.

Please refer my previous posting on BW/4HANA modeling which I will use here to create a BW query on top of the info provider already existing.

Log into BW/4HANA system and connect to your BW project. Right click on the info provider, here it could be a Master data, advanced DSO, Composite provider, or anything else. Here we use the composite provider.

Provide the technical details and finish. There is an option to copy from existing BW query.

You will go to the General tab section. Here you have familiar options to set, like in Bex query designer. Suppressing results, rows, external access permissions, setting key date parameters and variables settings etc. Another new option is to generate HANA view from BW query itself. Below you can see in Remote access section.

I will jump to Sheet definition as this will initial step to draw the layout of the query.  As you see there are three sections which have the same name and mean the same as in old version but with a different layout.

You can find the info provider details on the top left of the screen.

Drag and drop the fields in respective sections.

Below you will see the key figure setting and options available. These are like old versions, like the conversions, calculating result as, scaling, renaming the description, add text variable and so on.

 

The characteristic properties as shown below. Like in the old versions. You can define a hierarchy if available and needed.

You can go to the filter tab to provide any filter restriction if needed. Drag and drop the required field in the top block – Fixed values and right click and restrict.

You have the option to restrict on value, variables, hierarchy node variables or nodes itself. Operator selection and ranges can be defined.

You can define conditions like in Bex. Let’s say for example you want to show the top 10 customers.

Goto Conditions tab, right click on condition workspace and New condition

You can change the description, click on Add. You can select the KPI from the member drop down. Select the required operator, in this case, its TopN. Provide the details and OK.

Next is to create an assignment on the field level. Click on Assignments tab and select the field, in this case, it is Customer.

Similarly, you can create Exceptions like in Bex. I will not walk through this. It is very much simple and similar to before.

You can see Dependency Tab. This is handy as well to provide information on any other queries which use the same variable for example as used in your query.

Now coming to the interesting part which is the Runtime properties. This can be defined from the same query space without having to log into separate application or transaction codes.

I will try to provide some information on these properties.

Common Runtime properties:

Process Key figure with High Precision – With this, the decimal floating points are stored as 34 decimals and 16 decimals if this setting is not activated.

No Parallel Processing – The query is processed as per info provider by splitting the query into subqueries.

Calculate Commut Formula after Aggregation – Sometimes calculating after aggregation improves performance.

Generation Log – Gives technical details on the query statistics and inconsistencies can be identified.

OLAP Effort for Query – With this, we use the benefits of Analytic Manager to handle queries with large results. There are options to select. If nothing selected then infinite is considered.

Runtime Profile Properties

The initial cache settings are same as old versions. I will talk about some features which would be different or new.

Update Cache objects in delta process – The setting helps to only cache the delta updates and not the entire for the complete rebuilding of cache. In the old version, this was available in RSA1 under info provider level.

Operations in SAP HANA – With this setting, we can optimize the operations in SAP HANA DB if we have HANA as underlying DB.

Materialize Intermediate Query Result – This option enables the reuse of intermediate calculations & subtotals.

Use Selection of Structure Elements – This should be activated to ensure only selections and key figures of the columns are passed to the database.

Read Child Members – With this the filters on the structure element will include all its child nodes/members.

Optimization Mode – This setting is to optimize OLAP processor internal memory size and processing.

Stats Detailed Level – This is to control the detail level for statistical data that can be recorded in the system. In earlier BW versions, this option was only available in the Admin workbench (RSA1).

Once the query design is finalized, go to file and click save.

You can find the query under the Composite provider.

 

I will walk through with creating some other query elements like Variables, restricted/calculated key figures.

Variable

Right click on the query and New Variable

Provide the technical details and selections. The details are similar like in older version.

Type of Variable

Processing by. You can write the exists using the BW GUI in the CMOD like earlier. A new option is create BW variables of processing type SAP HANA Exit

Variable represents

You select the reference characteristic accordingly as applicable and finis

Calculated Key Figure:

You can create CKF on info provider level.

Right click on info provider, in this case, the composite provider

Provide the technical details and finish

In the General Tab, you can define the properties such as Number of decimal places, Scaling, Hide or Display etc. like old versions.

In the Aggregation tab, you can define the exception aggregation condition to be handled and define the reference characteristic.

Any currency conversion definitions can be done in the conversion tab

The calculation definition parameters can be set in Calculations Tab. Like old versions

In the Formula Tab from the bottom of the screen, you can define the formula to be implemented. You have options to use from other objects such as Key figures, RKF, CKF, Variables, and apply different functions available.

Once the CKF has been defined click on save.

You can find the CKF under the Composite provider

 

Restricted Key Figure:

Right click on the composite provider

Provide Technical details. You have the option to provide the key figure to be used for restriction or you can define this later as well. I will keep this empty for now.

The General Tab is like what is explained in the CKF section. However, you will not see the Aggregation tab here.

In the selection Tab, right click and you can add the objects. You can continue to create nested objects as well and use re-usable components.

You can select the key figure to be used.

Then right click and add a characteristic to apply restriction On.

Once the RKF has been defined click on Save like done for CKF. You can find the RKF under the info provider.

You have the option to include New selection, New formula like in older version. The design interface is very much like earlier versions.

 

The query can be executed directly and the data can be analyzed in the eclipsed based BW Modelling area.

The query can be accessed in HANA View if the external HAVA View check was selected as mentioned in the steps above. 

 

The Reporting Preview shows the data format with the measures and dimensions defined in the query sheet definition.

The Analysis List UIBB gives more sophisticated data preview.

Hopefully, I shall provide the delta details on how to consume the query in further reporting tools like Analysis for Office/Lumira and in the HANA Views.