Data migration issue ... multi-valued Party attributes?

[David Gay-2]

Hi all!  We are in the process of migrating a legacy Access-based
solution into OFBiz, and have hit (and worked through) several snags
so far.  One in particular stands out, however, and we would
appreciate any advice or commentary.  (I don't know if this is a
developer-level question, so am following the advice was to post here
first.)

[A legacy-system table 'Table' will be denoted 'Legacy.Table', whereas
an OFBiz one will be designated 'OFBiz.Table'.  This notation is
extended for column names.]

We are given a list of product suppliers in Legacy.Supplier, each of
which is associated with a set of zero or more markup ranges in
Legacy.SupplierMarkup:
 - Legacy.SupplierMarkup.SupplierID
 - Legacy.SupplierMarkup.AmountRangeMin
 - Legacy.SupplierMarkup.AmountRangeMax
 - Legacy.SupplierMarkup.MarkupA
 - Legacy.SupplierMarkup.MarkupB

Sample rows:

Legacy.Supplier:
 - 'SA', 'Supplier A', ...
 - 'SB', 'Supplier B', ...
 - 'SC', 'Supplier C', ...
 ...

Legacy.SupplierMarkup:
 - 'SA', 0.0, 1000.0, 0.1, 0.2
 - 'SB', 0.0, 40.0, 0.3, 0.4
 - 'SB', 40.0, 800.0, 0.2, 0.4
 - 'SB', 800.0, 2500.0, 0.1, 0.3
 ...

We've mapped Legacy.Supplier to OFBiz.Party (with
.PartyTypeId='PARTY_GROUP'), but haven't found a way to associate
multiple related values to a Party without resorting to a key-encoding
hack, or an abuse of the Product/Feature mechanism.

Our proposed in-house solution is the addition of a new table to the
base OFBiz schema:

OFBiz.SupplierMarkup
 - SupplierPartyId:String [FK to Party.PartyId]
 - AmountRangeMin:Currency
 - AmountRangeMax:Currency
 - MarkupA:Numeric
 - MarkupB:Numeric

Alternatively, we could add an Enumeration type key (or other grouping
mechanism) to OFBiz.PartyAttribute.

In general, we would prefer to avoid making any schema changes
whatsoever, and would be quite glad to find out that we're simply
missing the obvious.  :-)

Any and all feedback is greatly appreciated!

[BJ Freeman]

ofbiz handles this with many entities.
if you hope to use ofbiz then I suggest you convert to the ofbiz model.
as far as schemas that is defined in the entityengine.xml,
            schema-name="OFBIZ"


David Gay sent the following on 7/11/2008 9:47 AM:

> Hi all!  We are in the process of migrating a legacy Access-based
> solution into OFBiz, and have hit (and worked through) several snags
> so far.  One in particular stands out, however, and we would
> appreciate any advice or commentary.  (I don't know if this is a
> developer-level question, so am following the advice was to post here
> first.)
>
> [A legacy-system table 'Table' will be denoted 'Legacy.Table', whereas
> an OFBiz one will be designated 'OFBiz.Table'.  This notation is
> extended for column names.]
>
> We are given a list of product suppliers in Legacy.Supplier, each of
> which is associated with a set of zero or more markup ranges in
> Legacy.SupplierMarkup:
>  - Legacy.SupplierMarkup.SupplierID
>  - Legacy.SupplierMarkup.AmountRangeMin
>  - Legacy.SupplierMarkup.AmountRangeMax
>  - Legacy.SupplierMarkup.MarkupA
>  - Legacy.SupplierMarkup.MarkupB
>
> Sample rows:
>
> Legacy.Supplier:
>  - 'SA', 'Supplier A', ...
>  - 'SB', 'Supplier B', ...
>  - 'SC', 'Supplier C', ...
>  ...
>
> Legacy.SupplierMarkup:
>  - 'SA', 0.0, 1000.0, 0.1, 0.2
>  - 'SB', 0.0, 40.0, 0.3, 0.4
>  - 'SB', 40.0, 800.0, 0.2, 0.4
>  - 'SB', 800.0, 2500.0, 0.1, 0.3
>  ...
>
> We've mapped Legacy.Supplier to OFBiz.Party (with
> .PartyTypeId='PARTY_GROUP'), but haven't found a way to associate
> multiple related values to a Party without resorting to a key-encoding
> hack, or an abuse of the Product/Feature mechanism.
>
> Our proposed in-house solution is the addition of a new table to the
> base OFBiz schema:
>
> OFBiz.SupplierMarkup
>  - SupplierPartyId:String [FK to Party.PartyId]
>  - AmountRangeMin:Currency
>  - AmountRangeMax:Currency
>  - MarkupA:Numeric
>  - MarkupB:Numeric
>
> Alternatively, we could add an Enumeration type key (or other grouping
> mechanism) to OFBiz.PartyAttribute.
>
> In general, we would prefer to avoid making any schema changes
> whatsoever, and would be quite glad to find out that we're simply
> missing the obvious.  :-)
>
> Any and all feedback is greatly appreciated!
>
>
>

