Hello All,
Welcome back to yet another blogpost on Bex Query/AFO Reports Development, Tips/Tricks & Performance Improvement Techniques. This blogpost will explain best practices to follow during the development of these objects, tips and tricks to follow, and how to optimize the performance of these queries so that we can develop better business reports for our customers.
Before deep diving, first let us understand the flow of data while creating a Business Explorer Report Aka Bex Query in a typical SAP BW or SAP BWONHANA or SAP BW4HANA and BW4HANA mixed and hybrid modelling scenario looks. PFB picture :
There are a lot of blog posts and learning videos available on the web to understand how to create BW objects for modeling before reaching the Bex query. Here I am not going to discuss BW development. Here we will discuss Bex query best practices to follow and optimization techniques.
1. When reports from Web Intelligence (also known as WebI or Webi) are connected via a BICS connection, all the data is extracted depending on the characteristics listed under the columns, rows, or free characteristics.In order for the SAP BW BEx Query to provide aggregated results, only necessary characteristics should be present in the columns, rows, and free characteristics.
2. Employ filters to reduce the query's expected result set as much as you can (to a maximum of 1000 lines). Large result sets produced by SAP BW BEx queries are resource-intensive and have an immediate effect on reporting performance.
3. Instead of using formulas in SAP BW BEx Queries, build calculated key figures and restricted key figures at the InfoCube level.This is due to the fact that whereas calculated key figures and restricted key figures are global in nature, formulae are local to a query.As a result, they may be applied to queries created using InfoProviders, resulting in values that are consistent across queries. Additionally, it reduces maintenance work because all queries that use a calculated key figure or limited key figure receive the effects of any modifications made to them.
4. Any computations (such as currency or unit conversions) that must be performed before aggregation should be done either before or during the transformation and loading into the InfoProviders operations. You can skip a significant, time-consuming query-processing step by processing currency or unit conversion during the data-load process and storing the result as part of the InfoProvider. As a result, queries run more quickly.
5. InfoCubes and MultiProviders, which are optimized for data aggregation, should be used to develop SAP BW BEx Queries for SAP BW systems running on non-SAP HANA databases. AVOID writing queries on InfoSets or DataStore Objects (DSOs). DSOs, which are flat-table structures, and InfoSets are largely join conditions and their analytical engines lack efficient data-processing components. Queries on DSOs are preferred for BW systems built on the SAP HANA platform. As a columnar database, SAP HANA is highly optimized for reading and aggregating data at the database level, producing high query performance.
6. The exclude operator should not be used in restricted key figures, filters, or selects since it is not designed to work with indexes. It is preferable to incorporate characteristic values in selections rather than to exclude them.
7. Because BusinessObjects client tools do not use the Result Rows parameter for each characteristic in a BEx Query, it should be set to Always Suppress.
8. If non-cumulative key figures are used in a BEx Query, InfoCube queries must be compressed. The two cumulative key figures that are used to define a non-cumulative key figure are processed more efficiently thanks to InfoCube compression, which also lowers the amount of records in InfoCube tables.
9. Whenever possible, let the global filtering of temporal characteristics be used. By reading data solely from the required database partitions that are constructed based on time characteristics, the Structure Query Language (SQL) engine is able to create an execution plan that is as efficient as possible.
10. Ensure that there is a filter order within the structures and that it is filtered at the highest level. The analytical engine constructs the where clause in the SQL statement using the appropriate filter order.
11. Decrease the amount of data by adding numerous necessary variables. Mandatory variables ensure greater performance by forcing the query user to supply filters at runtime and reducing the amount of data that needs to be read from the database.Make sure all exit variables used in a SAP BW BEx Query have optimized ABAP code. Performance can be greatly hampered by programs with poor ABAP coding.
12. Only incorporate navigational and time-dependent attributes into queries when necessary. The InfoCube tables do not store time-dependent qualities or navigational attribute data. In order to provide the output with time-dependent features and navigational attributes, additional table joins and data processing must be done at query execution time, which causes a performance lag.
13. Reduce the number of nodes in the query results if hierarchies are utilized in the query. The performance of a SAP BW BEx Query is significantly impacted when hierarchy nodes are used. This is because temporary hierarchy tables are created and then read during execution. Utilizing user-entry variables is one method of lowering the number of hierarchies.
14. Suppress the Not Assigned node at the hierarchy definition to remove it from the hierarchy. Performance is enhanced and unnecessary data is prevented from being fetched into the query-result set. To choose the hierarchy, enter the SAP BW transaction code RSH1 and choose Main Menu > Goto > Hierarchy attributes from the menu. Set the Suppress 'Unassigned' Node parameter check box in the pop-up window that appears (Figure below ), and then click the green checkmark symbol to save your settings.
15. Make sure that the Use Selection of Structure Elements parameter in the query
attributes is enabled for queries that contain a number of restricted and computed key figures.
By structuring the SQL statement, it is possible to only retrieve the structural elements or key
figures that are truly needed.
16. for a heavy Bex report where JAN-DEC MTD Data, JAN-DEC YTD Data, Q1-Q4 Data & H1-H2 data need to be shown better to create a period structure and try to load MTD, YTD,QTD,HTD data into the ADSO rather than calculating at the Bex Query. If flat data is loaded to ADSO and still if we create a bex query on top these ADSOs then also performance can be managed to some extent with above points. But if MTD,YTD,QTD,HTD calculations need to be done at a HANA CV level and you are trying to show these calculations in a Bex Query where raw data sits in HANA CV then definitely we will have performance issues as calculation is happening on the analytic engine then hits to DB eng before rendering the number on your screen.
So follow the strategy of calculate -> Store --> Report. (Do not do complex calculations at HANA CV level. If we have huge data volume then for sure report performance will go down)
For Example.
Create a period structure in the Bex Query with individual selections for optimum performance.
17. Avoid Cell restrictions in the Bex query or try to minimize as much as possible.
18. Once the trace is generated we can use of standard hints for the info provider.
How to do this directly without SM30 and as it is a cross client transparent table:-
Go to SAP T Code S37, & enter the function Module SE16N_INTERFACE
Once we execute the same as below :-
For best performance NO_JOIN_THRU_AGGR would be most promising.
why? answer is : Hints are typically used to optimize SAP HANA performance or memory consumption and have no effect on the result set of the request.
If we have taken care With the above steps, the bex query should be in a proper shape and manageable performance.
But still bottlenecks, then how can we analyze the performance of BexQuery?
Kindly follow the steps:
These steps are by my own learning experience by working with various SAP PE experts during actual project work.
How the ST 12 screen looks like. I found it difficult when I do by my own but took Basis help to collect the trace.
Now with the ST 12 screen open go to RSRT and run the Bex Query in my case it is ZTEST_NAREN_COPA_REORT. Once the report runs fine then again go back to ST 12 then clink end trace collection and click on Fullscreen below or click the SQL Summary:-
How the SQL summary looks :-
Next Click on the call statement
So total DB time arround 321.226 seconds, HANA performance analysis performed.
Now run the workbook and check TRACE in transaction RSTT
Rerun and Debug is possible here too.
Go to SAP T Code : RSDDSTAT to enable the statistics for BW Queries
Change the default setting for all BW Bex Queries as in below picture
Change setting for Individual query or Info providers
Go to SAP T Code ST13, tool - BIIBTOOLS.
Run BW Statistics Analysis
Select Statistics by User ID and Time .PFB below .
Check the query statistics analysis.
OLAP time reflects the complexity of the BW query design and the time spent on the ABAP time in the application.
Data Manager time reflects the data collection time in the database. PFB snapshot.
Kindly refer to below SAP notes as well
When we refresh a workbook it refreshed every query and the sheet in the workbooks sequentially.
Recommendation:
https://blogs.sap.com/2022/02/02/query-performance-optimization-in-bw-few-tips/
(By Pallab Haldar)
https://teklink.com/wp-content/uploads/2015/12/RA2015_Gupta_Optimizingbexqueryperformance.pdf
These pieces of information are neither copied from anyone nor performed in a client system. These queries are performed in a local SAP server. Where ever references were used I gave the credit to the blogpost author.
Thank you,
Narasingha
Welcome back to yet another blogpost on Bex Query/AFO Reports Development, Tips/Tricks & Performance Improvement Techniques. This blogpost will explain best practices to follow during the development of these objects, tips and tricks to follow, and how to optimize the performance of these queries so that we can develop better business reports for our customers.
Before deep diving, first let us understand the flow of data while creating a Business Explorer Report Aka Bex Query in a typical SAP BW or SAP BWONHANA or SAP BW4HANA and BW4HANA mixed and hybrid modelling scenario looks. PFB picture :
There are a lot of blog posts and learning videos available on the web to understand how to create BW objects for modeling before reaching the Bex query. Here I am not going to discuss BW development. Here we will discuss Bex query best practices to follow and optimization techniques.
Best Practices to follow during Bex Query Development
1. When reports from Web Intelligence (also known as WebI or Webi) are connected via a BICS connection, all the data is extracted depending on the characteristics listed under the columns, rows, or free characteristics.In order for the SAP BW BEx Query to provide aggregated results, only necessary characteristics should be present in the columns, rows, and free characteristics.
2. Employ filters to reduce the query's expected result set as much as you can (to a maximum of 1000 lines). Large result sets produced by SAP BW BEx queries are resource-intensive and have an immediate effect on reporting performance.
3. Instead of using formulas in SAP BW BEx Queries, build calculated key figures and restricted key figures at the InfoCube level.This is due to the fact that whereas calculated key figures and restricted key figures are global in nature, formulae are local to a query.As a result, they may be applied to queries created using InfoProviders, resulting in values that are consistent across queries. Additionally, it reduces maintenance work because all queries that use a calculated key figure or limited key figure receive the effects of any modifications made to them.
4. Any computations (such as currency or unit conversions) that must be performed before aggregation should be done either before or during the transformation and loading into the InfoProviders operations. You can skip a significant, time-consuming query-processing step by processing currency or unit conversion during the data-load process and storing the result as part of the InfoProvider. As a result, queries run more quickly.
5. InfoCubes and MultiProviders, which are optimized for data aggregation, should be used to develop SAP BW BEx Queries for SAP BW systems running on non-SAP HANA databases. AVOID writing queries on InfoSets or DataStore Objects (DSOs). DSOs, which are flat-table structures, and InfoSets are largely join conditions and their analytical engines lack efficient data-processing components. Queries on DSOs are preferred for BW systems built on the SAP HANA platform. As a columnar database, SAP HANA is highly optimized for reading and aggregating data at the database level, producing high query performance.
6. The exclude operator should not be used in restricted key figures, filters, or selects since it is not designed to work with indexes. It is preferable to incorporate characteristic values in selections rather than to exclude them.
7. Because BusinessObjects client tools do not use the Result Rows parameter for each characteristic in a BEx Query, it should be set to Always Suppress.
8. If non-cumulative key figures are used in a BEx Query, InfoCube queries must be compressed. The two cumulative key figures that are used to define a non-cumulative key figure are processed more efficiently thanks to InfoCube compression, which also lowers the amount of records in InfoCube tables.
9. Whenever possible, let the global filtering of temporal characteristics be used. By reading data solely from the required database partitions that are constructed based on time characteristics, the Structure Query Language (SQL) engine is able to create an execution plan that is as efficient as possible.
10. Ensure that there is a filter order within the structures and that it is filtered at the highest level. The analytical engine constructs the where clause in the SQL statement using the appropriate filter order.
11. Decrease the amount of data by adding numerous necessary variables. Mandatory variables ensure greater performance by forcing the query user to supply filters at runtime and reducing the amount of data that needs to be read from the database.Make sure all exit variables used in a SAP BW BEx Query have optimized ABAP code. Performance can be greatly hampered by programs with poor ABAP coding.
12. Only incorporate navigational and time-dependent attributes into queries when necessary. The InfoCube tables do not store time-dependent qualities or navigational attribute data. In order to provide the output with time-dependent features and navigational attributes, additional table joins and data processing must be done at query execution time, which causes a performance lag.
13. Reduce the number of nodes in the query results if hierarchies are utilized in the query. The performance of a SAP BW BEx Query is significantly impacted when hierarchy nodes are used. This is because temporary hierarchy tables are created and then read during execution. Utilizing user-entry variables is one method of lowering the number of hierarchies.
14. Suppress the Not Assigned node at the hierarchy definition to remove it from the hierarchy. Performance is enhanced and unnecessary data is prevented from being fetched into the query-result set. To choose the hierarchy, enter the SAP BW transaction code RSH1 and choose Main Menu > Goto > Hierarchy attributes from the menu. Set the Suppress 'Unassigned' Node parameter check box in the pop-up window that appears (Figure below ), and then click the green checkmark symbol to save your settings.
15. Make sure that the Use Selection of Structure Elements parameter in the query
attributes is enabled for queries that contain a number of restricted and computed key figures.
By structuring the SQL statement, it is possible to only retrieve the structural elements or key
figures that are truly needed.
16. for a heavy Bex report where JAN-DEC MTD Data, JAN-DEC YTD Data, Q1-Q4 Data & H1-H2 data need to be shown better to create a period structure and try to load MTD, YTD,QTD,HTD data into the ADSO rather than calculating at the Bex Query. If flat data is loaded to ADSO and still if we create a bex query on top these ADSOs then also performance can be managed to some extent with above points. But if MTD,YTD,QTD,HTD calculations need to be done at a HANA CV level and you are trying to show these calculations in a Bex Query where raw data sits in HANA CV then definitely we will have performance issues as calculation is happening on the analytic engine then hits to DB eng before rendering the number on your screen.
So follow the strategy of calculate -> Store --> Report. (Do not do complex calculations at HANA CV level. If we have huge data volume then for sure report performance will go down)
For Example.
Create a period structure in the Bex Query with individual selections for optimum performance.
17. Avoid Cell restrictions in the Bex query or try to minimize as much as possible.
18. Once the trace is generated we can use of standard hints for the info provider.
How to do this directly without SM30 and as it is a cross client transparent table:-
Go to SAP T Code S37, & enter the function Module SE16N_INTERFACE
Once we execute the same as below :-
For best performance NO_JOIN_THRU_AGGR would be most promising.
why? answer is : Hints are typically used to optimize SAP HANA performance or memory consumption and have no effect on the result set of the request.
How to Analyze the Bex Query performance
If we have taken care With the above steps, the bex query should be in a proper shape and manageable performance.
But still bottlenecks, then how can we analyze the performance of BexQuery?
Kindly follow the steps:
These steps are by my own learning experience by working with various SAP PE experts during actual project work.
Step 1 :- First go to ST 12 and do HANA trace collection from the bankend.
How the ST 12 screen looks like. I found it difficult when I do by my own but took Basis help to collect the trace.
Now with the ST 12 screen open go to RSRT and run the Bex Query in my case it is ZTEST_NAREN_COPA_REORT. Once the report runs fine then again go back to ST 12 then clink end trace collection and click on Fullscreen below or click the SQL Summary:-
How the SQL summary looks :-
Next Click on the call statement
So total DB time arround 321.226 seconds, HANA performance analysis performed.
Step 2: Now say a workbook is created on top this bex query then kindly follow below steps :-
- Open Analysis for Office the click on File --> Home-->Analysis -->TroubleShoot --> Advanced Mode -- >Analysis --> Then check mark Enable BW server tracing and Enable workbook Profiling (BW statistics). PFB the picture
Now run the workbook and check TRACE in transaction RSTT
Rerun and Debug is possible here too.
Step 3 : Enable BW query Statistics
Go to SAP T Code : RSDDSTAT to enable the statistics for BW Queries
Change the default setting for all BW Bex Queries as in below picture
Change setting for Individual query or Info providers
Step 4 : Check BW query Statistics
Go to SAP T Code ST13, tool - BIIBTOOLS.
Run BW Statistics Analysis
Select Statistics by User ID and Time .PFB below .
Check the query statistics analysis.
OLAP time reflects the complexity of the BW query design and the time spent on the ABAP time in the application.
Data Manager time reflects the data collection time in the database. PFB snapshot.
Kindly refer to below SAP notes as well
- SAP Note 1681396 - Query Performance.
- SAP Note 2620998 for Analysis for Office.
AO workbook design : Best Practices to follow
When we refresh a workbook it refreshed every query and the sheet in the workbooks sequentially.
Recommendation:
- Refreshing individual Data sources is faster than refreshing all sources.
- Use only one query in the sheet, and allow all users to change the columns in the WORKBOOK and refresh the query to change the granularity.
- Consider refreshing a single sheet instead of refreshing all at once.
- For pure reporting purpose, consider using BI tools instead of Excel. For example SAP BO WEBI and SAC can execute BW bex queries in parallel.
- do not check refresh workbook on opening and if we do this refresh before input variables in prompt window and after input new variables, the AO workbook will refresh again.
- This will cause duplicate workbook refresh on open.
- There are few blogpost and PDF document available on the internet kindly follow this document to check in Bex query optimization in detail and we can understand from scratch.
https://blogs.sap.com/2022/02/02/query-performance-optimization-in-bw-few-tips/
(By Pallab Haldar)
- The PDF credit goes to Pravin Gupta (As in the document ) and I do not own the rights for it. The link is just used for self-analysis and reference purposes, as I personally found it useful where each information is given from scratch to end of Bex Query optimization.
https://teklink.com/wp-content/uploads/2015/12/RA2015_Gupta_Optimizingbexqueryperformance.pdf
These pieces of information are neither copied from anyone nor performed in a client system. These queries are performed in a local SAP server. Where ever references were used I gave the credit to the blogpost author.
Thank you,
Narasingha
- SAP Managed Tags:
