7 Tips for Building Efficient BEx Queries for SAP BusinessObjects

1) 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 BEx Query.

2) 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.

3) 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 shown in figure, you can either create a Calculated Key Figure from the left pane or you can create a New Formula under Key Figures.

4) 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 (as shown in fig.). Mandatory BEx variables load the List of Values (LOV) in SAP BusinessObjects automatically, so you should set all your variables as optional.

5) 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.

6) 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.

7) 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. This increases your query performance when you have many calculated key figures and other calculations in the query.