7    Reporting and Analytics

Reporting and analytics are key processes in business intelligence, and several SAP and third-party tools are available to support different analysis needs. In this chapter, we’ll discuss SAP BW/4HANA’s high-performance tools and connectors that enable reporting and analytics in different visualization systems.

Reporting and analytics are crucial functions in business intelligence that collect and organize data available in your EDW landscape into different data visualizations. These visualizations can be used to understand certain key performance indicators (KPIs) and establish actions to improve them.

SAP BW/4HANA provides significant performance improvements by pushing OLAP operations and complex reporting calculations down to the database. In previous SAP BW systems, these reporting calculations and OLAP operations were processed in the application server layer by the OLAP engine.

This ability to process data in the database layer is one of the key factors that contributes to the efficiency in using advanced analytics methods, such as complex statistics functions, scenarios simulation, and predictive analysis.

There are several tools within SAP HANA that can be used together for complex analytic reporting, including SAP HANA analysis process, Application Function Library, Predictive Analysis Library, R scripts for statistical computing and data mining, and text analysis.

In this chapter, we’ll examine important available resources for reporting in SAP BW/4HANA, including its core component, the SAP BW/4HANA query, its integration with SAP BusinessObjects and third-party business intelligence tools, and its ability to make queries available as SAP HANA calculation views to be used as data sources in SAP HANA native developments.

Simple query examples will be used throughout the chapter to illustrate some of the capabilities from each tool presented.

7.1    SAP BW/4HANA Queries

SAP BW/4HANA queries are objects that contain several functions and properties to help you to perform multidimensional analyses on multi-scale datasets and to address different business requirements.

In SAP BW/4HANA, the component responsible for retrieving, processing, and formatting data requested by a query from its InfoProvider is called analytic manager. This is the main interface between you and the database and it makes multidimensional data available to different frontend tools via special integration protocols and components tailored for each of them.

Queries are defined and linked to a specific InfoProvider that contains the data you want to report on. The performance of the query execution is directly related to the precision of the query definition and the query size. A query with several calculation levels, few filters, or with a large number of fields may have longer runtime during its first execution and navigation.

Queries in SAP BW/4HANA have the following main parts:

Before we examine these elements in more detail, it’s important to highlight the process of creating new queries in SAP BW/4HANA and how it differs from previous SAP BW systems.

7.1.1    Creating a Query with Eclipse-Based Query Designer

SAP BW reporting users will be familiar with the SAP Business Explorer (SAP BEx) query designer tool. SAP BEx queries have been the main reporting feature for all previous versions of SAP BW. In SAP BW/4HANA, this feature was migrated from the SAP BEx toolset to the Eclipse platform.

Figure 7.1 shows the well-known SAP BEx query designer. The InfoProvider panel is where all available InfoObjects for reporting from the InfoProvider linked to the query are listed and ready for drag and drop to the desired section of the report. The central section, called Rows/Columns, is reserved to build the query sheet definition, the Properties panel shows all properties related to a certain query element, and the Messages panel shows all messages related to the query being designed.

SAP BEx Query Editor Sheet Definition Screen

Figure 7.1    SAP BEx Query Editor Sheet Definition Screen

Note

During SAP BW migration, existing SAP BEx queries are migrated via the SAP BW/4HANA Starter Add-On for SAP BW 7.5 powered by SAP HANA. After the migration, you need to Open each query using the Eclipse-based query designer, fix any inconsistencies, and Save it.

Figure 7.2 shows the new SAP BW/4HANA query editor based in Eclipse. The elements of the query in this editor are divided into tabs; each tab contains the different query design elements.

SAP BW/4HANA Query Editor in Eclipse

Figure 7.2    SAP BW/4HANA Query Editor in Eclipse

Note

Functions described in this chapter require the installation of the following add-ons in SAP HANA Studio or Eclipse:

For installation links to use in the Eclipse installation path, visit https://tools.hana.ondemand.com.

You can create a query in SAP HANA Studio in the BW modeling perspective in the Project Explorer panel in two ways:

  1. Select the node that has the BW project name or an InfoProvider and select the menu path File • New • Query…, as shown in Figure 7.3.
  2. Right-click the selected node and select New • Query....
    Creating Query via Context Menu from SAP BW Project Node

    Figure 7.3    Creating Query via Context Menu from SAP BW Project Node

