Dear BW Expert:

We are on BW 3.1 and our queries are defined with the BEx Query Designer. We define our variables in the Columns section of the BEx Query Designer rather than under Filters or Free Characteristics.

I always thought that when users specify a variable defined under the BEx Query Designer Filters, it was executed as a “real” filter, meaning that the OLAP processor extracted the data using that variable and any other filters. The front-end tool, then, applied the other variables and displayed the results.

A colleague recently told me that this is not what is happening. According to my colleague, the OLAP processor extracts all the data using the fixed filters and it is the front-end tool that applies the variables. Let me tell you, I have a hard time believing that’s true!

I was also under the impression that no variables are applied if they are added to the Free Characteristics section of the BEx Query Designer. Is that always the case? Even if you use Type H readings for queries in the Read only during navigation / hierarchy expand mode? Now, I hear that for variables, BW retrieves all of the data and then asks the users which subset they want to work with. Is that true!?

While we are on the subject of filtering, I’d like to ask you about navigational attributes. What happens if a query contains a navigational attribute in its free characteristics that is used for filtering only? Does the report display the pure attributes?

Guylaine Bélisle, Hydro Québec

Restricting data using variables is a core concept that I teach in all my front-end classes. It is a great way to enhance overall system performance. Although many query designers practice this basic design technique, most are not aware of why they do it and how it works.

Allow me to give you a quick answer to your questions and then I’ll elaborate further and provide you with some concrete proof. Fixed filters and variables are applied to the selection from the database server. Both take nearly an equal amount of time to process with the exception of variables for which the system must first derive a value if it is not supplied by the end user. These may take a fraction of a second longer. Let me quickly add that if custom ABAP is used to derive the variable, all bets are off!

I’ll show you the benefits variable-based restrictions offer to users of well-designed queries and share some convincing evidence for my answers, including those regarding navigational attributes. Most of my advice is general enough to pertain to any implementation of the BW.

I sense from your question that you are a skeptic and other BW practitioners may share your views, so I’ll explain how you can run my performance experiment yourself and see the result on your own system. In addition to underscoring my point, I’m certain you’ll find my experiment valuable when you make future query design decisions.

Definitions and Methodology

Before I get to the details, let’s define a few terms related to reporting and query design. For the sake of this article, let’s use the following definitions:

• Restricting: Limits a data set returned to the user based on specific characteristic values. With this term, I’m referring to limitations applied in the query design prior to the initial execution on the database. The purpose of restricting is to focus the user on relevant data and improve performance
• Filtering: Further limits data to be analyzed by end users and is accomplished through navigation options on the Web or in the BEx Query Analyzer. The purpose of filtering is to zero the end user in on a data set more specific than that initially supplied.
• Navigational attributes: Part of an InfoObject’s master data table, navigational attributes are not included in a dimension table of the basic star schema. They allow reports to be designed using master data linked to transactional data.

Now that you have the basic definitions, let me explain the methodology I use to examine the effects of restricting queries on variables, hard-coded entries, or both. My metric for performance calls for determining the number of records extracted to the application server, and the number of cells and formatting information transferred from there to the front end (Figure 1), and the time required by the system to perform these tasks. Note that I do not focus on time as much as the number of records, which closely correlate.

Figure 1

Key performance statistics

I use the BW statistics InfoCube to obtain performance data for query execution because it provides a user- friendly output monitor that is compatible with the entire BEx reporting tool set. You can also use transaction SE16 to view table RSDDSTAT, which contains raw statistics data. In addition, the Expert mode of transaction STO3 (Figure 2) displays extensive performance details, and you can call transactions RSRT and RSRT2 to access the Query Monitor, shown in Figure 3.

Figure 2

Transaction ST03 set to the Expert mode

Figure 3

Options available with transaction RSRT

You should be aware of a couple of settings. The statistics in the InfoCube used for my analysis are based on data contained in RSDDSTAT tables, and the statistics InfoCube must be enabled to collect stats for both reporting and loading. Use the Tools option for the InfoProvider area in RSA1 following the path Tools>Statistics for InfoCubes.

