Quick Tip: Best Practices for Designing SAP BW BEx Queries and a Comparison of Connection Options

The connection type used between SAP BusinessObjects and SAP BW, large data volumes, or computing resources may not be the only causes of poor performance for BusinessObjects reports. Oftentimes, the design of the data-sourcing BEx Query can negatively impact performance. Design considerations in SAP BW data models and SAP BW BEx Queries can improve the performance of BusinessObjects reports that use Business Intelligence Consumer Services (BICS) connections. The following is a list of design factors that should be taken into consideration and evaluated to help you design and develop optimized SAP BW BEx Queries.

16 Tips for Designing SAP BW BEx Queries

1. Web Intelligence (also called WebI or Webi) reports that are connected via a BICS connection extract all the data based on the characteristics placed under columns, rows, or free characteristics. Therefore, only necessary characteristics should appear in the columns, rows, and free characteristics so that the SAP BW BEx Query can produce aggregated results.

2.Keep the expected result set of the query as small as possible (a maximum of 1,000 lines) by using filters. SAP BW BEx Queries that generate large result sets are resource hungry and have a direct impact on reporting performance.

3. Build calculated key figures and restricted key figures at the InfoCube level instead of in formulas in SAP BW BEx Queries. This is because formulas are local to a query, whereas calculated key figures and restricted key figures are global in nature. Therefore, they can be used across queries built on the InfoProviders, producing consistent values from different queries. It also minimizes maintenance efforts as any changes made to a calculated key figure or restricted key figure are applied to all queries using it.

4. Any calculations that need to be made before aggregation (such as currency or unit conversions) should be done prior to or during the transformation and loading into the InfoProviders processes. By processing currency or unit conversion during the data-load process, and storing the result as part of the InfoProvider, you eliminate a big, time-consuming query-processing step. This results in faster query performance.

5. For SAP BW systems running on non-SAP HANA databases, build SAP BW BEx Queries on InfoCubes and MultiProviders as they are optimized for data aggregation. Avoid creating queries on DataStore Objects (DSOs) or InfoSets. DSOs (which are flat-table structures) and InfoSets are primarily join conditions, and do not have optimized data-processing mechanisms in their analytical engines. For SAP HANA-based BW systems, queries on DSOs are preferable. SAP HANA, as a columnar database, is highly optimized to read and aggregate data at the database level, which results in high performance of queries.

6. Avoid using the exclude operator in restricted key figures, filters, or selections as it is not optimized to use indexes. With selections it is better to include, rather than exclude, characteristic values.

7. The Result Rows setting for each characteristic in a BEx Query should be set to Always Suppress as it is not used by BusinessObjects client tools.

8. InfoCube requests must be compressed if non-cumulative key figures are used in a BEx Query. InfoCube compression reduces the number of records in InfoCube tables and helps to improve the data processing of two cumulative key figures that are used to define a non-cumulative key figure.

9. When possible, let the time characteristic restrictions be placed in a global filter. This helps the Structure Query Language (SQL) engine create an optimal execution plan that reads data only from the necessary database partitions that are created based on time characteristics.

10. Within structures, ensure that a filter order exists and that it is filtered at the highest level. Correct filter order is used by the analytical engine to create the where clause in the SQL statement.

11. Reduce the data volume selected by introducing many mandatory variables. Mandatory variables force the query user to provide filters at run time and reduces the data set to be read from the database to a manageable size, thereby ensuring better performance.

12. Ensure that the ABAP code in all exit variables used in a SAP BW BEx Query is optimized. Badly coded ABAP programs can severely hamper performance.

13. Use time-dependent characteristics and navigational attributes in queries only when required. Time-dependent characteristics and navigational attribute data are not saved in the InfoCube tables. As a result, additional table joins and data processing are required to be performed at query run time to produce the output with time-dependent characteristics and navigational attributes, which results in a performance lag.

14. If hierarchies are used in a query, minimize the number of nodes to be included in the query results. Using hierarchy nodes in an SAP BW BEx Query negatively impacts run-time performance. This is due to the creation of temporary hierarchy tables that then have be read at the time of execution. One way to reduce the number of hierarchies is to use user-entry variables.

15. Filter out the Not Assigned node in the hierarchy by suppressing it at hierarchy definition. This avoids fetching unwanted data in the query-result set and improves performance. Use SAP BW transaction code RSH1 to select the hierarchy, and follow menu path Main Menu > Goto > Hierarchy attributes. In the pop-up screen that opens (Figure 1), set the Suppress ‘Unassigned’ Node parameter check box and then click the green checkmark icon to save your settings.

For queries that have several restricted and calculated key figures, enable the Use Selection of Structure Elements setting in the query properties. This structures the SQL statement to fetch only those key figures (or structure elements) that are actually used.

A Comparison of Connection Options [head 2]