Also, you can create a new query as a copy of an existing one by right-clicking an existing query and selecting Copy… from the context menu, as shown in Figure 7.4.

Creating Query via Context Menu from Existing Query as Copy

Figure 7.4    Creating Query via Context Menu from Existing Query as Copy

The first pop-up screen in the query creation, shown on Figure 7.5, asks for the main general properties of the query.

These main general properties are as follows:

Note

The query used as a copy from when creating a new query must be from the same InfoProvider.

7.1.2    Query Properties

Query properties are organized into two groups: general properties and runtime properties. We’ll discuss each group in turn.

General Properties

The General tab shown in Figure 7.6 is the first tab viewed when creating a new query. It contains the general settings and properties of the query.

New SAP BW/4HANA Query: General Tab

Figure 7.6    New SAP BW/4HANA Query: General Tab

This screen is divided into the following sections:

Note

The External SAP HANA View option is only enabled for the query if the InfoProvider has also the External SAP HANA View option enabled.

You can save the query by following the menu path File • Save.

Runtime Properties

In the Runtime Properties tab (see Figure 7.7), you can override settings from the InfoProvider to provide specific values needed for the query. This tab is divided into two sections:

  1. Common Runtime Properties
  2. Runtime Profile Properties

7.1.3    Filters

Query filters are mechanisms to restrict the data selection for the query. They can be defined locally in the query or globally in the InfoProvider to be used in multiple queries. The following subsections describe the properties and use of filters and creation of global filters.

Query Filters

Filters are restrictions on the data selection for the entire query. These filters are sent to the database to reduce the volume of data retrieved and sent to the OLAP processor during the query execution.

The Filter tab (see Figure 7.9) allows you to create fixed-value filters and default-value filters for the characteristics available for the InfoProvider selected for the report. Fixed-value and default-value filters differ as follows:

In both sections, you can associate variables with query filters, allowing them to receive flexible values during query execution (see Section 7.1.4).

Special Feature: Query Definition without Key Figures

Say that you want to define a query that only contains characteristics in the query structure (rows/columns), but every query must contain at least one key figure. In this case, you can add a key figure to the Filter: Fixed Values panel and, if required, add filters to it. For example, you can create a list of customer details with individual sales order amounts greater than $10,000.

Global Filters

Global filters are reusable elements created to be present in different queries sharing the same InfoProvider. This allows you to have a group of queries with the same filter logic. When a change in logic is needed, you won’t need to change each query individually; instead, you only need to change the logic in the global filter and regression test the queries involved.

If you have a filter collection in your report and you want to make it a global filter to be reusable for different queries sharing the same InfoProvider, right-click any filter element in the filter area and choose Save as Global Filter…, as shown in Figure 7.10.

Context Menu of Filter with Save as Global Filter Highlighted

Figure 7.10    Context Menu of Filter with Save as Global Filter Highlighted

A create Filter screen will appear, in which you can enter the technical name and the description of the filter (see Figure 7.11).

Input Screen for Creating Global Filter

Figure 7.11    Input Screen for Creating Global Filter

After clicking Finish, you’ll see the new global filter created in your query, with all filter elements associated with your query (see Figure 7.12).

Global Filter and Its Definition Created from Existing Query Filters

Figure 7.12    Global Filter and Its Definition Created from Existing Query Filters

You can clean up the filter area by removing the local filters made into global filters. You also may want to change the global filter definition, which you can find under the InfoProvider associated with it, as shown in Figure 7.13.

Modeling Perspective Showing InfoProvider’s Associated Elements, Including Global Filters

Figure 7.13    Modeling Perspective Showing InfoProvider’s Associated Elements, Including Global Filters

The global filter modification screen has the same features as the Filter screen in the query designer (see Figure 7.14).

Global Filter Details Screen

Figure 7.14    Global Filter Details Screen

Using the same process, you can create global filters by right-clicking an InfoProvider and choosing New… • Global Filter….

7.1.4    Variables

Variables are used as query parameters for different types of query elements. Unlike fixed-value filters, a variable is filled with values only once the query is executed.

