OBIEE Reports/ Dashboards/Analysis Export

OBIEE Reports/ Dashboards/Analysis Export

OBIEE Reports/ Dashboards/Analysis Export to Unix/Windows/FTP

Procedure 1 using EjB:

Oracle Business Intelligence 11g, doesn’t provide any built-in method to allow Agent to automatically export/archive OBIEE11g content (Entire Dashboard(or) Dashboard Page(or) Analysis(or) Conditional Report of Agent(or) even Briefing Book) to file system, although this can be done by linking an action to the agent which invokes server scripts, like EJBs, VBs etc. which physically move the OBIEE11g content to the file system.

Action Framework of OBIEE 11g opens up direct integration with Java. So, any process that can be called via Java can be directly called from OBIEE 11g as well.

We will archive/export OBIEE content by Invoking a Java Method from Actions.

For Eg: Export OBIEE Dashboard page as pdf,excel,csv etc  and save it in any shared drive(C:\Middleware\Export)

Applies to:

This solution provided in this document is applicable on the below version of OBIEE11g.

Oracle Business Intelligence Suite Enterprise Edition – Version 11.1.1.7.0 and later

Information in this document applies to any platform.

Solution:

Invoke a Java Method (EJB)

This action type allows users to browse for target Java methods deployed in Enterprise Java Beans (EJBs) and then create an Agent à Action to invoke the selected Java method.

Steps Summary

• Deploy “OBIEEContentExportApp.ear” file into weblogic Managed Server (bi_server1)
• Update “ActionFrameworkConfig.xml” file to enable Java Method calls from OBIEE11g
• Create a credential map and key for oracle.bi.actions
• Create an Agent to Export/Archive OBIEE 11g content to FileSystem

1 Deploy “OBIEEContentExportApp.ear” file into weblogic Managed Server (bi_server1)

• Download the below “OBIEEContentExportApp.ear” file and place it under <MIDDLEWAREHOME>  (For Eg: C:\Middleware)

OBIEEContentExportApp.ear [Note once file loaded remove .txt from the end!]

(FYI: The above ear is developed by me using JDeveloper/ADF  .This ear is generic, reusable and no hardcoded report paths implemented in it. You can deploy and use it any OBIEE 11.1.1.7.0 + to export/archive OBIEE content to your shared/local file system)

• Open weblogic console(http://localhost:7001/console) and go to deployments as shown below.

• Click Lock & Edit from Change Center as shown below.
• Click “Install” from Summary of Deployments tab as shown below.

• Select Path: < Middleware>\OBIEEContentExportApp.ear (refer below screen for example path) and click Next.

• Select “Install this deployment as an application” radio button and click Next.

• Select bi_server1 as target and click Next.

• Leave the default settings in the below window and click Next.

• Select “No, I will review the configuration later.” radio button and click Finish as shown below.

• Click Activate Changes as shown below.
• You have to get the below message once Activate Changes successful.

• By default the deployed application will be in prepared state. Here select the application and start it as shown below.
• 

1.13 Deployed application should be in Active mode as shown below.

2. Update “ActionFrameworkConfig.xml” file to enable Java Method calls from OBIEE11g:
3. i)        We need to update the ActionFrameworkConfig.xml to enable Java Method calls from OBIEE. Navigate to “<MiddlewareHome\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication\ActionFrameworkConfig.xml” and update file as shown below.

Downloadactionframeworkconfig-xml [Note once file loaded remove .txt from the end!]

NB: make back of original actionalframeworkconfig.xml before overwriting.

(Refer the above file for updating your Action framework config file as below.Rename ActionFrameworkConfig.xml.txt to ActionFrameworkConfig.xml)

Registry:

<registries>

<registry>

<id>reg03</id>

<name>Sample EJBs</name>

<content-type>java</content-type>

<provider-class>oracle.bi.action.registry.java.EJBRegistry</provider-class>

<description>Custom Java classes which can be invoked as action targets</description>

<location>

<path/>

</location>

<custom-config>

<ejb-targets>

<appserver>

<context-factory>weblogic.jndi.WLInitialContextFactory</context-factory>

<jndi-url>t3://localhost:9704</jndi-url>

<server-name>localhost</server-name>

<account>WLSJNDI</account>

