The Return of the Burst

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

Author: Guillaume Slee, December 2015

This bursting series has definitely 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.  
 
If you recall 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_.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.