| Blogs
 I just want to give you an overview and not go into deep details or exact instructions. Just wanna give you some points from where you can start your analyze and tune your system. You should try out all the tables, views and transactions by yourself.
1. The common reasons for performance issues in summary
2. Query performance analyseI think this is a really important point (including the OLAP cache) and should be explained a little bit deeper. TA RSRT
2.1 RSRT Query PropertiesYou can turn off parallel processing for a single query. In the case of queries with very fast response times, the effort required for parallel processing can be greater than the potential time gain. In this case, it may also make sense to turn off parallel processing. Just play a little bit with RSRT and the different optionsto get the optimal settings for your queries! There are also some special read modes for a query. In the most cases the best choice is 'H' (Query to be read when you navigate or expand hierarchies - more information)
2.1 RSRT Query properties with grouping
- Performance Info -> Useage of aggregates, Cache (+delta), compression, status of requests
|
| BPSTOOLS | BW-BPS Performance Toolset |
| BIIPTOOLS | BI-IP Performance Toolset |
| BW_QUERY_ACCESSES | BW: aggregate/InfoCube accesses of queries |
| BW_QUERY_USAGE | BW: query usage statistics |
| BW-TOOLS | BW Tools (PC Analyze, Request analyse, Aggregate toolset, IP Analyse, DTP request analyse and IO Usage) |
| TABLE_ANALYSIS | Table Analysis Tools |
These tools use all RSDD* tables/views and displays them in a colorful and sorted way.
My favourites are BW-TOOLS, BW_QUERY_ACCESSES and BIIPTOOLS.
6. ST14
ST14 -> Business Warehouse -> plan analyze -> client 010 choose date , Basis Data (Top Objects) and Basis: Determine Top DB Objects and schedule it
you will get a great analyze for your whole BI system, including
- top 30 PSA, E-fact, F-fact, Dimension, master data tables, change logs, Cubes ODS/DSO, Aggregates and some special infos for BWA
- for those who use oracle also Tables with more than 100 partitions
- the upload performance for the last weeks
- Compression rate
- result of SAP_INFOCUBE_DESIGNS (D- and E-tables in relation to the F-tables)
- ...
6.1 ST14 Overview
If you have trouble with the growth of your system this is a great entry point to start your analyze to find out where the space is gone ;)
So you know now which requests should be compressed and how to get rid of partitions (maybe repartitioning; rsa1 -> administation -> repartitioning), but keep in mind that repartitioning creates shadow tables in namespace /BIC/4E<InfoCubename> and /BIC/4F<InfoCubename>.
This tables are exists until the next repartitioning, so you can delete them after the repartitioning is completed. Locate and delete empty F-partitions via report SAP_DROP_EMPTY_FPARTITION (note 430486)
7. Statistics
TA: RSDDSTAT statistic recording (tracing) settings for for Infoprovider/queries etc.
Views RSDDSTAT_OLAP (OLAP + Frontend statistics) RSDDSTAT_DM (multiprovider, aggregate-split, DB access time, rfc time)
Use TA SE11 to view there content.
Column AGGRAGATE to identify if it´s using aggregates or the BWA: aggregates are 1xxxxxx and BWA-Indizes with <InfoCube>$X
How to delete statistics
TA RSDDSTAT (manual deletion)
setting up the tracelevel of queries and setting up deleletion of statistics
automatical deletion
Table RSADMIN Parameter TCT_KEEP_OLAP_DM_DATA_N_DAYS (DEFAULT 14 days)
date is relating field Starttime in table RSDDSTATINFO
8. ST02
check every instance for swaps -> double click on the red marked lines and then click on current parameters and you will see which parameter you should increase.
Please read the sap help for each parameter it could be that there are dependencies!
(Memory and Buffer).
There are two possible reasons for swapping:
- There is no space left in the buffer data area -> buffer is too small
- There are no directory entries left -> Although there is enough space left in the buffer, no further objects can be loaded because the number of directory entries is limited -> increase the needed parameter for the directory entries!
Note : Before you change the settings, also have an eye on the pools via tool sappfpar! (on OS as sidadm: sappfpar check pf=<path-to-profile> )
9. Using the BW Administration Cockpit
Setup via SPRO (BI -> Seetings for BI Content -> Business Intelligence ->BI Adminstration Cockpit)
Prerequisites:
- min. NW 7.0 Portal Stack 5 + BI Administration package 1.0
- implement technical content (TA: RSTCC_INST_BIAC)
- Report RSPOR_SETUP
Pros:
- average and max. runtimes of queries
- PC runtimes
- trends for queries and bw-applications
- suggestion for obsolet PSA data
9.1 compressed and not compressed requests
9.2 process chain status
10. Optimizing performance of InfoProviders in summary
- Compress InfoCubes
- Partitioning (and repartitioning) of InfoCubes
- range partitioning (only for data base system which can handle partitions, e.g. oracle, DB2, MSSQL)
- clustering
- application level
11. ILM (Information Lifecycle Management)
- nearline (Vendors for nearline Storage are e.g. SAND Technology, EMC², FileTek, PBS ...)
- archiving (Archiving via fileserver or stape drives)
- deletion of data
Currently we don´t use any kind of ILM, but research is going on ;)
12. BWA Business Warehouse Accelerator (just a small summary):
- RSDDTREX_MEMORY_ESTIMATE (see screenshot)-> to estimate the memory consumption of the BWA for a specific InfoCube. That´s only the memory consumption and not the needed storage on the hard disk!
- RSDDV Display all your Indizes which are indexed by the BWA
- RSRV Analyze BW objects
- RSDDBIAMON2 BWA Monitor
- TREX_ADMIN_TOOL (standalone tool)
- Tables RSDDSTATTREX and RSDDSTATTREXSERV for analyzing the runtimes of BWA
- Table RSDDTREXDIR (Administration of the TREX Aggregates) , check this blog for more information
1) Report: RSDDTREX_INDEX_LOAD_UNLOAD to load or delete BWA Indizes from the memory of the BWA servers. This can also be done over the RSRV ->Tests in Transaction RSRV -> BI Accelerator -> BI Accelerator Performance Checks -> Load BIA index data into main memory/Delete BIA index data from main memory.
2) Optimize Rollup process with BWA-Delta-Index via RSRV (Tests in Transaction RSRV -> All Elementary Tests ->BI Accelerator ->BI Accelerator Performance Checks -> Propose Delta-Index for Indixes )
Note that the Delta index growth with every load. The Delta index should not be bigger than 10% of the main index. If this is the case -> merge both indexes via report RSDDTREX_DELTAINDEX_MERGE
3) Use the BWA/BIA Index Maintenance Wizard for DFI Support or the option 'Always keep all BIA index data in main store'. So they won´t be read from the disk, they stay always in memory! You can also activate and monitore DFI support via the trexadmin standalone tool. Control your memory consumption of BWA for this option!
12.1 result of report RSDDTREX_MEMORY_ESTIMATE
12.2 option index keep in memory via BWA/BIA Index Maintenance Wizard
12.3 BWA suggestion for delta indexes (RSRV, see 12. 2) )
13. Query analyzing example
find out which queries have a long runtime over ST03n:
13.1 ST03n - very high DB useage for this query
Check list
- how often data in this infoprovider were changed?
- RSRT -> Performance Info -> any aggregates, cache (+delta) mode, compression?
- which Infoprovider were hit by the query? RSRT -> Technical Information (in our case GRBCS_V11 - virtual cube and GRBCS_R11 - reporting cube)
- DB statistics for this table/indexes up-to-date?
- is it possible to index the Cube via BWA? (GRBCS_V11 can´t indexed because it is a virtual Cube, GRBCS_R11 is already indexed, the GRBCS_V11 includes GRBCS_M11 - a realtime infocube, which also can´t be indexed - and GRBCS_R11)
- check where the most part of the runtime is spent (execute query in RSRT with options 'Display Statistic Data' and 'Do not use Cache')
- check table RSDDIME if Line Item Dimension or High Cardinality used (if you not sure when you should use this features have a look below to the useful links)
In this case I would activate the OLAP Cache (which mode depends on the how often the basis data are changed and if they are filled at the same time -> grouping for multiprovider, see point 2) and talk to my colleagues which are responsible for modeling if we can change something on the compression time frames. For more details you can also check table RSDDSTAT_DM.
The high runtime causes also from a bug in the db statistics (results in a bad execution plan) which will be fixed in a merge fix (9657085 for PSU 1 and 10007936 for PSU2) for oracle 11g. (bug 9495669 see note 1477787)
13.2 You can see a high usage of the data manager (part of the analytic engine) = read access to the Infoproviders. In this case read time of the DB.
14. General Hints
- Use high cardinality only where it makes sense! It could result in bad query performance. Use table RSDDIME to get an overview over all properties of your dimensions.
- Check in table RSRREPDIR (Field Cachemode) if for all queries cache and read mode 'H' are activated (take also care of the Delta-Cache). If you have special cases for some queries, don´t change your config. To change the read mode for all queries, call transaction RSRT -> type 'RALL' as "OK code", and press 'Enter'. In the dialog box, choose the new read mode and press 'Enter'. To change the read mode for a specific query, enter the name of the query and select 'Read Mode'
- Tablespace PSAPTEMP should have minimum size of 2 times of your biggest F-fact table (e.g. we had some performance issues while executing some queries which are really took a lot of temp space in cause of aggregating and sorting, so now our temp space is 4 times bigger than our biggest F-table)
- Table RSTODSPART shows the amount of records per request
- BEx Information Broadcaster -> Fill OLAP-Cache via BEx Query Designer, BEx Analyzer, BEx Web Analyzer, WAD, Portal and BEx Report Designer (Scheduling on daily, weekly or monthly bases)
- All tables of an InfoCube can be listed with TA LISTSCHEMA.
- Report SAP_INFOCUBE_DESIGNS (Print a list of the cubes in the system and their layout)
- Delete PSA-tables in your process chains
- Delete Changelogs in your process chains
- check if your aggregates are wise or not (TA: RSMON -> Aggregates)
- Check SAP Note 1139396 and run reports SAP_DROP_TMPTABLES and SAP_UPDATE_DBDIFF to clean obsolete temporary entries.
I hope I could give you some useful hints for your analyses. I appreciate any kind of feedback, improvements and own experiences. Be careful with compression and partitioning, just use it if you know what you are doing and what is happening with your data!!!
May be I could show an old stager some new tables/transactions or some useful hints ;)
Some useful links and documents:
- OLAP Cache
- Hinweis 1026944 - Neuer Cache-Modus für BI 7.0 ohne Verzeichnis
- OLAP pre-filling via broadcasting
- BWA and BWA trexadmin standalone tool blog
- How To... Plan for, Implement and Maintain SAP NetWeaver BW Accelerator Use Cases
- Note 1148111 - BW Accelerator Revision / BW Support Package - Compatibility
- Note 1008833 - Supplementary SAP Note about repartitioning
- Partitioning on saphelp
- Repartitioning on saphelp
- Clustering on saphelp
- Reclustering on saphelp
- Line Item and High Cardinality
Book recommendation
Jens Gleichmann SAP Basis administrator
Please tell me what is your experience with performance tuning. Where is your starting point? Do you make any proactive tuning?
Comment on this weblog
Hi,
Here is Starting points for a BI performance analysis and some useful tables/reports regarding OLAP Cache, BWA, (re-)partitioning, ILM etc. Good article from author Jens Gleichmann.
Attached is an html file for reference. Link to article online : http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/21339
Regards
Raj Salecha
