importing data into ofbiz

[Si Chen-2]

Hi everybody.  I've been thinking about importing data into ofbiz.  
One of the issues is that we have a highly normalized data model  
which would require extensive mapping from external systems.  
Nevertheless, I think it is possible to create some standard  
"template" entities for importing data and thus create a standard  
practice and, eventually, standard tools for doing it.  Here's what  
I'm thinking, given in the context of importing product data:

1.  Create a standard non-normalized "holding" entity which contains  
productId, quantity on hand, available to promise, average cost,  
reorder point.  Note that none of these are linked to any entity in  
ofbiz.
2.  Add a timestamp fields to identify when the data was loaded into  
the "holding" entity and when it was imported into ofbiz.
3.  Write a standard datafile xml import template which corresponds  
to this "holding" entity.
4.  Write some processing script or service which takes new data from  
the holding entity and insert it into ofbiz.  For example, in this  
case it would be to create InventoryItem, ProductAverageCost, and  
maybe some AcctgTrans (maybe . . .)

It may be most efficient to write these holding entities  
corresponding to popular software programs where people might want to  
import data from, so they can mirror those programs' data layout more  
easily.

Si

[David E Jones-2]

Stepping back a bit and looking at this conceptually:

What you are proposing would be an import format that would work well for a certain piece of software that the data is coming from.

In fact, the normalized data model of OFBiz is far easier to transform data to than a more normalized form would be.

Consider Software A and Software B that both have very functionality specific, redundant, and generally non-normalized data models. The cardinality of fields relative to a product may be different, and incompatible, and they may treat similar information in very different ways. Because the OFBiz model is more flexible it may be possible to migrate A->OFBiz and B->OFBiz but it may not be possible to migrate A->B or B->A.

So, yes, something is needed to transform incoming data but trying to create something that is less normalized will just make it easier for some systems (those that are close to it) and far more difficult for other systems (that have more conceptual mismatches with it).

This is basically the unavoidable law of de-normalization.

As far as tools and such go, FreeMarker is actually a pretty good XML transformation tool. The Entity Engine import stuff does already support using FTL files along with an incoming XML file to pre-transform the data. There is an example in the ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice the root element is "entity-engine-transform-xml" instead of "entity-engine-xml".

-David


Si Chen wrote:

> Hi everybody.  I've been thinking about importing data into ofbiz.  One
> of the issues is that we have a highly normalized data model which would
> require extensive mapping from external systems.  Nevertheless, I think
> it is possible to create some standard "template" entities for importing
> data and thus create a standard practice and, eventually, standard tools
> for doing it.  Here's what I'm thinking, given in the context of
> importing product data:
>
> 1.  Create a standard non-normalized "holding" entity which contains
> productId, quantity on hand, available to promise, average cost, reorder
> point.  Note that none of these are linked to any entity in ofbiz.
> 2.  Add a timestamp fields to identify when the data was loaded into the
> "holding" entity and when it was imported into ofbiz.
> 3.  Write a standard datafile xml import template which corresponds to
> this "holding" entity.
> 4.  Write some processing script or service which takes new data from
> the holding entity and insert it into ofbiz.  For example, in this case
> it would be to create InventoryItem, ProductAverageCost, and maybe some
> AcctgTrans (maybe . . .)
>
> It may be most efficient to write these holding entities corresponding
> to popular software programs where people might want to import data
> from, so they can mirror those programs' data layout more easily.
>
> Si

[BJ Freeman]

Most of my clients are use to Excel.
So for them I have created columns, with column names they are familiar
with, for the data then a macro to add the entity info on a new
worksheet. the macro then exports them to a txt file with an xml extension.


Si Chen sent the following on 7/7/2006 11:23 AM:

> Hi everybody.  I've been thinking about importing data into ofbiz.  One
> of the issues is that we have a highly normalized data model which would
> require extensive mapping from external systems.  Nevertheless, I think
> it is possible to create some standard "template" entities for importing
> data and thus create a standard practice and, eventually, standard tools
> for doing it.  Here's what I'm thinking, given in the context of
> importing product data:
>
> 1.  Create a standard non-normalized "holding" entity which contains
> productId, quantity on hand, available to promise, average cost, reorder
> point.  Note that none of these are linked to any entity in ofbiz.
> 2.  Add a timestamp fields to identify when the data was loaded into the
> "holding" entity and when it was imported into ofbiz.
> 3.  Write a standard datafile xml import template which corresponds to
> this "holding" entity.
> 4.  Write some processing script or service which takes new data from
> the holding entity and insert it into ofbiz.  For example, in this case
> it would be to create InventoryItem, ProductAverageCost, and maybe some
> AcctgTrans (maybe . . .)
>
> It may be most efficient to write these holding entities corresponding
> to popular software programs where people might want to import data
> from, so they can mirror those programs' data layout more easily.
>
> Si
>

[Si Chen-2]

David,

