Best Practices for Implementing Oracle Data Integrator (ODI) July 21, 2011

(1)

Oracle Data Integrator (ODI)

July 21, 2011

Lee Anne Spencer

Cheryl McCormick

Lee Anne Spencer

Founder & CEO

Global View Analytics

Cheryl McCormick

Chief Architect

(2)

Agenda

• Introduction

• Oracle Data Integrator

• ODI Components

• Best Practices

• Implementation / Migration Approach

• Summary / Q & A

Summary / Q & A

(3)

Global View Analytics

•

Certified Experts in EPM and BI

Recognized Industry Leader _p

 Dashboards and Analytics

 Planning and Forecasting

 Financial Reporting and Compliancep g p

 Enterprise Data Management

•

National and International Consultants

ACE M th d l ™

• ACE Methodology™

Assess -> Configure -> Empower

 Based on Agile Deployment Techniques

•

Personalized Client Success Plans

(4)

Enterprise Performance Management

• Global View Analytics supports a complete solution

Dashboards & Analytics

Financial Reporting

Planning & Forecasting

and Compliance

Planning & Forecasting

(5)

Oracle Data Integrator

(6)

• Formerly Sunopsis acquired by Oracle Oct 2006

Introduction to ODI

• Formerly Sunopsis - acquired by Oracle Oct. 2006

• Broad integration - connect anything to anything

• Complete data movement and transformation

• Real-time solution

• Supports SOA (Service Oriented Architecture)

T l f IT

• Tool for IT users

• Delivered with System 11

 Hyperion Knowledge Modules (KMs)yp g ( )

(7)

• Business users vs IT users

Factors to Consider

• Number of different source / target systems

• Volumes of data

M t d t t t t

• Meta data management strategy

• Financial auditing & compliance

• Breadth of application solutionsBreadth of application solutions

• Corporate technology standards

• Internal skill sets

• Current versions and release dates

(8)

1 Define/Create EPM application EPM S EPM

Loading Metadata & Data to EPM with ODI

1. Define/Create EPM application 2. Identify source EPM sources

3. Create models with ODI Designer by reverse engineering

4 Create ODI Interfaces which

EPM Sources EPM

Application

ODI Build and Load

4. Create ODI Interfaces which

maps/transforms from source to target within ODI for metadata and data load 5. Test ODI Interfaces

6. Create ODI Package - connect all

1 2 3

ODI

Create Models

g

interfaces to perform complete build 7. Create Scenario based on package

-compiled version

8. Invoke Scenario, using Metadata

4 5

Create Package Test Create interface

6

Navigator (web enabled tool), using event, command line, SOA, etc. 9. Monitor Scenario Execution with

Metadata Navigator or ODI Operator Monitor

7

Create Scenario Execute Scenario

(9)

ODI Components

(10)

O l H i A li ti Ad t

Oracle Hyperion

Oracle Hyperion Oracle Hyperion Oracle Hyperion Oracle Hyperion Oracle Hyperion

ODI with Hyperion

Hyperion Hyperion Planning Planning Hyperion Hyperion Financial Financial Management Management Hyperion Hyperion Essbase Essbase Metadata Discovery   

Oracle Hyperion Application Adapters

Planning Planning Planning API Financial Mgt Financial Mgt HFM API Essbase Essbase Essbase API y

& Model Creation    Extract Data _{Essbase KM}Use   Extracts Dimension

Members

Use

Essbase KM  

Oracle Data Integration Suite Oracle Data Integration Suite

Data Distribution & Delivery APIs

Loads Data   

Loads Dimension

Members   

Other Features Cube Refresh Consolidate Calculate

Metadata Lineage Bulk/Trickle Loading Changed Data Capture Master Data Data Quality & Profiling ODI Knowledge Module Framework

Bulk and Real-Time Data Processing Oracle EBS PeopleSoft SAP/R3 Other Sources Data Warehouse CDC Message Queues Information Assets

(11)

• RKM - Reverse Engineering Modules

Generate logical source / target tables for mapping of data

