From user-return-55253-archive-asf-public=cust-asf.ponee.io@ofbiz.apache.org Fri May 3 17:29:31 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id B71FF18064D for ; Fri, 3 May 2019 19:29:30 +0200 (CEST) Received: (qmail 5611 invoked by uid 500); 3 May 2019 17:29:29 -0000 Mailing-List: contact user-help@ofbiz.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@ofbiz.apache.org Delivered-To: mailing list user@ofbiz.apache.org Received: (qmail 5582 invoked by uid 99); 3 May 2019 17:29:28 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 03 May 2019 17:29:28 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 4A2FAC2450 for ; Fri, 3 May 2019 17:29:28 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.001 X-Spam-Level: * X-Spam-Status: No, score=1.001 tagged_above=-999 required=6.31 tests=[KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_DNSWL_NONE=-0.0001, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id SLV7tHc1fvlj for ; Fri, 3 May 2019 17:29:26 +0000 (UTC) Received: from p3plwbeout17-05.prod.phx3.secureserver.net (p3plsmtp17-05-2.prod.phx3.secureserver.net [173.201.193.170]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 56E265F254 for ; Fri, 3 May 2019 17:29:25 +0000 (UTC) Received: from p3plgemwbe17-02.prod.phx3.secureserver.net ([173.201.193.136]) by :WBEOUT: with SMTP id MbzLhP0ebgBYkMbzLhgGmk; Fri, 03 May 2019 10:28:47 -0700 X-SID: MbzLhP0ebgBYk Received: (qmail 21833 invoked by uid 99); 3 May 2019 17:28:47 -0000 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" X-Originating-IP: 139.138.71.140 User-Agent: Workspace Webmail 6.9.57 Message-Id: <20190503102846.711664eeb53defe1e7c8f40d07deeb65.c2ccc9a630.wbe@email17.godaddy.com> From: To: user@ofbiz.apache.org Subject: RE: Legacy Data Date: Fri, 03 May 2019 10:28:46 -0700 Mime-Version: 1.0 X-CMAE-Envelope: MS4wfBU60O+lXvTL526/caF2a607AK6IO2T4B117+HSaHBrJ2BIN4cahARPCE8aBxrV8WqR2KmsKx2pkFnaqDzECNqK18y2HLMs1M0pL8uk1ENQ0F21zA0gh M/lqSoJ7CZB+sRvXhgeuRhUW84rycB1vuPlSyhlpkvwZlsjWYQ7EqF7cZuzMM2qNxMLFDSPvWzv0YYGYN1W/r1GCeYDzeM8sk9/iTKjyc4CxBihDjY8MftEt We have used a tool like Talend in the past but are there any open=0Asource= ETL with a graphical UI?=0A=0A-------- Original Message --------=0ASubject= : Re: Legacy Data=0AFrom: Pierre Smits =0ADate: Fri= , May 03, 2019 4:10 am=0ATo: user@ofbiz.apache.org=0A=0AThank you, Jacques,= for the suggestion. I will take that into=0Aconsideration.=0A=0ABest regar= ds,=0A=0APierre Smits=0A=0A*Apache Trafodion = , Vice President*=0A*Apache Directory , PMC M= ember*=0AApache Incubator , committer=0A*Apac= he OFBiz , contributor (without=0Aprivileges)=0As= ince 2008*=0AApache Steve , committer=0A=0A=0AOn = Fri, May 3, 2019 at 11:10 AM Jacques Le Roux <=0Ajacques.le.roux@les7arts.c= om> wrote:=0A=0A> Hi Pierre,=0A>=0A> Maybe you could enrich the related wik= i pages or create a new one using=0A> your message below?=0A>=0A> Thanks=0A= >=0A> Jacques=0A>=0A> Le 03/05/2019 =C3=A0 10:21, Pierre Smits a =C3=A9crit= :=0A> > In the recent past, and for several customer projects, I have been= =0A> involved=0A> > in moving quite a lot of data from the DBs of other (le= gacy) solutions=0A> into=0A> > others (DWH, etc.) with ETL solutions. One o= f those projects was from a=0A> > legacy system to the OFBiz Dbs (both the = transactional and the dwh).=0A> >=0A> > Before I delve deeper into benefits= and cost of using such a solution, I=0A> > want to remark that, no matter = what tool is used (Excel, CSV, etc.) to=0A> move=0A> > such legacy data int= o the transactional and dwh databases of OFBiz, such=0A> > activities requi= re a lot of planning [1], development [2] and testing [3]=0A> > before data= can be moved into the production environment.=0A> >=0A> > [1] The entity m= odel of OFBiz is way more complex than some may=0A> anticipate=0A> > (based= on experiences with their legacy solution) as there are a lot of=0A> > int= erlocking dependencies between tables. Careful planning of what needs=0A> t= o=0A> > be loaded in what sequence requires in-depth knowledge of the entit= y=0A> model;=0A> > [2] Legacy data does/may - in my experience - adhere to = different=0A> paradigms=0A> > regarding field type definitions. That requir= es - quite some -=0A> > transformation of field values before it can be loa= ded into the OFBiz=0A> > database(s),=0A> > [3] Before loading into the dat= abase(s) in the production environment,=0A> the=0A> > load part of the ETL = processes must be tested in a separate environment=0A> to=0A> > ensure that= - at the moment of loading into production - the disruption=0A> of=0A> > o= perational activities (of users and/or OFBiz services) is minimised as=0A> = the=0A> > risk of such (and the potential rebuild of the production environ= ment) is=0A> > quite costly.=0A> >=0A> > Using an ETL solution to migrate d= ate delivers several benefits:=0A> >=0A> > 1. Extracting data can be done a= gainst various sources, whether that=0A> be=0A> > from a (live) database, E= xcel sheets, various data files in CSV, JSON=0A> > and/or XML format,=0A> >= 2. Transforming data values, as well as combining data from different=0A> = > sources can be done (developed) in various ways, and tested step by=0A> s= tep,=0A> > 3. Changing where the ETL solution is going to load the data int= o is=0A> > fast, and this data can go into various formats: database, Excel= =0A> sheets,=0A> > various data files in CSV, JSON and/or XML format,=0A> >= 4. The whole of ETL processes can be executed both on-demand and to=0A> > = off-business hours (scheduled) to cater for both a) need data now=0A> and b= )=0A> > minimisation of operational disruptions.=0A> >=0A> > The key thing = to keep in mind is that migrating legacy data into OFBiz=0A> > databases do= es NOT mean that it is being processed by the OFBiz engine=0A> > automatica= lly. The OFBiz engine has a lot of services that are triggered=0A> by=0A> >= other services (consider the gl transaction creation on the state change= =0A> of=0A> > an invoice or payment). Loading the legacy data into the tran= saction db=0A> of=0A> > OFBiz implies that such state changes needs to happ= en on these kinds of=0A> > parent objects to get to the desired end result.= =0A> >=0A> > Another thing to keep in mind is that OFBiz is - for most - in= tended as a=0A> > transactional solution. With legacy data one should disti= nguish between=0A> > what is needed for operations and what is intended for= historical=0A> insights=0A> > through the business intelligence. Often, if= not always, a lot of the=0A> > legacy data is just intended for that, and = one should consider to=0A> aggregate=0A> > (some of) the data into the OFBi= z clap database.=0A> >=0A> > If anyone wants more insights and/or assistanc= e, feel free to contact me=0A> > directly.=0A> >=0A> > Best regards,=0A> >= =0A> > Pierre Smits=0A> >=0A> > *Apache Trafodion , Vice President*=0A> > *Apache Directory , PMC Member*=0A> > Apache Incubator , comm= itter=0A> > *Apache OFBiz , contributor (without= =0A> privileges)=0A> > since 2008*=0A> > Apache Steve , committer=0A> >=0A> >=0A> > On Thu, May 2, 2019 at 5:48 PM Paul Mand= eltort =0A> wrote:=0A> >=0A> >> Using Excel XML feature= s:=0A> >> -In Web tools:=0A> >> -Export the entity you want to work with, o= ne at a time=0A> >> - open it in Excel Windows=0A> >> - Last I checked, Mac= excel XML support sucks. But Mac=0A> >> Excel sucks in general.=0A> >> - E= xcel will figure out the schema and embed it so you can=0A> save it=0A> >> = as XML from there.=0A> >>=0A> >> I=E2=80=99m going off of memory here as I = haven=E2=80=99t done it in a while but I=E2=80=99ve=0A> >> found at the tim= e it was the easiest way to directly load/update data in=0A> >> the entity = engine outside of directly writing data.=0A> >>=0A> >> It=E2=80=99s possibl= e, but not sure if it=E2=80=99s advisable, to directly load data=0A> into= =0A> >> the underlying database via an ETL tool like Pentaho BI. Anyone kno= w the=0A> >> risks of doing this? I haven=E2=80=99t had time to research th= e implications.=0A> >>=0A> >> =E2=80=94P=0A> >>=0A> >>=0A> >>> On May 2, 20= 19, at 10:12 AM, Michael Brohl =0A> >> wrote:=0A>= >>> You can also easily generate load data by Excel itself:=0A> >>>=0A> >>= > - create a new sheet and insert a formula concatenating the static=0A> pa= rts=0A> >> with the dynamic parts/cells of the data sheet (see example).=0A= > >>> - apply the formula to each row by dragging the lower right corner of= =0A> >> the cell containing the formula for the number of rows containing d= ata=0A> >>> - copy the first column and paste into the load data xml file.= =0A> >>>=0A> >>> Example:=0A> >>>=0A> >>> =3DCONCATENATE(=0A> >>> " >> externalId= =3D'";sheet1!C65;"' statusId=3D'";sheet1!D65;"' />"=0A> >>> )=0A> >>>=0A> >= >> Regards,=0A> >>>=0A> >>> Michael Brohl=0A> >>>=0A> >>> ecomify GmbH - ww= w.ecomify.de=0A> >>>=0A> >>>=0A> >>> Am 01.05.19 um 18:22 schrieb james@pro= ductive1.com:=0A> >>>> We are trying to upload legacy customers into ofbiz = using the data=0A> >>>> import tool. Is there a spreadsheet template to uti= lize? I uploaded=0A> my=0A> >>>> file but the "not processed" field still s= hows 0. Can someone send me=0A> >>>> the excel template for uploading?=0A> = >>>>=0A> >>>> Thanks,=0A> >>>>=0A> >>>> James=0A> >>=0A>