OHL Layer Automation (Team C)

(Hive to Oracle Table & ODI Objects Creation using Groovy)

o
Team Involved
 Mahek Choudhary
 Deepesh Kimtani
 Indumathi JawaharJothi
 Panduranga Alla

©Larsen & Toubro Infotech Ltd. Privileged and 1

Confidential
Scope of Automation
 Reverse engineer tables (Hive/Hadoop) in ODI
 Create OHL (Oracle) table in defined schema
 Create all ODI objects (Projects, Models, Data stores, Mappings etc)
 Minimize the manual efforts

©Larsen & Toubro Infotech Ltd. Privileged and 2

Confidential
Features
 Parameterized approach of the solution
 Only few parameters are needed to be declared by user
 One click utility
 Low time consuming

©Larsen & Toubro Infotech Ltd. Privileged and 3

Confidential
Flow Diagram
User Inputs the parameter
and runs the scenario

ODI objects created for the

entered parameters in Dev
environment

©Larsen & Toubro Infotech Ltd. Privileged and 4

Confidential
One Click Utility – Package Steps..
Steps to create the OHL table
2 Enter Parameter
Execute scenario & User input
of parameter

1 Execute Scenario

Input Variable Window

©Larsen & Toubro Infotech Ltd. Privileged and 5

Confidential
One Click Utility – Package Steps..
Package Layout

©Larsen & Toubro Infotech Ltd. Privileged and 6

Confidential
Package Steps – 1) Variable Declaration
Variable declaration by user
 ODI User: User credentials
 ODI User Password: ODI login password
 Target Model Name: Target model name
 Target Loc Schema: Target logical schema
 Src. Model Name: Source model name
 Src. Loc Schema: Source logical schema
 Proj. Folder Name: Project folder name in which the mappings are needed to be
created
 Hive Table Name: Hive Table Name
 OHL Schema Name: Schema name in which OHL tables are to be created

©Larsen & Toubro Infotech Ltd. Privileged and 7

Confidential
Package Steps – 2) Proc. Hive Metadata
Hive Metadata is used to Reverse engineer the Hive table

Hive Procedure has

two steps

©Larsen & Toubro Infotech Ltd. Privileged and 8

Confidential
Package Steps – 2) Proc. Hive Metadata
Hive Metadata is used to Reverse engineer the Hive table

©Larsen & Toubro Infotech Ltd. Privileged and 9

Confidential
Package Steps – 3) Proc. Hive to Oracle
Oracle Object Creation Procedure

Oracle Object Creation

has two steps

©Larsen & Toubro Infotech Ltd. Privileged and 10

Confidential
Package Steps – 3) Proc. Hive to Oracle
Oracle Object Creation Procedure

©Larsen & Toubro Infotech Ltd. Privileged and 11

Confidential
Package Steps – 4) Proc. ODI Obj Creation
ODI Object Creation Procedure

ODI Object Creation

has two steps

©Larsen & Toubro Infotech Ltd. Privileged and 12

Confidential
Package Steps – Database Obj Step
Database Objects to validate the creation of the Hive and OHL tables. Also, these tables are used for
setting internal procedure steps

 Hive_Metadata
Table used to store DDL of Hive
Tables
 View_All_Tables_in_Schema
View to hold the latest Oracle OHL
Table. Along with maximum table
number counter

©Larsen & Toubro Infotech Ltd. Privileged and 13

Confidential
Objects Created..
ODI Objects Created after execution

1 Model Creation & Reverse Engineer of Hive Table 2 OHL Data Store Creation in ODI

©Larsen & Toubro Infotech Ltd. Privileged and 14

Confidential
Objects Created..
ODI Objects Created after execution

Allied ODI Objects creation – Sequences, Scenarios,

3 Project Creation and Project Folder Creation 4 Load Plan etc.

©Larsen & Toubro Infotech Ltd. Privileged and 15

Confidential
Advantages

Increase in code Reduction in Manual

Quality Efforts Time Saving

Minimal Parameter Efficient and Uniform One click Utility

Input Process

©Larsen & Toubro Infotech Ltd. Privileged and 16

Confidential
Deviation/Constraints
 Unique Key constraint not added to Target table.
 Automation will not work for table or object with more than 22 Char.

©Larsen & Toubro Infotech Ltd. Privileged and 17

Confidential
Target FTE Saving:

• With “Automated OHL Mapping Creation” in place daily manual efforts spend by each CDW development team members to create any OHL mapping
objects now takes hardly takes 1 minute
• Increase in Code Quality, Reduction in manual efforts, Minimum Parameter Input
• Efficient and Uniform Process and 1 Click Utility

