bex query designer video tutorial

Introduction

Query designer in BW/4HANA, an Eclipse based tool-set, is a new and improved tool for working with BEx Queries. Since the BW 7.5 edition for HANA, it has been made mandatory that this tool be used to create and edit queries. Harness the capabilities of this tool by understanding its new features, and enhancements to existing functionalities.

In this blog, I will provide an overview of the new Query Designer, highlight the ability to control query technical properties and compare this feature against the earlier versions of query designer.

BW/4HANA Query Designer enables you to access and update technical properties of a query while creating or modifying it, thereby eliminating the need for logging into the back-end system. This was earlier possible only through Query Monitor (RSRT) or Administrative Workbench (RSA1)

RSRT: Query Technical Properties

RSA1: Info-Provider Technical Properties

BW/4HANA: Query Technical Properties

Query technical properties are grouped as under:

Common Run-time Properties
Run-time Profile Properties

Common Run-time Properties

Process KF with High Precision

Used for queries where high accuracy of key figures in required. By enabling this setting, Decimal floating point key figures are stored internally with 34 decimal places. 16 decimal places are used by default if the setting is not enabled.

No Parallel Processing

Processes query based on an Info-provider by splitting it internally into sub-queries. In certain cases, sequential processing can achieve the best improvements in query performance.

Calculating Commutative Formulae after Aggregation

Commutative formulae can be calculated before or after aggregation with the same result. Calculating after aggregation can improve query performance in certain cases.

Generation Log

Generation log helps identify inconsistencies and performance bottlenecks.

OLAP Effort

Optimize the ability of Analytic manager in handling queries with large data sets and increased complexity. This property enforces filters and minimizes drilldown characteristics to optimally handle larger result sets.

Default: Allows users to enter a default value for query result set. If no value is specified then "infinite" is considered as the default value.
(4) Low: To handle queries with smaller result data sets
(6) High: To handle queries with larger result data sets
(5) Medium: To handle queries with medium sized data sets
(9) Infinite: No restriction on the query result set. Returns query results regardless of OLAP effort size

Run-time Profile Properties

Update Cache Objects in Delta Process

Enables only delta changes to be cached. Complete rebuild of cache is not required when underlying data changes. In earlier BW versions, this property was available only at the info provider level and was accessed from Admin Workbench (RSA1).

Specifying the Info-provider Grouping for Delta Caching

Optimize delta caching by controlling info-provider groupings

Advantage -> When data for just one Info-provider in the Multi-provider changes, there's no need to read all data.

Disadvantage -> Requires more memory space because the data cannot be aggregated using the various Info-providers

[0] No Provider Partitioning (Default Setting)
[1] Grouping depending on Info-provider Types
[2] Partitioning in Groups, Info-cubes separate (Key difference between this and earlier options is that infocubes are cached separately)
[3] Every Provider Separate

Operations in SAP HANA/BWA

If we are using the SAP HANA database, the system always checks whether optimized operations in the SAP HANA DB are possible for the Info-provider and selected query.

[0] No optimized operations in SAP HANA/No BWA
[2] Individual access for each Info-provider (as in Release 7.0)
[3] Optimized access – Enables joint optimized access for all infoproviders in composite provider
[6] Exception aggregation (Default) – For infoproviders where HANA optimized operations are not possible, data is read and exception aggregation is processed at the HANA DB level
[7] Formulas calculated in SAP HANA – To optimally process formulas with exception aggregation in HANA DB. Prerequisite – SAP HANA 1.0 SP11

Materialize Intermediate Query Result

Enables the reuse of intermediate calculations & subtotals.

Use Selection of Structure Elements

This option should be activated to ensure that only selections and key figures of the columns are passed to the database.

Read Child Members

Filters on the structure element will include all its child nodes/members.

Optimization Mode

Setting to optimize OLAP processor internal memory size and processing. Following options are available:

Query Will Be Optimized after Generation (0)
Query Optimization with Individual Periods in Days ["Optimization Period" can set our own value in days. By default it is (1).]
Query Optimization Inactive (9)

Stats Detailed Level

Control the detail level for statistics data that can be recorded in the system. In earlier BW versions, this option was only available in the Admin workbench (RSA1).

0 – Aggregated Data: The system writes only one OLAP event (event 99999) for the query.
1 – Only Front End/Calculation Layer Data: The system records all OLAP events
2 – All: Records all data including Front End/Calculation Layer & aggregated data
9 – No Data: The system does not record any data

Dependency Structure

Dependency structure is a valuable addition to the query designer. It graphically represents dependencies between various components, but does have certain limitations such as: in case of replacement path variables, it displays the variable but not the parent query which feeds variable values to child query.