Knowledge Modules

 Generate logical source / target tables for mapping of data

 Available for HFM, Planning, Essbase

• IKM – Integration Knowledge Modules

 Used to integrate metadata / dataUsed to integrate metadata / data

 Available for HFM, Planning, Essbase

• LKM - Loading Knowledge Modules

 Used to load metadata / data

 Available for HFM & Essbase (can also be used for Planning)

• CKM – Check Knowledge Modules

 Check constraints in Source and Targets for violations

• JKM – Journaling Knowledge Modules

 Create a journal of data modifications (insert, update and delete) of the source databases to keep track of the changes

• SKM – Service Knowledge Modules

(12)

• HFM

Versions Supported

 Backward compatible to version 9.2.0.3

 Support >= Oracle Hyperion Financial Management 11.1.1.3

• Hyperion Planning

 Backward compatible to version 9.2.0.3

 Support >= Oracle Hyperion Planning 11.1.1.3pp yp g

• Essbase

 Backward compatible to version 7.1.6

 Support >=Oracle Hyperion Essbase 11.1.1.3

(13)

Primary ODI Components

• Topology Manager - Defines the working environment

• Designer Where the integration work happens

• Designer - Where the integration work happens

• Operator - Monitoring your integrations

(14)

Topology Manager

This area is CRITICAL. Adequate DESIGN TIME is Essential!

• Defines the ‘topology’ of the information systems

• Defines all Source and Target Objects

(15)

Designer

• Knowledge Modules (KMs) Added Here via IMPORT

• Design Workflow through a graphical workflow navigation interface

• Primary Activities in Designer

include: include:

 Developing ‘Models’ of

the data using the Reverse Engineering KM  Creating Projects  Developing Interfaces  Developing ‘Scenarios’  Executing integrations  Executing integrations

(16)

Operator

P id i ibilit i t th t b

• Provides visibility into the step by

step integrations during execution

• Status and error messages are g

clearly displayed

• Ability to run a Scenario from

Operator Operator

• View scheduling information

(17)

Security Manager

• Defines Security Policy related to

ODI Objects, Instances & Methods

C f G & G

• Creation of Generic & Non-Generic

Profiles

• ODI user security defined by the

fil & h i h i d

user profile & the rights assigned to objects and instances

• Assignment of authorization rights

b fil b

by profile or by user

(18)

Best Practices

(19)

• Read the documentation

Correct Use of Topology & Contexts

• Read the documentation

– In ODI, all developments, as well as executions, are performed on top of a Logical Architecture (Logical schemas, logical agent), that resolves in a given Context to a Physical Architecture

that resolves, in a given Context to a Physical Architecture

(real/physical source/targets data servers/schemas and ODI run-time agents). Contexts allow you to switch the execution of the artifacts from one environment (context) to another

artifacts from one environment (context) to another.

• When you think you understand - Read it again

(20)

Enforce Data Quality

• Data Quality is a basic requirement

– Garbage in / Garbage moved & transformed / Garbage out

• Use ODI Static Checks to enforce data quality on the Source

Data

• Use ODI Flow Checks to enforce data quality before data is

(21)

Context-Independent Design

• Typical mistake we all make – we used qualified object names

E l t i t t bl h t i i th h

• Example: staging.temp_table where staging is the schema

name = context –dependant design

• Consider using Substitution Methods [ODIRef API]. Ensures

the qualified object name is determined according to the Context you are using to generate your code.

(22)

U d t d th i t f h f th KM

Choosing the Right KM

• Understand the requirements for each of the KMs

– For example, some technology-specific LKMs are dependant on

loaders. Make sure the loader is installed and configured correctly before choosing this LKM

before choosing this LKM.

• To start use a more generic SQL KM

• Don’t over-engineer - Performance is at risk

– For example, can you do a simple INSERT vs an Incremental Load?

(23)

Utilize the Standard KMs

• Before customizing or writing KMs, take the time to read and

understand the various options available with the delivered KMs

KMs

• Customized KMs are not supported

