Thursday, August 2, 2012

FDM - Interface to load data into Essbase

This article describes the interface development to load data into Essbase using FDM.

Technologies used in this article are
1) ORACLE database 11g
2) Hyperion Foundation Services 11.1.2.2
3) Hyperion Essbase 11.1.2.2
4) Hyperion Essbase Administration Services 11.1.2.2
5) Hyperion FDM 11.1.2.2
6) Essbase Excel Add-in 11.1.2.2

Preparation: Done the below preparatory work before proceeding with interface development.
1) Created a schema in Oracle Database as required to create a FDM application.
2) Modified the Sample/Basic application as FPLN/PNL with three additional dimensions Year, Version and Amt_Type. Renamed the Measures as Account.


Introduction: Spent a couple of hours before coming up with a sequence that keeps us in track of the interface development. After that every thing went cool.

The following steps outline the process for building a Hyperion FDM Interface to load data into an Essbase application:
Step 1: Create a new Hyperion FDM application
Step 2: Integrate the new application with the target system
Step 3: Populate the Hyperion FDM control tables
Step 4: Create the Hyperion FDM locations
Step 5: Import maps for each location
Step 6: Build import formats
Step 7: Test data loading
Step 8: Validate


clip_image002[4]
Flow Chart – To build an FDM interface for data loading.

Step 1: Create a new Hyperion FDM application

First step is to create a new FDM application.
Log onto Hyperion FDM using the <new application> option.
clip_image004

Enter the below details.
Application Name: ESSINT
Description: Essbase Integration
UNC path: \\WIN-OZCO68Q3T50\FDMData\
App Group: FDM
clip_image006

Enter the Database details:
OLEDB Provider: ORAOLEDB.ORACLE
Service: HYP001
Username: FDMESS
Password: xxxxxxxx

clip_image008

clip_image010

Step 2: Integrate the new application with the target system


Open the workbench and Login to the newly created application ESSINT
clip_image002

Import the Essbase Adaptor


clip_image004[4]

Browse to find the file ES11X-G4-J.xml and click Open.
clip_image006[4]
clip_image008[4]

Register Adaptor
clip_image010[4]

Browse to find the file named upsES11XG4J.exe and click Open.
clip_image012

Right Click on the Adaptor and Select Configure.
In the Machine Profiles Tab - Enter the Source and Target Machine details.
Here it is : WIN-OZCO68Q3T50
In the Hyperion Essbase Integration Tab - Enter as below
Essbase Application Name: FPLN
Essbase DB Name: PNL
Logon Method: Select 2-Global
Username: hypadmin
Password:xxxxxxxx
clip_image014

In the Dimensions Tab - Make the required dimensions active.

Step 3: Populate the Hyperion FDM control tables:

Control tables are used to supply the values displayed in system options. These tables allow the system administrator to control the system options and POV values that users can select.
Open the Metadata->Control Tables.
Add Periods. This will supply the Period and Year to the Target Essbase database.
The Period control table allows for the definition of the valid fiscal periods available to Hyperion FDM. The values in this table provide the list of possible periods that can be selected from the Period POV link. Changing the Hyperion FDM period will change where data is loaded in the target system.

clip_image016

Add Categories
The Categories Table contains the definition of data categories. These categories represent “buckets” (Scenarios) in which data can be loaded.

clip_image018

The values in this table provide the list of possible categories that can be selected from the Category POV link. Each Hyperion FDM category is associated with a target system’s scenario. Changing the Hyperion FDM category will change where data is loaded in the target system.

Verify the Currency Codes
The Foreign Currency Table contains a list of currency codes. Currency codes are assigned to locations. The currency code will be displayed on reports. This code does not impact any calculations, and is used for notation purposes only.

clip_image020

Step 4: Create the Hyperion FDM locations

Hyperion FDM Location Types
Hyperion FDM maintains two types of locations; Data Load and Controls Review.
Data Load — location that accepts data from a source system and loads data via mapping rules to a target system. A data load location can also be assigned financial controls. Data load locations can only be children in the controls structure, they cannot be parents.
Controls Review — location assigned only to financial controls. No data is loaded from a controls review location. Controls review locations are typically parents in the controls structure. A controls review location can also be a child of another controls review location.
clip_image022

Data loading locations have maps and are assigned an import format. Optionally, each data loading location may be assigned a logic group and validation rules.

Step 5: Build/ Import maps
The purpose of a mapping table is to map the source dimension members to their corresponding target system dimension members. A map can be loaded for each dimension in the target application defined in Hyperion FDM.
Verify the Mappable Dimensions - Metadata-Dimensions.
Here dimensions enabled for mapping are - Account, Region, Product, Amt_Type and Version.

clip_image002[4]

Define the Maps from Activities->Maps
Set the Source to Target Mapping for Accounts as below.
clip_image004[6]

Set the Source to Target Mapping for Entity(Region) as below.
clip_image006[6]

Set the Source to Target Mapping for Product as below.
clip_image008[6]

Step 6: Build import formats

Each data load location must be assigned an import group that will be used when importing data files. Import groups instruct Hyperion FDM how to interpret data files and transform them into data that can be loaded into the Hyperion FDM data structure.
The top grid shows import groups, and the bottom grid is where the fields for a group are defined.
By creating an import format, Hyperion FDM is able to interpret the file layout. Once the import format is created, it is important that the source file layout be unchanged each time you load data. If the file format of the source file changes, the import format will need to be updated.

clip_image002[6]
clip_image004[8]

Assign the Import Format to the Location - Sample here.


clip_image006[8]

Step 7: Data loading

Having the mapping tables loaded and the import formats created has prepared Hyperion FDM to import the month balance file and load it into the target system. The following steps will be repeated every month by data loaders to load the source data into the target system

Import source file
clip_image002[8]

Validate
clip_image004[10]

Validation got failed as there is no maps build for Amt_Type and Version. Initially have assumed that no mapping required if the source and target has the same value. Mapping details are must for all mappable dimensions.

Build the like Mapping for Amt_Type dimension as * to Initial.
There are four mapping types for each dimension -- Explicit, Between, In, and Like.

clip_image006[10]

Build the like Mapping for Version dimension as * to Initial.

clip_image008[8]

Again Go to Workflow and Validate. This time it is Success. See the Gold Fish.

clip_image010[6]

Click on Export

Choose "0-Replace"

clip_image012[4]

Export is success. see the Gold Fish.
clip_image014[4]

Validate using Essbase Excel Add-In.
clip_image016[4]

Validation is Successful.

Special Notes:

1) Not convinced with the provided mapping mechanism in the case if target is same as source. that should be simple with an option like equal to choose. Probably I haven't got that to make it work simple.
2) Not satisfied with performance of data load compared to native Essbase tools such as EAS/ MaxL.
3) Relatively easy once we are with the correct sequence/ process flow.
4) Essbase Integration with Essbase Adaptor also much like how it works for HFM data integration. It should have been simple based on how essbase can receive data.

Will see how we can tap the abilities of FDM in a much better way some time after. Thanks.

1 comment:

  1. this has rekindled memories in my junior year of school great thoughts indeed mazes used to freak me out big time thanks for this memory teaser

    ReplyDelete