Integrating EBS with OPEN SOURCE BI Prakash Ramamurthy Competency Head

Integrating EBS with OPEN SOURCE BI Prakash Ramamurthy Competency Head - EBS Doyen Systems Pvt. Ltd.

Presenter(s) Info Prakash Ramamurthy Competency Head - EBS, Doyen Systems Pvt Ltd 22 years of overall IT experience involving a spectrum of responsibilities Program management, Technical Leadership , Technical Development, Business processes understanding Associated with Oracle & related Technologies for 18 Years Handled various Integration projects played roles of varying capacities. Currently the. Prior to that he has been part of Organizations like Infosys Technologies, iGate Technologies.

Presenter(s) Info Anand Palanisamy Technical Consultant, Doyen Systems Pvt Ltd Technical Consultant in Oracle E-Business Suite with 5 years of experience Part of multiple Oracle E-Business Implementation & Upgrade projects with a focus on configuration, development and support of Oracle Applications

Agenda

Business Case Resource Intensive Reporting Solutions Impacts the ERP Systems : Transaction Processing ( OLTP) Limited Execution Window ( Off Business hours / Weekend / etc) End User Satisfaction Key Limiting Factors :

Business Case– Key Considerations

Solution Proposition – Open Source Tools

Statistics

Technical Approach

Architectural Components SOURCE ETL Reporting Tools It can be any data source such as Oracle, Non-Oracle, Flat files, etc. Input from multiple sources Easy to build business validation and transformation logic Visual flow of data transformation logic Supports Massive parallel processing Job schedule and reporting success/error log for easy monitoring Developer friendly with drag and drop facility Easy to build dashboards Sample report formats to study and build

Open Source Tools Some of the open sources provide integral package for reporting and ETL development, such as Pentaho. As per technical expertise business can use different open source DB, ETL & reporting tool such as Database ETL Tools Reporting Tools 1. MySQL 1. Pentaho PDI 1. Pentaho 2. PostgreSQL 2. Clover ETL 2. BIRT 3. SQLite 3. Talend Open Studio 3. Next Reports 4. Firebird 4. GeoKettle ETL 4. dmyReports 5. CUBRID 5. Jaspersoft ETL 5. DataVision 6. MariaDB 6. Jedox 6. Stimulsoft

Case Study : Open Source Tools Considered ETL Tools Database Reporting Tool

Case Study / Use Cases for evaluation Based on our Past engagements and Consultations with Consultants , below were some of the reports identified for Case Study Open Balance Listing Report Account Analysis Report Transaction Register – AR Purchase Register – AR On Hand Quantity Report Payment Register Report AR Aging Report PO Reconciliation Report PO Detail Report

Case Study : AR Aging Report MySQL PDI PDI PRD PRD

DB Objects Creation & Indexes Created following tables in destination source database with only required columns Created Indexes on the following Table for better performance Tables HZ CUST ACCOUNTS RA CUSTOMER TRX ALL HZ PARTIES RA CUST TRX TYPES ALL AR PAYMENT SCHEDULES ALL HR OPERATING UNITS XXDOY PENTAHO JOB LOG Case Study Demo-1 Case Study Demo-2

Pentaho Data Integrator PDI Used PDI to import, transform, and export data from EBS data source Pentaho Data Integrator comes with Pentaho suite which also includes Pentaho reporting tool, BI Server and more. Can input multiple sources Two technical components as part of PDI – Transformations (.ktr) – Jobs (.kjb)

Pentaho Data Integrator PDI Transformation Describes data flows for ETL such as reading from a source, transforming data and loading it into a target location Collection of Steps Step is an independent tasks which represents a data stream Conditional execution Job Executes transformation Coordinate ETL activities such as defining the flow and dependencies for what order transformations should be run Prepare for execution by checking conditions

Pentaho Data Integrator PDI You can launch the PDI design tool by clicking spoon.bat file under Data-Integrator directory

Transformation Creation In PDI A transformation is a network of logical tasks called steps Transformations are essentially data flows In below example transformation reads data from source DB table and transforms into target DB table

Job Creation and Scheduling

Job Creation and Scheduling

Pentaho Data Integrator PDI Used PDI's command line tools to execute PDI content from outside of Spoon

Job Schedule In BI Server We can schedule Job form BI Server as per the below command

Steps For Table Data Transformation

Validation Logic Log table in source database to capture start date of each transformation

Validation Logic Step to fetch start date and Job Code input values for log entry in source system

Validation Logic Step to block all other transformation steps to be completed

Validation Logic Step to update the log table in source system for data extract in next run incremental data

Step for Table Data Transformation Table input step - Incremental/Updated record extract using log table from source

Step for Table Data Transformation Insert/Update step - Populates the incremental/updated records in target system

Job Creation and Scheduling

Job Scheduler Log

Pentaho Reporting Tool – Template Selection

Pentaho Reporting Tool – Template Selection

Pentaho Reporting Tool - Connectivity

Pentaho Reporting Tool – Query Builder

Pentaho Reporting Tool

Pentaho Reporting Tool – Layout Design

Publish Report

Publish Report

Publish Report

Pentaho User Console

User Console

User Console - Schedule

User Console - Schedule

Report Output

Report Output

Data Transformation Statistics Initial Load Incremental Load Tables RA CUSTOMER TRX ALL HR OPERATING UNITS HZ PARTIES HZ CUST ACCOUNTS RA CUST TRX TYPES ALL AR PAYMENT SCHEDULES ALL Records Duration Records Duration 69986 285 41748 1125 1799 103010 1 hour 30 mins 0.9 secs 22 mins 51 secs 6 secs 8 secs 2 hours 11 mins 120 0 0 0 0 120 0.6 secs 0 sec 0 sec 0 sec 0 sec 0.7 sec

Report Statistics

Challenges/Learning

Solution Extensions

Visit our BLOGs Applications: EBS, APEX,. http://oraclemasterminds.blogspot.in/ Database: http://allappsdba.blogspot.in/

Thank You [email protected] www.Doyensys.com Q&A