• Customized KMs need to be manually maintained during

upgrades and possibly patches. upgrades and possibly patches.

(24)

Keep Out of the ODI Meta Data Repository

• Respect the ODI meta data repository

D ’t h k i t th b k d ODI it t bl Th i

• Don’t hack into the back-end ODI repository tables. There is

a lot of complexity, and it is easy to cause unintended results.

• Hacked ODI meta data repository table = NO SUPPORT

(25)

Implementation Approach /

(26)

Implementation Approach

1) Assess “integration” requirements

1) Assess integration requirements

2) If migrating, evaluate current HAL routines

3) Build ODI foundation

4) Design and develop ODI objects

5) Test & reconcile

Things to be aware of when migrating…

– This is not a one-to-one conversion processThis is not a one to one conversion process – ODI requires more IT involvement than HAL

(27)

1) Assess your Integration Requirements

• First Things First:

 Take a step back and define Integration Requirements (Not the ‘HOW’)

(Not the HOW )

 Source

 Target

 Data Cleansing

 Document

(28)

D t t HAL P j t / I t ti

2) If migrating, evaluate your current HAL routines

• Document current HAL Projects / Integrations

 Concentrate on ‘WHAT’ and not on ‘HOW’

- For example, if you need to concatenate a prefix,

d t th i t t h it i d i HAL

document the requirement not how it is done in HAL  The more detail the better

• Identify all required connections - Source and Target

• Identify ‘reusable’ HAL code

 Is there any vanilla SQL code in HAL that can be used in ODI?

• Identify ‘reusable’ RDBMS Objects

• Identify reusable RDBMS Objects

 Stored Procedures

(29)

3) Build the ODI Foundation

• Topology Manager

(This is Critical)

 Import your Physical & Logical Technologies

 Create your Physical & Logical Objects

 Create Physical & Logical AgentsCreate Physical & Logical Agents

 Define your Context

• Designer

 Import your Knowledge Modules (KMs)

 Develop your Models

 Reverse Engineer your Sources

 Reverse Engineer your Targets

(30)

4) Design and Develop ODI Objects

• Design integration work flow

• Identify ODI Objects to support work flow

• Select KMs & update the KM Options

• Determine how processes can be simplified

Determine how processes can be simplified

• Add Error Handling to work flow

• Add Email notifications to work flow

• Add automation

(31)

4) Design and Develop ODI Objects

• Packages - sequence of organized stepsg q g p

• Interfaces - a set of rules that define the loading of a Datastore or a temporary

target structure from one or more source Datastores

• Procedures - a reusable component that groups operations that do not fit in the

Interface framework. Can be encrypted.

• Variables - a variable’s value is stored in ODI & can be changed during

execution

S i bl t ti ll i t d h d

• Sequences - a variable automatically incremented when used

• User Functions - customized functions useable in Interfaces & Procedures

• Knowledge Modules - define methods related to a specific technology. can be

encrypted encrypted

• Markers - Flags (markers) are used to group and/or identify methodology

• Scenarios - when a component is finished it is compiled into a scenario. A

scenario is the execution unit for production and can be scheduled scenario is the execution unit for production and can be scheduled.

(32)

5) Test and Reconcile

• Involve business owners in system integration testing

• Identify “single version of the truth” for reconciliation

Identify single version of the truth for reconciliation

• Define criteria for user sign-off

(33)

D fi

S

& T

t D t

t

i th I t f

ODI Interface Development Sample

• Define Source & Target Datastores in the Interface

 CASE statements, substrings & concatenations can all be done here

 Filters on source data are done here

 Mapping Source to Target is done here (JOINS are defined here)

 Variables can be used throughout

• Flow Tab

Flow Tab

 Select required KMs

 Update KM Options Values

• Controls Tab

 Select required CKM

(34)

ODI Interface Development Sample

Sample ODI Package:

_•

_{Execution Tab}

 Define the Execution

O ti Options

 Test the interface

• Scenario Tab

 Once the object is

complete and tested, generate a scenario to prod ctioni e the object productionize the object