Creating Simple Queries

To analyze a dataset in SAP BW/4HANA, you define queries on top of InfoProviders. By selecting and combining InfoObjects such as characteristics and key figures, or reusable query elements such as restricted and calculated key figures in a query, you define how you want to evaluate the data that comes from the selected InfoProvider.

Note

Although it is possible to build BW Queries directly on top of all types of InfoProviders such as DataStore Object (advanced) and Open ODS Views, it is best practice to build a CompositeProvider on top of, for example, a DataStore Object (advanced) as part provider and then build the query on top of the CompositeProvider.

This is because the InfoProvider on which the query is built cannot be replaced later with another, but it is possible to replace the part providers of the CompositeProvider. So better create the fixed assignment between the query and the CompositeProvider.

A CompositeProvider is a very powerful modeling object. The strength of a CompositeProvider is in preparing data sets for consumption by queries. By comparison, the strength of a DataStore Object (advanced) is in its handling of complex data loading scenarios.

The key parts of a query definition are navigation and filters:

• For navigation, you choose a default layout assignment for each characteristic and key figure that has been selected from the InfoProvider. This means that you assign each characteristic and key figure to either the rows or columns section of the layout. Characteristics can also be left unassigned which means they belong to the free characteristic section. Those characteristics can moved to the rows or column by the business user when the query is executed and the results appear. The assignment to the rows and columns section for each characteristic and key figure determines the initial view for the query. The business user is able to change these assignments from the query results. Remember, they are only defaults when defined in the query.

• The filter selection apply to the entire query. When defining the filter, you select values from one or more characteristics to restrict the data. You can also define one key figure as a filter. If you do this, the query can only have characteristics in the rows and columns and not key figures. All the InfoProvider data is aggregated using the filter selection for the query.

You can parameterize queries by using variables for characteristic values, hierarchies, hierarchy nodes, formulas, and texts, or by defining new variables where necessary.

To make the selection of InfoObjects more precise, you can do the following:

• Restrict characteristics to characteristic values or hierarchy nodes

• Define selections

• Define formulas

• Define exception cells and help cells

• Use local structures

• Define conditions

• Define exceptions

• Define calculated and restricted key figures as reusable query components

The queries are built in the SAP BW Query Designer, which is a part of the SAP BW Modeling Tools in SAP HANA Studio.

The SAP BW Modeling Tools provide an Eclipse-based integrated modeling environment for the management and maintenance of SAP BW/4HANA metadata objects.

The main objective of the tool is to support SAP BW/4HANA metadata modelers in today’s increasingly complex BI environments by offering flexible, efficient modeling tools. These tools integrate with ABAP Development Tools as well as with SAP HANA modeling tools and the consumption of SAP HANA elements in SAP BW/4HANA metadata objects, such as Open ODS views or CompositeProviders. The SAP BW Modeling Tools have powerful UI capabilities.

The Sheet Definition tab is where you choose where the place the characteristics and key figures in the query layout.

The Sheet Definition tab has the following sections:

Columns

Place query objects, such as key figures or characteristics, in this section so that they appear in report columns when you execute the query.

Rows

Place query objects, such as key figures or characteristics, in this section so that they appear in report rows when you execute the query.

Free

Place characteristics that you would like to be available for a user, in this section. Free characteristics do not appear in the initial view of a query result set. The user can bring these characteristics into the rows or columns using the navigation controls of their chosen reporting tool.

Properties

When you select a query object, you can view and edit its properties on the right of the Sheet Definition tab. The properties influence the behavior and display features of each objects.

A filter is an object that describes a multidimensional extract of data from a data set.

You can use filters in reporting, analysis and planning, to restrict data to a specific business area, specific product groups, or specific time periods. By restricting the data set, you can ensure that users only have access to the data that is relevant to them.

On the Filter tab, you define the filters to apply restrictions to the characteristics. This type of filter applies to the entire query. There are other types of filters that restrict only parts of the query. You will learn about those later.

All the InfoProvider data is aggregated using the filter selection of the query.

You can define two different types of filters:

