question

Lambert avatar image
0 Likes"
Lambert asked Lambert commented

Automatically export optimizer best iteration

Hi good morning,

As the final output of my simulation, I would like to export and share the results of the best iteration in the optimizer.

Is there a way to automatically select the best iteration and export its results? Is this feasible? Maybe via external python code? Is there a post, manual or something to get me started on this? I've doing some search but cannot find anything related.

thank you very much

FlexSim 23.0.3
export data
· 2
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Joerg Vogel avatar image Joerg Vogel commented ·
Hello @Lambert , in which context do you want to get a best result. I think best relates often to an output chart. You need first find a chart and a criterium to be evaluated as output. Then you might get a list of results as ranked subnodes.
0 Likes 0 ·
Lambert avatar image Lambert Joerg Vogel commented ·

Hi Joerg, thanks for answering.

I am referring to this:


1675930229673.png


I would like to automatically export and share with stakeholders the results of the iterations marked with a star once the optimizer finishes.

0 Likes 0 ·
1675930229673.png (63.0 KiB)

1 Answer

Felix Möhlmann avatar image
2 Likes"
Felix Möhlmann answered Lambert commented

If you want to export graphs, then I don't know if that is possible outside of using the html report-builder.

What you can do is export the numerical data to an Excel file. The following code looks up the id of the best iteration in the last optimizer run and then exports the data of a state bar graph for that iteration to Excel. (If you don't specify the Excel file path further it will be created in the documents folder)

Database.Connection db = function_s(Model.find("Tools/Experimenter"), "getDBConnection");

int disconnect = 0;
if (!db.isConnected) {
    if (!db.connect()) {
        return 0;
    }
    disconnect = 1;
}

// Query for ID of best iteration
Database.PreparedStatement stmt = db.prepareStatement("SELECT best_solutions FROM optquest_run_results");
var result = stmt.execute();
string lastBest;
while (result.fetchNext()) {
    lastBest = result[1];
}
   
lastBest = lastBest.slice(2, -1);
int bestID = lastBest.toNum();

// Create new Excel workbook
excelcreateworkbook();
excelcreatesheet("StateBarData");
excelsetsheet("StateBarData");
excelwritestr(1, 1, "Object");
excelwritestr(1, 2, "State");
excelwritestr(1, 3, "Time");
excelwritestr(1, 4, "Utilization");
int row = 2;
   
// Query for state bar data and write to excel
stmt = db.prepareStatement("SELECT * FROM [/State Bar] WHERE task_id == ?");
stmt.bindParam(1, bestID, Database.DataType.SmallInt);
result = stmt.execute();
while (result.fetchNext()) {
    string objectPath = StatisticsCollector.getPathFromID(result[2], 1);
    excelwritestr(row, 1, objectPath);
    excelwritestr(row, 2, result[3]);
    excelwritenum(row, 3, result[4]);
    excelwritenum(row, 4, result[5]);
    row++;
}
excelsave("Optimizer_Iteration_" + lastBest + ".xlsx");
excelclose(0);

if(disconnect) {    
    db.disconnect();
}

@Jason Lightfoot actually gave a similar example very recently here.

I used the freeware tool linked below to explore the structure of the sqlite database file and get the correct table and columns to use in the queries.

https://sqlitebrowser.org/about/


The model attached below actually also contains my attempt at finding a solution for your question about dynamically skipping iterations.

My approach was to store the current best parameter values (for the example I just used the sum) in a database which the child processes running the iteration would then access at the start of each replication. If the current parameter set is worse, the run would be stopped immediately with "endreplication(1)".

From this post I got the idea to use the results file itself to store the best parameter set. It does mostly work, however, reading the best values from the database is not always successful (possibly due to parallel access attempts), so some "worse" iteration are still run.

1675937865424.png

-1 values are skipped iterations. 0 means unsuccessful, >0 successful runs (determined simply by range of parameters).

I originally decided not to post this, as I am not sure if this approach might cause other (stability) issues in larger runs.

The site won't let me upload the sqlite file, so I'll attach the model so you can run the optimization again to test the code above.

experimenter_db_test_3.fsm


· 9
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Lambert avatar image Lambert commented ·
Thank you so much for that!! Tremendously useful :)

Let me work on it and I'll get back to you on my progress

0 Likes 0 ·
Lambert avatar image Lambert commented ·

Hey @Felix Möhlmann , could you please explain to me what does this do?

// load the latest pfm nodes (highest id) from the database into the tree (limited to 16 | max number of concurrent runs)
treenode container = Model.find("Tools").subnodes.assert("pfms");
string stmtText = "\
SELECT name, loadnode(node, ?) \
FROM pfms p JOIN pfm_values pv ON p.id = pv.pfm_id \
WHERE name = 'ParameterSum' \
ORDER BY pv.task_id DESC LIMIT 16";


and also this one:

if(!objectexists(childexpfolder))


I am a bit lost with these pieces of code.

Thank you in advance!

0 Likes 0 ·
Felix Möhlmann avatar image Felix Möhlmann Lambert commented ·

This is mostly just the definition of the query string that is later executed to read from the database. It is spread over multiple lines for better readability. If you have questions about the SQL syntax you can look up the keywords online. The FlexSim manual only deals with the basics as far as keywords are concerned.

See the link further below to see what the query does.

The experimenter runs separate FlexSim instances in the background. These "child" processes can dump data into a certain folder that is then available to read from in the parent process (the FlexSim instance you are starting the experimenter in, if I understand it correctly). Checking whether that folder exists or not tells you if the trigger is executed in a child process or in the main process. (childexpfolder exists -> main process, does not exist -> child process)

See also this post, which the query text is taken from (and initially showed me that reading from the sqlite database was possible this way).

0 Likes 0 ·
Lambert avatar image Lambert Felix Möhlmann commented ·
Thank you sir!!
0 Likes 0 ·
Lambert avatar image Lambert commented ·

@Felix Möhlmann

Hi Felix, I have a follow up question if I may.

Instead of exporting a state table to excel like in your example, how can I export a table existing in my dashboard?

I have this table in the dashboard linked to a global table, but nothing is being recorded in the sqlite file about it.

1678796521123.png

thank you!!

0 Likes 0 ·
1678796521123.png (196.9 KiB)
Felix Möhlmann avatar image Felix Möhlmann Lambert commented ·

Only tables from Statistics Collector and Calculated Tables is stored in the database. Most (if not all) default charts draw their data from one of these, so they can be drawn based on the stored data.

To keep data from a global table, you can clone it into a Calculated Table. Though you might first want to add the row header as an extra column so the naming isn't lost.

Then, if you want to copy the entire table, the fastest way is to "Enable Direct Editing" and use the following query.

1678797424425.png

1678797451141.png

0 Likes 0 ·
1678797424425.png (1.1 KiB)
1678797451141.png (9.0 KiB)
Lambert avatar image Lambert Felix Möhlmann commented ·

Thank you Felix!

What is the best way to add the row headers as an extra column?

My row headers are generated dynamically during the simulation and change between iterations :(

1678873337544.png

0 Likes 0 ·
1678873337544.png (171.8 KiB)
Show more comments