Lawrence Berkeley National Laboratory MAXIMO Integrations using IBM

Lawrence Berkeley National Laboratory MAXIMO Integrations using IBM Datastage and OSLC JSON API Pankaj Bhide Computer Systems Engineer Office of Science 0 0

About LBNL • Managed and Operated by University of California • 4200 Employees, 3000 Affiliates, 10, 000 visitors processed annually • ~$800 M/year Budget • 180 buildings on site • 6 leased offices in the Greater Bay Area • 6 User Facilities Office of Science 1 1

LBNL Mission Biosciences Computing Sciences Solving national challenges in energy, environment and health issues; and advance the engineering of biological systems for sustainable manufacturing. . High-performance computing and networking through research in mathematical modeling, data analysis and computer system architecture and software. Earth and Environmental Sciences Energy Sciences Seeking solutions to the global energyrelated challenges that impact our planet through a deeper understanding of the interactions between energy and matter. Tackling some of the most pressing environmental and energy challenges of the 21 st Century in order to enable sustainable stewardship and judicious use of the Earth’s subsurface energy resources. Energy Technologies Physical Sciences Internationally recognized global leader in energyefficient technologies, developing science and policy solutions to address the world’s most critical challenges. Exploring the fundamental forces and particles of matter from deep within the hearts of atoms to the farthest edges of the universe. . Office of Science 2 2

