Have you seen this in OLAP Cache?

[Former Member]

I am doing some testing on OLAP Cache usage and found this that the cache gets generated and is resued for a small data set like for 2 months but once I run the query in the back end lets say for a larger set 12 months or so, though it gets registered in the cache (with a lesser K Bytes), its not resued at all, any subsequent query run still hits the database. I am thinking this has to do with the Reporting agent setting in which we select "FILL OLAP CACHE IF POSSIBLE". this seems to work only for small sets of data. If I have to fill the OLAP Cache with large data sets, is there any table size or buffer size that I can increase? if anyone has encountered this scenario please let me know...

Ram

[edwin_harpino · Accepted solution]

hi Ram,

try increase cache with RSRCACHE,

or from RSRT1.

default value 200mb(?)

refer to oss Note 656060 - OLAP: Cache main memory displacement not functioning

to set profile (RZ10), please ask basis help.

...

Solution

Increase the amount of shared memory. The size of the shared memory shouldbe at least 200 MB. You can refer to transaction RSRCACHE for the amount of memory reserved by the main memory cache (RSRCACHE -> Main Memory: Shared Memory/Buffer Reserved).

The corresponding settings are (at least):

rsdb/esm/buffersize_kb = 200000

and, as a precaution, also

rsdb/esm/max_objects = 10000.

Alternatively, for queries with large resulting quantities, you can convert the Cache Mode in transaction RSRT1 to

Cluster/Flat File Cache for Each Application Server (mode 3)

Cluster/Flat File Cache Across Each Application Server (mode 4)

hope this helps.

[Former Member · Accepted solution]

Hi Edwin,

This is helpful, let me do more testing on this and I will comeback with the results...Out currecnt global cache is 200 MB (Local is 100MB). the cache size for 2 months is about 2.6 MB, and if I extrapolate it to 12 months it would be maximum of 30 MB which was far less than 200 GB Global, so this would not have been as issue. But yes your suggestion on the Shared memory might be helpful becasue right now in our system we got about rsdb/esm/buffersize_kb size as 4 MB and rsdb/esm/max_objects as 2000 ( Pretty small ). Thank again for your answer...will get back with some exciting news!!

[Former Member · Accepted solution]

Haven't looked at in quite some time, but I beleive there are some restrictions on what will be cached - if it exceeds a certain number of blocks or(and/or?) a percentage of the global cache, it won't cache it to prevent a query result set from consuming most/all of the global cache. Think there was an RSADMIN entry to control this, but not sure about that.

Using the persistent cache options Edwin mentioned might be a better choice for such a large query result - the Cross Application Server option is probably best if you are in a mulit appl server environment, that way the query can be run once and cached to an area that is accesible to all appl servers, rather than having to run the query on each appl server and cached separately on each server.

Check to see if you have the option to store to a BLOB. This option was added a few SPs ago and should offer better performance for large (e.g. more than 1MB) result sets thatn teh cluster table option.

[Former Member · Accepted solution]

Hi Edwin and Pizzaman,

thanks for your suggestions, I am now able to cache more data and have increased those buffer paramters that you have mentioned. Also apart from the 2 parameters rsdb/esm/buffersize_kb (200 MB) and rsdb/esm/max_objects (10,000) are there any other parameters that you guys can think of that will help the Caching of large data may be 2 years?

Also what do you think of having the Cache set at query level than at the info provder level, is this a good plan? your suggestions are much appreciated...

[Former Member · Accepted solution]

If you have some queries that have generated large result sets, you want to consider using the persistent cache options we've talked about. Smaller results can probably pickup the InfoProvider default, which would probably be one of the in memory options.

Really depends on your environment - do you have lots of queries, do you allow queries to be created in prod, do you have a small number of quereis that are run hundreds or thousands of times a day, etc. Those are the things that might influence the way you go on this.

If you have lots of queries that are run infrequently, you probably want to turn OLAP caching off for them. No point in caching a query result for a query that is only run once a day or once a week. Especialy true if you are filling up your Global cache and causing it to either swap to disk or purge other cached results. I suppose there could always be an exception, e.g. the CEO is the person that runs it once a day.

[Former Member · Accepted solution]

Hi Pizzaman,

I am back with another issue :)..Cache works as expected on queries against Cube...no matter what size or history it is..but when I cache queries on ODS I have a problem...let me explain:

1. Setting on ODS Query is Main memory with Swapping on Flat file.

2. Ran the query as a reporting agent background job, saw that it registered in Cache. (variable used was on date range 01/01/2006 to 01/31/2006, variable is set to change during navigation)

3. Executed the query through RSRT in debug mode for a variable date of 01/01/2006, surprisingly it did not use the cache at all and ran from data base.

4. next time when I executed the same selection it was from Cache!!!

So I dont understand why it did not use cache in step 3 but on step 4 it did??

Can someone explain if they experienced this on ODS reports using Cache

Ram

[Former Member · Accepted solution]

We use Reporting Agent to warmup the OLAP cache for some ODS queries. We're BW 3.5 SP15 - might be worth a qucik check of OSS Notes if you haven't already done so, but I don't think that is your issue, although there continue to be Notes for caching.

Couple of things I'd double check, but don't have anyting solid to offer:

I'm assuming you didn't have the Do Not Use Cache option selected in RSRT - duh!.

You've confirmed that the initial cached result set wasn't invalidated due to a data load to the ODS?

The Reporting Agent run ran on the same Appl Server as your query? The Main Memory cache is Appl Server specific so if the RA job ran on Appl 1 (or Central Instance) and you are logged on to Appl 2, you won't hit the cached result from the RA job.

I'll give it some more thought, but that's all I can come up with at the moment.

[Former Member · Accepted solution]

Nope, I doubled checked all of these.

1. I did not check DO NOT USE OLAP CACHE in debug mode.

2. There were no data loads to ODS

3. There is only one app server (tests are in our dev environment, so no chance of having 2 servers )

We are on SP 18 BW 3.5, last thing left out is OSS Notes...thanks anyway Pizzaman I will keep working...and in case find out something will get back...