Bursting using OBIEE Agents

Part One

Author: Guillaume Slee, November 2015

 

The next few posts will be about how we can burst OBIEE reports using OBIEE agents.

Edit 06/01/2016: This bursting solution relies on invoking agent server scripts which is only supported on Windows servers.

Oracle's definition of bursting is:
"Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered."

One example could be that you have a Sales dashboard filtered by region and you would like to burst this report out for all regions and save each dashboard to a file share for consumption elsewhere.

The Problem

BI Publisher is the tool of choice for bursting in OBIEE. Unfortunately it's also it's the only choice.

Whilst BI Publisher is great for building pixel perfect reports and bursting, it's not the most intuitive of tools and lacks a lot of the features and functionality you get with Answers and the typical dashboards we create in OBIEE. It also requires that you model your data outside of the RPD (even if the RPD is the source).

I've encountered several customers who had invested time and energy in creating great dashboards in OBIEE only to find out that they would need to try and recreate their existing dashboards in Publisher to achieve any form of bursting. The result is inconsistencies in look and feel, double the number of reports and double the amount of report maintenance. No Thanks.

A quick Google shows that many people are in the same position so is there an alternative which will let you burst your existing dashboards?

The Solution

I'll take inspiration from watching Nigella and in true festive style I'm going to give you the yuletide recipe on how to burst OBIEE dashboards using OBIEE agents.

First of all, the ingredients:

  • One dashboard, available in all good OBIEE shops.
  • One Answers report which we'll use as our bursting definition.
  • Two agents.
  • One session variable.
  • One large slice of Saschinvoke.
  • A sprinkling of scripts (a pinch of jscript and your scripting language of choice).

Now, I want to say right from the beginning that this is not an elegant solution. It is however a very simple and effective way of bursting existing OBIEE dashboards with little or no changes to your existing dashboards. I'll let you decide if it's the golden goose or a mushy brussel sprout.

So, here is my dashboard:


It uses Cameron Lackpour's favourite database -  Sample Basic. This is an Essbase database but the data source is irrelevant.

My Region dashboard prompt top left filters all the components of the dashboard by region. The default region for each user is set by a session variable read from a table when the user logs in. All standard stuff.

Session Variable


When a user logs in a session variable called DEFAULT_REGION is set. This can then drive the default content in our dashboards.

Dashboard Prompt

  

Our dashboard prompt uses the variable to set the default region in our drop down.

 

User Defaults Table

 

This is the table which defines the default region for our users. I've created a specific user called "burster" which will be used during the bursting process.

 

Creating the Agent

Next we will create an agent to run the report and save the output to the file system. There isn't an option in the agent GUI to save to the file system so our agent will invoke some javascript to write the file to the file system. This process has been documented already in other blogs but I'll add it here for completeness. Here are the agent properties:

General Tab


It's important that we specify the correct user here. I've specified our "burster" user which we will use specifically for the bursting process.

 

Schedule Tab

 
It doesn't matter what we add here, we won't actually use the agent for scheduling.
 
 

Condition Tab

We're not using a condition in this example.

 

Delivery Content Tab


Here we just need to define the dashboard which we want to burst.

 

Recipients Tab

 

I've set the bi_admin user to receive the agent info.

 

Destination Tab

 

Again, we don't need to define anything here as our destination is the entire dashboard on the file system.

 

Actions Tab

Here we define the javascript we want to invoke as part of the agent. This will run the dashboard and then save the contents to the file system.

Select "Invoke Server Script":


Define which script we will invoke:

 


We define two parameters, the file type (PDF, Excel....) and the file name of the dashboard. In this example our dashboard will be saved to the file system in PDF format and will be called Sales_Dashboard.pdf.

I've saved the agent as Sales_Burst_Agent.

The script referenced in the agent contains the following:
/////////////////////////////////////////////////////////////
//
// save_to_file.js
// 
// Copies the result of an agent to the file system
// 
// Parameter(0) = Agent Result File Path
// Parameter(1) = Last Part of Output File Name (no path)
//
/////////////////////////////////////////////////////////////
var FSO = new ActiveXObject("Scripting.FileSystemObject");
var fileName = "E:\\OBIEE_BURSTING\\Agent_Temp\\" + Parameter(1);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);


The script above is almost a carbon copy of the simple example which can be found in the OBIEE documentation.

Folder Structure

I've highlighted the location of where our report will be written to.

  • The Agent_Output folder will be the final location for our bursted reports.
  • The Agent_Scripts folder contains the scripts which will drive the bursting.
  • The Agent_Temp folder is place where the reports will temporarily reside before being modified and copied into the Agent_Output folder.


Bug in OBIEE 12c or my dodgy install?

If you're using OBIEE 11g you should be able to run the agent now and see the PDF output in the Agent_Temp folder. I'm using version 12c and got the following error when I ran the agent:

[nQSError: 66006] Failed to make Oracle BI Script Engine library available in the script.

I had to register the nqActiveXHost64.dll library manually for the agent server script to work:


Once I had registered the nqActiveXHost64.dll my agent ran successfully:


I'm not sure if it's just my install that has this issue or whether it's a bug but at least this fixes it.

The Result

Here we can see my dashboard has been successfully written to our Agent_Temp folder: 


And here it is in PDF format: 

 

 

OK, that's enough for now. Come back for part two where we shall define our bursting definition and burst out this report.


Thanks for reading!