Question:
Hi,
I have to optimize a BW query. There are two ways. One is with Aggregate and other is OLAP Cache.
I have done with aggregtes few times. We are on BW3.1C with UNIX.
I have afound a document about improving a BW query performance using OLAP cache.
My question is that which one to choose from these two?
Is OLAP is only for Web Query or it is good for BEx as well. We have only BEx.
The document explaines about creating a Reporting Agent using Web Application Design. And run the query as an Agent which saves a result in OLAP Cache. I have never done any of those. Can it be done using SAP GUI as well.
I do not knwo how to do it.
Please help me out.
I will appreciate your help.
Answer:
SteThai
The OLAP cache is used as the name states, OLAP. This cache is used for both BEx and Web Queries as they use the same OLAP engine.
The cache is either activated at cube level, or can be activated by each query. I would recommend that you control the cache usage by query as it is can quite often cause more problems by having every query use the cache (cause many swaps in the buffer of the app server).
The cache can use either physical memory or a flat file but this is a global setting controll by the SAP profile maintained on your Central Instance. The default is memory and is set at 200mb. The best use of the cache is for small but frequently used queries as these can be read from memory instead of constant DB activity.
Aggregates better serve queries that process larger amounts of data, but it depends on the type of query. An example would be a cube with document number in a dimension table. It will be worthwhile to create an aggregate that does not contain the document number as the vast majority of queries do not require document level information and the user that do require this amount of detail will then have to expect a slower response time.
If this type of query was to use the OLAP cache, then any other query would be displaced from the cache to make room for the large query (even if it was only run infrequently).
Once you have correctly set both the cache and aggregates you can then look at standard buffer options on your CI or APP server and also use the SGA and memory pools of the database. In BW, you cannot buffer the contents of a dimension tables (table key contains Integers) but you can place the contents of the dimension table into a KEEP pool on Oracle and move the Fact table to a recycle pool, which frees up space in the Default buffer pool.
The whole concept is to reduce the amount of I/O activity and this carries the highest cost for a CPU (about 4000 times slower than memory).
Hope this gives you some insight, but you will have to work with your Basis guys and specialist DBA if you have one.
Cheers,
Kevin
Answer:
The cache is either activated at cube level, or can be activated by each query. I would recommend that you control the cache usage by query as it is can quite often cause more problems by having every query use the cache (cause many swaps in the buffer of the app server).
Hi,
Thanks for the explanation.
How will I find out that if cache is activated at Cube level or Query level. Is there any parameter or any settings. Please advice.
The cache can use either physical memory or a flat file but this is a global setting controll by the SAP profile maintained on your Central Instance. The default is memory and is set at 200mb.
Can you please please tell me which parameters you are talking about so that I can do more research on those.
Your help will be greately appreciated.
Thanks.
Answer:
Drop me a PM, I have a white paper on the use of the cache.
Cheers,
Kevin
Answer:
Hey Kevin,
Can I have that white paper too.I have a similar issue.
Thanks in advance.