OBIEE Cache

OBIEE includes two levels of caching, one level works at the OBIEE server level and caches the actual data for a query returned from the database in a file, the other level is at the OBIEE Presentation Server. This post will concentrate on the former.

NQSConfig

Caching is enabled and configured in the NQSConfig.ini file located at \server\Config, look for this section:

###############################################################################
#
# Query Result Cache Section
#
###############################################################################

[ CACHE ]

ENABLE = YES;
//A comma separated list of pair(s)
//DATA_STORAGE_PATHS = "C:\OracleBIData\nQSCache" 500 MB;(For ex)
DATA_STORAGE_PATHS ="C:\OracleBIData\cache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 100000; // 0 is unlimited size
MAX_CACHE_ENTRY_SIZE = 1 MB;
MAX_CACHE_ENTRIES = 1000; 
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;

The first setting here ENABLE controls whether caching is enabled on the OBIEE server or not. If this is set to NO then the OBIEE server will not cache any queries, if it is set to YESthen query caching will take place. 

DATA_STORAGE_PATHS is a comma delimited list of paths and data limits used for storing the cache files. This can include multiple locations for storage i.e. C:\OracleBIData\cache" 500 MB, C:\OracleBIData\cache2" 200 MB. Note that although the docs say that the maximum value is 4GB

MAX_ROWS_PER_CACHE_ENTRY is the limit for the number of rows in a single query cache entry, this is designed to stop huge queries consuming all the cache space.

MAX_CACHE_ENTRY_SIZE is the maximum physical size for a single entry in the query cache. If there are queries you want to enter the cache which are not being stored consider increasing this value to capture those results, Oracle suggest not setting this to more than 10% of the max storage for the query cache.

MAX_CACHE_ENTRIES is the maximum number of cache entries that can be stored in the query cache, when this limit is reached the entries are replaced using a Least Recently Used (LRU) algorithm.

POPULATE_AGGREGATE_ROLLUP_HITS is whether to populate the cache with a new entry when it has aggregated data from a previous cache entry to fulfill a request. By default this is NO.


USE_ADVANCED_HIT_DETECTION if you set this to YES then an advanced two-pass algorithm will be used to search for cache entries rather than the normal one-pass. This means you may get more returns from the cache, but the two-pass algorithm will be computationally more expensive.

Prevent Report from Hitting the Cache

Here i am back again with some stuff. Generally, when we run some reports the results get cached and we need to clear the cache for the report each and everytime when we run that report. This seems to be a tedious task when the RPD is in Online mode and we need to clear the cache their.

So, here is a technique to prevent the report from hitting the cache.

When we go to the Advanced Tab in Answers for that particular report, we have the Prefix and the Postfix sections there. Just go to the Prefix section and paste the below mentioned command:

SET VARIABLE LOGLEVEL=2, DISABLE_CACHE_HIT=1;

The next time you will run the report it wont hit the cache.

Kindly keep me posted with all your learnings and questions in BI, so that we can make this forum a more valuable place for learning BI.

Caching – A Cacophony in OBIEE Tuning !!!

Well ! A very interesting and controversial topic indeed , typically for Group Discussions . OBIEE  is not very much popular yet in OLAP Caching arena .There is hell lot of debates humming around regarding the BI server caching and obviously the aspects of performance tuning in effect of that .There are couple of benefits achievable in performance arena in true sense though sacrificing with couple of trade-offs .At the end of that day basic is , do Caching enable but do judiciously and make sure it would not get you into trouble in future . So need to configure the Caching considering substantial benefits as opposed to the trade-off. Here I am only focused on OBI server(SAS) level caching rather OBAW server(SAW).

Introduction

OBI has the ability to Cache query results, such that submitting the earlier processed requests do not pass through to the database and process it from filesystem.

Caching Framework

Benefits

Providing significant performance improvements as it frees up the database resources to perform other tasks and thus DB can entertain other set of user queries .

Native filesystem should perform better query processing and retrieve fast results comparing the data to and fro communication with database in network channel .

Moreover it conserves network resources by avoiding  connection to the database server so always less network traffic engagement could be markable .

