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 ProblemBI 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 SolutionI'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.
When a user logs in a session variable called DEFAULT_REGION is set. This can then drive the default content in our dashboards.
User Defaults Table:
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.
Delivery Content Tab:
Here we just need to define the dashboard which we want to burst.
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:
// 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 ResultHere 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!