[David Gay-2]

On Fri, Jul 11, 2008 at 12:54 PM, BJ Freeman <[hidden email]> wrote:
> ofbiz handles this with many entities.

That's good news, but which entities would you suggest?  I have yet to
find one that will do the job.

> if you hope to use ofbiz then I suggest you convert to the ofbiz model.

Yes, that is precisely what we're trying to do.  The problem I
described stems from having to import/store legacy data somewhere: our
client's business model uses multiple per-supplier markups which must
be stored in and used by the back end (OFBiz).

> as far as schemas that is defined in the entityengine.xml,
>            schema-name="OFBIZ"

We already have the OFBiz schema and sample data up and running on our
PostGreSQL server.  :-)

[BJ Freeman]

I am sure you can get someone here to help you for a fee.
My suggestion is you get familiar with the entities using the webtools.
https://demo.hotwaxmedia.com/webtools/control/entitymaint (online)
or
https://localhost:8443/webtools/control/entitymaint (on your machine)
then create your mapping from the access to ofbiz.
you can import either by adding a accessdb(odbc java driver) connected
to ofbiz or export the data as flat files and use the
/framework/datafile to import.



David Gay sent the following on 7/11/2008 10:24 AM:

> On Fri, Jul 11, 2008 at 12:54 PM, BJ Freeman <[hidden email]> wrote:
>> ofbiz handles this with many entities.
>
> That's good news, but which entities would you suggest?  I have yet to
> find one that will do the job.
>
>> if you hope to use ofbiz then I suggest you convert to the ofbiz model.
>
> Yes, that is precisely what we're trying to do.  The problem I
> described stems from having to import/store legacy data somewhere: our
> client's business model uses multiple per-supplier markups which must
> be stored in and used by the back end (OFBiz).
>
>> as far as schemas that is defined in the entityengine.xml,
>>            schema-name="OFBIZ"
>
> We already have the OFBiz schema and sample data up and running on our
> PostGreSQL server.  :-)
>
>
>

[Adrian Crum]

Take a look at The Data Model Resource Book, chapter 4, Agreements.

-Adrian

David Gay wrote:

