Calc Solvers Info Doc/wiki page Re: Calc solvers - what to say in the Calc Guide

Tue May 19 23:48:01 UTC 2020

Howdy,

Adding The Documentation ML to the thread.
Quick recap for the folks on the list:
Currently the LibreOffice wiki includes under the FAQs section a
question on Calc Solvers.
https://wiki.documentfoundation.org/Faq/Calc/109
The answer in the second sentence, for languages other then French,
refer the user back to the AOO wiki for information specific to the
NLSolver extension from Sun Microsystems. That answer page also refers
the user to API information via the AOO SDK.

The French language answer points to a copy (it appears) of that page
translated to French but hosted on the LibreOffice wiki.

So - drafted a LibreOffice specific version of the information found
on the AOO wiki.
This draft adds information to cover the five solver engines which
ship with the TDF baseline LibreOffice code (including the coming
7.0).

The latest update to the draft
https://nextcloud.documentfoundation.org/s/ebisHjZo2rowM4Q

There is one final change I'd like to make and that is the example; I
would like to switch the basic code out for an example that uses the
spreadsheet and model used in the data analytics section of the Calc
Guide. I'll be working on that tonight.

Otherwise - any review of the current state of the document is most
welcome. At the moment the file is in my private storage space on the
NC server; I would prefer to move it to the shared documentation
folder but wasn't sure where to put it?

Thanks,

Drew

On Mon, May 18, 2020 at 6:54 PM Drew Jensen <drewjensen.inbox at gmail.com> wrote:
>
> Great - well for now we can use an odt file to draft the content.
> Added this section for COINMP in Solvers section:
> -------------------
> CoinMP
>
> The CoinMP optimizer is an open source solver, it is part of the
> COIN-OR project which is an initiative to spur the development of
> open-source software for the operations research community. CoinMP at
> present is a linear and integer optimizer.
>
> Algorithmic Features
>
> COIN-OR is a large group of projects ranging from linear to nonlinear
> programming right through to heuristic and modelling interfaces,
> CoinMP is integrates the COIN's CLP (linear programming), CBC (Branch
> and Cut library) and CGL (Cut Generation library) libraries into one
> easy to use tool. Thus CoinMP can solve linear programming problems
> accessing the functionality embedded in the CLP library, solve integer
> and mixed integer programming problems utilizing the algorithmic
> features of both the CBC and CGL libraries.
> --------------
> https://nextcloud.documentfoundation.org/s/ebisHjZo2rowM4Q has been
> updated with the change and that pdf file has the .odt embedded in it
> BTW.
>
> I could sure use feedback on that added text from more technical folks.
>
> Drew
>
> On Mon, May 18, 2020 at 5:17 AM Stephen Fanning
> <stevemfanning.wh at gmail.com> wrote:
> >
> > Drew,
> >
> > Many thanks for your efforts in trying to get to the bottom of this.
> >
> > I think that putting the information on the wiki is a good idea. The relevant chapter of the Calc Guide (Chapter 9, Data Analysis) is already very thick and would not benefit from being expanded further to include more solver details.
> >
> > Regards,
> >
> > Steve
> >
> >
> > On Mon, 18 May 2020 at 06:39, Drew Jensen <drewjensen.inbox at gmail.com> wrote:
> >>
> >> HI,
> >>
> >> Well, I noticed something aside from the Guide and that is the FAQ
> >> over on the wiki.
> >> https://wiki.documentfoundation.org/Faq/Calc/109/fr
> >> Now, it turns out that the French version of that FAQ points to a page
> >> (first link on the page) on the TDF wiki but none of the other
> >> languages do. They point back to the AOO wiki and on that wiki page it
> >> has links to things like extensions LibreOffice doesn't need and API
> >> documentation in AOO SDK.
> >>
> >> So - I thought why not steal the content from the AOO page as a start
> >> of a TDF page (or PDF maybe ;-).
> >>
> >> I did that and then I updated a few things in it. Not done updating
> >> things, but done for tonight.
> >>
> >> You can review a draft of this
> >> https://nextcloud.documentfoundation.org/s/ebisHjZo2rowM4Q
> >>
> >> Can see some easy edits in the top part of the text and I need to ask
> >> for help filling in the information for scripting as needed for the
> >> additional algorithm engines in LO - for one thing maybe a bit more.
> >>
> >> Anyway, any feedback is welcome. Will post tomorrow when those changes
> >> are made.
> >>
> >> Thanks,
> >>
> >> Drew
> >>
> >> On Sat, May 16, 2020 at 7:31 PM Drew Jensen <drewjensen.inbox at gmail.com> wrote:
> >> >
> >> > This is an interesting point of differentiation.
> >> > Reviewing some of the tools yesterday and this morning;
> >> > AOO with 1 solver engine in default install (4.2 pre-release binary);
> >> >
> >> > OnlyOffice (latest desktop Linux binary) has no Solver equivalent in
> >> > either the desktop binary or the cloud version, not that I can find
> >> > (didn't see any in the available plug-ins, but maybe their is
> >> > something behind the paywall areas).
> >> >
> >> > WSP (May 2020 release) includes only one, a simplex linear solver.
> >> >
> >> > MS 365 and Google Sheets both require an plug-in, Frontline Systems
> >> > Inc looks to be the leading supplier for both tools, but there are
> >> > multiple other choices for each.
> >> >
> >> > Looking at this the first couple sentences, "The standard Microsoft
> >> > Excel Solver uses a basic implementation of the primal Simplex method
> >> > to solve LP problems.  It is limited to 200 decision variables.", from
> >> > this web page https://www.solver.com/linear-quadratic-technology and
> >> > thinking that we should be able to run down answers to some of the
> >> > bounds information for the different engines also.
> >> >
> >> > Below are the different options sets for the engines.  Most
> >> > specifically give bounds (ie Epsilon Level), but a few don't (ie
> >> > Learning Cycles); I don't know if it would be worth it to get those
> >> > numbers or not.
> >> >
> >> > I think it is worth it to, per engine, list these in the guide/help
> >> > file. It is a lot easier to highlight something like "ACR Comparator
> >> > (instead of BCH)" from a PDF/HTML then from that dialog box in the
> >> > application so that you can do a DDG search on the term ;)
> >> >
> >> > LibreOffice CoinMP Linear Solver & LibreOffice Linear Solver
> >> > Options
> >> > - Assume variables as integers. (Default FALSE)
> >> > - Assume variables as non-negative. (Default FALSE)
> >> > - Epsilon Level 0-3. (Default 0)
> >> > - Limit branch and bound depth. (Default FALSE)
> >> > - Solving time limit (seconds). (Default 100)
> >> >
> >> > DEPS Evolutionary Algorithm
> >> > Options
> >> > - Agent Switch Rate (DE Probability): (Default .05)
> >> > - Assume non-negative variables (Default FALSE)
> >> > - DE: Crossover Probability (0-1): (Default 0.9)
> >> > - DE: Max Scaling Factor (0-1.2): (Default 0.5)
> >> > - DE: Min Scaling Factor (0-1.2): (Default 0.5)
> >> > - Learning Cycles: (Default 2000)
> >> > - PS: Cognitive Constant: (Default 1.494)
> >> > - PS: Mutation Probability (0-0.005): (Default 0.729)
> >> > - PS: Social Constant: (Default 1.494)
> >> > - Show enhanced solver status (Default TRUE)
> >> > - Size of library; (Default 210)
> >> > - Size of swarm; (Default 70)
> >> > - Stagnation limit: (Default 70)
> >> > - Stagnation tolerance: (Default 0.0000001)
> >> > - Use ACR Comparator (instead of BCH). (Default FALSE)
> >> > - Use random starting point. (Default FALSE)
> >> > - Variable bounds testing. (Default TRUE)
> >> > - Variable bounds threshold (when guessing): (Default 3)
> >> >
> >> > SCO Evolutionary Algorithm
> >> > Options
> >> > - Assume variables as non-negative. (Default FALSE)
> >> > - Learning Cycles: (Default False)
> >> > - Show enhanced solver status (Default TRUE)
> >> > - Size of library; (Default 210)
> >> > - Size of swarm; (Default 70)
> >> > - Stagnation limit: (Default 70)
> >> > - Stagnation tolerance: (Default 0.0000001)
> >> > - Use ACR Comparator (instead of BCH). (Default FALSE)
> >> > - Use random starting point. (Default FALSE)
> >> > - Variable bounds testing. (Default TRUE)
> >> > - Variable bounds threshold (when guessing): (Default 3)
> >> >
> >> > LibreOffice Swarm Non-Linear Solver (experimental)
> >> > Options
> >> > - Assume variables as integers. (Default FALSE)
> >> > - Assume variables as non-negative. (Default FALSE)
> >> > - Solving time limit (seconds). (Default 6000)
> >> > - Swarm algorithm (0 - Differential Evolution, 1 - Particle Swarm
> >> > Optimization): (Default 0)
> >> >
> >> > Saw your mail about the version 7 Guide, but already had that typed in
> >> > so figured I'd send it along anyway.
> >> >
> >> >
> >> > On Sat, May 16, 2020 at 5:22 PM Drew Jensen <drewjensen.inbox at gmail.com> wrote:
> >> > >
> >> > > Well here is something
> >> > > Looking at the help file for version 7
> >> > > https://help.libreoffice.org/7.0/en-US/text/scalc/01/solver.html?&DbPAR=CALC&System=UNIX
> >> > > the final text box states that 'only linear solvers are in the default installation'.
> >> > >
> >> > > However 7.0 Alpha1 build adds a choice of, 'LibreOfficeDev Swarm non-linear Solver (experimental)' for an engine.
> >> > >
> >> > > So, assuming that engine ships with 7 the text box in the help file could go.
> >> > >
> >> > >
> >> > >
> >> > > On Fri, May 15, 2020 at 7:02 AM Stephen Fanning <stevemfanning.wh at gmail.com> wrote:
> >> > >>
> >> > >> Kohei and Drew,
> >> > >>
> >> > >> Many thanks for getting back to me on this topic.
> >> > >>
> >> > >> For the 6.4 update to the Calc Guide, I will follow Kohei's advice that it is probably beyond the scope of the document to provide guidance on how to select a solver.
> >> > >>
> >> > >> I'll try to make some time before the 7.0 Calc Guide update to re-visit this decision.
> >> > >>
> >> > >> Regards,
> >> > >>
> >> > >> Steve
> >> > >>
> >> > >>
> >> > >> On Fri, 15 May 2020 at 07:40, Drew Jensen <drewjensen.inbox at gmail.com> wrote:
> >> > >>>
> >> > >>> Tonight took the LO 7 alpha1 file and the one example workbook from that Excel tutorial and ran each of the five solver engines.
> >> > >>> For sure the LO CoinMP and LO Linear choices are crazy faster then DEPS and SCO (The firth choice Swarm non-linear is just as quick but the results are kaka, but it does say it is experimental).
> >> > >>>
> >> > >>> On Thu, May 14, 2020 at 7:55 PM Drew Jensen <drewjensen.inbox at gmail.com> wrote:
> >> > >>>>
> >> > >>>> hmm - there are bits and pieces here and there.
> >> > >>>>
> >> > >>>> For example the first two choices (DEPS & SCO) have some explanation over at AOO wiki
> >> > >>>> https://wiki.openoffice.org/wiki/NLPSolver
> >> > >>>> If that is still relevant for LibreOffice I can't say, but likely.
> >> > >>>> CoinMD (which I assume here is type of Coin-MP) has a lot of generalized references, even a wiki page with a paragraph or two that might be worth drafting for service here:
> >> > >>>> https://en.wikipedia.org/wiki/COIN-OR
> >> > >>>>
> >> > >>>> anyway - I'm just using DDG to search for info on the different solver types, and you get the idea.
> >> > >>>>
> >> > >>>> There is a couple of examples of tutorials the reference solvers in Calc (one of which actually uses the same example data to show how to solve both in Excel and Calc), but the couple I reviewed only use one as an example.
> >> > >>>>
> >> > >>>> I did find one for Excel that has an over view which I thought was a decent balance of information on the three solver models available there at:
> >> > >>>> https://www.tutorialspoint.com/excel_data_analysis/advanced_data_analysis_optimization_with_excel_solver.htm
> >> > >>>>
> >> > >>>> IDK if any of the helps, hope it does.
> >> > >>>>
> >> > >>>> Best wishes,
> >> > >>>>
> >> > >>>>
> >> > >>>>
> >> > >>>>
> >> > >>>> On Thu, May 14, 2020 at 7:19 PM Kohei Yoshida <kohei at libreoffice.org> wrote:
> >> > >>>>>
> >> > >>>>> On 10.05.2020 10:59, Stephen Fanning wrote:
> >> > >>>>>
> >> > >>>>> > What guidance can I insert into the Calc Guide to help the user if he
> >> > >>>>> > wonders which algorithm to select?
> >> > >>>>>
> >> > >>>>> This is strictly my personal opinion.
> >> > >>>>>
> >> > >>>>> Which optimization solver to pick really depends on the characteristics
> >> > >>>>> of the data being optimized, how (well) the algorithm is implemented in
> >> > >>>>> the versions included in Calc, the accuracy goal etc. etc.  It's more an
> >> > >>>>> art than science, and each user really needs to experiment with various
> >> > >>>>> solvers to see which one works best for his or her optimization cases.
> >> > >>>>> My personal opinion is that it is probably beyond the scope of Calc user
> >> > >>>>> guide to provide any sort of guidance on how to select a solver.
> >> > >>>>>
> >> > >>>>> Feel free to disagree with me on this.  I'm not an expert. :-)
> >> > >>>>>
> >> > >>>>> Kohei
> >> > >>>>> _______________________________________________
> >> > >>>>> LibreOffice mailing list
> >> > >>>>> LibreOffice at lists.freedesktop.org
> >> > >>>>> https://lists.freedesktop.org/mailman/listinfo/libreoffice