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.
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?
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:
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.
Our dashboard prompt uses the variable to set the default region in our drop down.
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.
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.
We're not using a condition in this example.
Here we just need to define the dashboard which we want to burst.
I've set the bi_admin user to receive the agent info.
Again, we don't need to define anything here as our destination is the entire dashboard on 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:
// 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.
I've highlighted the location of where our report will be written to.
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:
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.
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!