of oracle warehouse builder to oracle data integrator ... · pdf fileadministrative address:...

19
Administrative address: Office address: 21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60, 1000 Sofia, Bulgaria 1142 Sofia, Bulgaria tel.: + 359 2 491 72 79 tel.: + 359 899 100 166 1 White paper Of Oracle Warehouse Builder to Oracle Data Integrator Convertor

Upload: ngonhu

Post on 06-Feb-2018

219 views

Category:

Documents


3 download

TRANSCRIPT

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

1

White paper

Of

Oracle Warehouse Builder to

Oracle Data Integrator Convertor

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

2

INTRODUCTION OWB v.10.2 premier support ended on July 2010. OWB v.11.1 premier support ended on August 2012. OWB v.11.2 (available from September 2009) premier support ended on January 2015. No major enhancements are planned for OWB beyond the OWB 11.2 release. Earlier versions (9.2 and 10.1) of OWB will no longer be supported. In the latest Statement of Direction, Oracle announced following: “No major enhancements are planned for Oracle Warehouse Builder beyond the OWB 11.2 release. OWB 11.2 continues to be available and supported by Oracle, and patches and bug fixes will continue to be offered at regular intervals. Oracle will continue to support OWB 11.2 for the full lifetime of Database 11g and the full lifetime of the next major database release (“Database 12”) in accordance with Oracle’s Lifetime Support Policies for Database releases. Future database releases beyond Database 12 would not be certified with OWB 11.2.” In other words, OWB will not be supported anymore beyond the release 12 of the Oracle database. Which means that you will have to migrate before 2017. Oracle Data Integrator (ODI) is now Oracle’s Strategic Product for Data Integration. This means that customers will have to migrate from OWB to ODI, and needs to plan a manual or automated transfer of the OWB components to equivalent ODI components. Product overview OWB2ODI Converter acquires OWB repository’s metadata and it generates corresponding ODI repository metadata. Algorithms in OWB are transferred in ODI, and OWB operators are transformed in corresponding ODI operators when present or are substituted by a custom solution. Supplied service Conversion service of an Oracle Warehouse Builder project to a corresponding Oracle Data Integrator project. Additive services Performance tuning, ODI, DBA consulting, Oracle GoldenGate and Life Cycle Management for ODI

Supported product versions OWB v. 9.2 or higher ODI v. 10g or higher (up to v. 12c).

OWB to ODI converter service works for ALL the OWB and ODI releases and does the whole migration, not only part of it.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

3

Conversion process After the initial assessment phase with the client, a specific Converter’s component extracts metadata from the OWB .mdl file. Our Partner uses the OWB2ODI Converter in its laboratories to automatically generate a corresponding ODI project from the original OWB project. The generated ODI project will be sent to the client for acceptance test and rollout to production. The conversion process consists of several tasks, which are divided into 5 phases: 1. Assessment; 2. Conversion; 3. Test; 4. Parallel; 5. Production.

Phase 1 - Assesment

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

4

The assessment phase has following objectives: - To define the project framework, - To evaluate OWB project consistency (component number and type), - To define the conversion criteria.

The Assessment phase is composed of the following tasks: - Task 1: conversion assessment and statistics report generation, - Task 2: handling exceptions, - Task 3: topology definition, - Task 4: conversion mode explanation, - Task 5: KMs definition, - Task 6: configuration management definition. Task 1 needs an OWB .mdl file export, which the client has to send to the our Partner services center. The .mdl file export must: - Contain all project Locations, - Contain the Configuration, if it is different from default, - Contain all mappings and all process flows in validated state, - Contain all dependences.

After the generation of the report, the client and our Partner organize a meeting to execute tasks 2 to 6. This meeting and relative decisions are a basilar rock upon which the entire conversion project will be built.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

5

Task 1 - Conversion assessment and statistics report generation The .mdl file supplied by the client has to be validated and must include location and configuration parameters. It represents the necessary input for converter analyzer. Converter Analyzer can perform a multitude of operations. It analyses whether any special operators exist, for which essential changes are required. For example, consider the case of the splitter operator that allows the flow to direct itself towards several targets in the same mapping. This operator has to be carefully managed because the Oracle Data Integrator does not allow a mapping flow to end in multiple targets. In this case, it generates as many interfaces as the Oracle Warehouse Builder operator targets. Then it deals with the analysis of each mapping. It starts from the target operator and ends on the leftmost operator, ensuring the entire mapping structure and its related operators remain unchanged.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