I use RSRT to change the query properties to ensure a level playing field and prevent other influences from skewing my test results. Using the Properties button in RSRT, turn off the Cache Mode (Figure 4) to prevent OLAP-cache data from a prior query from being read and to ensure the executed query accesses the database. Set the Read mode for the query as the default, so the system reads only that data currently required instead of the details for all the free characteristic a user might possibly request. This Read mode setting determines how much data from the database server the application server reads.

Figure 4

Turn off Cache mode and set Read mode in RSRT

To ensure that aggregates don’t influence the results, I suggest you perform a little preventative maintenance on the InfoCube that supplied the queries via RSA1>Context menu>Maintain Aggregates. Once on the aggregate maintenance screen, turn off all the aggregates. After this is done, you will have created a testing environment that is free of outside factors.

Are Variables for Restricting “Real Filters”?

I set up five basic queries to examine what role using variables for restricting has on system performance. The first, query A, represents a simple Sold-to party report (Figure 5) and the other four are slight variations of that query.

Figure 5

Basic query with no restrictions

Query B has a hard-coded restriction set in the Rows of the BEx Query Designer (Figure 6). Instead of the hard-coded Becker Berlin value in query B, query C has a user- entry variable for its Customer 1000 value. The user-entry variable for query D restricts the customer to the Free Characteristics section of the BEx Query Designer, while in query E, the user-entry variable limits the customer data in the Filter section.

Figure 6

Restriction hard-coded to the Rows on Sold-to party

After executing all the queries once, load the BW statistics InfoCube using a delta load and access the RunTime Query in BW business content. It displays all the statistics related to a query’s runtime.

Table 1 shows the performance results for the queries defined above, and these numbers prove my earlier points. Comparing the results of the query with no restriction (query A) and to that using the hard-coded restriction (query B) shows huge improvements across the board for the restricted query. I’d like to add that there is no difference in the runtime of query B and one using a variable (query C), which is a user entry. Although a limited amount of time is involved to process the variable, I’d like to stick to the numbers in Table 1 for the sake of comparison because time can be influenced by other factors.

Let’s try to differentiate among the variables in different sections of the query designer and identify if they are “real” filters. The proof, of course, is in the data.

Looking at the results for queries C, D, and E, there is little difference, and the number of records selected is the same. This proves that both hard-coded and variable restrictions are “real ” whether they are used in the Rows, Columns, or Free Characteristics sections of the BEx Query Designer. If the Read mode of the query is set to Read on Demand, then the number of rows sent to the frontend increases when the characteristic is in the rows. No additional performance penalty is incurred if the object is in the Free Characteristics section.

The Case for Navigational Attributes

Now let’s take a look at what navigational attributes have to offer by comparing the performance of another set of queries. An all-new query A uses a dimension characteristic in the Material row in this example. A navigational attribute is added to query B for the customer’s country, and restriction is set in the Free Characteristics section of the BEx Query Designer. For query C, add a variable that restricts it to the US only and in query D add a variable restricting it to a country and the materials for that country in the Rows section of the query designer.

The results are shown in Table 2. Note that there are additional factors involved in reading the master data that could effect overall times, but I did not include that data because it is not related to this exercise.

Comparing query A to query B shows that when navigational attributes are used in free characteristics without restriction and the Read mode is set to Read on Demand, there is absolutely no cost in terms of performance, unless or until you navigate the characteristic to the rows.

Once a restriction is added to a navigational attribute regardless of where it is placed in the BEx Query Designer, it affects performance. Just as we saw in the first set of queries, a restriction limits the number of records that the database must select. In this case, placing restrictions on navigational attributes means an additional table must be accessed.

Consider the following example: Instead of applying restrictions directly on the Sold_to values Coke and Pepsi, you use a navigational attribute for the industry of the customer and select Beverage Industry as your value. In this case, BW must read the surrogate-ID (SID) table for Industry Type in addition to the appropriate SID table for the Sold_to Party. The result is still a selection on the customer dimension table with a value of Coke and Pepsi, but it takes a little longer. In most cases, the time factor is not that significant. If it is, you can improve the speed with an aggregate on the navigational attribute Industry Type.

Ned Falk

Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.

You may contact the author at ned.falk@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.