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

Physical Property Of table :Cache

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.

Cache Manager Cache Tab

Cache Manager Physical tabl

Cache Operations

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.

Seeding Cache

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.

OBIEE Cache Management

Back Ground

Business Intelligence system user usually queries a larger Data Warehouse or Data Marts.
And when ask few import things they are looking for, and performance ALWAYS comes!!!. Cache helps to make a BIG win when it comes to performance.

So Cache helps us in
1) Achieving better performance for query by not going to Database server and satisfying request from cache file on OBIEE server itself.( There are some criteria for cache hit to occur)
2) It saves network resources by not make round trip.

But with performance comes some cost.
1) Human Resources to mange
2) Physical Resource (data storage etc)
3) Keeping cache up to date. (If users gets data quickly but its not fresh/updated then it does not make sense either).

Below is the process flow or Architecture of OBIEE cache

Cache System Archicture

Steps to Configure the Cache

Step1: Change NQSConfig.ini file

Go to \ OracleBI\server\Config\ NQSConfig.INI

In NQSConfig.ini go to cache section you will see something as below screen shot

Cache Sesction of Config file

All the parameters are basically self explanatory like path of file , size , number of rows etc etc

Oracle suggests having cache file on high speed storage.

Step 2: Managing the cache.

We can do cache management with various techniques

A : Configuring cache parameters shown in the file above
B : Controlling cache at physical layer in Administrator tool
C :Using Cache manager
D: Automatically purging cache entries
E: Seeding cache and event pooling table.

I will cover all above five techniques in my next post.