Oracle BI and DWH

In Cache Management Part 1 I have written about over view of the cache in OBIEE system. Today I continuing from there…

A : Configuring cache parameters in NQSConfig.ini

Go to [ CACHE ] section of the config file

1 ENABLE  should be YES for cache to work.

2 DATA_STORAGE_PATHS : Need path where cache file to be stored and max allowed size

3 MAX_ROWS_PER_CACHE_ENTRY: Max row to be in any cache file. And hence his parameter helps to avoid run away query results being cache

4 MAX_CACHE_ENTRY_SIZE : Max size of each cache file

5 MAX_CACHE_ENTRIES: Max cache file in the directory specified.

Note:  When limit specified in MAX_CACHE_ENTRIES reached server will delete the Least Recently Used (i.e file which has not been used since long) to accommodate the new entry

6 POPULATE_AGGREGATE_ROLLUP_HITS: default is NO. It will store the agg result even if that request is being served from cache

7 MAX_SUBEXPR_SEARCH_DEPTH: it seaches the express till  the level specified for the cache hit.
B : Controlling cache at physical layer in Administrator tool

Please note the cacheable checkbox. By default all the physical tables are cacheable.

rest two options determines cache is valid till what time ?

Cache never expires will put the entry in cache permanently unless its cleaned by paramer 5 above

Cache persistence time This will determine validity time of cache

C :Using Cache manager

Open repository in online mode

Go to Manage–>Cache  You will see the screen as below. OBIEE administrator can control the cache from here as well.

You can see all the cache entries as shown in the screen above. You can purge the cache from here , see the sql being generated , Copy -save sql , and see the info which basically shows you the config parameters for the cache.

So this will be helpful in purging the cache entries manually.

I would suggest you to explore all the options.
D: Automatically purging cache entries

Cache entry will automatically gets purged when MAX_CACHE_ENTRIES limit reaches or when on physical layer it reaches that time.

Another Way to Automatically purge cache is to use even pulling table.

Event pulling table reads the data ( or status of the ETL ) and based on that it purges the cache. The problem with this is: it is not purely even base. i.e we can do like purge the cache and seed cache when ETL is done. However it can read the pulling table at specific interval and decides its action.

It a separate post in itself. I will address this in my future post.

E: Seeding cache

In normal English Seeding cache means running report automatically or manually (mostly in non business hours) before user actually needs it.

So when users needs that data it reads from the cache and hence performance is really amazing.

1 you can run report maually to generate cache( this is not possible for non business hours)

2 setting and iBot to run the report at specific time so that it generates cache.

Please refer the screen print below for better understanding.

You may create an iBot for the report for which you want to seed the cache. In Destinaton tab select Oracle BI Server Cache check box to seed the cache as shown above.