This also reduces the  BI Server Engine processing overhead over queues of user requests .

However all the above things are so nice to comprehend and so difficult to manage  as providing the flexibility of faster response would lead to users an inconsistent state of data if not properly managed and planned .

This management and planning have several glitches and lots of dependencies and it is called as Cache Purging as opposed to its counterpart as Cache Seeding .

Cache Management Techniques

Do change Cache configurable parameters first .Note if disk space for Cached file exceed its limit ,entries least recently used(LRU) will be discarded and replace automatically to make room for new entries .Below mentioned techniques could be broadly distinguished as Manual , Automatic and Programmatic way to manage Caching :

Cache Seeding

There are several means to do this business :

1)  Set Global Cache parameter on – This will cache query running on any physical tables .By default for all tables in repository is cacheable .

2) Switch on Cacheable  property – This will provides table level benefit and extra customisation that which Physical tables should participate in generating query cache . E.g : sometime user would be more interested on giant Fact table caching rather tiny dimension tables .

3) Scheduling iBot – iBot could be properly configured and used for cache seeding purpose .This will silently build the cache without having any manual intervention . Possibly triggered in a time window after daily ETL load finishes or can be further customised and automated based on result retrieved from another iBot in chain request .The second iBot necessary ping the DB to identify whether a database update has been done(after ETL finish) before passing the request to trigger its chained counterpart .This will build data and query cache for a dashboard request and not for entire set of tables .

4)  Running nQCmd utility :

Another fantastic way to handle query caching which doesn’t have any dependency on the ETL load .But overhead to accumulate the superset of the actual physical query needs to be fired against a request /report and put it down in a single file to pass as parameter of nQCmd.exe . This necessarily need to be invoked after ETL run and by the ETL job itself .It could be done using remote login to BI server and trigger nQcmd  automatically and thus iBot scheduling time dependency could be avoided .

Cache Purging

A very important and crucial mechanism which should be proven good and perfect to make Caching a success story :

1) Manual  purging – Usually a dedicated dumb Administrating job ,kind an overhead for a company .This could be done simply by deleting the existing Cache TBL files or by firing purge from BI Admin Tool .This purging could be done by categories i.e. (Repository , Subject areas , Users or by physical tables) from Admintool in online mode .

2) Calling ODBC Extension – Bundled ODBC extension function like SAPurgeCacheByDatabase() ,SAPurgeCacheByQuery(),SAPurgeCacheByTable(),SAPurgeAllCache() etc . could be called to free the cache table for specific queries, tables,database or all .See Oracle documentation for details .This should be called using nQCMD utility and just after ETL load and before Cache seed to ensure there is no time window related gap and dependency .

3) Event Polling table – A nice and robust concept but not so nice to manage and lead to extra overhead .Really a good technique to make BI server aware of that DB update done and now carry forward to do your business of purging . A Cache Polling frequency is an important step and should be predefined and robust to make it a success .Poll table will be populated by a auto insert DB Trigger each time target DB tables updated .Analytics server polls that table at specific set of intervals and invalidates cache entries corresponding to updated tables.

4) Calling iBots to Purge Cache – It could be done by calling a custom java scripts .This in turn call nQCmd and ODBC extension to free cache .However the catch in this feature is again the iBots need to be scheduled just after ETL run and before Cache seed . So you might not sure about stale data if  ETL doesn’t meet the SLA .Again this could be done after setting a chained iBots to trigger the Purging activity in proper time .So ground rule is that never rely on iBot schedule on time.Lets pick the status from DB to trigger it .

Trade-off

Not purging the outdated caches , known as Stale Caches , can potentially return inaccurate results over time .Think of a situation where your Cache didn’t get purged on time after ETL load finishes. In this scenario though database has been updated but the change is not going to be reflected in your cached data as the seeded cache having outdated data at your filesystem and thus results a stale data which would throw inconsistent and wrong result .This potentially will cause huge confusion to the users mind .Thus Cache retention ,refresh time is important.

