Difference between revisions of "File:Opasnet base connection.ANA"

Revision as of 05:38, 1 December 2008

This model is a compilation of image:RDB connection.ANA and image:RDB reader.ANA. It reads and writes variable results from and to Opasnet base.

Reading

Reading is allowed for everyone. The function Rdb_to_var reads the result of a variable from Opasnet base and transforms the information into the form of an Analytica node. Note that all indices required must be created before the function works properly. To learn about the indices, run the function Do_first.

Writing

You can freely open and use the module, but you cannot actually write information into the Result database unless you have a password. Note that the necessary variable, index, dimension, and run information will be asked. You must fill in all tables before the process is completed. You can insert several variables at the same time. Each variable MUST have at least one index.

To do: improvement needs

• Run_list table needs the field Var_id. This will help identification of variables in each run without the need to go through result_id. The model should contain formula to fill it.
• Loc_of_result.Ind_id must be updated in the RDB. Now it is empty. Does the model contain formula to fill it?
• There should be a button for converting nodes into edit tables. This is because some models are using external data that cannot be accessed with other versions than Analytica Enterprise. The button would simply do (Va1:= Va1) for each variable in the variable list.
• The correct IP should be used so that the model works in the Internet as well. The correct IP is 193.167.179.97.

Functions

RDB_to_var:

Brings the results from the Opasnet base and transforms it into variables of the correct form. NOTE! All necessary indices must be created before running this function. The necessary indices can be viewed by calling the function Do_first with the same two parameters as this function.

PARAMETERS:

• Var_name: the name of the variable in the result database.
• Run_id: the identifier of the run from which the results will be brought. If omitted, the newest result will be brought.
• Textornot: Tells whether the result is numerical or text. If parameter is omitted or false, numerical is assumed, otherwise text.

Do_first:

This function brings the variable from the Result database and analyses its structure. Each index used will be shown as a column along '.Ind_name', and each location of that index will be shown along '.K'. The last row of '.K' shows the samplesize of the variable. Use this information to create the necessary indices for your model and to adjust the samplesize of the model. If the samplesize of the model is smaller than in the result database, the remaining samples are omitted; if larger, the cells with no results in the database are replaced with null. NOTE! The indices created should be lists of labels (not lists of numbers).

PARAMETERS (see also above):

• Var_name
• Run_id

SQL code in the model

SQL for Dimension table:

<sql-query display="1"> SELECT Dim_id, Dim_name FROM `Dimension` </sql-query>

SQL for Indices table:

<sql-query display="1"> SELECT Ind_id, Ind_name, Dimension.Dim_id, Dim_name FROM `Index`, Dimension WHERE `Index`.Dim_id=Dimension.Dim_id </sql-query>

SQL for Locations table:

<sql-query display="1"> SELECT Loc_id, Location, Dimension.Dim_id, Dim_name FROM `Location`, `Dimension` WHERE Location.Dim_id = Dimension.Dim_id </sql-query>

SQL for Variables table:

<sql-query display="1"> SELECT Var_id, Var_name FROM `Variable` </sql-query>

Readdata: SQL query:

<sql-query display="1"> SELECT Variable.var_name, var_unit, Loc_of_result.result_id, result, sample, dim_name, ind_name, location, Run.run_id, run_method FROM Variable, Result, Loc_of_result, Location, Dimension, `Index`, Run_list, Run WHERE Variable.var_name = "'&Var_name&'" AND Run.run_id = '&Run_id&' AND Variable.var_id = Loc_of_result.var_id AND Loc_of_result.result_id = Result.result_id AND Loc_of_result.loc_id = Location.loc_id AND Loc_of_result.ind_id = `Index`.ind_id AND Location.dim_id = Dimension.dim_id AND Loc_of_result.result_id = Run_list.result_id AND Run_list.run_id = Run.run_id </sql-query>

Newestrun SQL query:

<sql-query display="1"> SELECT Run_id FROM Variable , Loc_of_result, Run_list WHERE Variable.var_name = "'&Var_name&'" AND Variable.var_id = Loc_of_result.var_id AND Loc_of_result.result_id = Run_list.result_id </sql-query>