Time Saved per month using this tool:

 No. of CDW Developers (LTI alone) : 14 resources
 Avg No. of mappings created by a developer per week : 1 mapping
 Approx. time savings for single mapping : Extraction of Hive Source DDL Script (15 mins) + Execution of procedure to
create DDL on DB (10 mins) + Coordination to get script and monitor Procedure execution (15 mins) = 40 mins
 Total time spent by CDW development team for migrations (dev to UAT)

= 14 people * 1 mapping * 40 mins * 4 weeks

= 2240 mins
= 38 Hrs/Month = 38/160 hrs per month= .237 FTE

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
18
Automation of daily load plan status
jobs

Team:
1. Bipradas
2. Vijaya
3. Shariyat
Scope of Automation

1. Reusable package to sent email notification with a predefined

email template
2. Notify critical warning to developer on long running sessions
3. Generate email report to capture all job status in last n hours
4. Advanced Analytics - Anomaly detection
Features

1. Option to activate or deactivate the mails

2. Parameterized approach of the solution, so many teams can
use it
3. Email template can be enhanced based on project need.
4. PowerBI Dashboard
Data source

1. Static data from project/support team for each load plan

 Current Owner: Support/Development Team
 Developer: Primary/Secondary Contact
 Priority: Priority information is maintained in COMALL. CALL251_LOAD_PRIORITIES table
 Enable_Notification: option to enable/disable the email notifications
Data source

1. Dynamic data captured from call004_all_loadplan_status table,

 Load plan Name
 Load Plan instance Id
 Load Start Date
 Load End Date
 Duration
 Status
 Context
 Log Captured Date
 Error_Stack
 Data source
 Main query to fetch load plan log information:

SELECT call004_all_loadplan_status.load_plan_name, call251_load_priorities.priority, status,

