Tuesday 15 December 2015

Bursting using OBIEE Agents: Part 1

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 2 where we shall define our bursting definition and burst out this report.

Thanks for reading!

9 comments:

  1.  Guillaume, I can't wait to read the next part of this write up. I'm interested to see what you came up with! Been frustrated by this puzzle in the past myself. Looking forward to part 2!

    Jeremy

    ReplyDelete
  2. Hi Jeremy, thanks for your support! I'll post part 2 next week.

    Guillaume

    ReplyDelete
  3. I was excited to try out this solution...until I read this in the documentation:

    "Scripting for agents and scripts defined by Oracle BI Scheduler Job Manager are supported only under Windows platforms and are not supported under UNIX."

    What a shame! Are you available of any alternatives for U/Linux deployments of OBIEE?

    ReplyDelete
    Replies
    1. Curses! I naively assumed that jscript would also work on Linux, I'll update the blog to say this is Windows specific. I think one solution could be to invoke a java method using an agent and apply the same scripting logic. There's a nice blog here: http://prasadmadhasi.com/2013/12/20/archiveexport-obiee-11g-content-to-shared-drive-by-creating-agents-and-invoking-java-method/ . However, I would check any licensing implications of deploying a custom web application on your OBIEE weblogic instance.

      Thanks,
      Guilaume

      Delete
  4. Hi Guillaume, I'm facing the same problem with this unregistered library! Looks like a bug by Oracle.

    ReplyDelete
    Replies
    1. Hi Dirk, at least it confirms it wasn't just my install! Thanks for posting.

      Delete
  5. The one and only documentation I found for 12c for this??!! You saved me, you rock. Thank you!

    ReplyDelete
    Replies
    1. Hahaa, many thanks for the feedback!

      Delete
    2. is it possible to burst more than one Dasboards/Analysis/Report at a time using agent concepts?

      Delete