Query elements are often associated with InfoObjects in SAP BW/4HANA; therefore, each InfoObject type restricts the variable types that can be used. When the variables are linked to InfoObjects, these variables become available for use in all queries linked to InfoProviders which contain these InfoObjects.

The following subsections describe the process of creating variables and variable exits (SAP exit, customer exit, and SAP HANA exit) to be used in SAP BW/4HANA queries.

Creating Variables

You can create a variable either via the menu path File • New • Variable or by right-clicking a characteristic, key figure, time, unit info object, or query node and selecting New • Variable.

The New BW Variable screen shown in Figure 7.15 allows you to enter the properties of this new variable.

SAP BW/4HANA: New Variable Properties Screen

Figure 7.15    SAP BW/4HANA: New Variable Properties Screen

When you create a variable, you must select a variable type and processing type, as follows:

Variable Exits: SAP Exits

SAP exits are variables delivered by SAP within BI Content that contain predefined sets of the most commonly used rules associated with different types of InfoObjects. You can use them according to your needs, but you may need to import them from the BI Content and activate them to be used in queries.

Tip

You can find the variables provided by SAP by searching for them using object type ELEM with subtype VAR. The technical names of SAP-delivered objects always begin with a number. For example, to retrieve the current calendar month/year, you can use the SAP-delivered variable Current Month (technical name: 0CMONTH), which is referenced to the characteristic Calendar Year/Month (technical name: 0CALMONTH) in the query filter.

Restriction

Note that the SAP exit processing type can’t be used with external SAP HANA views. The SAP HANA exit processing type must be used instead when the query is created with the External SAP Hana View setting.

Variable Exits: Custom Exits

You can create variables with specific coding to meet your requirements’ rules and complexity. Custom exits allow you to generate default values, populate variable values automatically, and perform validations on all variables after they’re populated.

To use a custom exit variable, you need to create a Business Add-In (BAdI) enhancement implementation for the enhancement spot RSROA_VARIABLES_EXIT; it must include the BAdI definition RSROA_VARIABLES_EXIT_BADI. You can find this enhancement spot in the ABAP package RSROA_VAR either via the ABAP Development Tools perspective in Eclipse or via Transaction SE80. The RSROA_VARIABLES_EXIT_BADI enhancement spot is called several times when the query is executed, and the I_STEP parameter identifies the event processed. Table 7.1 lists the purposes of and actions performed in each step.

Step Description
I_STEP = 0 The step isn’t called from the variables screen, but from the authorization check or query monitor.
I_STEP = 1 The step is called before the variables screen is shown.
I_STEP = 2 This step is only used for non-input-ready variables and triggered after proceeding from the variables screen.
I_STEP = 3 The step is called after proceeding from the variables screen. If any exception is triggered, the variables screen appears again.

Table 7.1    Custom Exit Processing Steps and Their Triggering Events

If a query contains multiple variables with customer exits, when the enhancement spot is called, the system passes all variables values in the internal table I_T_VAR_RANGE, allowing you to create complex logic combining values from several variables of the query.

Restriction

The customer exit processing type can’t be used with external SAP HANA views. The SAP HANA exit processing type must be used instead when the query is created with the External SAP Hana View setting.

Variable Exits: SAP HANA Exits

This processing type is used when you need to implement complex rules in variables and you also want to generate an SAP HANA calculation view linked to the query. The creation of an SAP HANA view is possible because this processing type allows you to use an ABAP-Managed Database Procedures (AMDP) class implementation to determine values for variables.

You can use this processing type with characteristic value variables and formula variables. For formula variables, the variable Entry Type must be set to mandatory.

Unlike customer variables, which allow multiple values and intervals on the variable, SAP HANA exit variables always represent a single value.

The enhancement implementation of an SAP HANA exit is executed during the query runtime and it is either called before the selection screen is shown for processing and populating the input-ready variables, or after proceeding the query execution from the selection screen for processing non-input-ready variables.

Note

You can read up to 20 variable contents from the other variables on the screen if you need to reference their contents during processing.

Since this processing type has SAP HANA database procedures associated with its implementation, you can only create the enhancement implementation for the enhancement spot RSROA_VARIABLES_HANA_EXIT in the ABAP Development Tools perspective in Eclipse.