> Hi all!  We are in the process of migrating a legacy Access-based
> solution into OFBiz, and have hit (and worked through) several snags
> so far.  One in particular stands out, however, and we would
> appreciate any advice or commentary.  (I don't know if this is a
> developer-level question, so am following the advice was to post here
> first.)
>
> [A legacy-system table 'Table' will be denoted 'Legacy.Table', whereas
> an OFBiz one will be designated 'OFBiz.Table'.  This notation is
> extended for column names.]
>
> We are given a list of product suppliers in Legacy.Supplier, each of
> which is associated with a set of zero or more markup ranges in
> Legacy.SupplierMarkup:
>  - Legacy.SupplierMarkup.SupplierID
>  - Legacy.SupplierMarkup.AmountRangeMin
>  - Legacy.SupplierMarkup.AmountRangeMax
>  - Legacy.SupplierMarkup.MarkupA
>  - Legacy.SupplierMarkup.MarkupB
>
> Sample rows:
>
> Legacy.Supplier:
>  - 'SA', 'Supplier A', ...
>  - 'SB', 'Supplier B', ...
>  - 'SC', 'Supplier C', ...
>  ...
>
> Legacy.SupplierMarkup:
>  - 'SA', 0.0, 1000.0, 0.1, 0.2
>  - 'SB', 0.0, 40.0, 0.3, 0.4
>  - 'SB', 40.0, 800.0, 0.2, 0.4
>  - 'SB', 800.0, 2500.0, 0.1, 0.3
>  ...
>
> We've mapped Legacy.Supplier to OFBiz.Party (with
> .PartyTypeId='PARTY_GROUP'), but haven't found a way to associate
> multiple related values to a Party without resorting to a key-encoding
> hack, or an abuse of the Product/Feature mechanism.
>
> Our proposed in-house solution is the addition of a new table to the
> base OFBiz schema:
>
> OFBiz.SupplierMarkup
>  - SupplierPartyId:String [FK to Party.PartyId]
>  - AmountRangeMin:Currency
>  - AmountRangeMax:Currency
>  - MarkupA:Numeric
>  - MarkupB:Numeric
>
> Alternatively, we could add an Enumeration type key (or other grouping
> mechanism) to OFBiz.PartyAttribute.
>
> In general, we would prefer to avoid making any schema changes
> whatsoever, and would be quite glad to find out that we're simply
> missing the obvious.  :-)
>
> Any and all feedback is greatly appreciated!
>

[David Gay-2]

On Fri, Jul 11, 2008 at 2:08 PM, Adrian Crum <[hidden email]> wrote:
> Take a look at The Data Model Resource Book, chapter 4, Agreements.

Aha!  That's the spot we were looking for, thanks a bunch!  :-)

[David Gay-2]

> I am sure you can get someone here to help you for a fee.

We may do that; a sanity-check of our progress from a seasoned
user/developer would be a good start.  Is there a process or etiquette
in place for such things?

> My suggestion is you get familiar with the entities using the webtools.
> https://demo.hotwaxmedia.com/webtools/control/entitymaint (online)
> or
> https://localhost:8443/webtools/control/entitymaint (on your machine)
> then create your mapping from the access to ofbiz.

We've been swimming (well okay, dog-paddling) in that space for
several weeks, now.  :-)

For context, we've mapped roughly 60% of the 100+ legacy system's
tables so far, which accounts for ~90% of the total row count (~1M
combined rows in our sample client dataset).

> you can import either by adding a accessdb(odbc java driver) connected
> to ofbiz or export the data as flat files and use the
> /framework/datafile to import.

Our migration strategy has a mapping stage and an ETL process
development stage.  We've settled on (shallow) XML as our
Access-export format, and an in-house mapping transformer (Java) which
outputs SQL scripts for data-insertion into the OFBiz tables.  I'll
pass on the advice about importing via /framework/datafile and get
back to my mapping task.  ;-)

Thank you all for the prompt and useful replies!

[BJ Freeman]

No specific process or etiquette I know, but others may know of it.
you can email someone on the mailing list that seems to know what they
are talking about or you can use the providers list.

you have done pretty good, for only being at it for several weeks.

I have found that doing flat file imports from another system takes more
time than if you connect the DB and write entities and services to
import. But that is me.
The reason is that then you leverage ofbiz services and the not much
talked about ECA take care of keeping things neatly connected.

David Gay sent the following on 7/11/2008 11:58 AM:

>> I am sure you can get someone here to help you for a fee.
>
> We may do that; a sanity-check of our progress from a seasoned
> user/developer would be a good start.  Is there a process or etiquette
> in place for such things?
>
>> My suggestion is you get familiar with the entities using the webtools.
>> https://demo.hotwaxmedia.com/webtools/control/entitymaint (online)
>> or
>> https://localhost:8443/webtools/control/entitymaint (on your machine)
>> then create your mapping from the access to ofbiz.
>
> We've been swimming (well okay, dog-paddling) in that space for
> several weeks, now.  :-)
>
> For context, we've mapped roughly 60% of the 100+ legacy system's
> tables so far, which accounts for ~90% of the total row count (~1M
> combined rows in our sample client dataset).
>
>> you can import either by adding a accessdb(odbc java driver) connected
>> to ofbiz or export the data as flat files and use the
>> /framework/datafile to import.
>
> Our migration strategy has a mapping stage and an ETL process
> development stage.  We've settled on (shallow) XML as our
> Access-export format, and an in-house mapping transformer (Java) which
> outputs SQL scripts for data-insertion into the OFBiz tables.  I'll
> pass on the advice about importing via /framework/datafile and get
> back to my mapping task.  ;-)
>
> Thank you all for the prompt and useful replies!
>
>
>

