Automated OBIEE report output extract into file system

In many places this might be a common requirement to download/extract the OBIEE output into a file system automatically. Manually it can be done by clicking on ‘Export’ button on the report page. Through IBOT/Agent also the output file can be shared via email.

OBIEE doesn’t provide any out of the box functionality to extract the report content into file system. But this can be done using Action framework (in 11g) which has a direct integration with Java. Any process that can be called via Java can also be invoked from OBIEE11g.

 

This section will cover how to extract the OBIEE report output into a shared folder in .xls/.csv/.pdf etc. format using a Java method (EJB).

Implementation:-

 

Create one .ear file (e.g. OBIEEContentExportApp.ear) and place it under <MIDDLEWAREHOME>.

Now to deploy –

n  Open weblogic console and go to deployments and then Lock & Edit

n  Click ‘Install’ from Summary of deployments tab

n  Select path e.g. <MIDDLEWAREHOME>\OBIEEContentExportApp.ear and click Next

n  Select ‘Install this deployments as an application’ option and then Next

n  Select bi_server1 as target  and click Next

n  Leave the default settings and click Next

n  Choose ‘No, I will review the configuration later’ option and click Finish

n  Click Activate changes

n  Select the application and then Start

Update “ActionFrameworkConfig.xml” file to enable Java Method calls from OBIEE11g.

Navigate to “<MiddlewareHome\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication\ActionFrameworkConfig.xml” and update the .xml file as described below:

< 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 an Action</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>

< accounts>
< account>
< name>WLSJNDI</name>
< description>Account used to access WLS JNDI.</description>
< adminonly>false</adminonly>
< credentialkey>JNDIUser</credentialkey>
< /account>
< /accounts>

Login to OBIEE Enterprise Manager à Goto Weblogic Domain à Credentials à Create Map for oracle.bi.actions.

Create a Credential Key for JNDIUser using Weblogic username/password.

Restart all OBIEE services.

Configuration & Testing:-

n  Login to OBIEE 11g & Create an Agent

n  Select the Content as ‘Analysis or Dashboard Page’ in Delivery Content tab

n  Select the Recipient

n  Select User Destination as “Home Page and Dashboard”

n  Invoke the Java Method from Action tab

n  Enter the filename with extension and path & choose the values as delivery content

n  Schedule the Agent or run it; it will generate the output file into predefined location

OBIEE12c Release – All New Features introduced

The long waited OBIEE12c is now officially released at Oracle Open World. The key feature of this release is to improve the agility and enabling self-service for end users. Compare to previous OBIEE10g & OBIEE11g, this 12c release enhanced with clean look n feel features and introduced Visual Analyser (VA) that brings Tableau style reporting capability to BI users. This release also enables user to import spreadsheets (currently supported on this format) of their own and create analysis without having to modify the Oracle Repository metadata. Let’s go through the few new features introduced in OBIEE12c release.

New features introduced in Metadata Repository:-

 

Several command line utilities have been added like:

Upload Repository Command: to upload the repository to Oracle BI Server

Syntax: uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Download Repository Command: to download the repository used by the service instance.

Syntax: downloadrpd -O RPDname [-W RPDpwd] -SI service_instance -U cred_username [-P cred_password] [-S hostname] [-N port_number] [-SSL] [-H]

List Connection Pool Command: to create a list of connection pools in JSON format for a specific service instance: to upload a modified JSON file containing updated connection pool values to a specific server instance

Syntax: listConnectionpool -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-V <true/false>] [-O <outputFile.json>] [-SSL] [-H]

 

List Repository Variables Command: to create a list of repository variables in JSON format for a specific service instance

Syntax:listrpdvariables -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-V <comma or new line separated FILE containing selected variables names>] [-O <outputFile.json>] [-SSL] [-H]

Update Connection Pool Command:  to upload a modified JSON file containing updated connection pool values to a specific server instance

Syntax:updateConnectionpool -C <connectionpoolList.json> -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

 

Update Repository Variables Command: to upload a JSON input file or a modified JSON file containing variable information to a specific server instance

Syntax:updaterpdvariables -C <rpdVariablesList.json> -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Rename Application Role Command: to rename application roles