• Filter: Fixed Values

  The selection scope cannot be enlarged at runtime, it only can be narrowed through navigation by the user.

  To enable navigation for a characteristic that is included in the Filter: Fixed Values section, it must additionally be included in the Rows, Columns, or Free Characteristics section. Otherwise, the filter is applied but further navigation on that characteristic is not possible.

• Filter: Default Values

  The selection scope can be enlarged, narrowed, or deleted at runtime using navigation.

  All characteristics contained in the Filter: Default Values section are enabled for navigation because they are automatically contained in the Rows, Columns, or Free Characteristics sections, and vice versa.

  Example: You want to analyze all customers in a query. In the query's start state however, you initially only want to see a particular customer.

In a query, you can combine fixed value filters with default value filters.

If you create a complex filter with several characteristics for a query, and if you think you will use this filter frequently, you can save the filter as a reusable filter.

A reusable filter belongs to an InfoProvider; therefore, it can be reused in all queries that are based on the InfoProvider.

A reusable filter can consist of fixed filter values, default filter values, and any combinations.

Changes made to the reusable filter affect all queries where it is used.

To make changes in a specific query only, convert the reusable filter into a query-level filter by choosing dereference. Changes made later to query-level filter do not affect the reusable filter or queries that you made with the reusable filter.

The General tab is used to define settings that apply to the entire result set of the query. The General tab is divided into several screen areas.

General Settings

In the General screen area of the General tab, you can view and configure the following settings:

Field	Description
Technical Name	This is the technical name of the query and must be unique in the SAP BW/4HANA system. A technical name cannot be changed.
Description	A short description of the query. You can change the description at any time. You can also include variables so that some words in the description are dynamic and determined at runtime - for example, dates.
InfoProvider	Displays the technical name of the InfoProvider that the query is based on. Choose the link to open the maintenance screen for the InfoProvider so you can learn more about it.
Key Date	Every query has a key date. For time-dependent master data such as attributes, texts, or hierarchies, the key date determines the date for which time validity the data is selected. The default value for the key date is the date when the query is executed: <today>. You can also enter a fixed date or refer to a variable for a dynamic date.

Note

Use a consistent naming convention for technical names to identify queries easily. A well thought-out naming convention is helpful for authorizations planning.

Display Settings

In the Output Settings, Result Location, Zero Suppression, and Universal Display Hierarchy screen areas of the General tab, you can view and configure the following settings:

Display Option	Description
Adjust Formatting after Refreshing	When a query is refreshed, result cells are formatted by default so that they are emphasized. Deselect this option to improve query performance or to use your own format templates.
Suppress Repeated Key Values	When a query is executed, only the first key or description displays by default for each characteristic. Additional identical keys or descriptions are suppressed.
Show Scaling for Measures	Include scaling factors in a row or column header, for example *1000 EUR.
Sign Format	Choose how to present negative values.
Zero Format	Choose how to present zero values in the result.
Result Location	You can choose where to position results in rows and columns. By default, row results are presented below the rows, and column results are presented at the right-hand side.
Zero Suppression	You can hide the complete row or column, if only the result of the row or column produce a result of zero. Alternatively, you can hide the complete row or column only when all the single values in the row or column are zero.
Universal Display Hierarchy	To achieve a better overview, the characteristics in the rows or in the columns can be arranged in a hierarchical display. In the Expand to Level field enter the characteristic up to which the hierarchical display should be expanded initially.

For queries and reusable components, SAP BW/4HANA provides tools to perform useful activities such as search, where-used, and to copy and delete them.

Note

Not all, but many of these tools are used during demonstrations and videos in this Learning Journey.

Here you can see the meaning of numerous icons:

Note

The following options are available for data preview:

• Reporting Preview
• Analysis for Office
• BW Cockpit
• UIBB

To preview the queries, Analysis for Office is mostly used in this course.

The data preview options are configured via the customizing transaction SPRO: SAP BW/4HANA Customizing Implementation Guide → SAP BW/4HANA → Analysis → Set Query Execution for Query Design Tools.

Many functions can be accessed from the context menu, as shown in the following figure:

In the InfoProvider view, if there are many Calculated Key Figures and Restricted Key Figures, you can create further folders for hierarchical grouping. This feature is not available for Filters and Structures.