DECODE(status,'E','Error','M','Done with Warnings','D','Done','W','Waiting','R','Running',status) AS status_desc,
DECODE(status,'E','1','M','2','D','5','W','3','R','4',status) AS status_mail_order,
(CASE WHEN dbms_lob.instr(dbms_lob.substr(TRIM(error_message),3999,1),'Caused By:') > 0 THEN
substr(regexp_substr(TRIM(error_message),'(Caused
By).*'),1,least(500,length(regexp_substr(TRIM(error_message),'(Caused By).*') ) ) )
ELSE regexp_substr(TRIM(error_message),'(ODI-||[:ALNUM:]).*') END) AS error_stack,
start_date AS start_time, end_date AS end_time, dur_in_min session_duration,
CASE WHEN end_date IS NULL THEN round( (SYSDATE - start_date) * (1440) ) ELSE dur_in_min END
running_duration
FROM comall.call004_all_loadplan_status
LEFT OUTER JOIN call251_load_priorities ON call004_all_loadplan_status.load_plan_name =
call251_load_priorities.load_plan_name
WHERE start_date >= TO_DATE(date_start,'DD-MON-YYYY HH24:MI') -- report start/end date
parameterized
AND start_date <= TO_DATE(date_end,'DD-MON-YYYY HH24:MI') -- report start/end date parameterized
AND status != 'D' --excluded successful executions
ORDER BY status_mail_order, start_time DESC
Data source
 COMALL. call004_all_loadplan_status--> Fetch required load
plan status logs

 COMALL. CALL251_LOAD_PRIORITIES– Can be enhanced to add

all status rather than only successful one.

 COMALL. CALL252_EMAIL_RECIPIENT_INFO – to maintain email

recipient information for load plan status emails.
Design/data flow - Daily Status email notification

COMALL tables
ODI JOB to validate status/
send email
job duration
(utl_mail.send)

static data

Email notification for long running sessions, report all job status.
From Email - scaniait.cdw@scania.com
Email Template : Daily Status email
notification
1. Two separate template configured to capture daily load plan
status.
Template 1: When no_of_distinct_failure > 20 OR no_of_failure > 60
Email Template : Daily Status email
notification

Template 2: When no_of_distinct_failure <= 20 AND no_of_failure <=

60
 DM procedure: Daily Status email
notification
COMALL procedure : SEND_HTML_EMAIL_LP
ODI Procedure:CALL254_TRIGGER_DAILY_MAILS_PROC
ODI Scenario: CALL254_TRIGGER_DAILY_MAILS_PROC Version 001
ODI Load Plan: DW_EMAIL_NOTIFICATION_LOAD

C:\Users\bxaaeu\
Documents\Automation\SEND
Schedule: Daily Status email
notification
Created two schedules for the load plan status email,
Night job status - afternoon 5 PM CET Until 8am CET - logic added in
ODI, if scheduled in morning 8:00 AM to16:59 PM then fetch Night job
status – Currently scheduled at 8:03 AM daily at pigato

day job status – From 8 am CET Until afternoon 5 PM CET – if

scheduled 17:00 PM to 07:59 AM next day then fetch day job status –
Currently scheduled at 5:03 PM daily at pigato
Design/data flow

Input Parameters:
1.ODI Load Plan Name
2. Email Recipients
Reusable ODI Package send email
3. Load Plan Global Id

Email notification package can be used in project to send mail with success./Failure/waring information.
This will trigger LOG_ERROR /LOG_WARNIBG package based on the status of the load plan.
ODI package: Job Status report

Package Name: CALL253_SEND_LP_STATUS_PKG

Scenario : CALL253_SEND_LP_STATUS_PKG Version 001
Procedure: CALL253_TRIGGER_EMAIL_PROC
How to use Job Status report in a
load plan

Scenario : CALL253_SEND_LP_STATUS_PKG Version 001

1. For Failed mail notification it should be added as an exception with
the scenario variables refresh enabled as shown below,
How to use Job Status report in a
load plan
Scenario : CALL253_SEND_LP_STATUS_PKG Version 001
2. For Success/Warning mail notification it should be added at the end
of the load plan with the scenario variables refresh enabled as shown
below,
Email Template : Job Status report -
success
Email Template : Job Status report -
fail
Future Scope & plan
1. Looking at the pattern of the re-occurring error messages for the load
plans an automation package can be created to assign the incident to the
right team to start working on the fix without any human intervention.
2. We can add all successful job logs as an attachment in the mail in next
release.
3. PowerBI report can be created with pattern of failures and health check
information.
4. Use of dynamic threshold limit for each load plan and notify warnings if
jobs are running beyond critical limit.
5. Advanced Analytics - Anomaly detection
Target FTE Saving
1. With this automation package in place daily manual efforts spend by CDW
support team members to find out all the failed instances and take action
based on priority of the load plans.
2. Also it will show the jobs that has executed more than the usual duration
and project team can be reported to check if any room for performance
improvements.
3. Enabling email notification for each load plan will help MM/assignment
leads to get notified about the delays if any in refreshing the data.

We are estimating this automation can save average 1-2 hrs of manual efforts
on a daily basis if used effectively. With the added features planned to add in
future it can be more useful in our day to day activities.
Questions/Feedback?
 ODI Auto
Deployment Suite
Team:
Ayyappa Raj
Arkadeep Bagal
Raghavendra B S
Swapnil Mohite
Scope of ODI Auto Deployment Suite:

• A Tailored tool to Automate Migration of ODI objects from Development

Environment to UAT environment with minimal human intervention
• Reports failure and success status of each deployment
• Multiple users would be able to use this utility parallelly at the same
time.
• Email notifications at the end of each major process

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
41
Features:

• Multiple users can use it parallelly

• Automatically exports and import ODI object from Dev to UAT.
• Automatically captures backup from UAT before deployment
• PowerBI dashboard available to show the details of all the
deployments that occurred till date

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
42
 Design Flow

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
43
Packages [Dev]

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
44
Packages [UAT]

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
45
Email Notification [Export Successful]

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
46
Email Notification [Backup Successful]

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
47
Email Notification [Import Successful]

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
48
PowerBI Report

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
49
Advantages:

• Saves lot of Time during Migration

• Avoids Manual Errors while Importing
• Migration Audits are maintained
• Quick and Easy to Use
• Email Notifications for each Migration Process
• PowerBI report to showcase the Status of each and every
Migration
• Scalable Framework

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
50
Future Scope & plan:

 This utility is a framework for automating deployment of ODI

Objects, and henceforth this tool is scalable to accommodate many
new features that can come up in the future. Few of those are
listed below.

 Schedule an ODI Migrations at a desired time

 Rollback the migration using the exports from the backup
folder.
 Tailor this tool further to do migrations between UAT and
Prod

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
51
Target FTE Saving:

• With “ODI Auto Deployment Suite” in place daily manual efforts spend by each CDW development team members to migrate any ODI objects from DEV
to UAT now takes no time.
• Now there will be no room for any manual errors.
• No additional effort required to take backup of each objects from UAT before the deployment process.

Time Saved per month using this tool:

 No. of CDW Developers (LTI alone) : 14 people
 Avg No. of migration done by a developer per week : 3 times
 Approx. time taken for single migration(Dev to UAT) : 10 mins
 Total time spent by CDW development team for migrations (dev to UAT)
=14people *3 times *10 mins*
4 weeks
=1680 mins= 28 Hrs/Month.

With the new features planned to add in the future it can be more useful during Prod Migrations and saving even more time.

©Larsen & Toubro Infotech Ltd. Privileged and

Confidential
52