Yes, the idea was to create something that would be better for some  
applications (namely, the one it's built for) than others.

But I'm very curious about this entity-engine-transform-xml.  So is  
this idea to paste a big block of XML into <entity-engine-transform-
xml> file and then write the .FTL to do the transformation?  And then  
run xml import on that file?

And what about the freemarker template?  Does it fall under their  
"Declarative XML Processing" documentation here: http://
freemarker.org/docs/xgui_declarative.html  I looked at it briefly and  
recognized some of syntax.

Si


On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:

>
> Stepping back a bit and looking at this conceptually:
>
> What you are proposing would be an import format that would work  
> well for a certain piece of software that the data is coming from.
>
> In fact, the normalized data model of OFBiz is far easier to  
> transform data to than a more normalized form would be.
>
> Consider Software A and Software B that both have very  
> functionality specific, redundant, and generally non-normalized  
> data models. The cardinality of fields relative to a product may be  
> different, and incompatible, and they may treat similar information  
> in very different ways. Because the OFBiz model is more flexible it  
> may be possible to migrate A->OFBiz and B->OFBiz but it may not be  
> possible to migrate A->B or B->A.
>
> So, yes, something is needed to transform incoming data but trying  
> to create something that is less normalized will just make it  
> easier for some systems (those that are close to it) and far more  
> difficult for other systems (that have more conceptual mismatches  
> with it).
>
> This is basically the unavoidable law of de-normalization.
>
> As far as tools and such go, FreeMarker is actually a pretty good  
> XML transformation tool. The Entity Engine import stuff does  
> already support using FTL files along with an incoming XML file to  
> pre-transform the data. There is an example in the ofbiz/
> applications/ecommerce/data/DemoContent.xml file. Notice the root  
> element is "entity-engine-transform-xml" instead of "entity-engine-
> xml".
>
> -David
>
>
> Si Chen wrote:
>> Hi everybody.  I've been thinking about importing data into  
>> ofbiz.  One of the issues is that we have a highly normalized data  
>> model which would require extensive mapping from external  
>> systems.  Nevertheless, I think it is possible to create some  
>> standard "template" entities for importing data and thus create a  
>> standard practice and, eventually, standard tools for doing it.  
>> Here's what I'm thinking, given in the context of importing  
>> product data:
>> 1.  Create a standard non-normalized "holding" entity which  
>> contains productId, quantity on hand, available to promise,  
>> average cost, reorder point.  Note that none of these are linked  
>> to any entity in ofbiz.
>> 2.  Add a timestamp fields to identify when the data was loaded  
>> into the "holding" entity and when it was imported into ofbiz.
>> 3.  Write a standard datafile xml import template which  
>> corresponds to this "holding" entity.
>> 4.  Write some processing script or service which takes new data  
>> from the holding entity and insert it into ofbiz.  For example, in  
>> this case it would be to create InventoryItem, ProductAverageCost,  
>> and maybe some AcctgTrans (maybe . . .)
>> It may be most efficient to write these holding entities  
>> corresponding to popular software programs where people might want  
>> to import data from, so they can mirror those programs' data  
>> layout more easily.
>> Si

[David E Jones-2]

Yes, this looks like a good description of the general idea.

-David


Si Chen wrote:

> David,
>
> Yes, the idea was to create something that would be better for some
> applications (namely, the one it's built for) than others.
>
> But I'm very curious about this entity-engine-transform-xml.  So is this
> idea to paste a big block of XML into <entity-engine-transform-xml> file
> and then write the .FTL to do the transformation?  And then run xml
> import on that file?
>
> And what about the freemarker template?  Does it fall under their
> "Declarative XML Processing" documentation here:
> http://freemarker.org/docs/xgui_declarative.html  I looked at it briefly
> and recognized some of syntax.
>
> Si
>
>
> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>
>>
>> Stepping back a bit and looking at this conceptually:
>>
>> What you are proposing would be an import format that would work well
>> for a certain piece of software that the data is coming from.
>>
>> In fact, the normalized data model of OFBiz is far easier to transform
>> data to than a more normalized form would be.
>>
>> Consider Software A and Software B that both have very functionality
>> specific, redundant, and generally non-normalized data models. The
>> cardinality of fields relative to a product may be different, and
>> incompatible, and they may treat similar information in very different
>> ways. Because the OFBiz model is more flexible it may be possible to
>> migrate A->OFBiz and B->OFBiz but it may not be possible to migrate
>> A->B or B->A.
>>
>> So, yes, something is needed to transform incoming data but trying to
>> create something that is less normalized will just make it easier for
>> some systems (those that are close to it) and far more difficult for
>> other systems (that have more conceptual mismatches with it).
>>
>> This is basically the unavoidable law of de-normalization.
>>
>> As far as tools and such go, FreeMarker is actually a pretty good XML
>> transformation tool. The Entity Engine import stuff does already
>> support using FTL files along with an incoming XML file to
>> pre-transform the data. There is an example in the
>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice the
>> root element is "entity-engine-transform-xml" instead of
>> "entity-engine-xml".
>>
>> -David
>>
>>
>> Si Chen wrote:
>>> Hi everybody.  I've been thinking about importing data into ofbiz.  
>>> One of the issues is that we have a highly normalized data model
>>> which would require extensive mapping from external systems.  
>>> Nevertheless, I think it is possible to create some standard
>>> "template" entities for importing data and thus create a standard
>>> practice and, eventually, standard tools for doing it.  Here's what
>>> I'm thinking, given in the context of importing product data:
>>> 1.  Create a standard non-normalized "holding" entity which contains
>>> productId, quantity on hand, available to promise, average cost,
>>> reorder point.  Note that none of these are linked to any entity in
>>> ofbiz.
>>> 2.  Add a timestamp fields to identify when the data was loaded into
>>> the "holding" entity and when it was imported into ofbiz.
>>> 3.  Write a standard datafile xml import template which corresponds
>>> to this "holding" entity.
>>> 4.  Write some processing script or service which takes new data from
>>> the holding entity and insert it into ofbiz.  For example, in this
>>> case it would be to create InventoryItem, ProductAverageCost, and
>>> maybe some AcctgTrans (maybe . . .)
>>> It may be most efficient to write these holding entities
>>> corresponding to popular software programs where people might want to
>>> import data from, so they can mirror those programs' data layout more
>>> easily.
>>> Si
>

[Si Chen-2]

Still, one issue I have is "exceptions handling": it seems that  
entity engine xml import would just rollback everything if one of the  
records to be imported is bad.  Isn't better (or standard) to have a  
system where the data could be imported and then each exception  
handled individually?

Si


On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:

>
> Yes, this looks like a good description of the general idea.
>
> -David
>
>
> Si Chen wrote:
>> David,
>> Yes, the idea was to create something that would be better for  
>> some applications (namely, the one it's built for) than others.
>> But I'm very curious about this entity-engine-transform-xml.  So  
>> is this idea to paste a big block of XML into <entity-engine-
>> transform-xml> file and then write the .FTL to do the  
>> transformation?  And then run xml import on that file?
>> And what about the freemarker template?  Does it fall under their  
>> "Declarative XML Processing" documentation here: http://
>> freemarker.org/docs/xgui_declarative.html  I looked at it briefly  
>> and recognized some of syntax.
>> Si
>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>>>
>>> Stepping back a bit and looking at this conceptually:
>>>
>>> What you are proposing would be an import format that would work  
>>> well for a certain piece of software that the data is coming from.
>>>
>>> In fact, the normalized data model of OFBiz is far easier to  
>>> transform data to than a more normalized form would be.
>>>
>>> Consider Software A and Software B that both have very  
>>> functionality specific, redundant, and generally non-normalized  
>>> data models. The cardinality of fields relative to a product may  
>>> be different, and incompatible, and they may treat similar  
>>> information in very different ways. Because the OFBiz model is  
>>> more flexible it may be possible to migrate A->OFBiz and B->OFBiz  
>>> but it may not be possible to migrate A->B or B->A.
>>>
>>> So, yes, something is needed to transform incoming data but  
>>> trying to create something that is less normalized will just make  
>>> it easier for some systems (those that are close to it) and far  
>>> more difficult for other systems (that have more conceptual  
>>> mismatches with it).
>>>
>>> This is basically the unavoidable law of de-normalization.
>>>
>>> As far as tools and such go, FreeMarker is actually a pretty good  
>>> XML transformation tool. The Entity Engine import stuff does  
>>> already support using FTL files along with an incoming XML file  
>>> to pre-transform the data. There is an example in the ofbiz/
>>> applications/ecommerce/data/DemoContent.xml file. Notice the root  
>>> element is "entity-engine-transform-xml" instead of "entity-
>>> engine-xml".
>>>
>>> -David
>>>
>>>
>>> Si Chen wrote:
>>>> Hi everybody.  I've been thinking about importing data into  
>>>> ofbiz.  One of the issues is that we have a highly normalized  
>>>> data model which would require extensive mapping from external  
>>>> systems.  Nevertheless, I think it is possible to create some  
>>>> standard "template" entities for importing data and thus create  
>>>> a standard practice and, eventually, standard tools for doing  
>>>> it.  Here's what I'm thinking, given in the context of importing  
>>>> product data:
>>>> 1.  Create a standard non-normalized "holding" entity which  
>>>> contains productId, quantity on hand, available to promise,  
>>>> average cost, reorder point.  Note that none of these are linked  
>>>> to any entity in ofbiz.
>>>> 2.  Add a timestamp fields to identify when the data was loaded  
>>>> into the "holding" entity and when it was imported into ofbiz.
>>>> 3.  Write a standard datafile xml import template which  
>>>> corresponds to this "holding" entity.
>>>> 4.  Write some processing script or service which takes new data  
>>>> from the holding entity and insert it into ofbiz.  For example,  
>>>> in this case it would be to create InventoryItem,  
>>>> ProductAverageCost, and maybe some AcctgTrans (maybe . . .)
>>>> It may be most efficient to write these holding entities  
>>>> corresponding to popular software programs where people might  
>>>> want to import data from, so they can mirror those programs'  
>>>> data layout more easily.
>>>> Si

[David E Jones-2]

The point of transaction management is to have a set of operations succeed or fail all together.

Would it really be easier with a large or small file to have all of the data go in except for the failures and then try to pick out all of the failures and fix and get those in individually in another file or by slimming down the original?

Another problem with not failing the entire file is if there are dependencies between the data or problems might be caused by having partial data in the system, then you'll have cascading failures....

So how would a partial failure be handled, or what sort of a process would it fit into?

-David


Si Chen wrote:

> Still, one issue I have is "exceptions handling": it seems that entity
> engine xml import would just rollback everything if one of the records
> to be imported is bad.  Isn't better (or standard) to have a system
> where the data could be imported and then each exception handled
> individually?
>
> Si
>
>
> On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:
>
>>
>> Yes, this looks like a good description of the general idea.
>>
>> -David
>>
>>
>> Si Chen wrote:
>>> David,
>>> Yes, the idea was to create something that would be better for some
>>> applications (namely, the one it's built for) than others.
>>> But I'm very curious about this entity-engine-transform-xml.  So is
>>> this idea to paste a big block of XML into
>>> <entity-engine-transform-xml> file and then write the .FTL to do the
>>> transformation?  And then run xml import on that file?
>>> And what about the freemarker template?  Does it fall under their
>>> "Declarative XML Processing" documentation here:
>>> http://freemarker.org/docs/xgui_declarative.html  I looked at it
>>> briefly and recognized some of syntax.
>>> Si
>>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>>>>
>>>> Stepping back a bit and looking at this conceptually:
>>>>
>>>> What you are proposing would be an import format that would work
>>>> well for a certain piece of software that the data is coming from.
>>>>
>>>> In fact, the normalized data model of OFBiz is far easier to
>>>> transform data to than a more normalized form would be.
>>>>
>>>> Consider Software A and Software B that both have very functionality
>>>> specific, redundant, and generally non-normalized data models. The
>>>> cardinality of fields relative to a product may be different, and
>>>> incompatible, and they may treat similar information in very
>>>> different ways. Because the OFBiz model is more flexible it may be
>>>> possible to migrate A->OFBiz and B->OFBiz but it may not be possible
>>>> to migrate A->B or B->A.
>>>>
>>>> So, yes, something is needed to transform incoming data but trying
>>>> to create something that is less normalized will just make it easier
>>>> for some systems (those that are close to it) and far more difficult
>>>> for other systems (that have more conceptual mismatches with it).
>>>>
>>>> This is basically the unavoidable law of de-normalization.
>>>>
>>>> As far as tools and such go, FreeMarker is actually a pretty good
>>>> XML transformation tool. The Entity Engine import stuff does already
>>>> support using FTL files along with an incoming XML file to
>>>> pre-transform the data. There is an example in the
>>>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice the
>>>> root element is "entity-engine-transform-xml" instead of
>>>> "entity-engine-xml".
>>>>
>>>> -David
>>>>
>>>>
>>>> Si Chen wrote:
>>>>> Hi everybody.  I've been thinking about importing data into ofbiz.  
>>>>> One of the issues is that we have a highly normalized data model
>>>>> which would require extensive mapping from external systems.  
>>>>> Nevertheless, I think it is possible to create some standard
>>>>> "template" entities for importing data and thus create a standard
>>>>> practice and, eventually, standard tools for doing it.  Here's what
>>>>> I'm thinking, given in the context of importing product data:
>>>>> 1.  Create a standard non-normalized "holding" entity which
>>>>> contains productId, quantity on hand, available to promise, average
>>>>> cost, reorder point.  Note that none of these are linked to any
>>>>> entity in ofbiz.
>>>>> 2.  Add a timestamp fields to identify when the data was loaded
>>>>> into the "holding" entity and when it was imported into ofbiz.
>>>>> 3.  Write a standard datafile xml import template which corresponds
>>>>> to this "holding" entity.
>>>>> 4.  Write some processing script or service which takes new data
>>>>> from the holding entity and insert it into ofbiz.  For example, in
>>>>> this case it would be to create InventoryItem, ProductAverageCost,
>>>>> and maybe some AcctgTrans (maybe . . .)
>>>>> It may be most efficient to write these holding entities
>>>>> corresponding to popular software programs where people might want
>>>>> to import data from, so they can mirror those programs' data layout
>>>>> more easily.
>>>>> Si
>

[BJ Freeman]

maybe consider breaking up the import so each line is a transaction then
dealing with the that line if there is an exception.
also have a flag weather to batch in one transaction or each line, from
the calling service.


Si Chen sent the following on 7/10/2006 10:15 AM:

> Still, one issue I have is "exceptions handling": it seems that entity
> engine xml import would just rollback everything if one of the records
> to be imported is bad.  Isn't better (or standard) to have a system
> where the data could be imported and then each exception handled
> individually?
>
> Si
>
>
> On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:
>
>>
>> Yes, this looks like a good description of the general idea.
>>
>> -David
>>
>>
>> Si Chen wrote:
>>> David,
>>> Yes, the idea was to create something that would be better for some
>>> applications (namely, the one it's built for) than others.
>>> But I'm very curious about this entity-engine-transform-xml.  So is
>>> this idea to paste a big block of XML into
>>> <entity-engine-transform-xml> file and then write the .FTL to do the
>>> transformation?  And then run xml import on that file?
>>> And what about the freemarker template?  Does it fall under their
>>> "Declarative XML Processing" documentation here:
>>> http://freemarker.org/docs/xgui_declarative.html  I looked at it
>>> briefly and recognized some of syntax.
>>> Si
>>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>>>>
>>>> Stepping back a bit and looking at this conceptually:
>>>>
>>>> What you are proposing would be an import format that would work
>>>> well for a certain piece of software that the data is coming from.
>>>>
>>>> In fact, the normalized data model of OFBiz is far easier to
>>>> transform data to than a more normalized form would be.
>>>>
>>>> Consider Software A and Software B that both have very functionality
>>>> specific, redundant, and generally non-normalized data models. The
>>>> cardinality of fields relative to a product may be different, and
>>>> incompatible, and they may treat similar information in very
>>>> different ways. Because the OFBiz model is more flexible it may be
>>>> possible to migrate A->OFBiz and B->OFBiz but it may not be possible
>>>> to migrate A->B or B->A.
>>>>
>>>> So, yes, something is needed to transform incoming data but trying
>>>> to create something that is less normalized will just make it easier
>>>> for some systems (those that are close to it) and far more difficult
>>>> for other systems (that have more conceptual mismatches with it).
>>>>
>>>> This is basically the unavoidable law of de-normalization.
>>>>
>>>> As far as tools and such go, FreeMarker is actually a pretty good
>>>> XML transformation tool. The Entity Engine import stuff does already
>>>> support using FTL files along with an incoming XML file to
>>>> pre-transform the data. There is an example in the
>>>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice the
>>>> root element is "entity-engine-transform-xml" instead of
>>>> "entity-engine-xml".
>>>>
>>>> -David
>>>>
>>>>
>>>> Si Chen wrote:
>>>>> Hi everybody.  I've been thinking about importing data into ofbiz.  
>>>>> One of the issues is that we have a highly normalized data model
>>>>> which would require extensive mapping from external systems.  
>>>>> Nevertheless, I think it is possible to create some standard
>>>>> "template" entities for importing data and thus create a standard
>>>>> practice and, eventually, standard tools for doing it.  Here's what
>>>>> I'm thinking, given in the context of importing product data:
>>>>> 1.  Create a standard non-normalized "holding" entity which
>>>>> contains productId, quantity on hand, available to promise, average
>>>>> cost, reorder point.  Note that none of these are linked to any
>>>>> entity in ofbiz.
>>>>> 2.  Add a timestamp fields to identify when the data was loaded
>>>>> into the "holding" entity and when it was imported into ofbiz.
>>>>> 3.  Write a standard datafile xml import template which corresponds
>>>>> to this "holding" entity.
>>>>> 4.  Write some processing script or service which takes new data
>>>>> from the holding entity and insert it into ofbiz.  For example, in
>>>>> this case it would be to create InventoryItem, ProductAverageCost,
>>>>> and maybe some AcctgTrans (maybe . . .)
>>>>> It may be most efficient to write these holding entities
>>>>> corresponding to popular software programs where people might want
>>>>> to import data from, so they can mirror those programs' data layout
>>>>> more easily.
>>>>> Si
>
>

[Si Chen-2]

I think what the entity engine tool does it the right way, and I'm  
not suggesting that we change it per se.  The entity engine tool is  
very good for importing seed and demo data as we have it.

There could be cases where a different process would be better.  For  
example, if you're importing a lot of initial data (say 10,000  
records), and not all of it is "clean" (say 10 bad records).  The  
entity engine tool would basically fail on each bad record, so you'd  
go back, fix one record, try all 10,000 again, and repeat that ten  
times.  It might be easier on the user if all 10,000 were read into a  
"temporary" table, the good rows imported, then the user fix the  
remaining bad rows in the database and insert those.  The trouble  
with this "process" is that it becomes kind of custom and is harder  
to standardize.  As I'm writing this I'm not sure what, if any of it,  
can be made part of the project.

Si

On Jul 10, 2006, at 10:27 AM, David E. Jones wrote:

>
> The point of transaction management is to have a set of operations  
> succeed or fail all together.
>
> Would it really be easier with a large or small file to have all of  
> the data go in except for the failures and then try to pick out all  
> of the failures and fix and get those in individually in another  
> file or by slimming down the original?
>
> Another problem with not failing the entire file is if there are  
> dependencies between the data or problems might be caused by having  
> partial data in the system, then you'll have cascading failures....
>
> So how would a partial failure be handled, or what sort of a  
> process would it fit into?
>
> -David
>
>
> Si Chen wrote:
>> Still, one issue I have is "exceptions handling": it seems that  
>> entity engine xml import would just rollback everything if one of  
>> the records to be imported is bad.  Isn't better (or standard) to  
>> have a system where the data could be imported and then each  
>> exception handled individually?
>> Si
>> On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:
>>>
>>> Yes, this looks like a good description of the general idea.
>>>
>>> -David
>>>
>>>
>>> Si Chen wrote:
>>>> David,
>>>> Yes, the idea was to create something that would be better for  
>>>> some applications (namely, the one it's built for) than others.
>>>> But I'm very curious about this entity-engine-transform-xml.  So  
>>>> is this idea to paste a big block of XML into <entity-engine-
>>>> transform-xml> file and then write the .FTL to do the  
>>>> transformation?  And then run xml import on that file?
>>>> And what about the freemarker template?  Does it fall under  
>>>> their "Declarative XML Processing" documentation here: http://
>>>> freemarker.org/docs/xgui_declarative.html  I looked at it  
>>>> briefly and recognized some of syntax.
>>>> Si
>>>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>>>>>
>>>>> Stepping back a bit and looking at this conceptually:
>>>>>
>>>>> What you are proposing would be an import format that would  
>>>>> work well for a certain piece of software that the data is  
>>>>> coming from.
>>>>>
>>>>> In fact, the normalized data model of OFBiz is far easier to  
>>>>> transform data to than a more normalized form would be.
>>>>>
>>>>> Consider Software A and Software B that both have very  
>>>>> functionality specific, redundant, and generally non-normalized  
>>>>> data models. The cardinality of fields relative to a product  
>>>>> may be different, and incompatible, and they may treat similar  
>>>>> information in very different ways. Because the OFBiz model is  
>>>>> more flexible it may be possible to migrate A->OFBiz and B-
>>>>> >OFBiz but it may not be possible to migrate A->B or B->A.
>>>>>
>>>>> So, yes, something is needed to transform incoming data but  
>>>>> trying to create something that is less normalized will just  
>>>>> make it easier for some systems (those that are close to it)  
>>>>> and far more difficult for other systems (that have more  
>>>>> conceptual mismatches with it).
>>>>>
>>>>> This is basically the unavoidable law of de-normalization.
>>>>>
>>>>> As far as tools and such go, FreeMarker is actually a pretty  
>>>>> good XML transformation tool. The Entity Engine import stuff  
>>>>> does already support using FTL files along with an incoming XML  
>>>>> file to pre-transform the data. There is an example in the  
>>>>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice  
>>>>> the root element is "entity-engine-transform-xml" instead of  
>>>>> "entity-engine-xml".
>>>>>
>>>>> -David
>>>>>
>>>>>
>>>>> Si Chen wrote:
>>>>>> Hi everybody.  I've been thinking about importing data into  
>>>>>> ofbiz.  One of the issues is that we have a highly normalized  
>>>>>> data model which would require extensive mapping from external  
>>>>>> systems.  Nevertheless, I think it is possible to create some  
>>>>>> standard "template" entities for importing data and thus  
>>>>>> create a standard practice and, eventually, standard tools for  
>>>>>> doing it.  Here's what I'm thinking, given in the context of  
>>>>>> importing product data:
>>>>>> 1.  Create a standard non-normalized "holding" entity which  
>>>>>> contains productId, quantity on hand, available to promise,  
>>>>>> average cost, reorder point.  Note that none of these are  
>>>>>> linked to any entity in ofbiz.
>>>>>> 2.  Add a timestamp fields to identify when the data was  
>>>>>> loaded into the "holding" entity and when it was imported into  
>>>>>> ofbiz.
>>>>>> 3.  Write a standard datafile xml import template which  
>>>>>> corresponds to this "holding" entity.
>>>>>> 4.  Write some processing script or service which takes new  
>>>>>> data from the holding entity and insert it into ofbiz.  For  
>>>>>> example, in this case it would be to create InventoryItem,  
>>>>>> ProductAverageCost, and maybe some AcctgTrans (maybe . . .)
>>>>>> It may be most efficient to write these holding entities  
>>>>>> corresponding to popular software programs where people might  
>>>>>> want to import data from, so they can mirror those programs'  
>>>>>> data layout more easily.
>>>>>> Si

[Daniel Kunkel]

Hi

In general I agree that failing for a whole file is appropriate, however
there is at least one table where partial completion would be
advantageous, and this feature would make it far easier to find and fix
importing errors in the future.

A while back I ran into this with one table in particular, I think it
was the product category table.

The table is self referencing creating a hierarchy, so even though all
of the entries were in the file to be imported, the import function
choked on the first item that didn't have a valid parent and causing the
whole file to fail.

Although it was possible to turn off referential integrity to import the
file, it could have also worked to extend the cyclical import function
to "write out" a new xml data file with only the rows that fail during
each pass.

If I understand correctly, the current cyclical import function will
load xml data files atomically, deleting those files that work and
retrying any files that are left again again until the number of files
does not decrease in a pass.

A revised system would perform the same process, but instead of working
on the file level, work on the level of the row. For each file, if a row
is not accepted, write a new import xml file with that row that could
not be imported.

Rather than stopping when a pass results in no fewer files, stop when a
pass was unsuccessful at adding any additional rows to the database.

I think the best part of this scheme is that it separates the needle
from the hay when importing data sets with errors. After running it on a
data set that has real referential integrity issue (the needle), all of
the good records (the hay) are removed. This helps the developer to
quickly find and fix the problems.

Thanks

--
Daniel

*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
Have a GREAT Day!

Daniel Kunkel           [hidden email]
BioWaves, LLC           http://www.BioWaves.com
14150 NE 20th St. Suite F1
Bellevue, WA 98007
800-734-3588    425-895-0050
http://www.Apartment-Pets.com  http://www.SatelliteRadioZone.com
http://www.Cards-Visa.com       http://www.ColorGlasses.com
*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-

[Adrian Crum]

How about having a utility that simply outputs all 10 errors WITHOUT importing
anything, so the ten can be fixed, then the entire file is imported.

Si Chen wrote:

> I think what the entity engine tool does it the right way, and I'm  not
> suggesting that we change it per se.  The entity engine tool is  very
> good for importing seed and demo data as we have it.
>
> There could be cases where a different process would be better.  For  
> example, if you're importing a lot of initial data (say 10,000  
> records), and not all of it is "clean" (say 10 bad records).  The  
> entity engine tool would basically fail on each bad record, so you'd  go
> back, fix one record, try all 10,000 again, and repeat that ten  times.  
> It might be easier on the user if all 10,000 were read into a  
> "temporary" table, the good rows imported, then the user fix the  
> remaining bad rows in the database and insert those.  The trouble  with
> this "process" is that it becomes kind of custom and is harder  to
> standardize.  As I'm writing this I'm not sure what, if any of it,  can
> be made part of the project.
>
> Si
>
> On Jul 10, 2006, at 10:27 AM, David E. Jones wrote:
>
>>
>> The point of transaction management is to have a set of operations  
>> succeed or fail all together.
>>
>> Would it really be easier with a large or small file to have all of  
>> the data go in except for the failures and then try to pick out all  
>> of the failures and fix and get those in individually in another  file
>> or by slimming down the original?
>>
>> Another problem with not failing the entire file is if there are  
>> dependencies between the data or problems might be caused by having  
>> partial data in the system, then you'll have cascading failures....
>>
>> So how would a partial failure be handled, or what sort of a  process
>> would it fit into?
>>
>> -David
>>
>>
>> Si Chen wrote:
>>
>>> Still, one issue I have is "exceptions handling": it seems that  
>>> entity engine xml import would just rollback everything if one of  
>>> the records to be imported is bad.  Isn't better (or standard) to  
>>> have a system where the data could be imported and then each  
>>> exception handled individually?
>>> Si
>>> On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:
>>>
>>>>
>>>> Yes, this looks like a good description of the general idea.
>>>>
>>>> -David
>>>>
>>>>
>>>> Si Chen wrote:
>>>>
>>>>> David,
>>>>> Yes, the idea was to create something that would be better for  
>>>>> some applications (namely, the one it's built for) than others.
>>>>> But I'm very curious about this entity-engine-transform-xml.  So  
>>>>> is this idea to paste a big block of XML into <entity-engine-
>>>>> transform-xml> file and then write the .FTL to do the  
>>>>> transformation?  And then run xml import on that file?
>>>>> And what about the freemarker template?  Does it fall under  their
>>>>> "Declarative XML Processing" documentation here: http://
>>>>> freemarker.org/docs/xgui_declarative.html  I looked at it  briefly
>>>>> and recognized some of syntax.
>>>>> Si
>>>>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>>>>>
>>>>>>
>>>>>> Stepping back a bit and looking at this conceptually:
>>>>>>
>>>>>> What you are proposing would be an import format that would  work
>>>>>> well for a certain piece of software that the data is  coming from.
>>>>>>
>>>>>> In fact, the normalized data model of OFBiz is far easier to  
>>>>>> transform data to than a more normalized form would be.
>>>>>>
>>>>>> Consider Software A and Software B that both have very  
>>>>>> functionality specific, redundant, and generally non-normalized  
>>>>>> data models. The cardinality of fields relative to a product  may
>>>>>> be different, and incompatible, and they may treat similar  
>>>>>> information in very different ways. Because the OFBiz model is  
>>>>>> more flexible it may be possible to migrate A->OFBiz and B- >OFBiz
>>>>>> but it may not be possible to migrate A->B or B->A.
>>>>>>
>>>>>> So, yes, something is needed to transform incoming data but  
>>>>>> trying to create something that is less normalized will just  make
>>>>>> it easier for some systems (those that are close to it)  and far
>>>>>> more difficult for other systems (that have more  conceptual
>>>>>> mismatches with it).
>>>>>>
>>>>>> This is basically the unavoidable law of de-normalization.
>>>>>>
>>>>>> As far as tools and such go, FreeMarker is actually a pretty  good
>>>>>> XML transformation tool. The Entity Engine import stuff  does
>>>>>> already support using FTL files along with an incoming XML  file
>>>>>> to pre-transform the data. There is an example in the  
>>>>>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice  
>>>>>> the root element is "entity-engine-transform-xml" instead of  
>>>>>> "entity-engine-xml".
>>>>>>
>>>>>> -David
>>>>>>
>>>>>>
>>>>>> Si Chen wrote:
>>>>>>
>>>>>>> Hi everybody.  I've been thinking about importing data into  
>>>>>>> ofbiz.  One of the issues is that we have a highly normalized  
>>>>>>> data model which would require extensive mapping from external  
>>>>>>> systems.  Nevertheless, I think it is possible to create some  
>>>>>>> standard "template" entities for importing data and thus  create
>>>>>>> a standard practice and, eventually, standard tools for  doing
>>>>>>> it.  Here's what I'm thinking, given in the context of  importing
>>>>>>> product data:
>>>>>>> 1.  Create a standard non-normalized "holding" entity which  
>>>>>>> contains productId, quantity on hand, available to promise,  
>>>>>>> average cost, reorder point.  Note that none of these are  linked
>>>>>>> to any entity in ofbiz.
>>>>>>> 2.  Add a timestamp fields to identify when the data was  loaded
>>>>>>> into the "holding" entity and when it was imported into  ofbiz.
>>>>>>> 3.  Write a standard datafile xml import template which  
>>>>>>> corresponds to this "holding" entity.
>>>>>>> 4.  Write some processing script or service which takes new  data
>>>>>>> from the holding entity and insert it into ofbiz.  For  example,
>>>>>>> in this case it would be to create InventoryItem,  
>>>>>>> ProductAverageCost, and maybe some AcctgTrans (maybe . . .)
>>>>>>> It may be most efficient to write these holding entities  
>>>>>>> corresponding to popular software programs where people might  
>>>>>>> want to import data from, so they can mirror those programs'  
>>>>>>> data layout more easily.
>>>>>>> Si
>
>
>

[Andrew Sykes]

Si,

What about setting a flag that fails the whole transaction at the end,
but allows the import to continue.

This way a report of the bad data could be assembled.

This looks like it would solve your problem and be an enhancement to the
general import functionality.

- Andrew

On Mon, 2006-07-10 at 12:36 -0700, Si Chen wrote:

> I think what the entity engine tool does it the right way, and I'm  
> not suggesting that we change it per se.  The entity engine tool is  
> very good for importing seed and demo data as we have it.
>
> There could be cases where a different process would be better.  For  
> example, if you're importing a lot of initial data (say 10,000  
> records), and not all of it is "clean" (say 10 bad records).  The  
> entity engine tool would basically fail on each bad record, so you'd  
> go back, fix one record, try all 10,000 again, and repeat that ten  
> times.  It might be easier on the user if all 10,000 were read into a  
> "temporary" table, the good rows imported, then the user fix the  
> remaining bad rows in the database and insert those.  The trouble  
> with this "process" is that it becomes kind of custom and is harder  
> to standardize.  As I'm writing this I'm not sure what, if any of it,  
> can be made part of the project.
>
> Si
>
> On Jul 10, 2006, at 10:27 AM, David E. Jones wrote:
>
> >
> > The point of transaction management is to have a set of operations  
> > succeed or fail all together.
> >
> > Would it really be easier with a large or small file to have all of  
> > the data go in except for the failures and then try to pick out all  
> > of the failures and fix and get those in individually in another  
> > file or by slimming down the original?
> >
> > Another problem with not failing the entire file is if there are  
> > dependencies between the data or problems might be caused by having  
> > partial data in the system, then you'll have cascading failures....
> >
> > So how would a partial failure be handled, or what sort of a  
> > process would it fit into?
> >
> > -David
> >
> >
> > Si Chen wrote:
> >> Still, one issue I have is "exceptions handling": it seems that  
> >> entity engine xml import would just rollback everything if one of  
> >> the records to be imported is bad.  Isn't better (or standard) to  
> >> have a system where the data could be imported and then each  
> >> exception handled individually?
> >> Si
> >> On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:
> >>>
> >>> Yes, this looks like a good description of the general idea.
> >>>
> >>> -David
> >>>
> >>>
> >>> Si Chen wrote:
> >>>> David,
> >>>> Yes, the idea was to create something that would be better for  
> >>>> some applications (namely, the one it's built for) than others.
> >>>> But I'm very curious about this entity-engine-transform-xml.  So  
> >>>> is this idea to paste a big block of XML into <entity-engine-
> >>>> transform-xml> file and then write the .FTL to do the  
> >>>> transformation?  And then run xml import on that file?
> >>>> And what about the freemarker template?  Does it fall under  
> >>>> their "Declarative XML Processing" documentation here: http://
> >>>> freemarker.org/docs/xgui_declarative.html  I looked at it  
> >>>> briefly and recognized some of syntax.
> >>>> Si
> >>>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
> >>>>>
> >>>>> Stepping back a bit and looking at this conceptually:
> >>>>>
> >>>>> What you are proposing would be an import format that would  
> >>>>> work well for a certain piece of software that the data is  
> >>>>> coming from.
> >>>>>
> >>>>> In fact, the normalized data model of OFBiz is far easier to  
> >>>>> transform data to than a more normalized form would be.
> >>>>>
> >>>>> Consider Software A and Software B that both have very  
> >>>>> functionality specific, redundant, and generally non-normalized  
> >>>>> data models. The cardinality of fields relative to a product  
> >>>>> may be different, and incompatible, and they may treat similar  
> >>>>> information in very different ways. Because the OFBiz model is  
> >>>>> more flexible it may be possible to migrate A->OFBiz and B-
> >>>>> >OFBiz but it may not be possible to migrate A->B or B->A.
> >>>>>
> >>>>> So, yes, something is needed to transform incoming data but  
> >>>>> trying to create something that is less normalized will just  
> >>>>> make it easier for some systems (those that are close to it)  
> >>>>> and far more difficult for other systems (that have more  
> >>>>> conceptual mismatches with it).
> >>>>>
> >>>>> This is basically the unavoidable law of de-normalization.
> >>>>>
> >>>>> As far as tools and such go, FreeMarker is actually a pretty  
> >>>>> good XML transformation tool. The Entity Engine import stuff  
> >>>>> does already support using FTL files along with an incoming XML  
> >>>>> file to pre-transform the data. There is an example in the  
> >>>>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice  
> >>>>> the root element is "entity-engine-transform-xml" instead of  
> >>>>> "entity-engine-xml".
> >>>>>
> >>>>> -David
> >>>>>
> >>>>>
> >>>>> Si Chen wrote:
> >>>>>> Hi everybody.  I've been thinking about importing data into  
> >>>>>> ofbiz.  One of the issues is that we have a highly normalized  
> >>>>>> data model which would require extensive mapping from external  
> >>>>>> systems.  Nevertheless, I think it is possible to create some  
> >>>>>> standard "template" entities for importing data and thus  
> >>>>>> create a standard practice and, eventually, standard tools for  
> >>>>>> doing it.  Here's what I'm thinking, given in the context of  
> >>>>>> importing product data:
> >>>>>> 1.  Create a standard non-normalized "holding" entity which  
> >>>>>> contains productId, quantity on hand, available to promise,  
> >>>>>> average cost, reorder point.  Note that none of these are  
> >>>>>> linked to any entity in ofbiz.
> >>>>>> 2.  Add a timestamp fields to identify when the data was  
> >>>>>> loaded into the "holding" entity and when it was imported into  
> >>>>>> ofbiz.
> >>>>>> 3.  Write a standard datafile xml import template which  
> >>>>>> corresponds to this "holding" entity.
> >>>>>> 4.  Write some processing script or service which takes new  
> >>>>>> data from the holding entity and insert it into ofbiz.  For  
> >>>>>> example, in this case it would be to create InventoryItem,  
> >>>>>> ProductAverageCost, and maybe some AcctgTrans (maybe . . .)
> >>>>>> It may be most efficient to write these holding entities  
> >>>>>> corresponding to popular software programs where people might  
> >>>>>> want to import data from, so they can mirror those programs'  
> >>>>>> data layout more easily.
> >>>>>> Si
>

--
Kind Regards
Andrew Sykes <[hidden email]>
Sykes Development Ltd
http://www.sykesdevelopment.com

[BJ Freeman]

Si I was not suggesting changing code.
I was suggesting a Wrapper that would would have a flag.
it would either submit the complete import as one batch or submit each
line as an individual batch.
When the wrapper got the the exception, it would print the line that
errored.



Si Chen sent the following on 7/10/2006 12:36 PM:

> I think what the entity engine tool does it the right way, and I'm not
> suggesting that we change it per se.  The entity engine tool is very
> good for importing seed and demo data as we have it.
>
> There could be cases where a different process would be better.  For
> example, if you're importing a lot of initial data (say 10,000 records),
> and not all of it is "clean" (say 10 bad records).  The entity engine
> tool would basically fail on each bad record, so you'd go back, fix one
> record, try all 10,000 again, and repeat that ten times.  It might be
> easier on the user if all 10,000 were read into a "temporary" table, the
> good rows imported, then the user fix the remaining bad rows in the
> database and insert those.  The trouble with this "process" is that it
> becomes kind of custom and is harder to standardize.  As I'm writing
> this I'm not sure what, if any of it, can be made part of the project.
>
> Si
>
> On Jul 10, 2006, at 10:27 AM, David E. Jones wrote:
>
>>
>> The point of transaction management is to have a set of operations
>> succeed or fail all together.
>>
>> Would it really be easier with a large or small file to have all of
>> the data go in except for the failures and then try to pick out all of
>> the failures and fix and get those in individually in another file or
>> by slimming down the original?
>>
>> Another problem with not failing the entire file is if there are
>> dependencies between the data or problems might be caused by having
>> partial data in the system, then you'll have cascading failures....
>>
>> So how would a partial failure be handled, or what sort of a process
>> would it fit into?
>>
>> -David
>>
>>
>> Si Chen wrote:
>>> Still, one issue I have is "exceptions handling": it seems that
>>> entity engine xml import would just rollback everything if one of the
>>> records to be imported is bad.  Isn't better (or standard) to have a
>>> system where the data could be imported and then each exception
>>> handled individually?
>>> Si
>>> On Jul 7, 2006, at 1:27 PM, David E. Jones wrote:
>>>>
>>>> Yes, this looks like a good description of the general idea.
>>>>
>>>> -David
>>>>
>>>>
>>>> Si Chen wrote:
>>>>> David,
>>>>> Yes, the idea was to create something that would be better for some
>>>>> applications (namely, the one it's built for) than others.
>>>>> But I'm very curious about this entity-engine-transform-xml.  So is
>>>>> this idea to paste a big block of XML into
>>>>> <entity-engine-transform-xml> file and then write the .FTL to do
>>>>> the transformation?  And then run xml import on that file?
>>>>> And what about the freemarker template?  Does it fall under their
>>>>> "Declarative XML Processing" documentation here:
>>>>> http://freemarker.org/docs/xgui_declarative.html  I looked at it
>>>>> briefly and recognized some of syntax.
>>>>> Si
>>>>> On Jul 7, 2006, at 12:03 PM, David E. Jones wrote:
>>>>>>
>>>>>> Stepping back a bit and looking at this conceptually:
>>>>>>
>>>>>> What you are proposing would be an import format that would work
>>>>>> well for a certain piece of software that the data is coming from.
>>>>>>
>>>>>> In fact, the normalized data model of OFBiz is far easier to
>>>>>> transform data to than a more normalized form would be.
>>>>>>
>>>>>> Consider Software A and Software B that both have very
>>>>>> functionality specific, redundant, and generally non-normalized
>>>>>> data models. The cardinality of fields relative to a product may
>>>>>> be different, and incompatible, and they may treat similar
>>>>>> information in very different ways. Because the OFBiz model is
>>>>>> more flexible it may be possible to migrate A->OFBiz and B->OFBiz
>>>>>> but it may not be possible to migrate A->B or B->A.
>>>>>>
>>>>>> So, yes, something is needed to transform incoming data but trying
>>>>>> to create something that is less normalized will just make it
>>>>>> easier for some systems (those that are close to it) and far more
>>>>>> difficult for other systems (that have more conceptual mismatches
>>>>>> with it).
>>>>>>
>>>>>> This is basically the unavoidable law of de-normalization.
>>>>>>
>>>>>> As far as tools and such go, FreeMarker is actually a pretty good
>>>>>> XML transformation tool. The Entity Engine import stuff does
>>>>>> already support using FTL files along with an incoming XML file to
>>>>>> pre-transform the data. There is an example in the
>>>>>> ofbiz/applications/ecommerce/data/DemoContent.xml file. Notice the
>>>>>> root element is "entity-engine-transform-xml" instead of
>>>>>> "entity-engine-xml".
>>>>>>
>>>>>> -David
>>>>>>
>>>>>>
>>>>>> Si Chen wrote:
>>>>>>> Hi everybody.  I've been thinking about importing data into
>>>>>>> ofbiz.  One of the issues is that we have a highly normalized
>>>>>>> data model which would require extensive mapping from external
>>>>>>> systems.  Nevertheless, I think it is possible to create some
>>>>>>> standard "template" entities for importing data and thus create a
>>>>>>> standard practice and, eventually, standard tools for doing it.  
>>>>>>> Here's what I'm thinking, given in the context of importing
>>>>>>> product data:
>>>>>>> 1.  Create a standard non-normalized "holding" entity which
>>>>>>> contains productId, quantity on hand, available to promise,
>>>>>>> average cost, reorder point.  Note that none of these are linked
>>>>>>> to any entity in ofbiz.
>>>>>>> 2.  Add a timestamp fields to identify when the data was loaded
>>>>>>> into the "holding" entity and when it was imported into ofbiz.
>>>>>>> 3.  Write a standard datafile xml import template which
>>>>>>> corresponds to this "holding" entity.
>>>>>>> 4.  Write some processing script or service which takes new data
>>>>>>> from the holding entity and insert it into ofbiz.  For example,
>>>>>>> in this case it would be to create InventoryItem,
>>>>>>> ProductAverageCost, and maybe some AcctgTrans (maybe . . .)
>>>>>>> It may be most efficient to write these holding entities
>>>>>>> corresponding to popular software programs where people might
>>>>>>> want to import data from, so they can mirror those programs' data
>>>>>>> layout more easily.
>>>>>>> Si
>
>

[Ray Barlow]

Si,

There is certainly room for enhancements in the area of more standard
data imports and exports as far as the user is concerned. For example
questions often arise like "Can I have a list of all products and their
prices?" and soon as you deliver a flattened version of the xml export
files in Excel they come back with "here's the new price list!"
expecting it to magically import back in. Obviously this has
complications as the data structure for OFBiz is very flexible and
something simple like price updates could be difficult with multiple
currencies, date ranges and pricing types but I'm sure for a large
percentage of users a product has a price in a currency so a simplified
import/export feature would be very useful.

I had thought this sort of feature would fall into the "DataIO" tab that
I added in the catalog application for the froogle merchant export:
  http://jira.undersunconsulting.com/browse/OFBIZ-886

Ray

Si Chen wrote:

> Hi everybody.  I've been thinking about importing data into ofbiz.  
> One of the issues is that we have a highly normalized data model which
> would require extensive mapping from external systems.  Nevertheless,
> I think it is possible to create some standard "template" entities for
> importing data and thus create a standard practice and, eventually,
> standard tools for doing it.  Here's what I'm thinking, given in the
> context of importing product data:
>
> 1.  Create a standard non-normalized "holding" entity which contains
> productId, quantity on hand, available to promise, average cost,
> reorder point.  Note that none of these are linked to any entity in
> ofbiz.
> 2.  Add a timestamp fields to identify when the data was loaded into
> the "holding" entity and when it was imported into ofbiz.
> 3.  Write a standard datafile xml import template which corresponds to
> this "holding" entity.
> 4.  Write some processing script or service which takes new data from
> the holding entity and insert it into ofbiz.  For example, in this
> case it would be to create InventoryItem, ProductAverageCost, and
> maybe some AcctgTrans (maybe . . .)
>
> It may be most efficient to write these holding entities corresponding
> to popular software programs where people might want to import data
> from, so they can mirror those programs' data layout more easily.
>
> Si
>

[Ray Barlow]

I did at one stage implement this in the webtools xml import routines
and you may find a patch either in the old Jira, but from vague memory,
more likely on the mailing list. Problem is though it was a couple of
years ago and the xml import routines have changed considerably since
then so it will not likely patch against the current SVN so would be no
good other than a quick reference for thoughts on a possible implementation.

It was certainly useful when pushing data in as problems would be left
in the XML file which you could review, modify and keep pushing as
needed until you got it all in, with referential integrity active.

Ray


Daniel Kunkel wrote:

> Hi
>
> In general I agree that failing for a whole file is appropriate, however
> there is at least one table where partial completion would be
> advantageous, and this feature would make it far easier to find and fix
> importing errors in the future.
>
> A while back I ran into this with one table in particular, I think it
> was the product category table.
>
> The table is self referencing creating a hierarchy, so even though all
> of the entries were in the file to be imported, the import function
> choked on the first item that didn't have a valid parent and causing the
> whole file to fail.
>
> Although it was possible to turn off referential integrity to import the
> file, it could have also worked to extend the cyclical import function
> to "write out" a new xml data file with only the rows that fail during
> each pass.
>
> If I understand correctly, the current cyclical import function will
> load xml data files atomically, deleting those files that work and
> retrying any files that are left again again until the number of files
> does not decrease in a pass.
>
> A revised system would perform the same process, but instead of working
> on the file level, work on the level of the row. For each file, if a row
> is not accepted, write a new import xml file with that row that could
> not be imported.
>
> Rather than stopping when a pass results in no fewer files, stop when a
> pass was unsuccessful at adding any additional rows to the database.
>
> I think the best part of this scheme is that it separates the needle
> from the hay when importing data sets with errors. After running it on a
> data set that has real referential integrity issue (the needle), all of
> the good records (the hay) are removed. This helps the developer to
> quickly find and fix the problems.
>
> Thanks
>
>  

[Ray Barlow]

This has to be a user driven option as there are always circumstances
where either total rollback is desired or exceptions just left out is
desired. Neither one of the two options is totally wrong but the user
needs to be offered and understand the implications of using each option.

Ray

> Si Chen sent the following on 7/10/2006 10:15 AM:
>> Still, one issue I have is "exceptions handling": it seems that
>> entity engine xml import would just rollback everything if one of the
>> records to be imported is bad.  Isn't better (or standard) to have a
>> system where the data could be imported and then each exception
>> handled individually?
>>
>> Si
>>