Bursting Using OBIEE Agents Part 3: The Return of the Burst

Happy New Year!

This bursting series has got legs...maybe I could follow the Star Wars model and do prequels, sequels and bursting franchises?

In the final part of the agent bursting series we will piece all of this together using a batch script.

Remember in Bursting using OBIEE Agents Part 1 our Sales dashboard was filtered by a default region session variable (defined in an SQL table)?

Here is the secret sauce: will use a batch script to modify the default region in the SQL table and then run the dashboard agent for each region.

Our script will do the following:

• Launch the busting definition agent and write all our bursting regions to Region_Definition.txt.
• Read the Region_Definition.txt file and for each line:
• Create a temporary SQL script to update Default_Region SQL column for our "burster" user.
• Run the dashboard agent as "burster" (so the dashboard is filtered on the Default_Region set in the previous step)
• Rename Sales_Dashboard.pdf to Sales_Dashboard_<REGION>.pdf and copy to E:\OBIEE_BURSTING\Agent_Output
• Delete the temporary Sales_Dashboard.pdf
• Delete the temporary SQL script
• Loop round to 2nd line of Region_Definition.txt and repeat.

Got that? Too messy for you? I did warn you that it wasn't elegant.

Here are the final ingredients:

• Batch scripting language of your choice.
• Oracle or SQL client in order to run sqlplus/sqlcmd from the command line.

In my example I am using Windows 2012 and SQL Server <gasp!> and my scripting language will use the windows command. Unlike me, I recommend that you step out of the 1990s and use a more up-to-date language!

The Batch Script

The first issue we have is that the SaSchinvoke.exe doesn't provide us with a flag to define a password so you need to echo the password and pipe the output to the SaSchinvoke.exe command. There are lots of blogs on this but Rittman Mead's was the earliest I could find so I'll quote it here. The link is relatively old now but still useful as you could obviously integrate this script to any 3rd party ETL/scheduler tool like ODI.

I'll break the script up for explanation purposes and then copy the full script at the end.

Run the Bursting Definition Agent to Create the Burst Definition File

Here is the first part of our script. At the top it sets a bunch of variables. It then runs our Region_Burst_Agent from the command line. Remember the agent writes our list of bursting regions to a text file.


@echo off
REM Set Bursting Variables

set SCRIPT_HOME=E:\OBIEE_BURSTING\Agent_Scripts
set BURST_DEFINITION=E:\OBIEE_BURSTING\Agent_Temp\Region_Definition.txt
set OBIEE_BATCH_HOME=E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\server\bin
set BURST_SOURCE=E:\OBIEE_BURSTING\Agent_Temp
set BURST_OUTPUT=E:\OBIEE_BURSTING\Agent_Output
echo ##################################################################
echo #### Create list of Regions to burst from                       ##
echo ##################################################################
echo Bursting started %date% %time% >> %SCRIPT_HOME%\bursting.log
REM Launch the Region_Burst_Definition agent to create the list of bursting
E:
cd %OBIEE_BATCH_HOME%
echo PASSWORD|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Region_Burst_Agent" -m epmdb:9511
timeout 5 /nobreak

Notice the timeout at the end of the script. I've added this as we need to wait for the bursting definition file to be written before we move to the next step. I advise you to add better logic here and poll for the file to be written before going to the next step.

Update our Default Region in SQL Table

Once our region definition file is created our batch will read the file and for each line it will update the default region in the SQL table for the "burster" user:

echo ##################################################################
echo #### Begin bursting. Dashboards are written to %BURST_OUTPUT%   ##
echo ##################################################################
REM Parse Region_Definition.txt file created by the Region_Burst_Definition agent.

REM Start For Loop
for /f "delims=" %%i in ('type %BURST_DEFINITION%') do (

REM Create temporary SQL statement to set default Region and execute
echo UPDATE OBIEE_Defaults SET Default_Region='%%i' WHERE User_Name='burster'; >> %SCRIPT_HOME%\update_region.sql
sqlcmd -S SERVER -d DATABASE -U USER -P PASSWORD -i "%SCRIPT_HOME%\update_region.sql"

Run the Dashboard Agent to Write Dashboard to PDF

With our default region set we can now call the Dashboard agent to write the dashboard to PDF. Notice again how I've put in a timeout to ensure that the dashboard is run and saved to PDF before we loop round to the next region in our Burst Definition file. I would recommend this polls for the file to be written before going to the next step.

REM Launch OBIEE Agent to create PDF
echo PASSWORD|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Sales_Burst_Agent" -m epmdb:9511
timeout 5 /nobreak

Rename the Dashboard with the Region Location. Copy to Output Folder

Here I rename the dashboard so it includes the location name. You can of course add a timestamp or anything else you want to with the name/location.

REM Rename file with Region name and move out BURST_OUTPUT folder
echo F|xcopy /Y %BURST_SOURCE%\Sales_Dashboard.pdf "%BURST_OUTPUT%\Sales_Dashboard_%%i.pdf"

REM Clean up temporary files
del /q %SCRIPT_HOME%\update_region.sql
del /q %BURST_SOURCE%\Sales_Dashboard.pdf

REM End For Loop
)

The last steps of this section clean up the temporary SQL script and temporary dashboard.pdf

Finally Delete the Region Definition File

Once all the busting has completed the last step is to delete the file that contains our bursting definition.

REM Delete Region list file
del %BURST_DEFINITION%
echo Bursting finished %date% %time% >> %SCRIPT_HOME%\bursting.log
echo ##################################################################
echo #### Bursting complete.                                         ##
echo ##################################################################

Complete Script

Here is the complete script. If you remove the commentary and variables there is only 14 lines of code.

@echo off
REM Set Bursting Variables

set SCRIPT_HOME=E:\OBIEE_BURSTING\Agent_Scripts
set BURST_DEFINITION=E:\OBIEE_BURSTING\Agent_Temp\Region_Definition.txt
set OBIEE_BATCH_HOME=E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\server\bin
set BURST_SOURCE=E:\OBIEE_BURSTING\Agent_Temp
set BURST_OUTPUT=E:\OBIEE_BURSTING\Agent_Output
echo ##################################################################
echo #### Create list of Regions to burst from                       ##
echo ##################################################################
echo Bursting started %date% %time% >> %SCRIPT_HOME%\bursting.log
REM Launch the Region_Burst_Definition agent to create the list of bursting
E:
cd %OBIEE_BATCH_HOME%
echo Hyp3r10n|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Region_Burst_Agent" -m epmdb:9511
timeout 5 /nobreak
echo ##################################################################
echo #### Begin bursting. Dashboards are written to %BURST_OUTPUT%   ##
echo ##################################################################
REM Parse Region_Definition.txt file created by the Region_Burst_Definition agent.

REM Start For Loop
for /f "delims=" %%i in ('type %BURST_DEFINITION%') do (

REM Create temporary SQL statement to set default Region and execute
echo UPDATE OBIEE_Defaults SET Default_Region='%%i' WHERE User_Name='burster'; >> %SCRIPT_HOME%\update_region.sql
sqlcmd -S SERVER -d DATATBASE -U USERNAME -P PASSWORD -i "%SCRIPT_HOME%\update_region.sql"

REM Launch OBIEE Agent to create PDF
echo PASSWORD|saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Sales_Burst_Agent" -m epmdb:9511
timeout 10 /nobreak

REM Rename file with Region name and move out BURST_OUTPUT folder
echo F|xcopy /Y %BURST_SOURCE%\Sales_Dashboard.pdf "%BURST_OUTPUT%\Sales_Dashboard_%%i.pdf"

REM Clean up temporary files
del /q %SCRIPT_HOME%\update_region.sql
del /q %BURST_SOURCE%\Sales_Dashboard.pdf

REM End For Loop
)
REM Delete Region list file
del %BURST_DEFINITION%
echo Bursting finished %date% %time% >> %SCRIPT_HOME%\bursting.log
echo ##################################################################
echo #### Bursting complete.                                         ##
echo ##################################################################

The Result

And here is the result:

Our Sales dashboard has been saved as PDF to our output location and burst across all the regions we defined in our Burst Definition report.

Performance

Don't expect to burst out 1000 reports per minute; there is a delay when you launch an agent via Sachinvoke and you need to wait for the dashboard to be written before moving onto the next one. However you could use multiple users to run the process; using 2 user accounts could half the time.

Remember, the object of this exercise is to burst our existing OBIEE dashboards, not to replace BI-Publisher.

Conclusion

