27interviews.blogspot

This blog doesn't contain any copied information or copyright content from other sites. Every topic i presented in this blog is authored myself. Every time i tried to help the sapients to understand the concept in an easier way. Thats it. Have a Grt Day!
https://www.google.com/cse/home?cx=013447253335410278659:k8ob9ipscwg


Google Search engine custom search :
This link is very very useful. Because u can search for documents also and download.

Simply ur search got simplified.

Search engine details
Site Search for SAP Community Network powered by Google. Find discussions (forums), blogs, documents, videos, topic spaces and the latest news found on SCN.
In the custom search i searched for SAP BW TCODES, and i clicked on Documents.
U can see the result in the below image.


searches sites include: 
http://wiki.scn.sap.com, 
http://scn.sap.com, 
http://www.sdn.sap.com

Optimising Reporting Performance SAP BEx ( FRONT END ASPECTS)


Dear friends, happy to see u all again!

How to optimize Reporting Performance  SAP BEx Front End Aspects
Query Performance Overview


INDEX How to optimize Reporting Performance

 i.            Eagle View: About Optimizing Reporting Performance:
ii.            TRANSACTION CODES FOR QUERY PERFORMANCE
iii.            Different Monitor Tools:
iv.            QUERY PERFORMANCE TRANSACTION CODES EXPLAINED BELOW IN DETAIL: EXTENSIVELY USED
1.       RSTT- TRACE TOOL BASIC SETTINGS
2.       ST05 -PERFORMANCE ANALYSIS
3.       BW Statistics (RSDDSTAT)
4.       ST03 & ST03N- WORKLOAD MONITOR  OVERALL QUERY PERFORMANCE
5.       RSRTRACE -Tool available to analyse a specific query in detail
6.       RSRT- Tool available to analyse a specific query in detail
7.       RSRV BASIS Analysis and repair of BI Objects (DB Checks for Statistics & Indexes
8.       ST02- TUNE SUMMARY
9.       ST04 - PERFORMANCE OVERVIEW
10.   SE30 - ABAP RUNTIME ANALYSIS
11.   ST06N – OPERATING SYSTEM MONITOR
12.   RSRCACHE - CACHE MONITOR
13.   RSA3 – EXTRACTOR CHECKER S-API

       v.            DIFFERENT PERFORMANCE PROBLEMS RELATED TO A QUERY REGARDING RUNTIME AND PROPORTIONS:

     vi.            Runtime:
a.       So what to do if query has performance bottle neck regarding Runtime:
b.      Queries with High OLAP runtime
c.       Queries with High Front End run Time

   vii.            High Proportion
a.       So what to do if query has performance bottle neck regarding High Proportion:
b.      All Queries with High OLAP Proportion
c.       All Queries with High Front End Proportion
 viii.            QUERY PERFORMANCE QUIDELINES (BULIDING EFFECTIVE QUERIES & REPORTS)


 SAP Business Explorer (SAP BEx): How to optimize Reporting Performance
Query Performance Overview

i.            Eagle View: About Optimizing Reporting Performance:
With heavy effort am writing this article. So my experience helped me to write this article in blog. In the next post I will explain all about Back End Modeling And Extraction Optimization Techniques.

I will insist on the condition that EFFICIENT DESIGNS = EFFECTIVE REPORTS
Effective designs of Modeling and extraction (Back End) = Effective Reports (Front End)

But still we can see the bottle necks in query performance.

Questions will be raised:
How you analyse the Query Performance?
So what to do in Query performance?
Where inconsistency occurred, in InfoCube or ODS?

We can divide the concept into functional checks (WE, SAP BI BW CONSULTANTS) and Technical checks (BASIS People), because sometimes we must be in the shoes of BASIS people also, but there must be authorization to do so.

 ii.            TRANSACTION CODES FOR QUERY PERFORMANCE

TRANSACTION CODE
TOOL USED BY
TRANSACTION CODE DESCRIPTION
RSA3
BW

Extractor Checker
RSRT
BW

Query Report Monitor
RSRTRACE
BW

Trace Tool
RSRTRACE > RSRCATTTRACE

BASIS
Trace Tool
RSRV
BW
BASIS
Analysis and repair of BI Objects
(DB Checks for Statistics & Indexes)
SE30

BASIS
ABAB TRACE : Test FM, useful in suspecting a program or FM or a Routine causing a bottle neck in performance
RSRCACHE
BW

Testing default OLAP Cache
RSDDSTAT
BW

Performance Evaluation: Query Statistics
ST02

BASIS
Tune Summary
ST06N

BASIS
OS Monitor
ST04

BASIS
DB Monitor
ST05

BASIS
SQL Trace Performance Analysis: performance traces in different areas as SQL Trace, Enequeue Trace, RFC Trace, Buffer Trace
ST03 & STO3N




 iii.            Different Monitor Tools:
The tools available to monitor can be divided into:
·         Monitor a single query
·         Monitor a query in detail.
·         Monitor all queries





iv.            QUERY PERFORMANCE TRANSACTION CODES EXPLAINED BELOW IN DETAIL:
SAP BI BW developers develop Back end and front end. So support people will see the various traces and log files to debug the root cause for Query performance.

SO THESE ARE THE IMPORTANT TRANSACTIONS USED VERY EXTENSIVELY.
1.       RSTT- TRACE TOOL BASIC SETTINGS
2.       ST05 -PERFORMANCE ANALYSIS
3.       BW Statistics (RSDDSTAT)
4.       ST03 & ST03N- WORKLOAD MONITOR  OVERALL QUERY PERFORMANCE
5.       RSRTRACE -Tool available to analyse a specific query in detail
6.       RSRT- Tool available to analyse a specific query in detail
7.       RSRV BASIS Analysis and repair of BI Objects (DB Checks for Statistics & Indexes
8.       ST02- TUNE SUMMARY
9.       ST04 - PERFORMANCE OVERVIEW
10.   SE30 - ABAP RUNTIME ANALYSIS
11.   ST06N – OPERATING SYSTEM MONITOR
12.   RSRCACHE - CACHE MONITOR
13.   RSA3 – EXTRACTOR CHECKER S-API


1)      RSTT TRACE TOOL BASIC SETTINGS
TRACING ON THE SAP BW ENVIRONMENT USING RSTT



2) ST05 TRANSACTION TO SEE THE SQL STATEMENTS – PERFORMANCE ANALYSIS
Tracing can also be applied to the database using ST05



