# How do Transformations work?

30 January 2003

Transformations and expressions provide the key to adapting STEM to a wide range of applications. Can you explain the difference between calculations made in the Editor and those performed at run-time? This technical article looks into the mechanics of evaluating expressions and compares four classes of calculation in STEM.

## Expressions in the STEM Model Editor

STEM allows you to create links between different inputs, and to calculate inputs using algebraic expressions, just like you can in a spreadsheet. As a simple example, consider a Resource whose capital cost is known, but for which the maintenance cost must be estimated at 5% of the capital cost. In the illustration below, you can see that a simple formula is used to calculate this value, nicely illustrating both the concept of an ‘internal reference’ and the use of a formula.

An internal reference is just a text label used to identify another input, analogous to a cell reference in a spreadsheet. Rather than having to look up or remember these labels, you can just click with the mouse while editing a formula to pick up references to other inputs.

STEM automatically re-evaluates a formula whenever any of the ‘precedents’ (i.e. the references made within the formula) are modified. This process is confined to the Editor, and the calculated values are stored in the .DTM file when a model is saved.

## Time series

Expressions can also be used to relate and calculate time-series inputs. Whereas the example above calculates just a single value, a formula entered for a time-series input calculates a series of values for each year or period of the model run. For example, if two complementary services are provided to the same customer base, then you might define the penetration for the second service as:

1. "Service 1".Penetration.RefValue

The resultant input is calculated for each period in turn, applying the same formula to each of the corresponding values of the first input.

## User Data

In addition to creating linkages between the standard STEM inputs, time-series expressions can also be used to reference unbound User Data as user-defined inputs, providing a completely flexible mechanism for extending the set of distinct inputs to a model. For example, for a piece of equipment imported from abroad, you might define User 1 and User 2 as Nominal cost and Exchange rate respectively (by selecting Rename Field from the Edit menu in the User Data dialog), and then link these through to the Capital Cost with a suitable formula, as shown below.

Sometimes you may wish to share a common set of assumptions between several inputs. For example, a set of lifetime data might be shared by several Resources, while specifying different unit capacities. By selecting Defaults from the icon menu for an element, you can identify a source element from which all unset values should be inferred in the target element. Alternatively, and more intuitively, you can use drag-and-drop to create this relationship directly. STEM captures this dependency as a formula for the target element, which acts on each field in turn, except where an explicit value has been entered.

In the following illustration, the value entered for the Unit Capacity of Resource 2 overrides the default values linked from Resource 1.

### External references

STEM provides comprehensive linkages with Excel and other database tools. Selecting Links from the File menu in the Editor enables you to create an ‘external reference’ to scalar and time-series data from Excel spreadsheets, any ODBC-compliant database or the inputs and results of a STEM model. The syntax for a link to Excel might look like this:

‘C:\DATA[COSTS.XLS]Sheet3’!\$A\$2

which is the same format as is used in Excel for an inter-workbook link. The syntax for linking to databases is described in detail in Section 5 of the User Guide.

Rather than create many parallel links between a STEM model and the same external file, you can use the defaults mechanism to link several or all inputs for a given element with one formula. This formula can reference a range of cells in a spreadsheet, or rows in a database query, where the individual data are identified by labels within the external file.

## Run-time expressions and the STEM Model Engine

Transformations were first introduced in STEM 3.0 to satisfy a widespread desire to model ‘equipment-driven demand’, that is to say a requirement for secondary equipment to be dimensioned directly in terms of the installation of some primary equipment. A simple example would be various services for differing customer segments requiring a copper loop to an exchange, and then each copper loop requiring a line card at the exchange. Mapping the demand directly from cooper loop to line card avoids the need to repeat separate links from each service to the line card, as well as offering a more intuitive representation of the actual network architecture.

A key attribute of a Transformation is that, within each period of the model run cycle, STEM waits until it has calculated the demand for all the inputs of a Transformation before calculating the Transformations own Output, and then proceeds to calculate demand for any dependent elements. The ‘size’ of each input is defined by the input Basis, in this example the demand for line cards being the number of loops actually in use.

The simple Resource Transformation simply passes on demand, whereas, in general, most Transformations actually modify their inputs.

### Predefined Transformations

