Initial Data Load Extension Module Webinar

1. Initial Data LoadExtension Module Webinar February 4th, 2009

2. Agenda Functionality and Demo 15 min. Process and Tools 10 min. Development Technique 5 min. Q & A 30 min.

3. Agenda Functionality and Demo Process and Tools Development Technique Q & A

4. Functional Highlights Initial Data Load Goal: Provide a set of automated data import processes to quickly load core ERP data into Openbravo. QuickStart customers: predefined templates (simplicity). Support full “new-install-to-production-ready” instance. Benefits: Enable end users to manage the process. Minimize considerably time of migration. Ensure fully-integrated set of validated ERP data. Complete and detailed documentation. Solution Approach: Deliver as a extension module. Included in QuickStart. Spanish Translation.

5. Initial Data Load Functional Highlights Simple and effective process Configure default values Define CSV Validate Import • Supported entities: • Products • Price Lists • Bank Accounts • Business Partners • Open Payables • Open Receivables • Assets • Journal Entries • Standard Cost • On hand quantity / Stock • Data validation prior to import. • Concise, easy-to-understand error log. • Default value definition. • REST Web service interface.

6. Initial Data Load Functional Highlights Fill several Openbravo entities from one CSV file Business partners Addresses Business partners Contacts Templates: Simplicity. Intermediary step Fix structure.

7. Initial Data Load Functional Highlights DEMO Initial Data Load Demo

8. Initial Data Load Future plans. More entities to import: Balance sheet Payrolls. Improvements for consultants: Flexibility: ability to customize templates adding new fields. More integration capabilities Import from other data formats. (XML, Excel, ...)‏ Import from other third party applications.

9. Agenda Functionality and Demo Process and Tools Development Technique Q & A

10. Initial Data Load Process and tools. Extract, Transform and Load. Extract Transform and Load (ETL) takes part part in: * Synchronization or replication of databases * Right-time or batch exchanges of data * Data warehousing * Data migration * Data quality Extract Transform and Load (ETL) is a process in database usage and especially in data warehousing that involves: Extracting data from outside sources Transforming it to fit operational needs (which can include quality levels) Loading it into the end target (database or data warehouse)

11. Initial Data Load Process and tools. Load process structure Validate button Process button Extracting Transforming Loading Talend job * CSV file parsing * Data validation Openbravo load process & REST web service * Data transformation * Data load using DAL

12. Initial Data Load Process and tools: Talend Open Studio overview Talend Open Studio is a project for data integration based on Eclipse RCP Operates as a code generator Open source Metadata-driven solution Graphical designer Large list of integration components

13. Process and tools: Talend Open Studio demonstration Initial Data Load DEMO Quick Talend Open Studio review.

14. Agenda Functionality and Demo Process and Tools Development Technique Q & A

15. Development technique. REST web services for Openbravo ERP. Initial Data Load REST stands for Representational State Transfer. It relies on a stateless, client-server, cacheable communications protocol and in virtually all cases, the HTTP protocol is used. Definition of a REST web service: * The base URI: for example http://example.com/resources * The MIME type of the data supported by the web service, XML, JSON, ... * The set of operations supported using HTTP methods. POST, GET, PUT, DELETE

16. Development technique. REST web services for Openbravo ERP. Initial Data Load POST /openbravo/ws/org.openbravo.idl.process/Costing HTTP/1.1 Host: localhost <?xml version="1.0" encoding="UTF-8"?> <ob:Openbravo xmlns:ob="http://www.openbravo.com"> <Costing> <Product>rma</Product> <Cost>5.5</Cost> (...)‏ request <?xml version="1.0" encoding="UTF-8"?> <ob:Openbravo xmlns:ob="http://www.openbravo.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <MaterialMgmtCosting id="FF808181266B867701266B8E6AE4008B" identifier="Raw material A Thu Jan 01 00:00:00 CET 2009"> <id>FF808181266B867701266B8E6AE4008B</id> <creationDate transient="true">2010-01-26T17:54:40.616Z </creationDate> (...)‏ response

17. Development technique. Code snipets, defining a new web service Initial Data Load <?xml version="1.0" encoding="UTF-8" ?> <provider> <bean> <name>process</name> <class>org.openbravo.idl.service.IdlWebService</class> <singleton>true</singleton> </bean> </provider> package org.openbravo.idl.service; public class IdlWebService implements WebService { @Override public void doPost(String path, HttpServletRequest request, HttpServletResponse response)‏ throws Exception { final String firstsegment = WebServiceUtil.getInstance().getFirstSegment(path); final VariablesSecureApp vars = new VariablesSecureApp(request); final ConnectionProvider conn = new DalConnectionProvider();

18. Development technique. Reading XML from request. Initial Data Load final SAXReader reader = new SAXReader(); final Document document = reader.read(request.getInputStream()); // check that the rootelement is the openbravo one final Element rootElement = document.getRootElement(); if (!rootElement.getName().equals(XMLConstants.OB_ROOT_ELEMENT)) { throw new OBException("Root tag of the xml document should be: " + XMLConstants.OB_ROOT_ELEMENT + ", but it is " + rootElement.getName()); } // check that there exit one element to import. if (rootElement.elements() == null || rootElement.elements().size() != 1) { throw new OBException("Root tag of the xml document should have one child element."); } Element entityelement = (Element) rootElement.elements().get(0);

19. Development technique. Writing XML to response. Initial Data Load BaseOBObject obj = service.doInternalProcess(impent.getValues()); try { final StringWriter sw = new StringWriter(); final EntityXMLConverter exc = EntityXMLConverter.newInstance(); exc.setOptionEmbedChildren(true); exc.setOptionIncludeChildren(true); exc.setOptionIncludeReferenced(false); exc.setOptionExportClientOrganizationReferences(true); exc.setOutput(sw); exc.process(obj); String xml = sw.toString(); response.setContentType("text/xml"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Encoding", "UTF-8"); final Writer w = response.getWriter(); w.write(xml); w.close(); } catch (final Exception e) { throw new OBException(e); }

20. Development technique. REST Web Service demonstration Initial Data Load DEMO REST Web Service execution

21. Wrap-Up Functionality and Demo Process and Tools Development Technique

22. Initial Data Load Q & A Interesting Links: http://forge.openbravo.com/projects/xidl http://forge.openbravo.com/projects/idlspanish Next Webinar:18th February Modularity Status UpdateAchievements in modularity during 2009 and roadmap -in regards of modularity- for 2010 adrian.romero@openbravo.com mikel.irurita@openbravo.com