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.