Oracle DRM – EPMA Integration using out of the box adapter

Posted on Leave a commentPosted in Master Data Management

Apart from classic integration of DRM with other Hyperion products, another approach is integrating DRM with Enterprise Performance Management Architect using out of the box adapter

The key points which needs o be configured for DRM – EPMA integration are

  • Configuring Data Relationship Management
  • Configuring Hyperion Foundation Services (Weblogic, Web Services Manager, Shared Services)
  • Deploying and configuring the DRM Web Service
  • Configuring EPM Architect for DRM integration

Once DRM is configured, load EPMA template (On the computer where Data Relationship Management is installed, navigate to C:\Oracle \Middleware\EPMSystem11R1\products\DataRelationshipManagement\server \app-templates) using migration utility.

The Performance Management Architect application template includes Data Relationship Management metadata objects for Financial Management, Planning, and Essbase application types. You can choose to load metadata objects for only a subset of these application types if necessary.

Pre-requisite settings required in DRM to integrate with EPMA

Setting up Version level properties

  • Select a version (Planning) and under EPMA property category , give application name

EPMA1

Setting up hierarchy level properties

  • Select Group By “Shared Dimension” in hierarchy section
  • Select a hierarchy and select Shared Dimension from EPMA property category and if there are no values (Account, Entity, generic) then right click shared Dimension and edit values

 

DRM – External Connections

Posted on Leave a commentPosted in Master Data Management

DRM External Connections:

A new feature for the 11.1.2 release in DRM is External Connections.

External Connections are used to access server file locations, FTP locations, and database tables.

We can create and apply default connections and we can apply connections individually. After We create a file connection, it can be referenced by any object that requires a file connection.

We have three built-in providers: Oracle, SQL Server, and OLEDB.

Data can be imported or exported by creating External Connections. We can use three instances to create External Connections in DRM.

  • Client File.
  • Server File.
  • Database

 

Server File

Ext1

For Connection Type, select Server File.

Enter the UNC path to the server file.UNC (Universal Naming Convention) is a standard for identifying servers, printers and other resources in a network, which originated in the UNIX community. A UNC path uses double slashes or backslashes to precede the name of the computer. (Example:-C: \Newfolder\Data).

In UNC path we define the Server path where we want to Export the file or From Where we want to import the file.

Click Test connection icon to test the server connection. Click OK.

 

Database

We need to configure the Database External Connections Tab. Below is the sample file

Ext2

External connections to relational databases can be used to import and Export data from database tables into Data Relationship Management. An import profile can source data from tables in a single database. Each section of an import profile can be mapped to a single database table. A query filter can be configured to import a subset of data from the source database tables.

Sample Database Table:

Ext3

IMPORTING DATA FROM DATABASE:

Sample Import Profile:

Ext4

About how to select Source to import data from Database into DRM.

Setting up import wizard to Importing Data From Database :

   Import files are organised into sections and columns. We are having five sections in import profile Version, Hierarchy, Node, Relationship and Hierarchy Node. We can create imports in the import wizard, which consist of source, style, columns, filters, target tabs. We can start the import wizard in the import task group.

For Importing the data from database we have to configure all these tabs in import wizard, In source we have to select device as Database and select the sections which we want to import. Configure columns tab by mapping each column in import profile in DRM with Database table Columns (the number and order of the columns must match). To import data from Database we have to configure an additional option that is Database options in columns tab. In target tab give version name you want to create and description.

Setting up Source tab: Set up a DRM import that loads the tables. We will need to set up an external connection to point to the tables. Once We have selected the external connection, We will see that the import tabs change, a little bit. Each source object will need to point to a table that associates to that section. set up the sections by pointing to the right source tables:

Sample Source tab:

Ext5png

 

Setting up a columns tab:

     Configure the columns according to the sections map each column in import profile in DRM with Database table Columns (the number and order of the columns must match).After setting up the appropriate properties for the import sections, We will notice that there is a new section in the Columns tab, called Database Options:

 Ext6

On this tab, you will be able to set up the property to source column relationships.

Ext7

 

 

Oracle Hyperion DRM – ESSBASE Integration

Posted on Leave a commentPosted 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