Automating cache purging through windows scheduler &Re-building Cache

windows scheduler.

Call SAPurgeAllCache();

Start command prompt and run following command. Note, there are four parameters in the command. You must replace this with values relavent to your implementation.

-d = ODBC datasource name
-u = Admin user name
-p = Admin Password
-s = sql file

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

1. Go to Control Panel> System and Security> Administrative tools> Schedule tasks

2. Click on Schedule tasks. It will open Task Scheduler window.






3. On the right hand side, click on Create task. It will open a new window to create task.

4. Depending on your choice, you can change the trigger setting. Here I have done repeat task every 1 hour.

6. Go to Actions tab. Click on New button to get New Action window. Select the .exe file which you want to schedule. Here we will select nQcmd.exe which is present in c:\oraclebi\server\Bin folder. Add the arguments which is required to open nQcmd.exe and purge the cache. Here the arguments are :

-d AnalyticsWeb -u Administrator -p Administrator -s c:\oraclebi\server\Bin\SQL.txt

-d is for Data Source Name i.e AnalyticsWeb.

-u is user name i.e Administrator

-p is Password i.e Administrator

-s is File path where the purge cache command is writtent.

Create a file SQL.txt and write the purge command:

{call SAPurgeAllCache()};

Place the file in Bin folder

Re-building CacheOnce the cache is purged, you may want to re-build cache for some of the frequently used reports to avoid delay in response to the users. Re-Building cache requires executing the "logical" SQL query against the data source. We can use NQCMD same way we used for purging cache. Instead of purge function, we have to include logical SQL statements in the text file.

Create a text file using notepad with name "BuildCache.txt" and add logical SQL commands to it. You can include more than one command separated by semicolon(;). Save the file to local disk (Let's assume to drive C:\). Below is example of the text file content.

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."SalesAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."CostAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;
Start command prompt and execute the command same way we did for purging the cache.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

CALL SASeedQuery('Query').


THANKS...:)