Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on March 13, 2008

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.

About these ads

8 Responses to “Oracle BI EE 10.1.3.3/2 – BI Server Cache Purge from Dashboards – Using Pl/sql Gateway and DBMS_SCHEDULER package”

  1. Sumant Sarkar said

    Hi Venkat, could you tell me which forum you refer to in the first line of this article?

  2. Venkatakrishnan J said

    Sumant – I referred to the BI EE forums here http://forums.oracle.com/forums/forum.jspa?forumID=378&start=0

  3. […] « Oracle BI EE 10.1.3.3/2 – BI Server Cache Purge from Dashboards – Using Pl/sql Gateway and DBMS_SCHE… […]

  4. vins77 said

    Hi,
    but I think that it’s possible to do it from ibot.
    Infect you can run an sql statement from vbs… and after you can run ibot from soap api.
    Using dot net webpage.
    I do something for refresh e db table on click.
    Do you try this?
    Vins

  5. BI said

    Hi Venkat,
    Is there a way to prepopulate cache through a batch file.That is every night after the ETL load is done,can we prepopulate the cache for some expensive queries?

  6. subhakar said

    we have two dedicated serrvers. biserver on one server ans biserver data(web catalog) on another server. job manager is configured on biserver. if we want to use java script for cache, we have to give it in biserver. so how can we guide that between two servers?

  7. Asif Husain Khan said

    Hi Venkat
    I am new to oracle business intelligence.Can u help me in creating a dashboard where selecting one of the radio button in a section effects & refreshes only other section to it which is interlinked,rather than refreshing whole page of dashboard?
    Thanks

  8. Robert Murray said

    Venkat,
    This and all other methods of automating cache purging are all talking about BI Server cache. Is that correct? Is it possible to script purging Presentation Services Cache?
    Thanks for sharing you BI Knowledge, I find your BLOG extremely helpfull.
    Bob

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: