Question regarding Catalog management in Ofbiz

[Ruth Hoffman-2]

David E Jones wrote:
> On Apr 30, 2010, at 12:41 PM, Ruth Hoffman wrote:
>
>  
>> In my world, "free" is the same as saying something has "no value".
>>    
>
> What a sad world that must be.
>  
Actually, David, my world isn't the least bit sad. Its really a pretty
nice place to be. I wouldn't have it any other way. Each and every day
it just gets better. I am so grateful for all that I have including the
ability to choose the work I do and to place my own value on those
efforts. I never work for "free". I may not always be paid with money,
but that doesn't mean I work for "free".
> Personally I've found that the most productive, innovative, and pleasant efforts I've been involved with have been unpaid. Along with that: I like working, I just hate working for money because those offering the money can (and often do) make the work a living hell in all manner of ways that not only make the work unpleasant, but also unproductive and in many cases altogether unsuccessful.
>  
But that has nothing to do with working for "free". It has everything to
do with managing your expectations.
> Thank goodness I can create things of value to myself and others for free, if not I'd probably go crazy. Also thank goodness for good clients, those few and far between (especially with current economic pressures), may we all find more such opportunities and maybe we would all be more civil here as well.
>  
Maybe. Tomorrow is another day. Can't wait to see what surprises it brings!
Regards,
Ruth
> -David
>
>
>  

[Deyan Tsvetanov-2]

Actually it would be better to use EntityListIterator ( Delegator.find()
method ) instead of a List. Using a list would load 1,000,000 categories
into the heap which is not good :)



On Fri, 2010-04-30 at 04:30 -0700, BJ Freeman wrote:

> to address your basic concern the solution would be to load only the
> ID's instead of the complete data for each row.
> this is a minor change to the find call.
> give that a try.
> List<GenericValue> org.ofbiz.entity.Delegator.findList(String
> entityName, EntityCondition entityCondition, Set<String> fieldsToSelect,
> List<String> orderBy, EntityFindOptions findOptions, boolean useCache)
>
>
> =========================
> BJ Freeman
> http://bjfreeman.elance.com
> Strategic Power Office with Supplier Automation <http://www.businessesnetwork.com/automation/viewforum.php?f=93>
> Specialtymarket.com <http://www.specialtymarket.com/>
>
> Systems Integrator-- Glad to Assist
>
> Chat  Y! messenger: bjfr33man
> Linkedin
> <http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro>
>
>
> Michal Cukierman sent the following on 4/29/2010 12:30 PM:
> > I am a PLM consultant interested in Open source projects. I am interested in
> > Ofbiz as a application framework, as well as the Fully flaged ERP system. I
> > have to say that it has a lot of great solutions (i.e. modularity and small
> > amount of code).
> > Please forgive me if my question will be stupid as I have downloaded the
> > Ofbiz sources today and I am quite new to the subject.
> >
> > So lets go to the point:
> >
> > In the line 96 (around, depending on svn version) of the class
> > CategoryWorker of the products component there is a line:
> >
> > ..
> > Collection<GenericValue> allCategories =
> > delegator.findList("ProductCategory", null, null, null, null, false);
> > ..
> >
> > Before I started to implement my solution using ofbiz I wanted to make some
> > test, especialy on large amounts of data.
> > So far I have got 1 000 000 categories in my database and after
> > selecing: Choose
> > Top Category <https://localhost:8443/catalog/control/ChooseTopCategory>action
> > from the catalog management I run out of memory (4 GB of Xmx).
> >
> > I was considering why do we have to select all ( one milion!) records from
> > the database if we want to choose only several of them?
> >
> > I have seen that you use PRODUCT_CATEGORY_ROLLUP table to keep the
> > parent-child relations between categories. I understand that there is a need
> > of having many to many relation.
> > But besides we also have Many to one relation: primary parent category -
> > child categories, shouldn't we consider those as well while selecting the
> > ROOT objects?
> >
> >
> > My solution was preaty trivial I have changed the line 96 in the mentioned
> > class to:
> >
> > Collection<GenericValue> allCategories = delegator.findList
> >             ("ProductCategory",
> > EntityCondition.makeCondition("PRIMARY_PARENT_CATEGORY_ID", null), null,
> > null, null, false);
> >
> > Is it a good solution? If it's not - I will find another way (some join
> > etc.), because from my point of view - selecting one milion objects if we
> > need a couple of them can be done by RDMS in almost realtime.
> >
> > Please correct me if I am wrong. I can be wrong.
> >
> >
> > Best Regards,
> > Michał Cukierman
> >
>
>

[Deyan Tsvetanov-2]

Yes, it is a good example. I've done a project with car parts so i'm
pretty familiar with the millions of categories :)

However there is a lot of room for optimization. In ofbiz you could add
a product into many categories so you don't have to create a category
"Brake-wheel hub" many times for each brand.

One additional feature of ofbiz that could be used here is the product
manufacturer party - manufacturer_party_id, so you don't have to create
each vendor as a category. Of course you are free to do it if it's
easier for you :)

Ofbiz data model is actually perfect for car or any types of parts,
because it's very flexible. You could associate products and using
different associations, for example: compatible with, replaceable by,
required or anything else ( product_assoc_type table )

Cheers,
Deyan


On Fri, 2010-04-30 at 13:02 +0200, Michal Cukierman wrote:

> Sorry for sending you another message but I have just found an interesting
> link:
>
> http://allegro.pl/category_map.php
>
> It's a category map.
>
> If you want to use OFBiz in real commercial systems (not for SMB industry)
> you need to face such use cases.
>
> Regards,
> Michał Cukierman
>
> W dniu 30 kwietnia 2010 12:56 użytkownik Michal Cukierman
> <[hidden email]>napisał:
>
> > Hi,
> >
> > Yes I am sure that I am talking about categories. See the example of
> > rockauto or any other automotive ecommerce shop:
> > 200 brands x 20 years x 10 models x 5 engine types x 12 part groups
> > (interior, engine, exterior, accesories)
> > 200 x 20 x 10 x 5 x 12 = 2 400 000.
> > Its only an example. Software like Alldata or PartExpert generates much
> > more cateogies. What about American car parts association?
> >
> > Anyway this is not a case. It's not a point to discuss.
> > Please consider the application with 10 000 categories and select * from
> > statement running concurently. Please try to update some category with
> > ReadCommited isolation level (As you use as default).
> >
> > This disscusion does not make sens. My question was:
> > - What is the way of using primary_parent_category?
> > - Is my fix ok?
> > - In addition I pointed you a mistake in your code (which kills my
> > application and is a performance issue)
> > - I said that i will prepare a patch as soon as I will find whats around
> >
> > Whats you response:
> > - read our books that could be bought here
> > - your fix is a bad one, no suggestion how to improve it
> > - our code is a good one and we don't need to change it. As ofbiz works on
> > my laptop
> > - after such constructive responses I don't really want to sent you my
> > patches (just to show you, not saying that you are going to commit it)
> >
> > All I wanted to do from my side is to give you my input and a little help.
> > After all, I still can't understand why selecting even 1000 record from
> > database if we need to use 10 is a good solution.
> >
> > Regards,
> > Michał
> >
> >
> >
> >
> > W dniu 30 kwietnia 2010 12:06 użytkownik BJ Freeman <[hidden email]>napisał:
> >
> > are you sure you are not referring to 1 million products.
> >> I run in the real world over 100,000 products.
> >> I have approx 18 top categories with 3-10 levels of sub categories.
> >> if your see 1,000,000 categories that way then it is feasible.
> >> I use automated updating from my suppliers to keep thing up-to-date.
> >> these updates happen every 24 for prices new and obsolete products.
> >> availability is as often as 15 min.
> >> The average Cpu usage during these operations is about 15% of a 1.6ghz
> >> Cpu with enough RAM to not use the swap file. the peak cpu usage is
> >> about 75% when the scheduled services and large imports (1 gig data) are
> >> happening.
> >>
> >>
> >> =========================
> >> BJ Freeman
> >> http://bjfreeman.elance.com
> >> Strategic Power Office with Supplier Automation <
> >> http://www.businessesnetwork.com/automation/viewforum.php?f=93>
> >> Specialtymarket.com <http://www.specialtymarket.com/>
> >>
> >> Systems Integrator-- Glad to Assist
> >>
> >> Chat  Y! messenger: bjfr33man
> >> Linkedin
> >> <
> >> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
> >> >
> >>
> >>
> >> Michal Cukierman sent the following on 4/30/2010 1:51 AM:
> >> > Hello,
> >> >
> >> > Thank you for your quick response. Regarding your question about real
> >> world
> >> > applications:
> >> > Ecommerce:
> >> > http://www.rockauto.com/
> >> > http://allegro.pl/
> >> >
> >> > There are applications that holds > 1 000 000 categories.
> >> > In a PLM industry it's also very common to have a couple of milions of
> >> > objects (consider the complexity of a train or a plain for example).
> >> >
> >> > The point of my post was not a fix, but the implementation of  the find
> >> root
> >> > category method.
> >> > I think that you should avoid such a code. You have got great database
> >> layer
> >> > (great possibility to improve performance)  so it would be great to
> >> utilize
> >> > it as much as it's possible.
> >> > I really like Ofbiz application thats why I try to give you my input.
> >> >
> >> > Once again thank you for the response and for the link you send me. I
> >> will
> >> > come back with the better solution after studing  the datamodel.
> >> >
> >> > Regards,
> >> > Michał Cukierman
> >> >
> >> >
> >> >
> >> > W dniu 30 kwietnia 2010 10:11 użytkownik Jacques Le Roux <
> >> > [hidden email]> napisał:
> >> >
> >> >> For instanc
> >> >
> >>
> >>
> >>
> >

[Michal Cukierman]

Thank you Deyan for your post,

Actualy those are very precious sugestions. Thank you very much for that. I
am now investigating possible ways for solving my concerns, Ofbiz is one of
tools that are pretented to be the best. There is one problem regarding
creation of complex categories structure. To be more precise: In my system I
am going to import all categories structures automaticly from suppliers.
Those can differ from each other. Your solution would be great if there will
be one model to refer. I tried to use structures from automotive software
like PartEpert or AllData. Unfortunately I am unable to gather data from
those databases. What is more: different part manufactures use different
numbering schema (yes, I am PLM consultant:)). Have you found a way of
modeling your structure, is there an open database keeping all those data?
I assume that most of companies do it manualy. In my case it could be
dificult, because of automatic  import process.

The best thing would be to have a categories structure based on Parts
numbers. We would be able to find replacements, variants and uses for each
part. As far I have not found a good solution for that.

Regarding EntityListIterator, if its an implementation of Lazy loading
(fetch data 'just in time'), it could be a problem as well (1 000 000 calls
to a database is slow as well - but it will not kill my app). I see the
following solutions for solwing the problem:

1) join on rollup and Product category.
2) select product categories where parent category id is not in ( select
from rollup ).

I have not checked that as far as I don't have time for this at the moment.
Anyway, single query is the best solution. Databases are optimised for
operations like that and we should use it.


Regards,
Michał

2010/5/1 Deyan Tsvetanov <[hidden email]>

> associations, for example: compatible with, replaceable by,
> required or anything else ( product_assoc_type table )
>

[BJ Freeman]

the rollup is the complexity but the actual category is entered onces.
for imports I have a entity that while importing converts the supplier
categories to the once you use in ofbiz.
this allows you to have standard categories for all suppliers, in the
catalogs used for customer ordering.

each supplier has their own catalog with their categories for ordering
from them (see supplier_product)
then there is/are catalog(s) for the ecommerce(customer) ordering.

I implemented this for a Autoparts store where their system was updated
nightly with inventory prices and new or obsolete parts.

Association of parts, has nothing to do with categories but products.
categories are used for quick look up of a product. once you at the
product then association to other products for compatible, replacement
are used.
from there then the suppliers for that product is looked up.

at least that is the way I implemented it.

=========================
BJ Freeman
http://bjfreeman.elance.com
Strategic Power Office with Supplier Automation <http://www.businessesnetwork.com/automation/viewforum.php?f=93>
Specialtymarket.com <http://www.specialtymarket.com/>

Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man
Linkedin
<http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro>


Michal Cukierman sent the following on 5/1/2010 12:08 PM:

> Thank you Deyan for your post,
>
> Actualy those are very precious sugestions. Thank you very much for that. I
> am now investigating possible ways for solving my concerns, Ofbiz is one of
> tools that are pretented to be the best. There is one problem regarding
> creation of complex categories structure. To be more precise: In my system I
> am going to import all categories structures automaticly from suppliers.
> Those can differ from each other. Your solution would be great if there will
> be one model to refer. I tried to use structures from automotive software
> like PartEpert or AllData. Unfortunately I am unable to gather data from
> those databases. What is more: different part manufactures use different
> numbering schema (yes, I am PLM consultant:)). Have you found a way of
> modeling your structure, is there an open database keeping all those data?
> I assume that most of companies do it manualy. In my case it could be
> dificult, because of automatic  import process.
>
> The best thing would be to have a categories structure based on Parts
> numbers. We would be able to find replacements, variants and uses for each
> part. As far I have not found a good solution for that.
>
> Regarding EntityListIterator, if its an implementation of Lazy loading
> (fetch data 'just in time'), it could be a problem as well (1 000 000 calls
> to a database is slow as well - but it will not kill my app). I see the
> following solutions for solwing the problem:
>
> 1) join on rollup and Product category.
> 2) select product categories where parent category id is not in ( select
> from rollup ).
>
> I have not checked that as far as I don't have time for this at the moment.
> Anyway, single query is the best solution. Databases are optimised for
> operations like that and we should use it.
>
>
> Regards,
> Michał
>
> 2010/5/1 Deyan Tsvetanov <[hidden email]>
>
>> associations, for example: compatible with, replaceable by,
>> required or anything else ( product_assoc_type table )
>>
>

[Deyan Tsvetanov-2]

Hi Michal,

I agree, that the categories part is tricky. In the legacy system
database you will most probably have the same category many times with
different ID for each vendor. If you want to use the model with one
category for all vendors than you will need to maintain mapping when you
do the import process. This is some labor, I agree. However you should
calculate the pros and cons. Should you perform this labor you will get
some precious features. The end users will be able to search for a part
in the "Brake-wheel hub" category which is compatible with BMW 330XD
model year 2010 for example. End users wouldn't care about the vendor
but the compatibility, because most of the car parts are often
compatible with other brands and car models. The customers need a car
part that works for them :)
Do you know a software called TECDOC ? Have a look there, you'll get
some ideas.

About EntityListIterator: it is a very very very recommended solution
over the generic List. When you make a query which gets 1,000,000
results the database would create a cursor for you which contains the
results. Using the EntityListIterator you would get the results one by
one. You are not doing anything illegal as this is the model in JDBC as
well - the java.sql.ResultSet class is also an interator. If you don't
believe me you could perform a simple test - create 1,000,000 records
into a table and fetch them in a List, do it 10 times without restarting
the JVM and monitor the heap usage. You have a case where the memory
usage depends on the number of the results - a classical example of
memory leak :)

Fetching the results in a List is convenient for small result sets -
like nomenclature tables. All the rest should be done using
EntityListIterator.


Regards,
Deyan


On Sat, 2010-05-01 at 21:08 +0200, Michal Cukierman wrote:

> Thank you Deyan for your post,
>
> Actualy those are very precious sugestions. Thank you very much for that. I
> am now investigating possible ways for solving my concerns, Ofbiz is one of
> tools that are pretented to be the best. There is one problem regarding
> creation of complex categories structure. To be more precise: In my system I
> am going to import all categories structures automaticly from suppliers.
> Those can differ from each other. Your solution would be great if there will
> be one model to refer. I tried to use structures from automotive software
> like PartEpert or AllData. Unfortunately I am unable to gather data from
> those databases. What is more: different part manufactures use different
> numbering schema (yes, I am PLM consultant:)). Have you found a way of
> modeling your structure, is there an open database keeping all those data?
> I assume that most of companies do it manualy. In my case it could be
> dificult, because of automatic  import process.
>
> The best thing would be to have a categories structure based on Parts
> numbers. We would be able to find replacements, variants and uses for each
> part. As far I have not found a good solution for that.
>
> Regarding EntityListIterator, if its an implementation of Lazy loading
> (fetch data 'just in time'), it could be a problem as well (1 000 000 calls
> to a database is slow as well - but it will not kill my app). I see the
> following solutions for solwing the problem:
>
> 1) join on rollup and Product category.
> 2) select product categories where parent category id is not in ( select
> from rollup ).
>
> I have not checked that as far as I don't have time for this at the moment.
> Anyway, single query is the best solution. Databases are optimised for
> operations like that and we should use it.
>
>
> Regards,
> Michał
>
> 2010/5/1 Deyan Tsvetanov <[hidden email]>
>
> > associations, for example: compatible with, replaceable by,
> > required or anything else ( product_assoc_type table )
> >

[Michal Cukierman]

Thank you for your advices. I will save days of wark thanks to you.

@TECDOC:
Is a great software, just what I wanted to have. The pricelist is good as
well. Exposed web serwise or standalone DVD catalogue is all I need. I will
investigate this solution in a days. What is very important for my at the
moment is USA market support (I have seen some supliers so far). The another
thing to investigate is the licence. Is it legal to use it in exposed web
applications?

@Regarding top categories:
I have implemented SQL function for doing the magic, it's quite simple join
(as expected to be). The performance on my huge database is excelent (just
in time, after first run results are cached).
            SELECT D.PRODUCT_CATEGORY_ID FROM PRODUCT_CATEGORY_ROLLUP P
