30 Aug 2018 Manage OBIEE 12c Server Cache from ODI 11g

The Oracle Data Integrator is one of the most powerful and robust data integration tools. But except for the ETL purposes, ODI can also interact with the BI server and execute the BI server functions that can be performed in the Issue SQL module in the BI analytics.

In this blog article we will address a use case for automating the OBIEE 12c BI Server cache purge using ODI 11g. This approach can be reused for executing any BI server functions via ODI 11g.

1. Use case and challenge

The OBIEE 12c application can maintain a local set of results from the database SQL queries that are generated during the report refresh in the server where it is hosted. This data set can be reused when a similar query is requested.

The BI server cache aka Query cache is widely used to improve the performance of the reports at the cost small disk space in the server.

The BI server cache has to be regularly refreshed – Purged and Seeded in order to avoid the risks of displaying the stale data in the reports. So, whenever the database tables get loaded with a new set of records, the cache must be refreshed on the BI side. But it is a hectic manual process to monitor the load plan and run the purge scripts to refresh the cache.

2. Assumption

Let´s assume that OBIEE 12c is hosted on the server – obiee12c.clearpeaks.com and the ODI 11g is hosted on odi11g.clearpeaks.com.

3. Steps to automate the cache purge

3.1. On the OBIEE server side

• The purge_cache.sh file connects to the BI analytics web application using the weblogic credentials and executes the code available in the purge_cache.txt file. This is similar to running a script in the Issue SQL.
• This script exposes the admin (weblogic) password which is a potential security threat. This can be addressed by the organization´s specific password encrypting techniques where this solution is to be deployed.

Figure 1: OBIEE Issue SQL.

• Next step is to create another file – purge_cache.txt under the following directory –
  /user_projects/domains/bi/bitools/bin with the content below:

  call SAPurgeAllCache();
  

   

  This file consists of the script that is to be executed in the BI server issue SQL.

3.2. On the ODI server side

Create a new ODI Procedure from the ODI Studio as per below code:

import com.jcraft.jsch.*;
String USER_NAME = "<Server User Name>";
String HOST_NAME = "<Server Host Name>";
int SSH_PORT = 22;
String PASSWORD = "<Password>";
String COMMAND = "<Oracle_Home>/user_projects/domains/bi/bitools/bin/purge_cache.sh><Oracle_Home>/user_projects/domains/bi/bitools/bin/purge_cache.log";
JSch jsch = new JSch();
Session session = jsch.getSession(USER_NAME,HOST_NAME, SSH_PORT);
session.setPassword(PASSWORD);
session.setConfig("PreferredAuthentications", "password");
session.setConfig("StrictHostKeyChecking", "no");
session.connect();
Channel channel = session.openChannel("exec");
((ChannelExec) channel).setCommand(COMMAND);
channel.setInputStream(null);
((ChannelExec) channel).setErrStream(System.err);
InputStream in = channel.getInputStream();
channel.connect();
while (true) {
    if (channel.isClosed()) {
        break;
    }
    try {
        Thread.sleep(1000);
        } catch (Exception ee) {
        throw ee;
    } }
channel.disconnect();
session.disconnect();

Next replace the following tags in the script file with the respective details:

• Server User Name
• Server Host Name
• Password

Figure 2: ODI Script.

Include this procedure to the last step of the load plan in ODI to make sure it executes after the data has been refreshed in the tables.

Figure 3: ODI Load Plan.

Conclusion

This above process can significantly reduce the manual effort that usually goes into the monitoring and executing of the scripts to purge the BI server cache.

Another advantage using this code is its reusable nature. The same code structure can be deployed to automate any BI server function that can be executed in the issue SQL page in BI analytics from ODI 11g.

Stay tuned for the next article on this topic in which we will deep-dive into automating the cache seeding process from ODI 11g.