Data and Metadata Integration with Planning & Essbase
Using Oracle Data Integrator (ODI)
Trey Daniel
Financial Systems, Reporting, and Processes @ GameStop
February 19, 2015
About Me
B.S., Management Information Systems, Louisiana State University (LSU)
MBA, Louisiana State University (LSU)
Over 10 years working with Hyperion as both Consultant/Oracle Partner & Customer
PricewaterhouseCoopers, LLP (PwC), 2004 to 2010
Accenture, 2010 to 2014 (with 2 years as US Hyperion Data Integration Lead)
Financial Reporting, Systems, and Processes @ GameStop, 2014 to Present
Oracle Certified Implementation Specialist
HFM 11 (2012)
Essbase 11 (2013)
-2-
About GameStop
A global multichannel video game, consumer electronics and wireless services retailer with more
than 6,600 stores worldwide, GameStop makes the most popular technologies affordable and simple
Besides GameStop stores and online, the GameStop family also includes:
GameInformer (worlds leading print and digital video game publication)
Kongregate (browser-based game site)
BuyMyTronics (online consumer electronics trade-in platform)
Simply Mac (Apple authorized seller of all Apple products & certified warranty + repair services)
Spring Mobile (post-paid AT&T services and wireless products)
Cricket Wireless (AT&T-branded pre-paid wireless services, devices and accessories)
-3-
Hyperion Data & Metadata Integration Products
Over The Years: 2015:
HAL HAL
LedgerLink LedgerLink
DIM DIM
Enterprise Conversion Tables Enterprise Conversion Tables
Upstream / FDM Upstream / FDM
EAS EAS
EIS EIS
EAL EAL
Essbase Studio Essbase Studio (low market share vs. EAS)
ODI ODI
EPMA EPMA (primarily metadata)
ERPi ERPi
FDMEE FDMEE
Razza / DRM Razza / DRM (metadata only)
EA EA (data only)
-4-
About ODI
Oracles Fusion Middleware ELT (Extract Load Transform) application for integrating data
between almost any type of application (RDMBS, OLAP, Big Data, MS Office, Flat File, etc.)
Oracles approach to defining metadata/data transformation and integration processes through a
unified infrastructure & utilizing RDBMS engines
-5-
Key ODI Elements
Technology Project
Project Folder
Data Server
Physical Schemas Package
(Cubes)
Interfaces
Context
Knowledge Modules
Logical Schemas
-6-
Key ODI Elements (Continued)
ODI Security Profiles
Execution Logs
ODI Users
ODI Objects
Scenarios
-7-
Inside an Interface (Mapping)
Target Datastore
(Hyp. Planning)
Source Datastore
(SQL Database)
Data
Transformation
Field Filter
Data Mapping
(SQL)
-8-
Inside an Interface (Flow)
Source Datastore &
Data Staging Area Hyperion Integration Knowledge Module
Target Datastore
Knowledge
Module
Parameters
-9-
Configuring an Integration with Hyperion
1. Create a Data Server under Hyperion Planning or Hyperion Essbase Topology
2. Create a Physical and Logical Schema for the cube, assigned to a Context (Global is default)
3. Create a Model, using the Hyperion RKM to pull in the dimension and members as Datastores
4. Create an Interface using the Hyperion datastores as a Source or Target Datastore (or both)
5. Perform field and data transformation mappings in the Interface
6. Configure Hyperion IKM and LKM flows to import and export data where needed (Needs .rul/.rpt)
7. Execute the Interface to perform the integration
8. Create a Packing to layer in additional Interfaces and other actions
9. Create a Scenario to compile the package, making it available for scheduling & automation programs
10. Schedule nightly/weekly/etc. automated integrations with Windows, Tibco, Tidal, etc.
- 10 -
Knowledge Modules for Hyperion (per ODI 11.1.1.7)
Reverse Engineer (RKM) Pulls the Hyperion dimension and member structure into ODI for mapping
RKM Hyperion Essbase
RKM Hyperion Planning
Load (LKM) Loads data/metadata from Hyperion into staging table
LKM Hyperion Essbase DATA to SQL
LKM Hyperion Essbase METADATA to SQL
IKM SQL to Hyperion Planning [for data and metadata]
Integration (IKM) Loads data/metadata from staging area into Hyperion
IKM SQL to Hyperion Essbase (DATA)
IKM SQL to Hyperion Essbase (METADATA)
Also recommend importing LKM File to SQL and IKM SQL to File Append to test loading flat files to a
SQL staging area or exporting SQL staging data to a flat file, respectively, for testing purposes
- 11 -
Better Practices for Hyperion Integration
Test against a flat file on the server/laptop first to eliminate additional points of failure
Compare against metadata before data loads for error trapping
String together multiple Interfaces via Packages
Automate Packages where possible via Scenarios & pre-packaged ODI Windows .bat files
Set up email notifications on both Pass and Fail conditions
Customize where needed via Java and built-your-own Knowledge Modules
ODI-centric blogs for much more info:
http://www.oracle.com/technetwork/middleware/data-integrator/overview/index.html (ODI Homepage)
https://blogs.oracle.com/dataintegration (Oracles Official ODI Blog)
https://gurcanorhan.wordpress.com (Gurcan Orhan)
http://devepm.com/ (DEV EPM)
http://john-goodwin.blogspot.com/ (More To Life Than This)
- 12 -
ODI vs FDMEE
Discussion Point ODI FDMEE
Primary Users IT Finance
Out-of-the-box availability Minimal Heavy
Customization High with included tools Available with Jython
Primarily Supports Any technology Hyperion applications
Workflow and Automation Must be built Included & configurable
Product Maturity Mature Early
HFM Support Up to 11.1.2.3 (OOTB) Ongoing
Configuration Multiple, deep steps Drop boxes, forms
Reporting Capabilities None within app Multiple pre-built
Ongoing EPM Support Uncertain Part of EPM product family
- 13 -
ODI and Hyperion 11.1.2.4
Per Oracle Support Document 1969035.1 (Oracle Enterprise Performance Management and Oracle
Data Integrator Statement of Direction), released Feb 9, 2015:
Starting with EPM release 11.1.2.4, a new Planning Adapter will be added to the Application Adapters for Data
Integration for the ODI release 12c, but the HFM KM will not be available
Please note that the ODI KMs for stand-alone Essbase will still be available and the licensing has not been
changed
Users can use ODI to load HFM in release 11.1.2.4, but will need to develop custom ODI content and call the HFM
java APIs from ODI to load data and metadata
FDMEE uses a version of ODI that is installed and configured during the EPM installation process, and is not
impacted by the future certification status of the Knowledge Modules.
The KMs for EPM release 11.1.2.3 and ODI release 11.1.1.7 are not certified with EPM release 11.1.2.4.
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1969035.1
- 14 -
Appendix: Key ODI Elements (Definitions)
Master Repository: main database for all ODI foundational elements
Work Repository: main database for a specific ODI Project(s) elements
Data Server: Vendor-specific database or file for source or target data
Physical & Logical Schemas: Vendor-specific settings and addresses for the Data Server
Context: A grouping of Physical and Logical schemas
Model: Collection of datastores using a Reverse Knowledge Module to retrieve file/database layouts
Datastore: A specific file or table associated with a Model
Project: A collection of KMs, Interfaces, Packages, and Scenarios for an integration
Knowledge Module: Pre-written adapters/connectors to almost any data source/target
Interface: Field and data mappings between a Source and Target Datastore
Package: Collection of Interfaces and other actions used to automate the integration
Scenario: A compiled Package able to be called by scheduling programs
- 15 -