6

The Converter analyzer generates the following report on the OWB process flows and mappings:

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

7

Our Partner and the client discuss the conversion assessment & statistics report in a specific meeting organized to execute tasks 2 to 6. During the meeting, the client and our Partner will compile the “Roles and responsibilities matrix”. This matrix assigns roles and responsibilities to the client and our Partner for every task within the conversion process. Task 2 – Exceptions handling This task is dedicated to deciding how to manage (work-around or manual conversion) any eventual case that cannot be automatically converted to ODI. A case can be represented by:

A particular OWB operator in a mapping,

A particular OWB activity or a particular transition condition in a process flow,

An external workflow layer (not in OWB) used to execute the OWB project’s components.

These are extremely rare contingencies: most used OWB components are automatically converted. Task 3 – Topology Definition In ODI, the definition of the topology, logical architecture, and physical architecture are necessary to indicate where the data are physically located. To correctly collect that information, the OWB2ODI Converter analyzes the OWB Repository Metadata to obtain:

Technology

Machine where the data is located

Database schema and/or file metadata ODI was developed to operate with any database technologies available (Oracle, DB2, Teradata, SQL Server, and many others). ODI provides the ability to refer to a database schema/user by its logical form. The logical user or logical schema is an abstract reference to a physical schema, which is defined within ODI as a real user in a specific database technology. Logical schemas and physical schemas are related through contexts. For example, the BUDGET logical schema may be associated with the BUDGET physical schema in the ORCL database through the CTX_BUDGET_ORCL context, etc.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

8

Everything is configured in ODI’s topology section, which contains all the information needed to switch between the logical and physical side. The OWB2ODI Converter only works on logical schemas because the pointers to physical schemas are configured in the topology, and the context is assigned either at runtime or when “conversions” are executed. The result of that process shows how easy it is to generate a technology-independent code. As long as the right context is set up, everything will work properly. Please note: the logical schema is always associated with its own technology, although it can be easily moved to another by deleting it from the old platform and building it in the new one. Task 4 – Conversion mode explanation This task is dedicated to give a detailed explanation of the conversion mode of every OWB component to the client‘s project team. The OWB2ODI Converter can convert: • OWB mappings into ODI interfaces and ODI packages; • OWB process flows logic into ODI native tools, ODI packages, ODI procedures and ODI load plans (available since ODI v.11.1.1.5). Task 5 – KMs definition ODI’s capabilities to handle any RDBMSs are represented by its KMs (Knowledge modules). The OWB2ODI Converter has a specific console to set parameters and options related to the use of the KMs. The present task will affect ODI project’s performances. The following is an example of how it is possible to customize the OWB2ODI Converter to choose the KMs better suited to the client’s technology and needs.

KMs customizable choosing grid

KNOWLEDGE MODULE SOURCE

TECH

TARGET

TECH

TYPE OF

LOADING

DEFAULT

OPERATING MODE

Oracle SQLLDR ORACLE ORACLE INSERT SET BASED

Oracle Incremental Update ORACLE ORACLE UPDATE SET BASED

OUR PARTNER Custom KM ORACLE ORACLE INSERT/UPDATE SET_BASED

DSNTIAUL DB2 DB2 TRUNCATE/INSERT SET BASED

Client Custom KM DB2 DB2 INSERT/UPDATE SET BASED FAIL

OVER ROW BASED

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

9

EC Our Partner’s customized KMs can also manage other aspects such as:

Management of ANALYZE on target table,

Management of the maximum number of allowable errors on target table,

Management of loading HINT,

Management of selecting HINT,

Etc… Task 6 – Configuration management definition The last, but not the least important task. To obtain best results, it would be opportune to freeze any maintenance activity about OWB project to convert. The ideal scenario would be: - To rollout every OWB project undergoing modification into the client’s production

environment, - To align each of the client’s environment (development, test and production), - To freeze any maintenance activity. The client has to decide if freezing is possible or not. If not, how does the client intend to execute configuration management of the OWB project to be converted? In this case, the client and our Partner have to define a detailed operative protocol to be applied during the conversion period. During this task, the “fixing protocol”, which is the management process (roles, responsibilities, actions, etc.) needed to fix any bugs identified during the acceptance test task, has to be defined. TECH TYPE OF

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