RIGHT JOIN
            PRODUCT_CATEGORY D ON P.PRODUCT_CATEGORY_ID and
D.PRODUCT_CATEGORY_ID
            WHERE PARENT_PRODUCT_CATEGORY_ID is null;

I am now learning the OFbiz database API to implement the solution above. I
have not found an API to create RIGHT/LEFT joins so far.
EntityListIterator is a good solution but implementation of SQL statement
above would be even better in my opinion.  Anyway in case of problem I have
a backup in your idea.

Regarads and many thanks,
Michał




2010/5/2 Deyan Tsvetanov <[hidden email]>

> TECDOC

[Deyan Tsvetanov-2]

DynamicViewEntity does the joins. Search through the code for  examples,i'll be able to send you one tomorrow as it is night here :)

 

----- Reply message -----
From: "Michal Cukierman" <[hidden email]>
Date: Mon, May 3, 2010 00:32
Subject: Question regarding Catalog management in Ofbiz
To: <[hidden email]>

Thank you for your advices. I will save days of wark thanks to you.

@TECDOC:
Is a great software, just what I wanted to have. The pricelist is good as
well. Exposed web serwise or standalone DVD catalogue is all I need. I will
investigate this solution in a days. What is very important for my at the
moment is USA market support (I have seen some supliers so far). The another
thing to investigate is the licence. Is it legal to use it in exposed web
applications?

@Regarding top categories:
I have implemented SQL function for doing the magic, it's quite simple join
(as expected to be). The performance on my huge database is excelent (just
in time, after first run results are cached).
            SELECT D.PRODUCT_CATEGORY_ID FROM PRODUCT_CATEGORY_ROLLUP P
RIGHT JOIN
            PRODUCT_CATEGORY D ON P.PRODUCT_CATEGORY_ID and
D.PRODUCT_CATEGORY_ID
            WHERE PARENT_PRODUCT_CATEGORY_ID is null;

I am now learning the OFbiz database API to implement the solution above. I
have not found an API to create RIGHT/LEFT joins so far.
EntityListIterator is a good solution but implementation of SQL statement
above would be even better in my opinion.  Anyway in case of problem I have
a backup in your idea.

Regarads and many thanks,
Michał




2010/5/2 Deyan Tsvetanov <[hidden email]>

> TECDOC

[Scott Gray-2]

You are better off using a static view-entity definition, DynamicViewEntities are for when you want to be able to alter the view definition on the fly.
To create an outer join just use the rel-optional attribute on the view-link element.

Regards
Scott

HotWax Media
http://www.hotwaxmedia.com

On 3/05/2010, at 10:09 AM, [hidden email] wrote:

> DynamicViewEntity does the joins. Search through the code for  examples,i'll be able to send you one tomorrow as it is night here :)
>
>
>
> ----- Reply message -----
> From: "Michal Cukierman" <[hidden email]>
> Date: Mon, May 3, 2010 00:32
> Subject: Question regarding Catalog management in Ofbiz
> To: <[hidden email]>
>
> Thank you for your advices. I will save days of wark thanks to you.
>
> @TECDOC:
> Is a great software, just what I wanted to have. The pricelist is good as
> well. Exposed web serwise or standalone DVD catalogue is all I need. I will
> investigate this solution in a days. What is very important for my at the
> moment is USA market support (I have seen some supliers so far). The another
> thing to investigate is the licence. Is it legal to use it in exposed web
> applications?
>
> @Regarding top categories:
> I have implemented SQL function for doing the magic, it's quite simple join
> (as expected to be). The performance on my huge database is excelent (just
> in time, after first run results are cached).
>            SELECT D.PRODUCT_CATEGORY_ID FROM PRODUCT_CATEGORY_ROLLUP P
> RIGHT JOIN
>            PRODUCT_CATEGORY D ON P.PRODUCT_CATEGORY_ID and
> D.PRODUCT_CATEGORY_ID
>            WHERE PARENT_PRODUCT_CATEGORY_ID is null;
>
> I am now learning the OFbiz database API to implement the solution above. I
> have not found an API to create RIGHT/LEFT joins so far.
> EntityListIterator is a good solution but implementation of SQL statement
> above would be even better in my opinion.  Anyway in case of problem I have
> a backup in your idea.
>
> Regarads and many thanks,
> Michał
>
>
>
>
> 2010/5/2 Deyan Tsvetanov <[hidden email]>
>
>> TECDOC