Skip to Content

Performance Tuning

Ø   When the query is running slow, how should we improve the query performance? Query Performance

Ø   When we are extracting data from source system to BI, Loading might be going slow? Loading Performance

Ø   Query Performance: Query Execution Process: Whenever we execute the query it triggers the OLAP processer, it first check the available of data in OLAP cache if cache is not available, it identifies the info provider on which BEx report should be executed on & it triggers the info provider & selects the records & aggregates the records based on characteristic values in the OLAP processer & transfer to the front end (BEx) and the records are formatted in the front end.

Ø   Frontend Time: Time spent at the Bex to Execute the query is called as Frontend time

Ø   OLAP Time: Time spend at the processer to perform the process called as OLAP time

Ø   DB Time: The time spent at the database to retrieve the data to the processer is called as DB Time

Ø   Total Time taken to execute query = Frontend time + OLAP time +DB time

Ø   Aggregation ratio: Number of records selected from the database to the OLAP processor / number of records transferred to the BEX

Ø   1. How to collect the statistics:RSA1 à Tools à Setting for BI statistics (Tcode: RSDDSTAT) à(RSDDSTAT_DM & RSDDSTAT_OLAP tables will collect the statics)à If the tables already having data delete the contents of table à You can find a button delete statistical data à It will ask the period à Delete à Observe the job in SM37 à Now select the info provider & Query on which you want maintain the statics à Make the necessary settings

Ø   Save à Now if any one execute the query the statics will be maintained in statistical tables

Ø   How to analyse the statistics collected:1) By looking at the contents of the tables RSDDSTAT_DM, RSDDSTAT_OLAP

Ø   Another Way: By using the Transaction code ST03N

Ø   Another Way: By Implementing BI statistics

Ø   Go to statistical tables à Contents à Settings à List Format à Choose Fields à Deselect all à Select INFO CUBE & QUERY ID (Name of the query) & QDBSEL (Number of records selected from data base) & QDBTRANS (Number of records transferred to BEX) & QTIMEOLAP (Time spent at OLAP) à  QTIMEDB (DB TIME) àQTIMECLIENT (FRONTEND TIME)à TRANSFER à Observe the statics

Ø   Another way: ST03 à Expert Mode à Double click on BI  Work Load à Select drop down for aggregation à Select Query à Filter your query à Go to all data tab à Observe the statistical information

Ø   Another Flexibility by implementing BI statistics (RSTCC_INST_BIAC) àInstead of looking the data in the table what SAP has done is they have given some readymade queries, info cubes, transformations, readymade multi providers, install them & load the data to these cube à There are some readymade BEx queries which will give the analysis of the reports

Ø   0TCT_C01 (Front-End and OLAP Statistics (Aggregated))

Ø   0TCT_C02(BI Front-End and OLAP Statistics (Details))

Ø   0TCT_C03(Data Manager Statistics (Details))

Ø   0BWTC_C04(BW Statistics – Aggregates)

Ø   0BWTC_C05(BW Statistics – WHM)

Ø   0BWTC_C09(Condensing Info Cubes),

Ø   0BWTC_C11(Data deletion from info cube),

Ø   0TCT_MC02 (MULTIP PROVIDER – Front-End and OLAP Statistics (Details))

Ø   0TCT_MC01 (Multi Provider – Front-End and OLAP Statistics (Aggregated))

Ø   0BWTC_C10 (Multi Provider – BW Statistics)

Ø   Most of the system maintenance reports come from these contents, Like How many number of users used some reports & Administration Reports

Ø   STEPS: Install the Business content data source à RSA5 à Expand the application component Business Information Ware house àExpand application component TCT àInstall the data sources (Total 6)àReplicate the data source using My Self Connectionà RSA13 à Select My self-Connection  à Data Source overview à Expand BW data sources à Expand Business information warehouse à Technical content à Context menu à Replicate à

Ø   Install all other contents like info cubes, reports, multi providers, info packages, transformations, DTP’s

Ø   RSOR àExpand the Multi Provider à Double click on select objects àFind à0BWTC_C10 à Select Inflow Before & After àInstall in the background à Once the installation is done

Ø   Load the data to the all cubes by scheduling the info package & DTP’s

Ø   2 reports which are mainly used for report analysis àutilizing OLAP per query (0BWTC_C10_Q012)& utilizing OLAP per Info cube (0BWTC_C10_Q013)

Ø   Open the query Q012 in analyzer à Execute à Specify the cube name & query name à Execute à Observe the statistics

Ø   Different aspects what we can do to improve query performance:

Ø   If DB TIME IS MORE: 1.Modelling Aspects 2. Query design 3. Compression

  1. 4. Aggregates 5. Partitioning 6. Read mode of the query 7. Pre calculated web template 8. Line Item dimension 9. Indexes
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply