It is possible to retrieve default values for whole model elements from a database. To do this, a SQL query must provide the requisite information to identify the fields within the element with which the data should be associated.
Numeric data
In the simplest case, where you are only concerned with scalar (i.e., non-time-series) numeric data, the query should return at least two columns, where the first column of each row contains a field label and the second column contains a number. Alternatively, the label and value can be in any columns, so long as the columns are called ‘Field’ and ‘Value’ respectively. STEM looks for the columns by name first, and otherwise uses the first two columns. Any other columns are ignored.
The field labels are the same as those used in internal references, relative to the type of structure being extracted. For example, if a Resource has a formula
'database'!query, the strings in the ‘Field’ column should be Capacity, Costs.CalibYear, Costs.Capital, etc.
Figure 1: Sample database query for Resource defaults
If the Resource Costs data has a formula (move up from the Costs dialog to set a formula on the button for all the costs), the labels should just be CalibYear, Capital, etc.
Figure 2: Sample database query for Cost defaults
Textual data
If you want to retrieve text from a database, e.g., for the Label or Capacity Unit fields in a Resource, a third column must be present in the query, containing the text data where appropriate. STEM first looks for a column called ‘Text’, and otherwise uses the third column of the query. For each row in a query, STEM will retrieve data from either the ‘Value’ column or the ‘Text’ column, according to whether the field inferred from the ‘Field’ column is numeric or textual, and will ignore any value in the other column.
Figure 3: Sample database query including text
Sample labels
A sample Microsoft Access database is supplied with STEM, called
model.mdb, which shows all the labels for every field in each type of element, together with a set of named queries which indicate the appropriate usage. For example, the query ‘Resource’ includes labels for every field in a Resource, and can be used to provide defaults for a Resource, through the formula
'MODEL.MDB'!Resource. A listing of model.mdb is included as an appendix – see 11.2 External Reference Labels for databases.
Note: This sample database is also supplied as a Microsoft Excel workbook,
modeldb.xls, which can be compared directly with the standard sample spreadsheet,
model.xls.
When you store data in your own database for reference in STEM, only those fields you actually require need be present in the specified query, and these fields can be returned in any order. STEM ignores any rows where the ‘Field’ column is blank.