MAXIMO@LBNL Brief implementation history 1. 1996 - Rel 3. 0. 2 (pure windows app, work order tracking, procurement, SQRs) 2. 1998 - Rel 3. 2 (Assets, Locations, Inventory) 3. 2000 - Rel 4. 0. 1 (Y 2 K compliance, Job Plans, PMs) 4. 2001 - Rel 4. 1. 1 (More functions - MEA, Safety Plans) 5. 2003 - Rel 5. 2 (Web enabled, replace MEA with MIF, SQR with Actuate) 6. 2008 - Rel 6. 2. 6 (Implemented at the Advanced Light Source division 7. 2009 - Rel 6. 2. 6 (JAVA extensions, development of home grown web apps 8. 2010 Rel 6. 2. 6 (Lockshop / data. Splice for mobile implementation) 9. 2016 - Rel 7. 6. 0. 2 (replace JAVA with Jython, BIRT with Actuate, SSO) 10. 2017 - Rel 7. 6. 0. 6 (MRO workflow for WPC activities) 11. 2018 - Rel 7. 6. 0. 8 (Akwire from Solufy) 12. Re-design and develop the integrations using Datastage and OSLC JSON Rest API………………. . . Office of Science 3 3

Integrations at a glance Slide 1 Office of Science 4 4

Integrations (technologies used - past) Slide 1 Office of Science 5 5

Integrations (technologies used - past -[contd. ]) ● Collect/analyze integration requirements. ● Using MAXIMO sql logger, find out the “select/insert/update/delete” statements using the application. ● Develop common functions/procedures. ● Develop Oracle PL/SQL programs/Java that perform the needed “insert/update/delete” statements required for the integration needs. ● Develop a unix shell script that launches the PL/SQL programs. ● Using Tidal job scheduler, schedule their execution at the predetermined intervals. Office of Science 6 6

Integrations (technologies used - past -[contd. ]) ● Limitations ○ Can only work with the Oracle database. ○ Not supported by IBM. ○ May need to revise the code before upgrading MAXIMO to the newer release. ○ Old technology (not useful for web services, features similar to meta data sharing, upsert etc. ) Office of Science 7 7

Integrations (options considered) ❖ Options evaluated for the integration ● ● Flat files export/import Interface table/s processing HTML calls to object structures (XML based) MAXIMO REST API ● OSLC JSON REST API Office of Science 8 8

Integrations (OSLC JSON REST API) ● Primary features of OSLC JSON REST API RESTful Provides Insert, Read, Update, Delete capabilities Payload in JSON format Columns and filtering criteria can be supplied similar to Structured Query Language. Utilizes Object Structures Allows loading the data in bulk mode Filtering using Saved queries Queries using relationships Dynamic paging Handling Attachments Provides aggregation functions Tight integration with Automation scripts Office of Science 9 9

Integrations (OSLC JSON REST API - Quick Examples) ● Get the data from MAXIMO ○ No extra installation/configuration is needed. ○ Find out the out-of-box object structure [OS] (if not available, create a new OS from the underlying MBO). ○ Perform HTTP Get by specifying the MAXIMO host, path, the columns (from the object structure) and the filtering criteria (if needed) ○ Depending upon the filtering criteria, MAXIMO will fetch the results from the database and it will display the results in JSON format. Office of Science 10 10

Integrations (OSLC JSON REST API - Quick Examples) Get a few details of the corrective maintenance work orders reported by 813149 : https: //maximo. fac. dev. lbl. gov 1/maximo/oslc/os 2/MXWO 3? lean=1 &oslc. select 4=wonum, description_longdescription, rep ortedby&oslc. where 5=supervisor=” 813149” and worktype=”CM” 1. 2. 3. 4. 5. Host name Path for OSLC resource Name of the Object Structure Details of the work orders (columns from the OS) Filtering criteria Office of Science 11 11

Integrations (OSLC JSON REST API - Quick Examples) Sample HTTP Response JSON Office of Science 12 12

Integrations (OSLC JSON REST API - Quick Examples) Inserting work orders (request): Provide details of work orders (bulk-load) to be inserted Office of Science 13 13

Integrations (OSLC JSON REST API - Quick Examples) Inserting work orders (response) : 2 work orders are created. Office of Science 14 14

Integrations (How to utilize API? … Options) Now, it has been decided to utilize OSLC JSON REST API for the integration. . . …. How to utilize ? ? A few options were evaluated: 1. Development of the programs using Python that read the data from the source application and performs HTTP Post/Get to MAXIMO and vice versa. 1. Development of the programs using Java that read the data from the source application and performs HTTP Post/Get to MAXIMO and vice versa 1. Utilize Data Extraction and Loading (ETL) tool that reads the data from the source application and performs HTTP Post/Get to MAXIMO. Office of Science 15 15

Integrations (IBM Info. Sphere Data. Stage) IBM® Info. Sphere® Data. Stage® is a data integration tool for designing, developing, and running jobs that move and transform data. ● Info. Sphere Data. Stage, allows: ● ● Design data flows that extract information from multiple source systems, transform the data as required, and deliver the data to target databases or applications. ● Connect directly to enterprise applications as sources or targets to ensure that the data is relevant, complete, and accurate. ● Reduce development time and improve the consistency of design and deployment by using prebuilt functions. ● Minimize the project delivery cycle by working with a common set of tools across Info. Sphere Information Server. Office of Science 16 16

Integrations (Let us review an example…!!!) ● ● High level requirement ○ The data about buildings/floors/rooms originates in Archibus. It needs to be periodically synchronized into MAXIMO’s “locations” MBOs. Analysis ● Review tables/columns of the source application in Archibus ● Prepare a custom object structure (OS) based upon “locations” MBO in MAXIMO ● Prepare a mapping document that describes how the columns from the source table will get mapped to the columns of the OS in MAXIMO and if needed any transformation rules. Office of Science 17 17

Integrations (An example. . . continued) Office of Science 18 18

Integrations (An example… Overall job - before review) Office of Science 19 19

Integrations (An example. . . ) Let us build the datastage job…. . Office of Science 20 20

Integrations (An example. . . ) Let us build the datastage job…. . Office of Science 21 21

Integrations (An example. . . ) Let us build the datastage job…. . Office of Science 22 22

Integrations (An example. . . ) Office of Science 23 23

Integrations (An example. . . ) Office of Science 24 24

Integrations (An example. . . ) Office of Science 25 25

Integrations (An example. . . ) Office of Science 26 26

Integrations (An example. . . ) Office of Science 27 27

Integrations (An example. . . ) Office of Science 28 28

Integrations (An example. . . ) Office of Science 29 29

Integrations (An example. . . ) Shared container Office of Science 30 30

Integrations (An example… Overall job - revisited) Office of Science 31 31

Integrations (concluding points) ● ● ● Automation scripts using Jython that are tightly integrated with OSLC JSON REST API and Datastage. ZERO SQL (for insert/update/delete) for any MAXIMO Objects (strict interaction with MAXIMO business objects using OSLC JSON REST API). Throttling input and output traffic for MAXIMO. Office of Science 32 32

Integrations (concluding points) Office of Science 33 33