Not only that,in large platform , Caching should be separated and spreaded across  multiple folders/mountpoints for better utilization of I/O across the filesystems .The query Cache storage should be on local, high-performance ,high-reliable storage devices .The size of consumption of the Cached files would be a bottleneck for performance improvement across disk usage space .It should be used with proper Caching replacement algorithm and the policy towards the number of maximum cache entries  defined under NQSCONFIG.ini file . A potential Cache related problem found in Clustered environment where the Cache files build on one native host is not sharable with other one and this leads to be the same cache build across clustered participants as it could not be ensured that which BI server will handle which request .Until and unless the request get processed Cluster participant can’t determine there was already some Cache hit based on the query generated  and the request need not to be processed from other Clustered BI server .Again Purging need to be done from both Clustered servers .Cluster Aware Cache is propagated across Clustered Servers only when Cache is seeded via iBots and not using general dashboard request or answer based queries . So finally if you understand your business and requirements you could achieve success using Cache management by availing any of the above techniques .But again , be cautious ,Caching is not so reliable candidate at least from my experience .Hope Oracle will surely looks upon it to make it robust and non-debatable . Wish you the best and good luck with Cache implementation

Oracle BI EE 10.1.3.3/2 – BI Server Cache Purge from Dashboards – Using Pl/sql Gateway and DBMS_SCHEDULER package

I came across a good question on forums the other day. The question is how do we purge the BI Server cache from Answers or Dashboards. Basically, the user wanted to have a link clicking on which should enable him to purge the cache. Currently there is no out of the box way to achieve this since cache purging is done through some ODBC calls. It would have been much easier if we had a go URL to automatically purge the cache. But unfortunately there is none (if anyone comes across a GO URL or some other method of doing this feel free to share in the comments section). Lets look at a round about approach today to achieve this. Remember to test this out completely since it involves a lot of dependency across multiple components. Our methodology is based on below list of steps.

1.   Create a batch file which would make a call to nqcmd.exe to purge the cache.
2.   Call this batch file using DBMS_SCHEDULER package of Oracle Database.
3.   Create a package which would call the above dbms_scheduler package and then would do HTP.P prints to a static Html file.
4.   Expose this package to the end users using Pl/SQL gateway
5.   Call the Pl/SQL gateway URL from dashboards.

One of the main reasons of using the above approach is that this gives us a lot of flexibility. We shall be seeing more blog entries next week based on this approach. Lets start first with creating a simple batch file. This batch file would have the following command.

D:\Oracle\OracleBI\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Administrator -s D:\CachePurge.txt -o D:\Output.txt

As you see what this batch file does is it calls nqcmd.exe to purge the cache. NQCMD.exe is basically an utility which can make ODBC calls. CachePurge.txt file would contain the below command.

{call SAPurgeAllCache()};

      

      

The next step is to create a DBMS_SCHEDULER job to make a call to this batch file. DBMS_SCHEDULER is a database package which has a variety of features. One of its features is to make a call to command line utilities or shell scripts. In order, to use this package you need to ensure that that Job Scheduler service of the database (not BI EE) to be up and running. For all the database packages and procedures i have used the HR schema.

      

The job that you need to create is as shown below.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1′,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

      

Now, once this is done, create another pl/sql procedure to run this job on demand. The procedure is shown as below

CREATE OR REPLACE PROCEDURE PRO_INVOKECACHEPURGE IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1′);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>CACHE SUCCESSFULLY PURGED</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall execute via the pl/sql gateway. Thats why we are using HTP.P procedure inside the above procedure.

      

Now, the next step is to configure the Pl/SQL gateway. In order to configure Pl/SQL gateway use a 11g database and configure APEX. That will automatically configure the pl/sql gateway. For more details on this check my blog entry here. Once the above procedure has been compiled, lets grant execute on this procedure to anonymous so that everyone can execute this (Remember this has security implications. Try this only on a test instance. To use this on production you need to make this more secure by granting access on this only to relevant users).

GRANT EXECUTE on PRO_INVOKECACHEPURGE to ANONYMOUS;

Once this is done, ensure that you have added this procedure to the list of accepted and accessible procedures by adding it to wwv_flow_epg_include_mod_local function.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKECACHEPURGE’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

Now, test this procedure using the URL as shown below.

http://localhost:7779/apex/hr.pro_invokecachepurge

      