3)      BW Statistics (RSDDSTAT)

1) Turn on the BW Statistics: RSA1, choose Tools> BW statistics for InfoCubes, (Choose OLAP and WHM for your relevant Cubes)
2)Check whether you have overall query performance problem or Single Query Performance problem
4)      ST03N : WORKLOAD MONITOR  OVERALL QUERY PERFORMANCE PROBLEM
ST03N (USING EXPERT MODE)


Transaction Profile Screen (Service Engineer Mode)

Work load overview Screen ( Service Engineer Mode)

Use ST03N -> BW System load values to recognize the problem. Use the number given in table 'Reporting - InfoCubes: Share of total time (s)' to check if one of the columns %OLAP, %DB, %Frontend shows a high number in all InfoCubes.
You need to run ST03N in expert mode to get these values
Single/specific Query performance
TX- ST03N same as above figure, Use Details to get the runtime segments

5)      RSRTRACE (Tool available to analyse a specific query in detail)




RSRCATTTRACE TOOL: TEST PROGRAM




6)      RSRT (TOOL AVAILABLE TO ANALYSE A SPECIFIC QUERY IN DETAIL)
Query Monitor RSRT WHEN USING THE RSRT TRANSACTION COLLECTS QUERY RUNTIME STATISTICS ETC.



Transaction RSRT > select BEx query > Query Properties

Query Monitor RSRT icons: can be used in following ways:


Cache monitor> Main Memory>

       7)      RSRV BASIS Analysis and repair of BI Objects (DB Checks for Statistics & Indexes)



8)      ST02 TUNE SUMMARY


9)      ST04 PERFORMANCE OVERVIEW

10)   SE30 ABAP RUNTIME ANALYSIS:

11)   ST06N OPERATING SYSTEM MONITOR

12)   RSRCACHE CACHE MONITOR

13)   RSA3 EXTRACTOR CHECKER S-API


v.            DIFFERENT PERFORMANCE PROBLEMS RELATED TO A QUERY REGARDING RUNTIME AND PROPORTIONS:




Here we can see that Queries with high DB Runtime, High OLAP runtime, High Front End run Time
Also All Queries with high DB Proportion, High OLAP Proportion, High Front End Proportion

     vi.            Runtime:
So what to do if query has performance bottle neck regarding Runtime:
a.       Queries with high DB runtime
·         Check if an aggregate is suitable (use All data to get values "selected records to transferred records", a high number here would be an indicator for query performance improvement using an aggregate)
·         Check if database statistics are update to data for the Cube/Aggregate, use TX RSRV output (use database check for statistics and indexes)
·         Check if the read mode of the query is unfavourable - Recommended (H)

b.      Queries with High OLAP runtime
·         Check if a high number of Cells transferred to the OLAP (use "All data" to get value "No. of Cells")
·         Use RSRT technical Information to check if any extra OLAP-processing is necessary (Stock Query, Exception Aggregation, Calc. before Aggregation, Virtual Char. Key Figures, Attributes in Calculated Key Figs, Time-dependent Currency Translation) together with a high number of records transferred.
·         Check if a user exit Usage is involved in the OLAP runtime?
·         Check if large hierarchies are used and the entry hierarchy level is as deep as possible. This limits the levels of the hierarchy that must be processed.
·         Use SE16 on the inclusion tables and use the List of Value feature on the column successor and predecessor to see which entry level of the hierarchy is used.

c.       Queries with High Front End run Time
·         Check if a very high number of cells and formatting’s are transferred to the Frontend ( use "All data" to get value "No. of Cells") which cause high network and frontend (processing) runtime.
·         Check if frontend PC are within the recommendation (RAM, CPU Mhz)
·         Check if the bandwidth for WAN connection is sufficient. 

   vii.            High Proportion