10

PHASE 2: CONVERSION

EFAULT OPERATING The Conversion phase has the following objectives: - To convert an original OWB project into a new ODI project, - To generate the new ODI project’s metadata for the future import into the client ODI

repository. The Conversion phase is composed of the following tasks: - Task 7: conversion of OWB Mapping and OWB Process Flow, - Task 8: formal test, - Task 9: ODI project metadata generation.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

11

Task 7 is the core of the entire conversion process and it is executed by our Partner services center. Before starting the task, the following should be carried out: - To supply a .mdl file aligned with the latest OWB project version, - To supply a DB schema export aligned with the latest OWB project version, - To freeze any maintenance activity related to the OWB project to convert. Task 8 is executed by our Partner’s services center and only concerns formal correctness of the new ODI project, because data are not available for a real test run. Task 9 is dedicated to the transmission of the new ODI project metadata generated by the Converter. Task 7 – Conversion of OWB Mapping and OWB Process Flow According to task 3 “Topology definition” results, our Partner’s services center carries out the ODI topology setting for its internal conversion environment. Then, all mappings are analyzed and a first transformation is applied whenever specific operators are found. This requires an onerous process in order to guarantee the proper functioning of the new Oracle Data Integrator flow, while keeping the semantic flow unchanged. This task, performed automatically by the OWB2ODI Converter, shows how costly and time-consuming it would be to carry out an entire manual conversion, not to mention the risk of introducing new mistakes due to haste or technical misunderstandings. Once additional mappings are “normalized”, recursive techniques are used to generate the operator’s tree of each mapping. The tree is retraced and each operator involved is transformed according to the new Oracle Data Integrator semantic. The correct topological information is maintained, considering the possible overruling of the location using a database of links or different schemas. The following tables indicate which OWB components the OWB2ODI Converter automatically converts and which components have to be converted via manual activity. From a conceptual point of view, OWB and ODI are similar, but they do not have equivalent features and have deep and significant differences. Thus, the OWB2ODI Converter does not handle some of the OWB components because they are rarely used or do not have a corresponding ODI function or because conversion would be too complex or ineffective or inefficient.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

12

OWB mapping: converted operators

Aggregator Joiner Sorter

Constant Mapping input/output parm Splitter

Deduplicator (Distinct) Match-Merge Table

Expression Materialized View Transformation

External Table Pivot/Unpivot View

Filter Pre/Post mapping process Anydata Cast (since 11.1)

Flat File (File multirecord) Sequence

Key Lookup Set Operation

W B mapping: not handled operators

OWB mapping: not handled operators

Dimension Expand object Pluggable mapping

Cube Varray iterator Queue (11.2)

Construct Name and address Subquery filter (11.2)

Data generator Table function LCR cast/splitter (11.2)

OWB process flow: converted activities

Data auditor FTP And / Or

Mapping Manual End

Subprocess Notification End Loop

Transform Set status For Loop

Assign Sqlplus While Loop

Email User Defined Fork

File Exists Wait Route

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

13

OWB process flow: converted transition conditions Success Error Complex

OWB process flow: not handled activities EJB / Java class (11.2) EJB / Java class (11.2) OMB plus (11.2)

OWB process flow: not handled transition conditions Warning* Extended

*Since ODI does not have a “warning status”, the OWB “warning transition condition” is handled depending on each client’s specific needs. This is a possible topic to be covered in the assessment meeting. The OWB “Fork activity” is converted by using the ODI “Load plan” feature that is only available from ODI v.11.1.1.5 onwards. Therefore, in order to manage and convert all of the most used and useful activities in the Process Flow, we need an ODI version that is equal or greater than ODI v.11.1.1.5. Task 8 – Formal test This is the first formal non-regression test executed on empty data structures, without data. This is carried out by our Partner’s services center. Task 9 – ODI project metadata generation The last task of the conversion process is dedicated to the ODI project metadata generation. These metadata are included in the .xml files that are delivered to the client.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

14

PHASE 3: TEST

The test phase has following objectives: - To compare new ODI project results with old OWB project results (acceptance test), - To tune new ODI project performances. The test phase is composed of the following tasks: - Task 10: test environment arrangement, - Task 11: acceptance test, - Task 12: performance tuning.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

15

