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.
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.
Posts
