GRC
HR
SCM
CRM
BI
Expand +


Article

 

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

by Shreekant Shiralkar, Global Head, SAP Analytics CoE, Tata Consultancy and Deepak Sawant, Leader of SAP BW CoE, Tata Consultancy Services Ltd. and Jyoti Jain, Associate at Tata Consultancy Services Ltd.

February 15, 2017

This checklist helps you learn SAP BW BEx Query design best practices to enable you to build performance-optimized SAP BW BEx Queries. Also see a quick comparison of the three SAP BusinessObjects connection types: Business Intelligence Consumer Services (BICS), relational, and Business Application Programming Interface (BAPI) connections. This quick tip also helps you decide which SAP BusinessObjects connection type is right for your scenario.

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.


Figure 1
Suppress the unassigned node

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.

An email has been sent to:





 

Shreekant Shiralkar

Shreekant W. Shiralkar is a senior management professional with experience on leading and managing business functions as well as technology consulting. He has authored best selling books and published many white papers on technology. He also holds patents for innovations. Presently he is global head of the SAP Analytics Centre of Excellence at Tata Consultancy.


Deepak Sawant

Deepak Sawant is an SAP analytics professional with 16 years of experience designing, developing, and maintaining large and complex BI programs for a variety of Fortune 500 companies. He played a key role in the design, development, and deployment of business analytic solutions in financials, supply chain, product costing, production, and planning functions. Deepak has performed multiple roles in technology consulting, delivery management, pre-sales, and operations management for leading organizations, namely Tata Consultancy Services Ltd., IBM, Reliance Industries, and Wockhardt Group. Currently he is leading the SAP BW Center of Excellence at Tata Consultancy Services Ltd., where he has been instrumental in establishing innovative consulting service offerings.


Jyoti Jain

Jyoti Jain is an associate at Tata Consultancy Services Ltd., with 10 years of experience in IT, primarily serving the banking, manufacturing, life-science, and travel and hospitality industries. She is part of Tata Consultancy Services’ SAP Center of Excellence and is engaged in architecting and designing solutions on the SAP BusinessObjects platform.



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