8 Tips for Building Efficient BEx Queries for SAP BusinessObjects

Key Concept

To analyze a dataset in SAP NetWeaver BW, you define BEx queries for InfoProviders using BEx Query Designer. By choosing and defining characteristics and key figures in a query, you determine how to navigate through and evaluate the data in the selected InfoProvider. You can then display this data in a report format that is meaningful to you, for example, in a table or pie chart.

The best way to retrieve data from SAP NetWeaver BW for reporting in SAP BusinessObjects applications is by using BEx queries. Unlike InfoProviders, BEx queries provide flexibility for using data stored in SAP NetWeaver BW.

You can apply the functionalities of BEx queries with calculated key figures, restricted key figures, and variables. Because SAP NetWeaver BW and SAP BusinessObjects are separate products, you need additional steps when transferring data with MDX statements and universes from SAP NetWeaver BW to SAP BusinessObjects. For this reason, when it comes to building BEx queries for SAP BusinessObjects tools, you should be very careful to avoid performance problems.

I offer eight tips for creating efficient BEx queries for use in SAP BusinessObjects Web Intelligence, Xcelsius, or SAP Crystal Reports.

Note

For more information about creating universes and bringing the data into SAP BusinessObjects applications, refer to Ned Falk’s article “SAP BusinessObjects Universes: A Key to Reporting Integration with SAP NetWeaver BW” and Ingo Hilgefort’s article “SAP BusinessObjects Web Intelligence Plus SAP NetWeaver BW Equal Empowered Users.”

Use Copies of Your BEx Queries

You can use existing BEx queries, but this is not efficient when it comes to change management. If you use the same queries for reporting in SAP NetWeaver BW and SAP BusinessObjects, then any reporting requirements changes in SAP NetWeaver BW also affect SAP BusinessObjects — even if you don’t want to change anything on the SAP BusinessObjects side. Instead of using existing queries, use a copy of them.

Use Free Characteristics

Every characteristic used in rows or columns increases the detail of data, whether it is used in SAP BusinessObjects or not. Therefore, performance of queries decreases as the detail levels increase. Instead of using characteristics in rows and columns, it is much more effective to use them as free characteristics. You can drill down in SAP BusinessObjects reports to the required detail levels using free characteristics. Keep as few characteristics in the Rows/Columns areas as possible. Instead, put them into the Free Characteristics area as shown in Figure 1.  

Figure 1

Free Characteristics area

Use Formulas or Calculated Key Figures for BEx Query Calculations

SAP NetWeaver BW performs better than SAP BusinessObjects products when it comes to manipulating results. For this reason, make all calculations in BEx queries with formulas or calculated key figures. This increases the performance of SAP BusinessObjects reports and dashboards, as well as the usability of structures. As Figure 2 shows, you can either create a Calculated Key Figure from the left pane or you can create a New Formula under Key Figures.

Figure 2

Use formulas or calculated key figures for calculations

Restrict Data in Filters by Directly Selecting Values

Restricting data directly in a BEx query decreases the number of records that the system retrieves from SAP NetWeaver BW and increases report performance. You can also use BEx variables that are directly translated to prompts in SAP BusinessObjects, such as Material and Key Date (Figure 3). Mandatory BEx variables load the List of Values (LOV) in SAP BusinessObjects automatically, so you should set all your variables as optional.

Figure 3

Variables directly translated to prompts

Use SAP NetWeaver BW Variables in SAP BusinessObjects Prompt Screens

When you refresh the LOV in SAP BusinessObjects, the system retrieves SAP NetWeaver BW values from master data tables or InfoProviders, depending on the setting in the BEx Query Designer. By selecting the Only Values in the InfoProvider option in the Filter Value Selection During Query Execution section of the characteristic properties, you increase the performance of prompt screens (Figure 4).

Figure 4

Retrieve data only in InfoProviders by selecting the Only Values in InfoProvider option

Provide Access to SAP NetWeaver BW Queries

External systems cannot use SAP NetWeaver BW queries unless you allow them access. To allow external access to a query, select the Allow External Access to this Query check box from the Advanced tab of the Properties area of the query as shown in Figure 5.

Figure 5

To access external systems, select the Allow External Access to this Query check box

Only Select Used Structure Elements

BEx queries may contain several structure elements (e.g., key figures and characteristics), but not all of them are used in SAP BusinessObjects reports. Instead of retrieving all the structure elements, retrieving only the used elements increases the performance of your query. To do this, go to transaction RSRT and select the Use Selection of Structure Elements check box on the Properties tab (Figure 6). This increases your query performance when you have many calculated key figures and other calculations in the query.

Figure 6

Select the Use Selection of Structure Elements to select only used structure elements from the database

Check the Limitations on Your BEx Queries to Avoid Rework

Although BEx queries are better data sources for SAP BusinessObjects than InfoProviders, they also have some restrictions. Table 1 lists some common functions and whether or not SAP BusinessObjects supports them. In Table 1, Support in MDX means that the correct value and formatting for the query is passed via MDX statements to SAP BusinessObjects. Subsequently, Support in universe means that when a dataset is passed to SAP BusinessObjects, the SAP BusinessObjects universe supports the values. As a general rule, MDX supports calculations carried out before aggregation, but calculations carried out after aggregation are not supported.

Tip!

For more information about MDX and BEx queries, refer to SAP Note 820925 “MDX - Restrictions.”

 

Table 1

SAP BusinessObjects and BEx functions