Syntax:renameapproles -T <inputfile.json> [-L <plugin list>] -SI <service instance> -U <cred username> [-P <cred password>] [-S <hostname>] [-N <port number>] [-SSL] [-H]

 

Rename Users Command:  upload a JSON file containing a list of application roles that you want to delete from a specific server instance

Syntax:renameusers -T < usernames.json> [-L <plugin list>] -SI <service instance> -U <cred username> [-P <cred password>] [-S <hostname>] [-N <port number>] [-SSL] [-H]

Delete Application Role Command: to delete application roles

Syntax:deleteapproles -T <inputfile.json> [-L <plugin list>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Delete Users Command: to upload a JSON file containing a list of users that you want to delete from a specific server instance

Syntax:deleteusers -T <usernames.json> [-L <plugin list>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Logical Level Sequence Numbers for Time Dimensions: 

The Sequence Numbers tab has been added to the Logical Level dialog. The new tab allows you to add absolute or relative sequence numbers to time dimensions.

New features introduced in BI Presentation Server:- 

Graphs Enhancements:  Ability to sort, drill graph views by using a context menu (right-click).

Enhancements to Analysis: specify whether to multiply data by a 100 to display it as a percentage when setting the properties of a column a long overdue feature 

Views: Introduction of a new type of view (heat matrix), ability to sort graph, heat matrix, pivot table, table, tree map, and trellis views by using a context menu 

Enhancement to Scorecards: In this release, you can style and customize KPI Watchlists and every watchlist that appears in a scorecard. You can set custom styles for each watchlist and rename and change the order and visibility of the watchlist columns 

Enhancements to Dashboards: In this release, you can set advanced page properties to specify incoming navigation parameters you want to set on the current dashboard page. This enables you to control how parameters associated with the navigation actions are applied.

Security Feature Enhancements: 

User GUIDs Removed: to simplify user administration, GUIDs have been replaced with user names. 

Database Security Store: Security Store (Policy and Credential Stores) is configured in a relational database rather than in a file adding robustness; this file was prone to corruption. 

Easier SSL Configuration: End to end SSL configuration has been simplified. 

Migrating Catalog Groups to Application Roles: Introduction of a new process enables you to migrate Catalog groups to application roles. 

BISystemUser and BISystem Removed: No need to have BISystemUser in LDAP. The system user concept is now deemed “virtual” and is represented by the credential oracle.bi.system/system.user, for which the values are securely randomly generated by the Configuration Assistant.

New features in Administration: 

OBIEE12c Installation: Much faster and straight forward. And mostly, the configurations & folders directories are simple and clean. 

OPMN: All services including OBI System & Java services are managed by WebLogic, no more OPMN concepts in 12C. 

Migrating from OBIEE 11g to 12c: We can migrate the Oracle BI 11g metadata to 12c is a two step process, and is carried out by using the BI Migration Script (migration-tool.sh).

New Features in OBIEE 11.1.1.9.0 release

New Features in OBIEE 11.1.1.9.0 release:

The new release expanded to support Oracle 12c database. Some more new features have been introduced in latest OBIEE 11.1.1.9.0 release:-

Analysis Enhancement:

1.     You can initiate a search within the subject area displayed in the subject area pane; the option is “Subject Areas Pane”.

2.     You can save columns to Presentation Catalog and reuse the same for other analysis

3.     Full text search option is now available in this version

Export Functionality:

1.     Export a column is basically an image to Excel. The extract blank column used to appear in previous version will no longer appear in this version

2.     Export menu no longer includes the Excel 2003 and PowerPoint 2003 options

3.     It is possible to set a limit to the number of rows downloading as CSV format

4.     The format component in Agent editor – delivery content tab now also includes Excel 2007+ value

New RPD checkout option:

1.     Whole RPD checkout option is now available to checkout the entire repository rather than using projects for multiuser development.

New Option in Administration Tool:

1.     Display Translation Key in presentation tree and Edit Presentation names options are now available in Administration Tool option for this release.

Change in Usage Tracking:

1.     Few new features have been introduced in Usage Tracking dashboard such as user session id, millisecond precision, initialization block execution and physical query hash.

New Features in OBIEE11g compared to OBIEE10g

There has been a lots of new feature introduced in OBIEE11g version with comparing to the earlier 10g version. Let’s go through some of the most important enhancements in OBIEE1g.

Administration tool:

The most significant difference between OBIEE 10g and 11g -

Deployment to Oracle WebLogic Server (WLS) and the integration of OBI with Oracle Fusion Middleware Control

The administration is done using:- 
 Oracle WebLogic Server Administration Console and Fusion Middleware Control  

The configuration settings are now managed by Fusion Middleware Control.

Security:

In previous version of Oracle Business Intelligence the users and groups could be defined within a repository file whereas in current 11g version users and groups are defined using an Oracle WebLogic Server authentication.

It is now possible for OBIEE to find out the "groups" belonging to a user directly from LDAP with the new 11g security model.

Oracle OLAP Integration:

OBIEE 11g supports Oracle OLAP as a data source. If you need to optimise the underlying Data Warehouse then oracle OLAP is a definite option for OBIEE.

Repository:

11g repositories are now stored in a compressed format to reduce the storage. As a result, size of RPD file opened and saved in this release is significantly smaller than the size of RPD files from previous releases.

Map view:

11g provides new configuration settings for table, pivot table, graph, and gauge views. The release also introduces a new type called map view.

Generate and Apply XML Patch Files:

It is now possible to generate an XML patch file that contains only the changes made to the RPD. This patch can be then applied to the old version of the repository to create the new version.

You can also use the Oracle BI Server XML utilities to create a generic, XML-based representation of the OBI repository metadata.

Multiuser Development:

 1. MUD projects now explicitly include subject areas (Presentation Catalog). Object security
 permissions no longer affect which presentation objects are included in a checked-out repository.

 2. Set options in the MUD options file to automatically check consistency and equalize during
 merges.

  3. Improved Merge Repository Wizard integration enables a single step input for RPD, no need to

 select repositories one by one and wait for them to load.

  4. Two-way merge feature (full merge without a common parent) enables easier consolidation of
  separate RPD files.

     

Master Detail Linking:

In addition to drill-down and navigation features a new Master-Detail Linking capability has been
introduced which will allow users to click on one table or graph or map to automatically apply filters
to other views within the same Analysis.

    

BI Publisher:

It is now possible with BI Publisher 11g to upload your own local MS Excel files and use them as a
data source for your BI Publisher reports.

Presentation Variables:

In OBIEE11g version it is now possible to assign multiple values to a Presentation Variable.

Metadata Import:

   

If you right click on a Connection Pool in physical layer -- there is a new Import Metadata option  
which is much better than the File à Import menu option when adding further tables because all the
new metadata will get imported directly into the existing Physical Database.

  

This new Import Metadata option will perform as an incremental update of existing tables and
columns. So if a new column is created into database, the tables in the RPD will be incrementally
updated.

 

Terminology Changes:

In 11g release, few terminology has been changed:

iBots have been renamed to agents

Reports have been renamed to analyses

Presentation columns have been renamed to attribute columns

Charts have been renamed to graphs

Archive/Unarchive:

It is now possible archive/unarchive BI Presentation Catalog directly within the dashboard, no need to use the Catalog Manager utility.

Dashboard Prompts:

New dashboard prompt called Variable Prompt will allow to specify custom list of values for the dashboard prompt.

Multiple Subject Areas:

In OBIEE11g Answers can now be sourced from multiple Subject Areas. This means no longer need to cram lots of content into a single Subject Area.

Repository Password:

In 11g release RPD is now have a repository password which encrypts the contents of RPD using a strong encryption algorithm to ensure that repository metadata is secure, including data source credentials in connection pool.

Note: Blank repository password is not allowed in 11g release which was allowed in earlier release (10g).

XMLA Objects:

Drag and drop XMLA objects into the logical layer to automatically create a consistent business model.

Search Capability:

This release provides a mechanism for searching objects in the Oracle BI Presentation Catalog that is similar to a full-text search.

Actions:

Actions will help to navigate to related content or that invoke operations, functions, or processes in external systems.

Conditions:

Conditions return a single Boolean value that is based on the evaluation of an analysis or of a key performance indicator (KPI).