So what to do if query has performance bottle neck regarding High Proportion:
a.       All Queries with high DB Proportion
Check:
·         If the database statistic strategy is set up properly for your DB platform (above all for the BW specific tables)
·         If database parameter set up accords with SAP Notes and SAP Services (EarlyWatch)
·         If Buffers, I/O, CPU, memory on the database server are exhausted?
·         If Cube compression is used regularly
·         If Database partitioning is used (not available on all DB platforms)

b.      All Queries with High OLAP Proportion
Check:
·         If the CPUs on the application server are exhausted
·         If the SAP R/3 memory set up is done properly (use TX ST02 to find  bottlenecks)
·         If the read mode of the queries is unfavourable (RSRREPDIR, RSDDSTAT,   Customizing default)

c.       All Queries with High Front End Proportion
Check:
·         If the database statistic strategy is set up properly for your DB platform 
  (above all for the BW specific tables)
·         If database parameter set up accords with SAP Notes and SAP Services (EarlyWatch)
·         If Buffers, I/O, CPU, memory on the database server are exhausted?
·         If Cube compression is used regularly
·         If Database partitioning is used (not available on all DB platforms)

 viii.            QUERY PERFORMANCE QUIDELINES (BULIDING EFFECTIVE QUERIES & REPORTS)
In the below diagram you can see, what the factors influencing / issues affecting the query performance bottlenecks. You can also see some Back End terms here as, aggregates, MultiProviders (ODS, DSO, InfoCubes, Hierarchies etc. Everything in SAP is linked from one process to the other process. So have a glance at the below diagram.


 ·         Use Inclusions Instead of Exclusions
o   Because by using inclusions, we can use DB indexes
o   Characteristics in exclusion cannot use BD indexes
o   Thumb rule: avoid exclusion when using Filters, RKF & selections.
·         Suppress Result rows
o   Use the option Always supress the Result rows, which improves Query performance
·         Use SAP Exits & less Customer Exits       
·         Free characteristics in reports should be limited to 8-10.
·         Reduce RKFs & CKFs in the Query
o   Thumb rule: Try to build CKF & RKF on InfoCube instead on Query
o   Too many brains spoil the experiment, i.e. too many RKFs & CKF will spoil the ultimate performance i.e. we must not use many RKFs and CKFs.
·         Using KeyFigures:
o   Always use summation for key figures in Local calculations. In the queries actually when we are reporting, the KeyFigures will pull the data accordingly from the database and captures the data to report.
o   So thumb rule is: Calculate result as = Summation.
o   Summation means here adding the KFs.
·         Run BI Statistics for query
o   If we open the Query definition, we can see what the fields are used in aggregates
·         Using InfoCube for Reporting:
o   If you are reporting on InfoCube, create aggregates (small baby cubes). Always make aggregate as small only, don’t make it huge which impairs query performance
o   Delete the unused Aggregates (Aggregates Vs Performance) because unused aggregates will become bottle neck for query performance.
o   Do logical and physical partitioning on InfoCube
o   Maintain partitioning criteria in RRKMULTIPROVHINT table
·         ODS/DSO:
o   Try to avoid/ decrease using reporting on DSO. Reporting on DSO is not suggestible.
·         Archive unused data, hardware consumption during loading and querying
·         Use MultiProviders ( actually Backend Technique)
o   Reports designed on MultiProviders. The reports built on MP will be faster because, It searches data from various basic InfoCubes
o   0INFOPROVIDER field to restrict to the specific InfoProviders in Queries         
·         Use  Extensive Filters at Query level
·         Select appropriate Read mode settings for MultiProviders when building queries (in Query Properties)
o   In query properties we can see 2 modes
§  Read Mode
§  Cache Mode
o   Read mode determines which data is to be get from OLAP processor.
o   Read modes:
§  H: Query to be read when you navigate or expand hierarchies
§  X: Query to read data during navigation
§  A: Query to read all data at once
§  Thumb rule: use H Read mode
o   Cache Mode:
§  In cache mode the pre calculated data for reporting is saved in as highly compressed form, so OLAP Processor will helps to calculate the query results. Always use Cache Mode 5 (BLOB/Cluster Added is Enhanced). Using cache mode helps improving query performance because, if we set cache, the query will not hit the database again and again.
§  Filling cache will be in BEx Broadcasting query result for statistical report performance
·         Surely Delete unused Queries & temporary Query Views
·         Be careful while creating Cell Structures, as cell structures consume high DB Query runtimes( You can see the topic below on this)
·         Remove the unused Cache entries by using Program RSR_CACHE_RSRV_CHECK_ENTRIES
·         Query read mode and Cache mode settings in RSRT >Properties: Cache mode could be 1 or 5
·         Transaction RSH1 to remove unassigned nodes In Hierarchies and use less number of hierarchies.
·         Enable “Use Selection of Structure Elements” for queries which have several restricted and calculated key figures. (In transaction RSRT, enter query and select ‘Query Properties)



 Thanks for reading

Sign In