Filtering Data

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Implement a filter to restrict data

Using Filter Operations

Filtering data is often required when analyzing data, to reduce the result set to make it more meaningful to the business user. Typically, you might want to retrieve the sales details for a particular country or region, for a particular range of products, or specific customers (new customers, customers who haven’t ordered any product for more than one year). These three examples relate to filtering based on attributes.

On the other hand, you might want to filter data based on measures. For example, you might want to list only the sales orders for which the total amount exceeds a threshold.

The two approaches can also be combined, for example, when you want to retrieve the list of US customers who have created more than 10 orders during the past month. In this case, filtering the data by country (US) can be done as early as possible in the data flow, but you must compute the total number of orders per customer (for last month) before applying the threshold (10).

So, filtering is sometimes a question of mitigating between performance (filtering as early as possible) and the consistency of the result you get (filtering too early without care can lead to wrong results). This is particularly true when data is aggregated.

Filter Criteria: Hard-coded in Calculation View or Flexible at Run-Time?

A key question when defining filters is whether they should be hard-coded in the calculation view, or provided at runtime to provide more flexibility. It is possible to execute a query with a reporting tool that passes different filter criteria to the calculation view.

A primary goal during modeling is to minimize data transfers between the database and the front end consuming tools. For example, an end user will never need to display a million rows of data. Such a large amount of information just cannot be consumed in a meaningful way.

Whenever possible, data should be aggregated and filtered to a manageable size before it leaves the data layer on its way to the front end reporting tool.

Filters in Calculation View Nodes

In Calculation Views, filters can be applied to many node types. These include the following:

  • Projection

  • Union

  • Join

  • Rank

  • Aggregation

  • Star Join

To define a filter, you display the Filter Expression tab and define a valid expression using the SQL language.

An example of filter expression is "COUNTRY"='US'. The expression can combine several columns and logical operators; for example, "COUNTRY"='US' OR "COUNTRY"='CA'.

Caution

When a column from a data source, for example COUNTRY, is mapped to the node output with a different name, for example, COUNTRY_CODE, the output column name must be used in the expression. In this case, the correct expression would be something like "COUNTRY_CODE"='US'. If you enter the expression "COUNTRY"='US', the expression might validate but building the calculation view will fail.

The name of a column that is used in a filter expression cannot be changed. This is to avoid creating inconsistencies. If you need to change a column name , first remove the filter expression, and set the (adapted) filter expression back after the name has been changed.

Where and When is the Filter Applied?

The behavior of filter expressions depends on the node where the filter is defined.

  • Filters defined in the top node

    In the top node, a filter expression is generally applied before the data processing defined in this node. For example, in an Aggregation or Star Join node, the filter is applied to the data before the aggregation is executed.

  • Filters defined in other nodes
  • In other nodes (NON top nodes), the filter expression is generally applied to the output of the node.

Define and Use Filters

Client-Dependent Views

The CLIENT (sometimes also called SAP client to avoid any confusion with the concept of customer) is a general concept in SAP Systems such as SAP S/4HANA, SAP Business Suite and SAP Business Warehouse. The main purpose is to isolate different types of data (for example, development versus test data) based on a specific column (generally named CLIENT or MANDT, which stands for Mandant (a German word for Client). The values in this column are three-digit numbers, such as 001, 200, 800.

Almost all tables in SAP applications are client-dependent. When you select data form an SAP application table it usually only makes sense to request data for one client number. Mixing client data makes no sense.

The good news is a calculation view can use the SAP client column to enable automatic client filtering when you work on data from SAP applications. You do not need to define an explicit filter on the client column each time you create a calculation view.

Whenever you create calculation views on SAP data sources that include a client column, we recommend that you always define joins on this column. Otherwise, your view might return inconsistent results if there is data for several clients. For example, you might join data for clients 200 and 800 (cartesian product), which in general would not make sense.

Defining Client-Specific Filtering in Calculation Views

Client-specific filtering makes sense only when some (or all) of the source tables have an SAP Client column.

Watch this video to learn about creating a Client-Dependent View.

To implement client–based filtering, you proceed as follows:

  • Define, for all relevant data source tables, the SAP client column.

    This is done in the Mapping tab of the node where the source table is referenced, with the Client Column property.

  • Define how you want to retrieve data.

    This is done in the Semantics node of the Calculation View. You choose one of the three following options for the Default Client setting:

    • Cross-Client: All the data is retrieved regardless of the client number.

    • Fixed Client: You specify an SAP Client number (for example, 200) and the data sources are automatically filtered to include only the rows with this client number.

    • Session Client: The source tables are filtered dynamically based on a client value that is specified for each user in the USERS table of SAP HANA Cloud.

      The Session Client is the default setting applied to a new view.

Let’s consider a simple table that contains data for two different clients, 200 and 800. This information is stored in a column named MANDT. You create and build a calculation view based on this table, and then query the data with your user. The following table shows what data is retrieved depending on both the default client setting for the calculation view and whether or not the MANDT column has been defined as the (SAP) Client Column in the view properties.

Default Client Setting Effect

Default Client SettingEffect when NO Client Column is specifiedEffect when MANDT is defined as the Client Column
Cross-ClientAll rowsAll rows
Fixed Client: 200All rowsOnly rows for which MANDT=200
Session Client (use case 1: the user has no default client assigned)All rowsNo rows at all
Session Client (use case 2: the user has default client 800)All rowsOnly rows for which MANDT=800

Important Note Regarding the Technical User in the SAP Business Application Studio

In the SAP Business Application Studio, you connect with a regular user, but most of the tasks you perform with defining a calculation view and previewing its data are executed on your behalf by a technical user.

Because the technical user has no default client assigned, if you specify a Column Client property for a calculation view and set the Default Client to Session Client, the data preview in the Developer perspective will not retrieve any data.

There are two alternative approaches that can be used to bypass the technical user. These are as follows:

  • You can query the data from an external tool with your regular user (for example, STUDENT##)

  • You can display the column view data in the Database Explorer perspective from a connection to the classic database (for example, in the training environment, H00 DB). A query from the classic database connection does not involve the technical user.

    This can be done either by opening the column view or by executing an SQL query on top of it.

Define Filtering on SAP Client

Save progress to your learning plan by logging in or creating an account