<ejb-exclude>mgmt</ejb-exclude>

<ejb-exclude>PopulationServiceBean</ejb-exclude>

</appserver>

<ejb-app>

<server>localhost</server>

<app-context>ActionSamplesEJB</app-context>

</ejb-app>

</ejb-targets>

</custom-config>

</registry>

</registries>

Account:

<accounts>

<account>

<name>WLSJNDI</name>

<description>Account used to access WLS JNDI.</description>

<adminonly>false</adminonly>

<credentialkey>JNDIUser</credentialkey>

</account>

</accounts>

 

3. Create a credential map and key for oracle.bi.actions:
   • Login to OBIEE enterprise manager -> goto Weblogic Domain -> Security -> Credentials
   • 
     • Create a new credential map named “oracle.bi.actions”
     • Create a new credential key for JNDIUser using your WebLogic username/pass:

Now, restart all OBIEE 11g services.

4. Create an Agent to Export/Archive OBIEE 11g content to FileSystem:

4.1  Login to OBIEE 11g (http://localhost:9704/analytics) and create an agent as shown below.

4.2      Navigate to Delivery Content tab and select Content as Dashboard page and select Dashboard page as shown below and click Ok.

4.3    Navigate to Recipients tab and select any “Role” as recipient . Here I selected BIAdministrator role for example. Refer the below screenshot.

4.4 Select User Destinations as “Home Page and Dashboard” as shown below.

4.5  Navigate to Actions tab and from Add New Action -> Invoke a Java Method as shown below.

4.6  From Select Java Method pop-up window, select “ArchiveOBIEEContent” method as shown below and Click OK.

4.7  You will get New Action Window as shown below.

4.8  Here in this example, I added the below parameters

Enter Location to Archive: C:\Middleware\Export\test.pdf

Content to Archive: Delivery Content

And click OK.

Note: When you select “Content to Archive: Delivery Content”, you may get the below warning. Ignore this and click OK.

 

4.9  Save the agent and Run it.

Now, you can see that your Delivery content : Overview dashboard page saved under given path as shown below!!!.

Observations:

• You can save/archive all the below formats .
• You can save even conditional analysis (the one we create condition on Agent as shown below)

Procedure 2 using VB Script

To accomplish this purpose, we can use a Server script which will allow us to take the generated file from OBIEE and send it to a FTP server.

Some of the solutions that I found in internet recommends generate 2 Agents:

1. To get the generated content from OBIEE and put in a different folder
2. Send the content from step 1 to a FTP server

The solution here uses just 1 Agent and can be applied also to send the content to a Network Folder.

Procedure

1.   Enter to OBIEE analytics
2.  Create a sample Analysis. For this example: Export_ftp_test is used which is just a basic analysis
3.  Create a new Agent.
4.  Fill the tabs: General, Schedule and Condition according your requirements.
5.  In the Delivery Content Tab, Select the Analysis created in the point 1. (Could be Dashboard Page, Entire Dashboard and briefing book as well)
6.  Select the Format that you want to use
7.  Go to the Actions tab, section “Agent Condition True or No Condition exists”, and select Invoke Server Script
8.  Select VB Script as a language and in the Script Path write the vb script is located.

vb script can be found here: export_report.vbs [Note once file loaded remove .txt from the end!] or export_report_new.vbs

This script requires also the batch file: ftp.bat which is located here. [Note once file loaded remove .txt from the end!]

Important:

Edit the vb script file to adjust the path where the result should be delivered.

Edit the file ftp.bat to adjust the access to the ftp server

9.  Using the “+” icon, add a new parameters like the last image.

Important:

Make sure that Value column is property configured with the pdf document icon, or the format that you selected in the “Delivery content” Tab.

10.  Press OK to close the window and save the agent.
11.  Test the agent using the icon in the toolbar.

 

Procedure 3 using java script

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 = [‘c:\\Middleware\\Export\\’] + 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 C:\Middleware\Export\. 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!

Hope this blog helped you..

Reference:

http://docs.oracle.com/cd/E23943_01/bi.1111/e16364/actions.htm

http://www.rittmanmead.com/2010/09/oracle-bi-ee-11g-action-framework-java-ejbs-and-pdf-watermarks/