Monday, October 25, 2010

Pushing OBIEE Reports or Dashboards to an FTP Server

Pushing reports with Oracle BI Publisher to an FTP server is built into its bursting capability. However, pushing OBIEE Answers reports to an FTP server requires a few customizations. One approach for accomplishing this functionality in OBIEE Answers is to create two iBots: The first iBot will save the report file to local disk and the second iBot will push the file to the FTP server.

Here are the step-by-step instructions for how to accomplish this task.

1) Create the following Java script file in the {OracleBI}\server\Scripts\Common folder. For this demonstration's sake, let’s call this file Testing.js.

Content for Testing.js:
var fileName
var filesysobj = new ActiveXObject(['Scripting.FileSystemObject']);
fileName = ['D:\\public\\data\\OBI\\Reports\\'] + Parameter(1) + ['.PDF'];
var fooFile = filesysobj.CopyFile(Parameter(0), fileName, true);
This script expects the filename as in input parameter (Parameter(1)). In this example, the script adds the extension ’.PDF’ and writes the file to D:\public\data\OBI\Reports\. This can be customized to meet your needs.

2) Now we have to create an iBot that executes the Testing.js script. In order to do that, go to Delivers and create a new iBot. Select an existing Answers report or a dashboard page and specify the delivery format such as HTML, PDF or CSV.



Now, click on the Advanced tab. In the Filename textbox, enter the name of the script to execute (Testing.js) and select Java Script as the file type. Under Results, choose “Pass delivery content to script”. Under Other Parameters, enter the filename of the report output file. This value will be passed into the Parameter (1) in the Testing.js script.


3) The iBot in step 2 wrote the report file to local disk. Before we can create the iBot that pushes the file from local disk to an FTP server we first have to create several files in the {OracleBI}\server\Scripts\Common folder:


The first file (ftp_mht.js) is a Java Script that executes a Windows batch file ftp_mht.cmd.


ftp_mht.js:

var wshShell = new ActiveXObject("WScript.Shell");
var sdsdsds =
"D:\\Public\\server\\apps\\OracleBI\\server\\Scripts\\Common\\ftp_mht.cmd";
wshShell.Run(sdsdsds, 0, true);

The Windows batch file ftp_mht.cmd executes the FTP batch command. In our example, the control file ftp_mht.txt provides the input parameters for the FTP command as outlined below.
ftp_mht.cmd:

ftp -n -i -s:C:\OracleBI\server\Scripts\Common\ftp_mht.txt

ftp_mht.txt:
open {Hostname}
user {username} {password}
cd {target_directory_on_FTP_server}
binary
mput C:\TEMP\*.PDF
bye
4) Now we can create an iBot to execute the ftp_mht.js script. Since the script does not expect any input parameters, we will have to select the “Pass no results to script” option in the Advanced tab.


5) When scheduling the FTP delivery of this particular OBIEE report or dashboard, these two iBots will have to be chained.

Please drop us a comment if you have any questions!

4 comments:

  1. Can also be done with one IBot ;-)

    As Srinivas said, thanks for sharing.

    Cheers
    Nico

    ReplyDelete
  2. Thanks for document it's helpful,i have only one question how we need to put server name and username and password into ftp_mht.txt file .Could you please give an example by given sample hostname,username and password,i gave details like given below but its not taking that >could you please tell me where i am getting wrong

    open abcd-prod.com
    user {abcd} {abcd}
    cd {c:\IBOT}
    binary
    mput c:\IBOT\export.xls
    bye

    ReplyDelete
  3. Can this be applied/adapted for a linux bi server?

    ReplyDelete