The RSROA_VARIABLES_HANA_EXIT enhancement spot is also contained in the package RSROA_VAR. The BADI implementation class requires an AMDP class and must contain the following interfaces: IF_BADI_INTERFACE, IF_AMDP_MARKER_HDB, and IF_RSROA_VAR_HANA_EXIT.

Figure 7.16 shows the enhancement spot for the SAP HANA exit variable implementation with the methods to be used: GET_PROPERTIES and PROCESS. After the method name, you must enter the addition by database procedure for hdb language SQLSCRIPT and your code must comply with SQLScript syntax.

Enhancement Spot in Package RSROA_VAR for SAP HANA Exit

Figure 7.16    Enhancement Spot in Package RSROA_VAR for SAP HANA Exit

There are two methods to consider, as follows:

Tip

The CL_RSROA_HANA_EXIT_FALLBACK class can be used as a template to create the customized class for this enhancement spot. It has all the methods and interfaces defined according to the prerequisites listed previously.

Once the implementation is active, as shown in Figure 7.17, access the ABAP Development Tools perspective in SAP HANA Studio via the menu path Window • Open Perspective • Others….

ABAP Development Tools Perspective Icon in List of Available Perspectives

Figure 7.17    ABAP Development Tools Perspective Icon in List of Available Perspectives

Once the ABAP Development Tools perspective is connected to SAP BW/4HANA via a project, you can search for your class and open it in SAP HANA Studio, as shown in Figure 7.18.

Coding for AMDP Class in SAP HANA Studio

Figure 7.18    Coding for AMDP Class in SAP HANA Studio

This code processes variables at the moment the query is executed before displaying the variables screen. Then, default values are shown as filter suggestions for the query data selection (see Figure 7.19).

Query Execution Showing Prompt Screen, with Reporting Year Populated with Last Year per Rule

Figure 7.19    Query Execution Showing Prompt Screen, with Reporting Year Populated with Last Year per Rule

7.1.5    Query Sheet Definition

Use the Sheet Definition tab shown in Figure 7.20 to create the structure of your query. Add collections of characteristics, key figures, formulas, structures, and restrictions to help you navigate in the report, perform different analyses, and create significant information for your business. These elements can be placed in Rows or Columns, or be set as Free characteristics.

Free characteristics are objects that won’t be shown in the default view of the report immediately after executing it. They’re available in the report to be added during runtime, helping you create dynamic analyses.

Query Sheet Definition Tab

Figure 7.20    Query Sheet Definition Tab

The object types available to be added to the query are defined as follows:

You can add objects in each section of the report sheet by right-clicking the area in which you want to add the objects and selecting the desired option from the context menu, as shown in Figure 7.21.

Context Menu with Options to Add Objects to Report Sheet

Figure 7.21    Context Menu with Options to Add Objects to Report Sheet

7.1.6    Query Elements

Query elements are objects added to the query sheet definition to form the structure of the query. The following subsections will describe the properties and use of characteristics, key figures, and formulas.

Characteristics

When you add characteristics to the query sheet definition, they inherit properties defined in the InfoObject as default settings for reporting. However, you can override some of them for the specific needs of your query, along with adding extra options.

Figure 7.22 shows the options you can specify for query characteristics. All properties that you can override have a Toggle Default Value/Manual Entry button (pencil icon) beside them; the ones for which you can use variables to determine their contents during query execution have an Add Text Variable button icon inline image beside them.

Properties of Query Characteristics

Figure 7.22    Properties of Query Characteristics

Let’s examine these properties and some important details of each:

If your characteristic has hierarchies associated with it, the Hierarchy tab allows you to select specific hierarchies and levels to be used in the query, as shown in Figure 7.23.

Hierarchy Properties for Characteristics in Query Design

Figure 7.23    Hierarchy Properties for Characteristics in Query Design

The hierarchy properties are as follows:

Key Figures

Key figures also have specific properties that can be customized for specific query needs. Figure 7.24 shows the following general properties:

Local Calculation Properties in Query Designer

Figure 7.26    Local Calculation Properties in Query Designer

Recommendation

Because local calculations are performed on the client side, for large datasets, these local calculations may significantly affect the performance of the query. Therefore, we recommend using exception aggregations or calculated key figures, because they’re pushed down to be executed on the SAP HANA database instead.