[David E Jones]

David,

The best way to communicate the data concepts you'd like help mapping  
is to use "data statement" sentences to describe what each data  
element (entity, field, etc) means and how it relates to other things.  
Don't try to match it up to your current data model, try to describe  
it in terms of how the data is used.

Based on what you've laid out I don't know where best to put this  
data. For example, is it related only to a supplier, or also to a  
product? What do you do with this data? There may or may not be  
something similar around already, and with that information we can  
talk about that.

-David J.


On Jul 11, 2008, at 10:47 AM, David Gay wrote:

> Hi all!  We are in the process of migrating a legacy Access-based
> solution into OFBiz, and have hit (and worked through) several snags
> so far.  One in particular stands out, however, and we would
> appreciate any advice or commentary.  (I don't know if this is a
> developer-level question, so am following the advice was to post here
> first.)
>
> [A legacy-system table 'Table' will be denoted 'Legacy.Table', whereas
> an OFBiz one will be designated 'OFBiz.Table'.  This notation is
> extended for column names.]
>
> We are given a list of product suppliers in Legacy.Supplier, each of
> which is associated with a set of zero or more markup ranges in
> Legacy.SupplierMarkup:
> - Legacy.SupplierMarkup.SupplierID
> - Legacy.SupplierMarkup.AmountRangeMin
> - Legacy.SupplierMarkup.AmountRangeMax
> - Legacy.SupplierMarkup.MarkupA
> - Legacy.SupplierMarkup.MarkupB
>
> Sample rows:
>
> Legacy.Supplier:
> - 'SA', 'Supplier A', ...
> - 'SB', 'Supplier B', ...
> - 'SC', 'Supplier C', ...
> ...
>
> Legacy.SupplierMarkup:
> - 'SA', 0.0, 1000.0, 0.1, 0.2
> - 'SB', 0.0, 40.0, 0.3, 0.4
> - 'SB', 40.0, 800.0, 0.2, 0.4
> - 'SB', 800.0, 2500.0, 0.1, 0.3
> ...
>
> We've mapped Legacy.Supplier to OFBiz.Party (with
> .PartyTypeId='PARTY_GROUP'), but haven't found a way to associate
> multiple related values to a Party without resorting to a key-encoding
> hack, or an abuse of the Product/Feature mechanism.
>
> Our proposed in-house solution is the addition of a new table to the
> base OFBiz schema:
>
> OFBiz.SupplierMarkup
> - SupplierPartyId:String [FK to Party.PartyId]
> - AmountRangeMin:Currency
> - AmountRangeMax:Currency
> - MarkupA:Numeric
> - MarkupB:Numeric
>
> Alternatively, we could add an Enumeration type key (or other grouping
> mechanism) to OFBiz.PartyAttribute.
>
> In general, we would prefer to avoid making any schema changes
> whatsoever, and would be quite glad to find out that we're simply
> missing the obvious.  :-)
>
> Any and all feedback is greatly appreciated!

[David Gay-2]

> The best way to communicate the data concepts you'd like help mapping is to
> use "data statement" sentences to describe what each data element (entity,
> field, etc) means and how it relates to other things. Don't try to match it
> up to your current data model, try to describe it in terms of how the data
> is used.

Good advice, thanks.  :-)

> Based on what you've laid out I don't know where best to put this data. For
> example, is it related only to a supplier, or also to a product? What do you
> do with this data? There may or may not be something similar around already,
> and with that information we can talk about that.

While it may not remain the ultimate location (we're going to iterate
the data model for our application), the Agreement and
AgreementAttribute tables are a good, logical fit for that particular
aspect of our legacy data.  In other words: problem solved!  =]

.Dave