Cache in OBIEE!!

Different ways to Manage Cache in OBIEE!!

One of the most powerful features of OBIEE is the way it uses it’s cache. Good cache management can really boost your performance. From the system management point of view there are a couple of tips and tricks to influence the cache performance.

Here are few Examples of Managing Cache.

1. Purging the whole cache.

If you have a completed database reload or want to do some performance testing with your repository you might want to purge the whole cache.
Put the following in a .txt file in your maintenance directory

// Purge complete cache
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s

c:\obiee\mscripts\purgecompletecache.txt Call SAPurgeAllCache()

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\purgecompletecache.txt
2. Purging the cache by table

If you have a major update of your dimensional tables you might want to clear the cache for just one table.
Put the following in a .txt file in your maintenance directory:

// Purge complete cache
// FileName: PurgeTableCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeTableCache.txt

Call SAPurgeCacheByTable( ‘AQIES_SH’, NULL, ‘SH’, ‘TBLTRY’ );

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeTableCache.txt

3. Purging the cache by query

Sometimes you only want to purge only “old” data from your cache.
Put the following in a .txt. file in your maintenance directory:

// Purge cache by Query
// FileName: PurgeQueryCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeQueryCache.txt

Call SAPurgeCacheByQuery(’SELECT * FROM Transactions
WHERE Transactions.Date_entered <= TIMESTAMPADD(SQL_TSI_YEAR, -1,NOW())’); // The “query” line must be one continues line! You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeQueryCache.txt

4 Purging the cache by database

Put the following in a .txt. file in your maintenance directory:
// Purge cache by Database
// FileName: PurgeDataBaseCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeDataBaseCache.txt

Call SAPurgeCacheByDatabase( ‘AQIES_SH’ );

// The “dbName” is the OBIEE name!

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeDataBaseCache.txt

You can also purge cache direct from Dashboard by following the given steps.

Goto  Settings—>Administration —> Issue sql

call sapurgeallcache() and click issues sql..below is the screen shot

Thanks to-http://techblog.aqies.com/2011/04/11/cache-management-in-obiee/

PRESENTATION CACHEBy default, the cache files for the presentation server reside in the \tmp directory within the respective subdirectories sawcharts, sawrptcache and sawvc; while the xml cache files lying in the \tmp folder itself.   

Chart Cache - \OracleBIData\tmp\sawcharts\
Report Cache - \OracleBIData\tmp\sawrptcache\
State Pool Cache - \OracleBIData\tmp\sawvc\
XML Cache - \OracleBIData\tmp\
See image below for an example of how to modify the instance config to explicitly change the default presentation cache directory locations. 


Additionally, and only specific to the XML Cache directory location change, you must also make a change to the nqconfig file as follows:

WORK_DIRECTORY_PATHS = "C:\DataSources\Cache\tmp";

ENABLE PRESENTATION CACHE
In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )

<ServerInstance>
<Cache>
<Query>
<MaxEntries>100</MaxEntries>
<MaxExpireMinutes>60</MaxExpireMinutes>
<MinExpireMinutes>10</MinExpireMinutes>
<MinUserExpireMinutes>10</MinUserExpireMinutes>
</Query>
</Cache>
<ServerInstance>



DISABLE PRESENTATION CACHE FOR ENTIRE APPLICATION
In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )
<ServerInstance>
<ForceRefresh>TRUE</ForceRefresh>
</ServerInstance>

BYPASS PRESENTATION CACHE

Sometimes you want to bypass the presentation / cache for development purposes. Or more often when you get weird write back behaviour. Add this to the instanceconfig file:

<CacheMaxExpireMinutes>-1</CacheMaxExpireMinutes>
<CacheMinExpireMinutes>-1</CacheMinExpireMinutes>
<CacheMinUserExpireMinutes>-1</CacheMinUserExpireMinutes>

CLEAR PRESENTATION CACHE
Via Oracle BI Web Application
Settings-->Administration-->Reload Files and Metadata-->Finished

or

Via Server Service
Shut down Presentation Services to remove the files for the Presentation Cache

**If you delete cache files when Presentation Services are still running or SAW does not shut down cleanly, then various cache files might be left on disk.



BI SERVER CACHE
By default, the BI Server Cache is stored in the \OracleBIData\cache\ directory and stored as NQS*.tbl files. 

BI SERVER CACHE FILE NAME FORMAT

NQS(Prefix)_VMVGGOBI(Originating Server Name)_733547(Days passed since 1-1-0000)40458(Seconds passed since last midnight)_00000006(Incremental number since last BI server start).TBL

ENABLE BI SERVER CACHE
To enable the query cache for the entire application, you must set the ENABLE cache parameter value to YES in the file nqsconfig.  ( \OracleBI\server\Config\NQSConfig.INI )
ENABLE = YES;


DISABLE BI SERVER CACHE FOR ENTIRE APPLICATION
To disable the query cache for the entire application, you must set the ENABLE cache parameter value to NO in the file nqsconfig.  ( \OracleBI\server\Config\NQSConfig.INI )
ENABLE = NO;


BYPASS BI SERVER CACHE FOR SINGLE REPORT
In Answers, goto Advanced Reporting tab when building report, set Prefix value to following:
SET VARIABLE DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1, LOGLEVEL=7;


CLEAR BI SERVER CACHE
To clear the BI Server Cache files, run the following command file:  \OracleBI\server\cache_purge_reseed\call.bat  

This calls the purge.txt file, which simply contains the following command:
call SAPurgeAllCache();

This will clear the BI Server Cache (.TBL) files in the Cache directory: 
\OracleBIData\cache\

or

An alternate way of doing this is via the OBI Admin Tool, using the Cache management feature.

 

BI SERVER CACHE PERSISTENCE

...When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.

Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.

BI SERVER CACHE ENABLED BUT NOT CACHING
OBIEE cache is enabled, but why is the query not cached?...

Non-cacheable SQL function:  If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.

Non-cacheable Table:  Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.


Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

Caching is not configured: Caching is not enabled in NQSConfig.ini file.


Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.


Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.

Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.

OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2

Thanks to-http://mjlong999.blogspot.in/2011/07/disabling-cache.html

Thanks to-http://prolynxuk.com/blog/?p=496

OBIEE Purge/Clear Cache using Answers

without comments

I found this one quite useful to clear the cache using an Answer request, its very handy and can be scheduled to run on a regular basis as well. I have tried this using OBIEE 11g and works, guess it should work in 10g as well, as the concept is very basic.
Just follow the steps here
1. Open the RPD, create a new database and select Allow direct database requests by default

Name: BIServer and Database: ODBC Basic
2. Create a connection pool with no username and password, set the right permissions on the connection pool

connection pool name – AnalyticsWeb
Select ODBC 2.0 and the DSN is AnalyticsWeb (this should match with the entry in odbc.ini)

Save the RPD.
4. Create a new Answer (or Analysis in 11g) and select “Create a New Direct Database Request”

In the connection pool, enter “BIServer”.”AnalyticsWeb” (database.connection pool)
In the sql statement, enter the command
Call SAPurgeAllCache();
Clicking on “Validate SQL and Retrieve columns” should retrieve couple of columns as shown.
Save the request and that’s it, this request can now be run anytime to purge the cache or even scheduled.