Formulas

You can create specific calculations using key figures in a structure in the query by using a formula. You can include basic key figures, restricted key figures, calculated key figures, reusable key figures, and variables in the formula definition.

To add a formula, open the context menu of the structure that contains the key figures and select New Formula…. Figure 7.27 shows the formula definition screen in which you define your formula. The properties shown are as follows:

Formula Definition Screen in Query Editor

Figure 7.27    Formula Definition Screen in Query Editor

Note

Some operations, such as % of the Grand Total, aren’t available in SAP HANA views. For such operations, you should develop these calculations on the frontend tool for which the SAP HANA view is used as the source of data.

7.1.7    Query Conditions

You can use query conditions to formulate different sets of rules to narrow down your analysis and filter the query data so that only part of the data retrieved is displayed in the query results—for example, to display only the top 10 countries with the largest volumes of sales items delivered.

On the General tab (see Figure 7.28), you can define the following:

Conditions Definition Screen in Query Editor

Figure 7.28    Conditions Definition Screen in Query Editor

On the Assignments tab, you can define characteristics that will be used to apply the conditions. You can apply the conditions independently on each characteristic or on the most detailed characteristic used in the drill-down.

Note

You can add more than one condition parameter and more than one active conditions. When you have multiple active conditions rules, the system displays only data that satisfies all conditions rules.

However, if you have multiple condition parameters in one condition rule, the system shows a union of the data retrieved using each of the condition parameters.

7.1.8    Query Exceptions

Query exceptions allow you to define different limits for query results. Deviations from these limits are highlighted in different colors, which helps you quickly identify areas that require attention. In the General tab shown in Figure 7.29, you can define the following:

In the Definition tab, you can define in which cells or group of cells the alerts will be displayed. For example, in Figure 7.30, we set the alerts defined on the quantity column, performing the evaluation of the amounts before list calculation, and display the alerts in data cells of quantity column and also display in characteristics cells on the row linked to the quantity amount being alerted.

In the Cell Restrictions tab, you can define restrictions to apply to alerts. For example, in Figure 7.31, we selected to show alerts on individual rows but not on result cells.

Exceptions: Definition Tab in Query Editor

Figure 7.30    Exceptions: Definition Tab in Query Editor

Exceptions: Cell Restrictions Tab in Query Editor

Figure 7.31    Exceptions: Cell Restrictions Tab in Query Editor

A visual example of the alerts settings described in the previous paragraphs is shown in Figure 7.32. There, we can see that the Quantity column and the characteristics present in the report have different alerts; the totals remain set in their standard colors.

Example of Query Alerts in Transaction RSRT

Figure 7.32    Example of Query Alerts in Transaction RSRT

Note

Alerts aren’t transferred to the SAP HANA views associated with the SAP BW/4HANA query. Therefore, you can see alert colors by using an ABAP BICS interface via Transaction RSRT or in a frontend tool such as SAP Lumira Designer.

7.1.9    Query Transport

When queries or other SAP BW/4HANA objects will be transported to an environment for quality testing and then to a productive environment, best practice is to create these queries under a package that requires a transport request to collect all the query elements, ensuring that you transport the intended version of all developments to the upper environments.

When creating a query, as described in the Section 7.1.1, you had to link the query to a package. In SAP BW/4HANA, we have a package with the technical name $TMP for local developments, package delivered by SAP, and custom client development packages. If you enter a package name other than $TMP, a new pop-up window will open, asking for the transport request to be used (see Figure 7.33).

In the Selection of Transport Request window, you can do one of the following:

Selection of Transport Request for Queries

Figure 7.33    Selection of Transport Request for Queries

Note

You can access transport requests associated with your user via ADT Perspective • Transports or via Transaction SE09.

To help organize the transport list and verify that the dependent objects for the query are selected, go to the Dependency Structure tab of the SAP BW/4HANA query editor. This tab shows all global elements associated with and used in the query. As shown in Figure 7.34, the Dependency Structure tab shows that the calculated key figure ZCK_PRICEAVG is used in the selected query.

Dependency Structure Tab in Query Editor

Figure 7.34    Dependency Structure Tab in Query Editor