Thursday, July 19, 2012


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,


Satya Ranki Reddy

11 comments:

  1. current day date query sholud always go to database and remaining should fetch from cache

    ReplyDelete
  2. we dont have dwh for every 15min data will always changing and for current date always it should bring always from database

    ReplyDelete
  3. Hi Sandeep,

    What type of cache mechanism have you implemented?

    Kindly elaborate your issue some more extend.

    Thanks,
    Satya

    ReplyDelete
  4. step 1: first we have implemented cache never expires for all the tables used in schema
    step 2: every day morning we used agents to run the reports for yesterday date and all the results are stored in cache.
    so we are getting results at a faster performance because it is fetching data from cache
    step 3: the client requires for current date always query should go to the db to bring data.
    step 4: because the data for current date has been changing for evry 15 min

    ReplyDelete
  5. we have prompt for a dashboard which has default date for yesterday date and when we change the date to current date and apply the filter always for the current date it should be cached all fresh data should be displayed

    ReplyDelete
  6. Hi Sandeep,

    Yes, you can apply today date in prompt level and apply the filter also the n it will fetch data from database not in Cache.

    Note: You don't need to fetch the data from cache then bypass the cache in your individual report
    Using this command (set variable disable_cache_hit=1)

    How to bypass the cache in report wise.
    Please refer the link.
    http://satyaobieesolutions.blogspot.in/2012/08/step-by-step-to-bypass-all-caches-today.html

    Thanks,
    Satya

    ReplyDelete
  7. hi satya,

    in the above comment you have said to set variable diasble cache hit =1 we have implemented previously for every query its going to database its ok but our requirement is for yesterday date it should go to cache and bring the data and for current day date always it should go to database

    ReplyDelete
  8. hi satya

    waiting for your reply

    thanks

    sandeep

    ReplyDelete
    Replies
    1. Hi Sandeep,

      Sorry for delay response.

      I have to check my end and update you shortly whether it is possible or not will update u.

      Thanks,
      Satya

      Delete
  9. Hi,

    How to purge cache user level..can u give the how to configure user based?????/

    Advance Thanks, KailashReddy

    ReplyDelete
    Replies
    1. HI Kailash,

      I haven't tried user level cache purge.
      Try below method.

      You can pass a text file with SQL statements to the utility (script mode), or you can enter SQL at the command line (interactive mode). Queries are run against the default subject area, unless the object names used in the query are fully qualified.

      For example to purge BI Server cache run below command

      In windows:

      nqcmd -d AnalyticsWeb -u -p -s purgecache.txt

      In Unix:

      ./nqcmd -d AnalyticsWeb -u -p -s purgecache.txt

      Note: User coming from LDAP then try to create same user name in rpd then try to execute the above scrift. --- Am not sure this will work or not but you can try and let me know output.
      Thanks,
      Satya

      Delete