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

 

Approver Summarizer Formatter in Service Now

Posted on Leave a commentPosted in ServiceNow

Approvel Summarizer with Multiple Sections.

What is Approvel Summarizer:-

The summary at the bottom of an approval form is called Approval Summarizer. It is created by the approval summarizer formatter.  The approval summarizer displays different information depending on what’s being approved (such as a change request or a service catalog request).  By default, the  approval summarizer form contains only one section.  We can customize this form to display multiple sections with different information using UI macros. The following information provides how to create a approval summarizer form with multiple sections.

An Approval form is invoked when the workflow triggers.  When approval  form invokes approver receives a mail  with ServiceNow URL, This URL displays Activities and Approval Summarizer.  Approval Summarizer is a summary of requested items. Approver can view only one section in approval summarizer , To view multiple sections in summary It is possible with Approval Form Design and Ui macros. In Approval Form design Create multiple views.

Creating Multiple Views in Approval Form Design

To create a new View, from the left navigation pane,

select Service Desk- > My Approvals ->Approval->Configure->Form Design.

  1. We can create different views for webforms as shown in the below screens.

 

 

Approver Summ

 

 

2. In Form Design select appropriate table from the list and select New from view list

 

sn2

 

 

sn3

 

3. In Create new view window give a view name and click ok. Created new view is display under View List. Select view and give a section name with fields and save the form.

 

sn4

 

Create multiple views with the same process and give different sections and fields for each view. Use these views in Ui macro Xml field which contains jelly Script

 

By Using these views create a Ui macro

Creating UI Macros:-

The Approver Summarizer takes the details from approver summarizer default UI macro. If Approver wants to see more information in the summary of requested items then we have to create a new UI macro.

 

sn5

 

 

Approval summarizers are stored in the Macro [sys_ui_macro] table.  To create a new macro, from the left navigation pane, select System UI > UI Macros.  Summarizers use a naming convention of approval_summarizer_ + ‘<table_name>.

 

 

sn6

 

Name: A unique and descriptive name for the macro. Name must be followed by table name.

Active: Select the check box to render the element as defined.  Clear the check box to disable the element without deleting the code.

Description: Describe the purpose of the macro and parameters passed to it.

XML: Jelly script that defines the macro.

Each summarizer is written in Jelly script, which is used by ServiceNow to define internal forms.  The script is stored in XML field at the bottom of the UI Macro form.  In the below Script used different views which Contains Different forms. Using this we can display number of items under Summary.

On clicking this Link we can see a part of the script which consists of different Views like My_View, Plan 1, and Plan 2 which are custom defined views for web forms.

 

sn7

 

Below Screen shows Approval Summarizer with multiple Sections.

 

sn8

Srujana Anugu

DRM Consultant

Steps to migrate EPMA Import Profiles from DEV2 region to CIT region using LCM

Posted on Leave a commentPosted in Informative

STEP1 : Exporting Import Profiles from Enterprise Performance Management Architecht

  • To export Import Profiles from EPMA DEV2 , login to Workspace.
  1. Select Navigate -> Administer -> Shared Services Console

 

1

 

2. Expand Application Groups and select Foundation -> EPM Architect

 

2

 

 

3. Under Browse, expand Dimension Access – > Import Profiles (if the import profiles are for shared dimensions). To export import profiles for a particular application then expand Application metadata and expand Application and select import profiles.

 

3

 

4.Select the import profiles we wish to migrate and click on Export which is found at the bottom right of the screen

 

4

 

5. Give the File system Folder name and click on Export.

 

5

 

6.We can see the exported file under File System

 

6

 

7. We can see the status of the export under Migration status report.

 

7

 

8. Once the status is complete, download the exported file from File System into local system

 

8

 

STEP2 : Importing Import Profiles Enterprise Performance Management Architecht using LCM

  • To import Import Profiles from local system, logon to EPMA Shared Services Console in CIT

1.Right click File System and Upload

 

9

 

2.Browse the file location where it was saved while downloading it from DEV2. Select the zip file location and click on Finish

 

10

3. After uploading, the file can be seen under File System. Select the file from File System and right click the file and select Import

 

 

11

 

4. It prompts you to proceed with import. Select OK

12

 

5. Check the status of the import in Migration Status Report

 

13

 

6.Once the status is complete, the profile can be seen under Artifact list under Import Profiles

 

14

 

7.The import profiles which are migrated can now be executed to load dimensions.

 

Soumya Chandanam

DRM Consultant

Automate Metadata load into ESSBASE cubes using Maxl scripts

Posted on Leave a commentPosted in Informative

 

What is MAXL:

MaxL is the multi-dimensional database definition language (DDL) for Essbase Server. Using MaxL, you can easily automate administrative and query operations on Essbase Server.