Task 10 – Test environment arrangement During this task, each test environment’s element has to be arranged. In detail it is necessary: - To install OWB product, - To install ODI product, - To copy OWB project’s components, - To import the two .xml files supplied from our Partner’s services center into ODI repository, - To copy the production DB twice: one copy for OWB project and another copy for ODI project. Task 11 – Acceptance test The ODI project is tested to ensure that regression issues are not present. In detail, a complete parallel run of the two projects is executed and then respective results are compared to verify their matching. Each incorrect result is investigated to determine relative causes. The fixing protocol established during the client and our Partner initial meeting (Phase 1 – Assessment) is applied for each bug detected. Task 12 – Performance tuning The last step of the Test phase is related to performances. During this task, the ODI Project’s performances are tuned with data base administrator support.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

16

PHASE 4: PARALLEL

The parallel phase has following objectives: - To assure new ODI project correctness in Production environment such as the Test environment, - To compare and eventually tune new ODI project performances compared to the original OWB project in Production environment.

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

17

The test phase is composed of the following tasks: - Task 13: parallel environment arrangement, - Task 14: verification test, - Task 15: performance verification. Task 13 – Parallel environment arrangement During this task, each parallel environment’s element has to be prepared in order to: - Deploy ODI project, - Copy production DB. Obviously, the DB copy has to be executed just before the parallel run is started. Task 14 – Verification test Theoretically, this is a redundant and unnecessary task, but experience teaches us that a parallel production period must be carried out in order to locate any possible problems that may affect the production environment (configuration, privileges, missing patches, etc.). The comparison of results of two projects (just executed during the Test phase) is executed again. Task 15 – Performance verification Theoretically, this is also a redundant and unnecessary task, but experience teaches us that a parallel comparison must be carried out between the original OWB project and new ODI project performances in the production environment.

PHASE 5: PRODUCTION

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

18

The production phase has following objectives: - To remove the original OWB project and its pertinences (OWB installation, DB schema, etc.) from production environment, - To switch the scheduling tool from the original OWB project to the new ODI project. The test phase is composed of the following tasks: - Task 16: production environment cleaning, - Task 17: switching from OWB to ODI. Task 16 – Production environment cleaning The cleaning consists of: - OWB uninstallation, - DB schema deletion, - Removal of any other software components that are not necessary to the new ODI project running. Task 17 – Switching from OWB to ODI This consists of physical switching from the old OWB project to the new ODI project.

Creator and owner of the OWB2ODI Convertor is our Partner:

Database & Technology s.r.l.

Address: Largo Promessi Sposi, 4

20142 Milano, Italy URL: www.databtech.com

For more information about OWB2ODI Converter, please visit:

www.owb2odiconverter.com

Administrative address: Office address:

21B Moskovska Str. fl. 3, 53 Graf Ignatiev Str., entr. G, fl. 1, ap. 60,

1000 Sofia, Bulgaria 1142 Sofia, Bulgaria

tel.: + 359 2 491 72 79 tel.: + 359 899 100 166

19

B.A.A.E.R. Ltd. is OWB2ODI Convertor exclusive sales representative

for: Bulgaria, Romania, Albania, Macedonia and Serbia.

We also have non-exclusive sales rights for: Benelux, Turkey... As Oracle Gold Partner, we also offer full pack of: - Oracle DBA services, Oracle Cloud services, remote DBA; - Nearshore development and consultancy; - Managed IT services. References:

- LACO: delivering some of our managed services and supporting their infrastructure.

- Umicore: Developing logistics and customs declaration system for import and export

in Oracle Apex.

- Proximus (Belgacom): Manage Oracle 10 & 11 RAC systems with Application

Server, Oracle Portal and others.

- Centea Bank & Verzekeringen: Merge of several banking applications into a new

system. Done with Oracle Forms, Designer, Oracle DB’s.

- KBC bank: Integration of the Centea environment into the KBC systems.

- Thomas Cook: Remote monitoring of Oracle environment on Linux platforms.

Installation of new environments - Oracle 11 DB, etc.

- Lanxess Chemicals (ex Bayer): Project Management, Consolidation and disaster &

recovery projects. Modernizing applications in Oracle Forms, VBA and .NET with

Oracle 11 & DataGuard.

- Yara Chemicals & Norsk Hydro (now Statoil): Maintenance of a logistics

application. Redesign of the application into Oracle Forms 10 and Application Server

on Oracle 10, etc.

- BCD Travel: .Net development.