SAP BusinessObjects 4.1 offers three different options for connecting to an SAP BW system:
1. BICS connections: A BICS connection is explicitly available to connect to SAP BW BEx Queries and InfoProviders since the release of BusinessObjects version 4.1. It allows BusinessObjects tools to access SAP BW Online Analytical Processing (OLAP) functionality.

2. Relational connection: This is primarily used to connect different heterogeneous sources of data, such as SAP BW BEx Queries and Oracle tables. The federation of these data sources is achieved by creating a Universe, and the Universe is then consumed by BusinessObjects reporting tools.

3. OLAP Business Application Programming Interface (BAPI) connection: This was the traditional method for connecting to SAP BW from the old BusinessObjects version (3.x).

Table 1 provides a brief capability comparison of the three connection options provided in the SAP BusinessObjects BI platform (e.g., BICS, relational, and OLAP BAPI connections) with an SAP BW BEx Query or InfoProvider as the data source. It is important to understand what type of connection should be used in which scenario.

SAP BW
BICS connection	Relational connection	BAPI connection
Used to connect SAP BusinessObjects reporting tools to SAP BW data (e.g., BEx Queries or InfoProviders). This is new type of connection was introduced with SAP BusinessObjects version 4.0.	Used to connect heterogeneous data sources (SAP and non-SAP).	Used to connect SAP BusinessObjects reporting tools to SAP BW BEx Queries or InfoProviders. This was the old connection type used with BusinessObjects version 3.X.
No manual creation of a Universe is required. When Web Intelligence is connected, a transient Universe is created automatically. Other BusinessObjects tools connect directly without Universes.	Data sources are federated by manually creating Universes (UNX). (These Universes are created using the Information Design Tool, and are saved in the BusinessObjects repository with a UNX file extension.) It can use InfoProviders as data sources to connect to the SAP BW system.	Manual creation of a Universe is required using SAP BW as the data source. It uses the MultiDimensional expressions (MDX) query language to communicate with SAP BW.
Direct interaction is involved between SAP BusinessObjects reporting tools and SAP BW data sources (e.g., BEx Queries and InfoProviders) when using BICS connections.	SAP BusinessObjects reporting tools requires a Universe to communicate with SAP BW data sources.	An additional Semantic Layer of a Universe (UNV) is required for SAP BusinessObjects reporting tools to communicate with SAP BW data sources. (These Universes are created with the Universe Design Tool and are saved in the BusinessObjects repository with a UNV file extension.)
There is no Semantic Layer between the SAP BW BEx Query or InfoProvider and the BusinessObjects tool.	New fields and measures can be created at the Semantic Layer when using this connection.	A Semantic Layer is required between the InfoProvider and the SAP BusinessObjects tool.
Structures created in SAP BW can be used. As a result, the ability to use SAP BW data sources (BEx Queries and InfoProviders) across multiple SAP BusinessObjects client tools is offered.	Structures created in SAP BW cannot be re-used as-is when building a Universe.	The structure of BEX Queries is flattened when using an SAP BAPI connection.
No custom fields can be created, thus restricting the ability to create all the fields only in SAP BW data sources (BEX Query and InfoProviders).	New fields can be created at the Semantic Layer, thus providing the scope to leverage the functionality of  Universe features.	Creation of new fields can be done only in a limited way. Reporting performance is degraded when using a BAPI connection.
Customization of metadata is not available in SAP BusinessObjects when a BICS connection is used. BICS is an optimized data-access protocol used by SAP systems to communicate within its internal technology platforms, thus enabling better performance.	Customization and creation of new objects in SAP BusinessObjects is available through a Universe in a limited way.This connection uses an SAP Java Connector (JCo), which does not perform as effectively as BICS; hence it is not suitable for large data sets.	Customization of fields is not recommended when using an SAP BAPI connection.Reporting performance is degraded when using a relational connection (Data Federator).Since BAPI uses an MDX parser (hence additional steps are required internally to parse the MDX), it has to convert the data in a structure that is understood by internal SAP BW calls, and then convert the results back to MDX, which tends to degrade the performance.
All the SAP BusinessObjects reporting tools support this connection with SAP BW data sources.	SAP BusinessObjects Web Intelligence, Dashboard Designer, and Explorer support relational connections with SAP BW and non-SAP BW data sources via a multi-source Universe. SAP BusinessObjects Design Studio does not support relational connections that use a multi-source Universe.	Only SAP BusinessObjects Web Intelligence and BusinessObjects Explorer support a BAPI connection with SAP BW data sources. No other BusinessObjects reporting tool supports this type of connection.

Table 1
A comparison of SAP BusinessObjects connection types with an SAP BW system as the data source

A BICS connection is the most suitable option to connect SAP BusinessObjects client tools to SAP BW data sources. It uses a direct-client connectivity mechanism and does not have overhead to create and maintain a Universe used in relational connectivity or the limitations of MDX, which has performance issues.