A MaxL script contains a login statement and a sequence of MaxL statements, each terminated by a semicolon. Using of MaxL Script Editor to execute a MaxL script, the login statement is optional. Most MaxL statements begin with a verb and consist of grammatical sequences of keywords and variables. MaxL Script Editor color-codes the elements of the MaxL syntax and provides an auto-complete feature that helps to build statements.

Sample MAXL:

maxl

Using MAXL Shell:

We can pass MaxL statements to Essbase Server using the MaxL Shell. The MaxL Shell command-line interface is installed with Administration Server in:EASPATH\server\bin\essmsh.exe (EASPATH/server/bin/essmsh on UNIX)

Where EASPATH is the directory to which Administration Services is installed.

Why MAXL:

After building essbase analytic model and importing the data, the next big thing is to keep essbase data refreshed with each coming day’s new data. And it might be necessary to add new dimension member to the outline sometimes (e.g. we might have to add today’s date to the date dimension before loading today’s sales data).

MAXL is such a script language that can use to automate essbase in daily maintenance job by using some batch script to do it automatically, instead of use the admin console every time to add new member or execute calculation on the database.

 

We can write MaxL scripts which are easy to customize and re-use. A MaxL script contains a login and a sequence of MaxL statements, each terminated by a semicolon.

MaxL statements begin with a verb, and consist of grammatical sequences of keywords and variables. A single MaxL statement looks similar to an English sentence; for example,

Create application Newsamp as Sample;

Maxl DDL statements:

2

Verbs in MAXL ddl are:

3

Automate essbase using these maxl statements:

The following is an example to import data using maxl script

________________________________________________________________________________

/*maxl example*/

Spool on to c:\output.txt;

Login admin password on localhost;

Import database ‘Basic’.’Sample’ dimensions from data_file ‘C:\essbase\LoadFile.Product.csv’ using local rules_file ‘C:\essbase\product.rul’ on error append to ‘C:\essbase\dataload.err’;

Logout;

Spool off;

Exit;

________________________________________________________________________________

In the above example spool on is used to Send output of MaxL statements to a file called output.txt, located in the pre-existing directory specified by a relative path.

And also we can direct errors to one file and output to another by placing the following lines in the script:

Spool stdout on to ‘output.txt’;

Spool stderr on to ‘error.txt’;

LOGIN is used to enter into the essbase without clicking on EAS.

Import data from text or spreadsheet data files, with or without a rules file.

LOGOUT s used to exit from essbase

The following is an example of a MaxL script, sent to Essbase via the MaxL Command Shell. This script creates a user, creates a filter, and then assigns the filter to the user. Note that all MaxL scripts must begin with a login to the Essbase system, which must be running.

________________________________________________________________________________

/* login admin identified by systempasswd on Esshost;

Create user Fiona identified by sunflower;

Create filter Sample.Basic.Diet read on ’@idesc(Diet)’;

Grant filter Sample.Basic.Diet to Fiona;

Logout;

Exit;  */

________________________________________________________________________________

Note: save the maxl file with a .msh extension

.Bat file to run maxl script automatically

________________________________________________________________________________

Essmshfilename with .msh extension (and) file path where the file exists

________________________________________________________________________________

AUTOMATING DRM EXPORTS AND LOADING INTO ESSBASE:

We can Automate DRM Exports using Batch Client

Following is an example for Batch Client Export for Market

maxl

If we run this VBScript Using Batch Client we can get Market.csv Export. We can import this file Directly into EAS using Maxl

5

 

This is a maxl script for Automate Market Dimension into EAS.

We can combine these two using a .bat file and run both at a time.

For Example

3

In the above examble first line is for DRM batch Client output and If Drm Export exists only then it calls Maxl Script If not exists it Writes file does not exist into out.txt log file.

 

 

Srujana Reddy

Oracle DRM Consultant

DataSprouts Technologies Pvt Ltd.

 

 

 

Oracle DRM – Classic HFM Integration Process

Posted on Leave a commentPosted in Informative

Oracle Hyperion Financial Management is a financial consolidation and reporting application built with advanced Web technology and designed to be used and maintained by the finance team. It provides financial managers the ability to rapidly consolidate and report financial results, meet global regulatory requirements, reduce the cost of compliance and deliver confidence in the numbers.

Oracle Data Relationship Management is a Master Data Management tool where master data is maintained, analyzed, and validated before moving throughout enterprise. It is the recommended tool of choice for organizing the financial views of financial chart of accounts, cost centers and legal entities that are then used to govern on-going financial management and consolidations for an enterprises.