There are several ‘built-in’ Transformations for which the model engine performs a standard parameterised calculation, as shown in the following table. Here the Output of the Transformation is always calculated in the same way, but with the influence of various input parameters, such as a multiplier, or a grade of service for the Erlang B Transformation. These alternatives are accessed from the Type menu in the Input and Transformation dialog.

Type Parameters Output
Multiplier Time-series multiplier A linear multiple of the input
Input–output mapping Mapping, usually an interpolated look-up A time-invariant non-linear mapping
Erlang B formula Grade of service;sites Number of circuits required (note: input is carried traffic, rather than offered traffic
Time lag Integer time lag Input from earlier year

### Expression Transformations

The most general Transformation element accepts a variable number of inputs (up to 20 in STEM 6.2) and calculates its output from a user-defined expression. Although this formula is entered in the Editor, it is not evaluated until run-time, as it acts on intermediate results from the model engine, as identified by the bases of the various Transformation inputs.

The canonical application is to sum a number of separate demands, such as two different kinds of cards requiring slots on a shelf in a cabinet. In this case, Installed Units is selected as the basis for both inputs, since this is what directly drives space required on the shelf.

An Expression Transformation allocates costs in fixed proportions between its inputs by default, but here the cost allocation basis is set to Input Proportions, prescribing that costs should be allocated in proportion to the respective numbers of cards. However, in this case, all costs will be ultimately fed back to the services driving the line card.

The expression for such a Transformation is known as a ‘run-time’ expression, in contrast to other expressions which are evaluated directly in the Editor. A run-time expression may also include internal references to other inputs, so that for example you can define additional parameters in User Data. However, it is important to understand that, while User Data may be defined by a time-series formula or linked to Excel, this is not possible for Transformation inputs, which serve only to identify intermediate results for selected input elements.

The final technicality relates to copying run-time expressions. As far as the Editor is concerned, a run-time expression is a value, so there is no distinction between paste values and paste formulae for such a field. Moreover, if you link defaults from one Expression Transformation to another, the form of the expression from the first Transformation will be reproduced in the second. In contrast, a time-series expression for the original User1 would yield only a set of default time-series values for the second element.

## Expression syntax for derived results

The STEM model engine is concerned with network business operations: the forecast of demand and revenues, and the calculated incremental and total installation of all types of equipment and other cost drivers. The investment required to fund this business activity is calculated separately by an iterative process in the Results program, with reference to the operational results from the model engine and certain financial inputs from the Editor.

The reasons for this separation are twofold. Firstly, the calculations in the Results program are done ‘on demand’, with no additional disk space requirements. (This applies not just to the investment layer, but also to all intrinsic summations of network revenues over Service elements and network costs over Resource elements.) Nowadays, disk space is no great concern; the more persuasive factor is the flexibility of the ‘derived results’ mechanism which delivers these financial results.

### Derived results

Select Define Results… from the Config menu in the Results program to explore this feature for yourself. For each type of result, you will see a large number of pre-defined derived results. For example, Service Operating Profit is defined as:

Revenue – "Operating Charge"

This single definition applies consistently to all Service elements. The terms in this expression relate to other Service results; when the result is calculated for a given Service, the Results engine automatically addresses the respective results for this element.

### Network aggregation

Expressions for derived results can contain all the usual arithmetic operators and algebraic functions. Special treatment is given to the sum() function. The conventional use is to sum several other results, for example:

sum (a, b, c)

but if, for a network result, you write:

sum (Services.Revenue)

then this single-argument sum performs a sum of the given results over all the elements of the given type. This method is used to evaluate all the network totals for revenue and cost.

### Collections

You can calculate automatic aggregations over arbitrary sets of elements by defining Collections in the Editor. For example, if you select the two card Resources in the example above and click the Collection button on the toolbar, the Editor will create a Collection element, effectively grouping these two Resources. The Collection provides a unified interface to the input assumptions for both Resources, and automatically appears in the list of Elements in the Choose elements to draw dialog in the Results program.

Derived network results may also refer to results for individual named elements, most usefully for Collections, which can be used to provide a categorised breakdown of costs versus revenues. ‘Defining results’ is explained in more detail in Section 4 of the User Guide.