Am I happy with this? Yes and no.

This is more complicated than it should be, it is not as fast as normal bursting methods and I have to hard-code passwords in the script (although arguably if someone is able to sniff around on your server you've got bigger issues than password protection). You can also have issues if agents start overlapping each other.

However, you can only work with the tools at your disposal. This is easy to implement and extremely flexible with the output. You can add emailing to the loop, you could upload to Sharepoint...the options are endless.

You can even use this javascript action script in an agent to call our batch script on-demand from the OBIEE browser:

/////////////////////////////////////////////////////////////
//
// launch_burst.js
// 
// Launches a batch script on the server
//
/////////////////////////////////////////////////////////////
var WshShell = new ActiveXObject("WScript.Shell");

WshShell.Run("E:\\OBIEE_BURSTING\\Agent_Scripts\\Agent_Burst.bat");


So there we have it. A more robust version of this script (error trapping, file polling etc) is currently running in a Production environment bursting up to a thousand reports daily and the customer is very happy with the result.

Please feel free to post opinions, both good and bad. Maybe this will spark an idea for you to come up with a better idea? Share and let us let us know!

Bursting Using OBIEE Agents Part 2: The Empire Bursts Back

Did you like the topical Star Wars reference? Have you seen the new film yet? Of course not, you've got bursting to do.

In my previous post Bursting Using OBIEE Agents Part1 we looked at how our dashboard was filtered by region using a session variable and we saw how an OBIEE agent can run the dashboard and save it to the file system. The format I chose was .PDF but there are other formats you can choose.

An analysis can be exported in all the above formats but a dashboard page is restricted to PDF, Excel and HTML.

In this post we shall:

• Create a new analysis which will define which regions we want to burst.
• Create a new agent which will save our regions as a list in a text file.
• Use the SaSchinvoke command line utility to run the agents from the command line.

So, to recap here is my dashboard:

Notice the regions in our prompt....I want to burst my report out across my regions.

Bursting Definition

We shall now create a new analysis which will list out the regions to drive the bursting.

I don't care about the formatting, I just want my analysis to list the regions:

These are the droids regions we're looking for. Just filter your analysis to match your desired burst output.

We shall now create an agent which will export the region definition to a text file. The bursting agent has exactly the same configuration as the dashboard agent we created in the previous post with the following exceptions:

Delivery Content

Here we define our bursting definition analysis:

Agent Action

Here we define the file format of the exported bursting definition (plain text):

Run the agent and check that it is exporting our bursting definition to our Agent_Temp folder:

and review the contents:

Yep, those are the regions I want to burst out.

Saschinvoke

Saschinvoke is the Oracle BI Scheduler command line job invocation tool. On Windows it is called saschinvoke.exe and on *NIX it is saschinvoke. It enables you to schedule a job via the command line. It is typically used to launch jobs outside of the OBIEE scheduler using a 3rd party scheduler or batch routine. Saschinvoke isn't anything new and it's been covered in other blogs but it is key to our process so I'll explain how it works.

We shall use sachinvoke to launch our agent via a command script.

The utility can be found in OBIEE_HOME\bi\bifoundation\server\bin
so in my case it is located in: E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\server\bin.

Here is a link to the command in the Oracle Documentation.

The syntax for the command is:

SASchInvoke -u <Admin Name>  (-j <job id> | -i <iBot path>)  [-m <machine name>[:<port>]]  [(-r <replace parameter filename> | -a <append parameter filename>)]

We will only need the following parameters to launch our agent:
-u: User name
-i: iBot path (the path to our agent)
-m: machine:port

The syntax is as follows:

saschinvoke -u bi_admin -i "/shared/Blog/Agent Burst/Region_Burst_Agent" -m SERVERNAME:9511

You will be prompted for the password for your BI administrator user:

I've highlighted the machine:port parameter because this isn't needed in 11g. Oracle haven't taken into account the change in port numbers in 12c and the saschinvoke utility will fail without the machine:port reference because it still defaults to the old 9705 port.

So now we can use the command line to launch an agent which will write our dashboard and bursting definition to the file system. This is an important step because we can use the power of scripting to read our busting definition file and burst the dashboard for each region.

In part 3, the final part of the series, we will use batch scripting to launch our agents and burst out the dashboard for each region in the burst definition file.

Have a great Christmas break!

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!