Now, all we need to do is to include this URL in dashboards by using the below in a URL object.

      

This is a very round about way of achieving such a small functionality. One of the main reasons why i have used the above approach is to introduce you to the above method. We shall see much more complex uses of the above approach in future blog entries.

This entry was posted on March 13, 2008 at 5:41 pm and is filed under All Posts, APEX, OBI EE Plus. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Cache management in a OBIEE clustered environment

Let us look at cache management in a clustered environment.

Consider a scenario in which I have two BI servers in a cluster – server A and server B and  I want seed the cache for users who have different data visibility.  A common approach to seed the cache is to use ibots. You could run an ibot as an administrator and  add the users as the recipients  to seed their cache. However, in this case because of the data-level security restrictions,  when you try to seed the cache for a particular user you might run into cache-seeding errors. Only users with administrator privileges in the RPD can seed the cache using ibots.

Another issue is that a request scheduled using ibots may be assigned to any one of the clustered BI servers. Even if a cache entry is made,  it will exist only in the node that processed the request. As a result, we will see a cache hit only if a query gets directed to the same BI server which has the cache entry.

A workaround to these issues is to use the NQCMD command line utility to seed the cache on all the clustered BI servers.  NQCMD needs to be run on all the nodes separately to seed the cache.

First we need to create a non clustered DSN entry in all the nodes in the cluster.

First step would be to purge the cache in all the nodes in the cluster.

Purging all Cache:

Open a text file and enter the following text:

Call SAPurgeAllCache()

Save this file as purgecompletecache.txt

Open another text file and enter the following:

nqcmd -d non_cluster_DSN -u Administrator -p Administrator -s purgecompletecache.txt

Save this file as purgecache.bat

When you execute purgecache.bat all the cache entries will be purged.

Purging Cache for a particular table:

Save the following in a text file:

