OBIEE: Seeding the Dashboards for End Users and Purging on Nightly Basis

Many dashboards on most systems can be seeded with an iBot that runs overnight and caches the reports to the dashboards for quick access and less queries to the database. We created a series of iBots that are triggered by an initial iBot the purges the old Cache and starts the process of reseeding the data. First we created the iBot Purge Cache:

This iBot is run at 5:00am every night after the ETL completion by the Administrator user. The delivery content is the first report we needed to cache. The destination for the iBot is the Oracle BI Server Cache.

Notice in the Advanced section the javascript purgeSASCache.js, Below are the parameters that were set in the Advanced tab.

The javascript purgeSASCache.js should be placed in the folder OracleBI/Server/Scripts/Common
The below is the javascript file:

outStream.Close();

return output;

}

//////////////////////////////////////////////////////////

// Get WshShell object and run nqCmd. Capture the output

// so that we can handle erroneous conditions.

var wshShell = new ActiveXObject("WScript.Shell");

// Create a temp file to input the SQL statement.

var fso = new ActiveXObject("Scripting.FileSystemObject");

var tempFolder = fso.GetSpecialFolder(2);

var tempInFileName = fso.GetTempName();

var tempOutFileName = fso.GetTempName();

tempInFileName = tempFolder + "\\" + tempInFileName;

tempOutFileName = tempFolder + "\\" + tempOutFileName;

var tempInFile = fso.CreateTextFile(tempInFileName, true);

tempInFile.WriteLine(sqlStatement);

tempInFile.Close();

try

{

// execute

var dosCmd = nqCmd + " -d \"" + dsn + "\" -u \"" + user

+ "\" -p \"" + pswd + "\" -s \"" + tempInFileName + "\"" +

" -o \"" + tempOutFileName + "\"";

wshShell.Run(dosCmd, 0, true);

var output = GetOutput(fso, tempOutFileName);

// Remove the temp files

fso.DeleteFile(tempInFileName);

if (fso.FileExists(tempOutFileName)) {

fso.DeleteFile(tempOutFileName);

}

// Check the output for any errors

if (output.indexOf("Processed: 1 queries") == -1) {

ExitCode = -1;

Message = output;

}

else if (output.indexOf("Encountered") != -1) {

ExitCode = -2;

Message = output;

}

else {

ExitCode = 0;

}

} catch (e) {

if (fso.FileExists(tempInFileName)) {

fso.DeleteFile(tempInFileName);

}

if (fso.FileExists(tempOutFileName)) {

fso.DeleteFile(tempOutFileName);

}

throw e;

}