Oracle Hyperion DRM – ESSBASE Integration

Posted on Posted in Master Data Management

Oracle ESSBASE is leading online analytical processing (OLAP) server for enterprise performance management (EPM) applications. Oracle ESSBASE supports forecasting, variance analysis, root cause identification, scenario planning and what-if modeling for both custom and packaged applications.

On other Hand, Oracle Data Relationship Management is a Master Data Management tool where master data is maintained, analyzed, and validated before moving throughout enterprise.

What is the need of integrating DRM with ESSBASE when dimensions can be created and maintained in ESSBASE server itself?  In current day enterprises, it becomes essential to manage master data across information silos that result from mergers and acquisitions, departmental initiatives, or legacy system proliferation. Data consistency, integrity , quality and accuracy suffer and no one trusts the information and insight that ensues. We can overcome this problem by maintaining master data in DRM and publish it to both transactional applications like PeopleSoft, Oracle EBS.. and Downstream applications like ESSBASE, Planning, HFM..

ESSBASE is one of the common applications DRM is integrated with. Metadata can be maintained at DRM end and pushed to ESSBASE cubes. DRM exports meta data in .txt format which is in turn accepted by ESSBASE. This whole process can be automated.

drm-essbase int

STEP 1: Initial Load of metadata into DRM:

When DRM is integrated with ESSBASE for the first time, along with metadata load we also need to create Metadata objects like ESSBASE specific properties, Property Categories, Node Types,Validations etc.. These metadata objects  can be  loaded into DRM using epma-app-template.xml through Data Migration Utility. Along with this we may also need to create certain custom properties as per business requirement.

STEP 2: Exporting Metadata from DRM:

DRM Export process can be automated using DRM-Batch Client.

DRM Batch Client is a command line utility that allows access to various DRM operations like Exports, Imports, Action Scripts… It can also be run using Configuration file.

Sample Export Profile

Essbase-Columns Export

Sample Book for ESSBASE Application

ESSBASE_Book

 

STEP 3: DRM EXPORT AUTOMATION

DRM Export process can be automated using DRM-Batch Client.

DRM Batch Client is a command line utility that allows access to various DRM operations like Exports, Imports, Action Scripts… It can also be run using Configuration file.

DRM-ESSBASE VB Script1

 

 

DRM-ESSBASE Batch Script

STEP 4: Importing Metadata into Classic ESSBASE Cube

 

Once the metadata is exported into a flat file or DB Tables from DRM next step is to import Metadata into ESSBASE cubes using rule files.

Dimension build rule files are used to map dimension members from a datasource to an ESSBASE outline. Dimension build rule files specify how, during dimension builds, members are processed.

Creating Dimension build rules file:

To create a rule file, you need to select dimension build fields from the View. Set the file delimiters for the data source and select a build method . If necessary, set the properties of members and dimensions you are building. Set field type information and  validate the rules file and save the rules file.

When you execute a dimension build, ESSBASE reads the member in the data source, change them based on the rules in the rules file and load the changed member into outline.

You can automate Metadata load into ESSBASE cubes using Maxl scripts. Below is a sample maxl script to import metadata from flat file

MaxlScript

You can run the maxl script either by creating a batch script or can be run directly using maxl editor.

 

 

Soumya Chanadanam

Oracle DRM Consultant

DataSprouts Technologies Pvt Ltd

Leave a Reply

Your email address will not be published. Required fields are marked *