Hi Pierre,
Maybe you could enrich the related wiki pages or create a new one using your
message below?
Thanks
Jacques
Le 03/05/2019 à 10:21, Pierre Smits a écrit :
In the recent past, and for several customer projects, I have been involved
in moving quite a lot of data from the DBs of other (legacy) solutions into
others (DWH, etc.) with ETL solutions. One of those projects was from a
legacy system to the OFBiz Dbs (both the transactional and the dwh).
Before I delve deeper into benefits and cost of using such a solution, I
want to remark that, no matter what tool is used (Excel, CSV, etc.) to move
such legacy data into the transactional and dwh databases of OFBiz, such
activities require a lot of planning [1], development [2] and testing [3]
before data can be moved into the production environment.
[1] The entity model of OFBiz is way more complex than some may anticipate
(based on experiences with their legacy solution) as there are a lot of
interlocking dependencies between tables. Careful planning of what needs to
be loaded in what sequence requires in-depth knowledge of the entity model;
[2] Legacy data does/may - in my experience - adhere to different paradigms
regarding field type definitions. That requires - quite some -
transformation of field values before it can be loaded into the OFBiz
database(s),
[3] Before loading into the database(s) in the production environment, the
load part of the ETL processes must be tested in a separate environment to
ensure that - at the moment of loading into production - the disruption of
operational activities (of users and/or OFBiz services) is minimised as the
risk of such (and the potential rebuild of the production environment) is
quite costly.
Using an ETL solution to migrate date delivers several benefits:
1. Extracting data can be done against various sources, whether that be
from a (live) database, Excel sheets, various data files in CSV, JSON
and/or XML format,
2. Transforming data values, as well as combining data from different
sources can be done (developed) in various ways, and tested step by step,
3. Changing where the ETL solution is going to load the data into is
fast, and this data can go into various formats: database, Excel sheets,
various data files in CSV, JSON and/or XML format,
4. The whole of ETL processes can be executed both on-demand and to
off-business hours (scheduled) to cater for both a) need data now and b)
minimisation of operational disruptions.
The key thing to keep in mind is that migrating legacy data into OFBiz
databases does NOT mean that it is being processed by the OFBiz engine
automatically. The OFBiz engine has a lot of services that are triggered by
other services (consider the gl transaction creation on the state change of
an invoice or payment). Loading the legacy data into the transaction db of
OFBiz implies that such state changes needs to happen on these kinds of
parent objects to get to the desired end result.
Another thing to keep in mind is that OFBiz is - for most - intended as a
transactional solution. With legacy data one should distinguish between
what is needed for operations and what is intended for historical insights
through the business intelligence. Often, if not always, a lot of the
legacy data is just intended for that, and one should consider to aggregate
(some of) the data into the OFBiz clap database.
If anyone wants more insights and/or assistance, feel free to contact me
directly.
Best regards,
Pierre Smits
*Apache Trafodion <https://trafodion.apache.org>, Vice President*
*Apache Directory <https://directory.apache.org>, PMC Member*
Apache Incubator <https://incubator.apache.org>, committer
*Apache OFBiz <https://ofbiz.apache.org>, contributor (without privileges)
since 2008*
Apache Steve <https://steve.apache.org>, committer
On Thu, May 2, 2019 at 5:48 PM Paul Mandeltort <[email protected]> wrote:
Using Excel XML features:
-In Web tools:
-Export the entity you want to work with, one at a time
- open it in Excel Windows
- Last I checked, Mac excel XML support sucks. But Mac
Excel sucks in general.
- Excel will figure out the schema and embed it so you can save it
as XML from there.
I’m going off of memory here as I haven’t done it in a while but I’ve
found at the time it was the easiest way to directly load/update data in
the entity engine outside of directly writing data.
It’s possible, but not sure if it’s advisable, to directly load data into
the underlying database via an ETL tool like Pentaho BI. Anyone know the
risks of doing this? I haven’t had time to research the implications.
—P
On May 2, 2019, at 10:12 AM, Michael Brohl <[email protected]>
wrote:
You can also easily generate load data by Excel itself:
- create a new sheet and insert a formula concatenating the static parts
with the dynamic parts/cells of the data sheet (see example).
- apply the formula to each row by dragging the lower right corner of
the cell containing the formula for the number of rows containing data
- copy the first column and paste into the load data xml file.
Example:
=CONCATENATE(
"<Party partyId='";sheet1!A65;"' partyTypeId='";sheet1!B65;"'
externalId='";sheet1!C65;"' statusId='";sheet1!D65;"' />"
)
Regards,
Michael Brohl
ecomify GmbH - www.ecomify.de
Am 01.05.19 um 18:22 schrieb [email protected]:
We are trying to upload legacy customers into ofbiz using the data
import tool. Is there a spreadsheet template to utilize? I uploaded my
file but the "not processed" field still shows 0. Can someone send me
the excel template for uploading?
Thanks,
James
