Performance of the BW system plays a key role in the business, as it directly impacts the user’s bandwidth. To solve performance issues there should be a standard life cycle maintained in every project at regular intervals to check validate and resolve the performance issues.
Some of the components which you can check to improve your query performance faster are:
At the data base level:
1. 1) Data Model: A data model defines the way the objects are developed for example dimensions in an infocube...
a) Make sure the dimensions in an infocube are as smaller as possible if you have individual characteristics
Example: Try not to include product and customer in a single dimension.
2. 2) Query Definition: The way you define a query in Query designer also impacts the performance of the runtime of the query.
a) Make sure that most of the calculations are done in transformations at infoprovider level rather than defining them in a query.
b) Be sure that the InfoCube is compressed if you use non-cumulative key figures.
3. 3) Aggregates: The main use of the aggregates is to fasten the response time of the query by reducing the amount of data which makes it a subset of an infocube.
a) Define aggregates for queries that have high data base read times.
b) If you have more aggregates the loading time also increases which effects the loading performance as roll up has to take place.
c) If the aggregates proposed by you or the system are too large and there is no good enough effect on the performance try using OLAP CACHE.
4. 4) OLAPCache: It is just a buffer area where the data of the frequently used queries is stored, so whenever you execute or refresh a query instead of going to the database and fetching the data the system brings it from the OLAP CACHE.
a) There are five Cache modes, to find them go to the tcode RSRT
b) Enter your query name and click on properties tab
c) After unchecking the infoprovider setting the Cache Mode will be highlighted where you can choose your Cache options.
1) Cache inactive,
2) Memory Cache without swapping
3) Memory Cache with swapping,
4) Cluster/Flatfile Cache per application server,
5) Cluster/Flatfile Cache cross-application server.
d) The stored OLAP CACHE data will be updated every time the new data is uploaded to the respective infoprovider also whenever the query is reactivated.
5. Pre-Calculated Web Templates: Pre-calculation is a process where you can distribute the workload of running the report when no one is using it and making it ready to access very fast when the users accesses the report. The main advantage is that it reduces the load on the server which makes the server to act faster to access data.
6. Compressing: Compressing an infocube transfer the data from f fact table to E fact table the requests are deleted and the records are aggregated which have the same keys
a) We have to compress all those requests as early as we can which are not likely to be deleted.
b) The same with the aggregates as well, compress them as soon as possible.
c) Compressing improves query performance of InfoCubes containing non-cumulative key figures significantly.
7) Indexes: We can define secondary indexes most of the secondary indexes have the negative impact on the data activation performance as they have to be maintained during the load
Example: We drop indexes before loading data into an infocube and rebuild them after loading
8. DBStatistics: We have to be certain that data base statistics are definitely maintained in the system with regular updating.
a) As most of the DBMS(database management systems) do not maintain the statistics for newly created infoproviders like infocubes we have to make sure that they are maintained before used for reporting.
b) If you have performance problems when building statistics use the alternate DBMS_STATS method.
I. Please go through SAP note 351163 (Creating ORACLE DB statistics using DBMS_STATS).
II. SAP note 129252 (ORACLE DB Statistics for BW tables) for details.
- SAP Managed Tags:
2 Comments