Some main features of DRM maintaining Master Data than creating in HFM, DRM is compatible in integrating with all the Hyperion and Non-Hyperion Systems like HFM, ESSBASE, PLANNING, Planning and budgeting cloud service (PBCS) and has a roadmap to integrate to all upcoming Oracle Cloud EPM applications. DRM Integrates with both HFM Classic and HFM EPMA.

HFM1

STEP 1 : Initial Load of metadata into DRM:

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

STEP 2: Importing Dimensions using Import Profile.

After loading metadata objects through migration utility into DRM ,Create import profiles to import data into DRM. After import profiles create custom properties ,property categories, validations and node types which are used to export data into HFM.

HFM2

STEP 3:   Exporting Metadata from DRM.

DRM Export in the form of Hierarchy Export is done and then a book of all related export are created by combining all the dimensions containing individual exports into a single file.

HFM3

STEP 4: 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 arm operations in batch mode including: Actionscript, Import, Blender, Export(Individual Export and Book Export),Opening and closing versions.

HFM4

STEP 5: Importing Data into HFM Applicaiton.

Once the metadata is exported into a flat file or DB Tables from DRM next step is to import Metadata into HFM Application using Load Task.

Create an Application Profile in the Client of HFM and create an Application on the Workspace. Dimension are loaded in HFM Application using Load Metadata option in Load Task.

 

STEP6:

This Process of Loading Metadata in HFM application can be automated using Task Automator and then scheduling the task using schedulers in the Workspace.

 

Oracle DRM – Creating Derived Properties Using Javascript

Posted on Leave a commentPosted in Informative

Property define the characteristics of the Node. Property Type “Derived” allows you to build dynamic business logic into your properties. Derived properties can simplify application maintenance and reduce the overall storage of your application by reducing or eliminating the need to store (override) additional values.

When you select “Derived” as Property Type, a new drop down will appear with the available options “Formula” or “Script”. Prior to 11.1.2.3 release of DRM, using the legacy formulas was the only option.

Oracle introduced Dynamic Scripting in DRM 11.1.2.3. Dynamic scripting enables Data Relationship Management administrators to develop business logic for derived properties and validations using JavaScript. Dynamic scripts provide a more robust and better-performing alternative to formulas, using a standard scripting language. Scripts allow for better organization and less complexity of logic through the use of multiple statements, variables, and in-line comments. Dynamic scripts also provide support for advanced concepts such as looping and regular expressions. JavaScript offers significant performance improvement over the legacy formulas.

Jav1

Fig: 1. Sample JavaScript Which returns “Hello”.

Jav2

Fig: 2 Sample JavaScript to Add Two Strings

//JavaScript to add two strings

var myNodeDescr; //Declare variables
var myVersionDescr;

myVersionDescr=node.Version.PropValue(“Core.VersionDescr”); //Assigning variables
myNodeDescr=node.PropValue(“Core.Descr”);

//Declare Function
function add() {
return myNodeDescr + ” ” +myVersionDescr
}
return add();

Oracle DRM – Assigning validations through Action Script

Posted on Leave a commentPosted in Informative

Today we are going to see how we can assign validations through action scripts. Validations enable us to enforce business rules on versions, hierarchies, nodes, and properties. We can run validations in either real time or batch mode, or in both modes. Real-time validations are run at the time of modification and prevent changes from being saved if the action violates the rules being enforced. We explicitly run batch validations. Validations can also be run using Action Scripts

 Action Script

Action scripts enable us to process bulk sets of incremental changes in an automated fashion. Each record within a script represents a separate action to be performed and processed separately from other actions. We can group actions of different types in the same script. Action scripts are particularly useful when we need to perform the same set of actions for multiple versions, hierarchies, or nodes. We can load action scripts from flat files, generate them from transaction logs, or create them from node models. In the Script task group, we can load and run only one action script at a time.

 

Types

AssignVersionValidations Ver Validation List ResultDescr  
AssignHierValidations Ver Validation List ResultDescr  

 

Version validation using action script syntax

AssignVersionValidations Ver Validation List ResultDescr

 

Example:

AssignVersionValidations;Sob_Ess_Year;Essbase.MemberNameLength;Result

Selecting Validation

After creating validations, we can assign them to versions, hierarchies, domains, and nodes. Multiple validations can be assigned at the same time. We can assign using Action Scripts as well. Select the related validation which we want to perform on a version.

Load Action Script

In the Script task group, we load the flat file that contains the action scripts. After loading, edit rows that display warning symbols to ensure proper processing.

Let’s take an example for loading the script, below is the script

 

AssignVersionValidations;Sob_Ess_Year;Essbase.MemberNameLength;Result in DRM script.

Val1

Run the Script

When we run the script of assigning validation at version level by using action script validations are inherited by all hierarchies and nodes within the version.

Val2

Check Assign Validations for Version

 This is how we can check by clicking version then right click assign validations tab

Val4

 

 

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