Call SAPurgeCacheByTable(‘DBNAME’,'CATALOG’,'SCHEMA’,'TABLENAME’)

Call this file in the batch file and execution of the batch file will clear all the cache entries associated with the physical table TABLENAME. Note that the catalog, schema and tablename are  the values in OBIEE and not the name in the database. If the CATALOG value is null then use ”.

Repeat this process on all the nodes and verify that the cache entries have been purged.

The next step is to seed the cache for the different users. The following steps outline the steps involved in this:

Seeding the cache:

Copy the Logical SQL for the request you want to cache from cache manager and paste it into a notepad file.

I have named this notepad file: sql_1.

Now open another notepad file and enter the following text:

nqcmd -d non_cluster_DSN -u EMP4 -p EMP4 -s sql_1.txt -o output.txt

You should use the username and password of a user who has the same data visibility as the users whose cache you want to seed.

Save this file with a .bat extension. I have named it seedcache.bat.

When you run this windows batch file, the the following events occur:

1. call the nqcmd utilty

2. Log into OBIEE server using the non-clustered DSN  with username: EMP4 and password:EMP4

3. Execute the logical sql in file sql_1.txt

4. Write the output in file: output.txt.

Execute this batch file in every node of your cluster and verify that a cache entry has been made in your RPD for user : EMP4

Cache Management in 10g

The query cache allows the Oracle BI Server to satisfy many subsequent query requests without having to access back-end databases.
Cache Parameters in NQSConfig.INI

Most of the configurations are provided on the NQSConfig.INI.

Steps to configure Cache strategies…

As updates occur on the back-end databases, the query cache entries can become stale. Therefore, Oracle BI Administrators need to periodically remove entries from the query cache using one of the following methods.                                                                                                                                                                  

Methods of Purging cache entries

1. Configuring cache parameters in the NQSConfig.INI
2. Controlling cache at physical layer in Administrator tool
3. Using Cache manager to manually purge cache entries
4. Automatically purging cache entries
5. Seeding cache and event pooling table

Oracle BI EE 10.1.3.3/2 – Scheduling Cache Purging – Phase 2 – Using Java and Delivers

If you had seen my previous blog entry here, i would have shown you a procedure to purge the cache on a periodic basis using Java Script. I was asked today about the possibility of doing the same using Java. So, lets look today how to go about scheduling cache purges using Java and Delivers.

1.   The 1st step to achieve this is to create a SQL.txt file containing the actual ODBC call.

{call SAPurgeAllCache()};

      

In my case, i have created this in D:\ drive.

2.   Now, create a batch file which would make the following call to NQCmd.exe. You can store the batch file anywhere. The following would be the contents of the batch file.

D:\Oracle\OracleBI\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Administrator -s D:\SQL.txt -o D:\Output.txt

3.   Open JDeveloper and compile the following java program.

package bieesoap;

import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;

public class CachePurgeRun implements SchedulerJavaExtension {
public CachePurgeRun() {
}

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException
{
try
{
Process p = null;
//String[] cmdAry = {nqCmd,” -d “, dsn, ” -u “, user, ” -p “, pswd, ” -s “, tempInFileName, ” -o “, tempOutFileName};
String[] cmdAry = {“D:\\CachePurge.bat”};
p = Runtime.getRuntime().exec(cmdAry);
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}

}

public void cancel() {
}

}

As you see, what this above code does is, it makes a call to the NQCMD.exe file and passes the SQL.txt file as its argument. Once this is executed the output would be stored in the output.txt file.

4.   Now, bundle the above java code into a jar file (for details on how to do this check my blog entry here

5.   Once this is done, just go to delivers and call this java class from the Advanced tab. You can schedule this periodically.

      

6.   Just keep checking the output.txt to know whether the cache purge has completed successfully.

      

Oracle BI EE 10.1.3.3/2 – Scheduling Cache Purging

One of the common questions that i typically get while on customer calls is how do we about purging the cache on a regular basis. Well this blog entry would give you a step by step approach for doing the same. This is just an example. You can customize this to your needs. Before going into the actual procedure lets look at an easier approach of purging the caches. If you go to the Admin tool you would basically find a cache Manager under Manager. If you open the cache manager you would find all the Logical SQL statements that have been cached. You can purge each of these one by one or you can do a Purge All.

      

      

      

Now lets look at automating the above process. In order to automate this we shall be using a utility called NQCMD. What this utility can do is it can accept ODBC sql calls and can execute them. So, our aim is to call this utility from an ibot via a java script. Lets first look at the syntax of NQCMD. If you go to {OracleBI}\Server\bin via command line and type in nqcmd/?, you would get the syntax as shown below.

      

So, basically it accepts the following important parameters

   1.   Username
   2.   Password
   3.   BI Server DSN
   4.   Input file containing the Purge SQL Statement
   5.   Output result file

Now, lets look at the list of steps to purge the cache one by one.

1.   Open a text editor and type in the following command. This command will be purging the cache.

      

{call SAPurgeAllCache()};

Save this file in say D drive as SQL.txt. This will be our input file.

      

2.   Lets start creating a simple javascript. Open a text editor and enter the following list of statements.

var nqCmd = “D:\\Oracle\\OracleBI\\server\\Bin\\nqcmd.exe”;
var dsn = “AnalyticsWeb”;
var user = “Administrator”;
var pswd = “Administrator”;
var tempInFileName = “D:\\SQL.txt”;
var tempOutFileName = “D:\\Output.txt”;
var wshShell = new ActiveXObject(“WScript.Shell”);
var dosCmd = nqCmd + ” -d \”" + dsn + “\” -u \”" + user+ “\” -p \”" + pswd + “\” -s \”" + tempInFileName + “\”" +” -o \”" + tempOutFileName + “\”";
try
{
wshShell.Run(dosCmd, 0, true);
}
catch(e)
{
throw e;
}

Save this file in {OracleBI}\Server\Scripts\Common as “Purge.js”. As you see above what this java script does it calls the nqcmd utility by passing in the relevant arguments.

      

3.   Now go to BI EE Delivers and start creating a simple ibot. Go to the advanced section and enter the details of the Java Script that we created. In the schedules section choose the frequency with which you would like to run this script. For testing purposes lets test this by running the script immediately

      

4.   Once this is done save the ibot. Now you can see that all the cache would have been purged and also you can see how much have been purged from the output.tx file.