Making Connections

A question we periodically get from our customers is: Can iThink or STELLA connect to a database? Saving and pulling information to/from databases presents a lot of advantages for storing, organizing and sharing model data. Thanks to iThink and STELLA’s ability to import and export data via commonly used spreadsheet file formats, it is possible to use comma separated value (CSV) files as a means to create a connection to database applications.

Essentially, data can be moved between a database and iThink/STELLA by using a CSV file as a bridge. CSV files are a widely supported file standard for storing table data, and both iThink/STELLA and many database programs are able to read and write to them.

The process of connecting to a database using CSV files as an intermediary

The process can be automated when you use iThink/STELLA’s ability to run models automatically from the command line (Windows only). Most database applications also have command line interfaces, allowing you to create a single macro script that moves data between your model and a database in a single process.

In this post I will use a simple example to demonstrate how to import data from a Microsoft SQL Server database into an iThink model on Windows. The model and all files associated with the import process are available by clicking here. If you don’t have access to Microsoft SQL Server, you can download a free developer’s version called SQL Server Express from the Microsoft web site.

The Model

The model used in this example is a variation of the Beer Game model. The structure shown below represents the ordering process for a simple retailer supply chain.

The model has been set up to import the initial values for On Order with Wholesaler and Unfilled Orders stocks, target inventory and actual customer orders (a graphical function with 21 weeks of data). The source of the imported data is the file named import.csv in the example files.

To set up this example, I manually created the CSV file using the initial model parameters. (Later in this post, you’ll see that this file will be automatically created by the database.) The model has been initialized in a steady state with actual customer orders at a constant level of 4 cases per week over the 21 week period.

Creating the SQL Server Database

Since the model is set up to import data from the import.csv file, we are ready to populate that same CSV file with data from the SQL Server database. But, first we need to create the database and the associated data table.

Using SQL Server Management Studio, create a new database named Simulation.

Dialog in SQL Server when adding new "Simulation" database

Once the database has been created, you can run the full CreateTable.sql script to create a table that will store the data. To open the script, select Open -> File from the File menu and navigate to the CreateTables.sql file. The script is set up to create a table named Import_Data with columns for each variable that is imported into the model. The script will also populate the table with data. It is important to note that the first row of the table contains the variable names exactly as they appear in the retailer supply chain model. Below is a sampling of the commands contained in the script:

CREATE TABLE [dbo].[Import_Data](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[OnOrder] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UnfilledOrders] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetInventory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActualOrders] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

INSERT INTO Import_Data
(OnOrder,UnfilledOrders,TargetInventory,ActualOrders)
values
(‘On Order with Wholesaler’,'Unfilled Orders’,'target inventory’,'actual customer orders’)
INSERT INTO Import_Data
(OnOrder,UnfilledOrders,TargetInventory,ActualOrders)
values
(8,4,20,4)
INSERT INTO Import_Data
(OnOrder,UnfilledOrders,TargetInventory,ActualOrders)
values
(”,”,”,4)
INSERT INTO Import_Data
(OnOrder,UnfilledOrders,TargetInventory,ActualOrders)
values
(”,”,”,10)

To run the script, select Execute from the Query menu. Note that the data shows an increase in actual customer orders in week 2 from 4 to 10 cases. In the model, actual customer orders is a graphical function which is reflecting order data over time.

Results tab after running CreateTable.sql script

Linking the Database to the Model

After the database has been prepared, the Transfer_Data.bat Windows batch file can be used to link the database to the model. Transfer_Data.bat is a script that pulls data from the database into the CSV file, opens iThink, imports the CSV data and runs the model. The batch file takes advantage of the utility Microsoft provides with SQL Server called Bulk Copy Program (BCP) and iThink’s ability to run models from the command line. You’ll need to make a few edits to the batch file shown below before you can run it.

BCP Simulation.dbo.Import_Data out import.csv -c -t, -U <Login> -P <Password> -S <Server name>

“%PROGRAMFILES%\isee systems\iThink 9.1.4\iThink.exe” -i -r -nq Supply_Chain.itm

Using a text editor such as WordPad or NotePad, replace the placeholders for <Login>, <Password> and <Server name> in the first line to match the authentication for the Simulation database that was previously created. If your SQL Server database is on your local machine, you can delete the –U, -P and –S settings altogether. This command will make use of the BCP utility to copy the contents of the Import_Data table to the import.csv file. Depending on the version of iThink or STELLA you are running (version 9.1.2 or later is required) and its location, you may also need to edit the second line of the batch file.

After the Transfer_Data.bat file has been edited and saved, double-click on the file to run it. Once the data has been retrieved from the database, iThink will import the data via the CSV file, and run the simulation. The results will appear in the graph in the model as shown below:

Updating the Data

In a real-world situation, the database that stores information we need to run a model is usually connected to another application that updates or changes the data. In the absence of a real-world application, I created a script that allows you to update the Simulation database and experiment with your own set of data.

In SQL Server Management Studio, open the script called UpdateTable.sql. Edit the values for each variable as desired.

USE [Simulation]
UPDATE Import_Data SET OnOrder=8, UnfilledOrders=4, TargetInventory=20, ActualOrders = 4
WHERE RowID=2
UPDATE Import_Data SET ActualOrders = 4
WHERE RowID=3
UPDATE Import_Data SET ActualOrders = 4
WHERE RowID=4

When you have finished editing the UpdateTable.sql script, execute the script to update the Import_Data table. Now you are ready to re-run the Transfer_Data.bat batch file and link the updated data with the Supply_Chain.itm model.

Summary

This example should give you a good idea of how the process of importing data from a database into an iThink or STELLA model can be set up and run automatically. Other database applications such as Oracle, MySQL and SQLite could also be used in a similar manner. I have not yet tried to set up examples, but these other databases also have command line interfaces for automating the transfer of data to CSV files that iThink and STELLA can connect to.

Stay tuned for part two of this post where I’ll provide an example of exporting model results to a SQL Server database.

The question we get asked most frequently by just about anyone who wants to know more about our modeling software is “What is the difference between STELLA and iThink?”  From a functional perspective, there are no differences between the STELLA and iThink software — they are two different brands of the same product.

The STELLA brand is targeted toward individuals in educational and research settings.  Supporting materials such as An Introduction to Systems Thinking with STELLA and sample models cover the natural and social sciences.

iThink, on the other hand, is targeted toward an audience of users in business settings.  An Introduction to Systems Thinking with iThink is written with the business user in mind and model examples apply the software to areas such as operations research, resource planning, and financial analysis.

Aside from the different program icons and other graphic design elements that go along with branding, there are just a few minor differences in the default settings for STELLA and iThink.  These differences are intended to pre-configure the software for the model author.  They do not limit you in any way from configuring the default setup to match your own individual preferences.

Below is a list of all the differences between the default settings for STELLA and iThink.

Opening Models

When opening a model with STELLA on Windows, by default, the software looks for files with a .STM extension.  Similarly, iThink looks for files with an .ITM extension.  If you want to open an iThink model using STELLA or vice-versa, you need to change the file type in the Open File dialog as shown below.

On Macs, the open dialog will show both iThink and STELLA models as valid files to open.

If you open a model with a file type associated with the different product than the one you are using, you’ll get a message similar to the one below warning you that the model will be opened as “Untitled”.  Simply click OK to continue.

Saving Models

When saving a model in STELLA, by default, the software saves the model with a .STM file extension.  Similarly, iThink saves model s with an .ITM extension.  If you’re using STELLA and want to save your model as an iThink file or vice-versa, use the Save As… menu option and select the appropriate type as shown below.

STELLA on Windows save dialog

STELLA on Mac save dialog

Run Specs

Since iThink is targeted toward business users who tend to measure performance monthly, the default Unit of time for iThink is set to Months.  It’s also easier to think about simulations starting in month 1 (rather than month zero) so we set the default simulation length in iThink to run from 1 to 13.  STELLA on the other hand, reports the Unit of time as “Time” and, by default, runs simulations from 0 to 12.

Run Spec Default Settings Comparison

Table Reporting

In a business context, financial results are generally reported at the end of a time period and the values are summed over the report interval.  For example, in a report showing 2010 revenues we would assume the values reflect total revenues at the end of the year.  In line with this assumption, the default Table settings in iThink include reporting Ending balances, Summed flow values, and a report interval of one time step.

In a research setting, scientists tend to prefer reporting precise values at a particular time.   For this reason, the default Table settings in STELLA are configured to report Beginning balances, Instantaneous flow values, and a report interval of Every DT.

Table Default Settings Comparison

STELLA or iThink

When choosing between STELLA or iThink, try to think about the kinds of models you intend to build and the problems you are looking to solve.  If your objective is to drive business improvement, chances are iThink will be a better fit.  If your purpose is to understand the dynamics of a natural environment or social system, STELLA will likely be your brand of choice.  Whatever you decide, both products will provide you with the exact same functionality and can easily be configured to suit your own preferences.

This is the last of a three-part series on the Limits to Growth Archetype.  The first part can be accessed here and the second part here.

Last time, we explored the effects of Type 1 rivalry (rivalry between different companies in a developing market) on the Bass diffusion model by replicating the model structure.  This part will generalize this structure and add Type 2 rivalry (customers switching between brands).

Bass Diffusion with Type 1 Rivalry

To model the general case of an emerging market with multiple competitors, we can return to the original single company case and use arrays to add additional companies.  In this case, everything except Potential Customers needs to be arrayed, as shown below (and available by clicking here).

For this example, three companies will be competing for the pool of Potential Customers.  Each array has one-dimension, named Company, and that dimension has three elements, named A, B, and C, one for each company.  Although each different parameter, wom multiplier, fraction gained per $K, and marketing spend in $K, can be separately specified for each company, all three companies use the same values initially.  All three companies, however, do not enter the market at the same time.  Company A enters the market at the start of the simulation, company B enters six months later, and company C enters six months after that.

Recall that the marketing spend is the trigger for a company to start gaining customers.  Thus, the staggered market entrance can be modeled with the following equation for marketing spend in $K:

STEP(10, STARTTIME + (ARRAYIDX() – 1)*6)

The STEP function is used to start the marketing spend for each company at the desired time.  The ARRAYIDX function returns the integer index of the array element, so it will be 1 for company A, 2 for company B, and 3 for company C.  Thus, the offsets from the start of the simulation for the launch of each company’s marketing campaign are 0, 6, and 12, respectively.

This leads to the following behavior:

Note that under these circumstances, the first company to enter the market retains a leadership position.  However, companies B and C could anticipate this and market more strongly.  What if company B spent 50% more and company C spent 100% more than company A on marketing that is similarly effective?  This could be modeling by once again changing the equation for marketing spend in $K, this time to:

STEP(10 + (ARRAYIDX() – 1)*5, STARTTIME + (ARRAYIDX() – 1)*6)

Read more…

This is part two of a three part series on Limits to Growth.  Part one can be accessed here and part three can be accessed here.

In part one of this series, I explained the Limits to Growth archetype and gave examples in epidemiology and ecology. This part introduces the Bass diffusion model, an effective way to implement the capture of customers in a developing market. This is also used to implement what Kim Warren calls Type 1 rivalry in his book Strategy Management Dynamics, that is, rivalry between multiple companies in an emerging market.

The Bass Diffusion Model

The Bass diffusion model is very similar to the SIR model shown in part one. Since we do not usually track customers who have “recovered” from using our product, the model only has two stocks, corresponding loosely to the Susceptible and Infected stocks. New customers are acquired through contact with existing customers, just as an infection spreads, but in this context this is called word of mouth (wom). This is, however, not sufficient to spread the news of a good product, so the Bass diffusion model also includes a constant rate of customer acquisition through advertising. This is shown below (and can be downloaded by clicking here).

The feedback loops B1 and R are the same as the balancing and reinforcing loops between Susceptible and Infected in the SIR model. Instead of an infection rate, there is a wom multiplier which is the product of the Bass diffusion model’s contact rate and the adoption rate. If you are examining policies related to these variables, it would be important to separate them out in the model.

The additional feedback loop, B2, starts the ball rolling and helps a steady stream of customers come in the door. If you examine the SIR model closely, you will see that the initial value of Infected is one. If no one is infected, the disease cannot spread. Likewise, if no one is a customer, there is no one to tell others how great the product is so they want to become customers also. By advertising, awareness of the product is created in the market and some people will become customers without having encountered other customers who are happy with the product.

The behavior of this model is shown below. Note it is not different in character from the SIR model or the simple population model.

Read more…

This is the first of a three-part series on the Limits to Growth Archetype.  The second part can be accessed here and the third part here.

The Limits to Growth Systems Archetype, also known as Limits to Success, combines growth with an exogenous or endogenous limit.  This Systems Archetype was formally identified in Appendix 2 of The Fifth Discipline by Peter Senge (1990), but made its first prominent appearance in World Dynamics by Jay Forrester (1971) and then The Limits to Growth by Meadows, Meadows, Randers, and Behrens (1972).  The Causal Loop Diagram (CLD) is shown below.

Real growth processes have inherent limits to growth.  Identifying these limits can help avoid problems in the future, whether the problem is overpopulation, increasing demand for a product that cannot be met, or growing a business in a mature market.  When growth is desired, but limited, it is always better to find ways to increase the limit before pushing for more growth.  Excessive growth in the face of a limit often leads to collapse.  Driving the system to the point of collapse can erode the ability to continue after the collapse, for example, by reducing the production capability of a piece of farmland or destroying the reputation of a company.

Classic examples of limits to growth include:

• The collapse of the deer population on the Kaibab plateau and on St. Matthew Island due to overpopulation and the attendant overgrazing of their habitat
• The overshoot and collapse of the human population on Easter Island
• Overgrazing in the Sahel region of Africa by cattle herders
• Overfishing of the oceans by fishermen
• The collapse of People Express due to sharp customer growth combined with slow personnel growth
• The sharp exodus of America Online subscribers after an intense marketing campaign increased the number of subscribers far beyond their capacity
• The contraction of the world economy in 2008 due to limiting oil supplies
• The productivity of staff deteriorating as a company grows, due to increased interactions and reporting overhead
• Business growth limited by the size of the potential market
• Yeast cells in the fermentation process, who suffer from both the loss of exogenously supplied sugar and the increase of endogenously produced pollution

Read more…

It seems like everyone has been talking about H1N1 (swine flu) the last couple of months.  If you have children in school, then you are probably very aware of how fast the virus is spreading.  Schools are the perfect environment for a virus to spread.  To help understand why, we created a STELLA model of a high school that introduces the H1N1 virus.  You can experiment with vaccination and “stay at home” policies to limit the spread of the flu.

The STELLA model is based on the SEIR compartmental model that epidemiologists use to model the progress of an epidemic.  SEIR models divide the population into compartments: Susceptible, Exposed, Infected and Recovered.  These ‘compartments’ translate nicely into stocks within the STELLA model where we can observe the dynamics of the spreading virus.

While developing the model we decided to explore some strategies that schools are pursuing to limit the virus’ spread.  We wanted to know if the “stay at home” (when you are sick) policy would be effective in the case where vaccines are not available quickly enough, (which as of November 2009 is the case).

Take a look:

Click the ‘Simulate’ link on the home screen above and try some different scenarios.  Be sure to click the ‘How does this simulation work?’ link for a guided tour of the model behind the simulation.

As you experiment with the simulation, consider the following:

• How does varying “% vaccinated” effect the number of sick students?
• How many days do infected students need to stay home to have a significant impact on the spread of the virus within the school?
• What impact does the “% effectiveness of vaccine” have on the flu outbreak?
• What combination of decisions results in the lowest number of sick students?  Are these decisions realistic in a real-world setting?

Note: Each time you dial in parameters and press run, a new plot will be added to the graph so you can compare the effectiveness of the different decisions.  Clicking on the blue reset button will clear the graph and reset all Knobs to their default value.

If you think this simple model is useful, feel free to share it or embed it on your own website; just click the sharing icon in the lower right corner.  If you want to dig deeper into the STELLA model you can download the model by clicking here.  You can open the model with STELLA 9.1, or the free isee Player.

My first introduction to the Systems Archetypes was years ago when I read Peter Senge’s book, The Fifth Discipline.  I remember relating these classic Systems Thinking stories to my own experience in business and thinking how useful it was to understand some of the problems we faced and why solutions didn’t always work out as intended.

What I’ve come to appreciate since then is how the characteristic themes of the Systems Archetypes transform across all sorts of different fields and situations — even our personal lives!

Take, for example, the basic story line of the “Success to the Successful” archetype:

When given the choice, we invest our resources where we expect them to deliver the best results.  By giving more resources to one option over another, we create a self-fulfilling prophesy whereby the favored option is perpetually more successful.

The story of the Success to the Successful archetype applies to all sorts of  situations leading to well-known patterns of behavior:

• rich getting richer and poor getting poorer
• winners being rewarded with the means to win again
• popular brands attracting more popularity
• one species driving another to extinction
• top salespeople being paid higher commissions

Exploring the Model Structure

We recently published a model of the Success to the Successful archetype to the web using isee NetSim. Exploring the model is a great way to understand the underlying structure of the system and think about ways to avoid the problems it creates.

You’ll also get an appreciation of how the decision policy for allocating resources can determine success rather than competence.

Running the Simulation

After you’ve explored the model, try running a simulation.  The base case scenario assumes no one has an advantage over the other.  As you can imagine, everyone is equally successful and it’s a win-win situation.  Try turning on the “Advantage A Switch”  to see how even a modest advantage for A can snowball into a disadvantage for B.  It’s surprising how quickly the gap can widen.

Using Modules to Create Models

In STELLA and iThink version 9.1, we added the ability to build models by linking together modules.  The Success to the Successful model is an example of how you can use modules to create a higher level map of your model.  This map can easily be presented as a causal loop diagram.

The beauty of modules is they simplify the process of transitioning from a CLD to a model that actually simulates.  If you’ve ever tried to convert a causal loop diagram into a stock and flow model, you can appreciate what I’m talking about!  By architecting your model into modules, you’ve got a built-in mechanism for developing your model in manageable chunks and communicating the high level causal relationships.

We just released another update to STELLA and iThink — Version 9.1.3.  This particular release has a number of updates specific to key features.  For example, if you’re a Macintosh user and rely on the data import/export functionality, you’ll definitely appreciate the updates we made to make sure the software is compatible with the latest versions of Excel for Mac.

Updates to the ARRAYRANK builtin include an optional parameter to specify a secondary sort field for variables with the same value.  You can now also use the ARRAYRANK builtin  in a non-arrayed variable.

My personal favorite in this release, though, is an enhancement we made to the Spatial Map utility. In addition to color configurations, you can now assign an image to a range of values.  This really expands your ability to create  interesting visual representations of spatial data.

Using images to visualize simulations

This simple model of trees burning in a forest illustrates how images can be used in a spatial map configuration.

The model is set up so that initially there is one tree in the middle of the forest that is burning.  All of the other trees in the forest are healthy living trees.

When you run the simulation, the images in Spatial Map allow you to see the fire spreading to adjacent trees. It really adds to the visual effect of what’s happening in the model. Eventually the burning trees die out and you’re left with a forest mostly full of dead trees — now that’s a visual!

If you want to learn more about Spatial Map, check out Karim Chichakly’s series on spatial modeling in iThink and STELLA .

A full list of the features and fixes in Version 9.1.3 is available on our web site.  If your Technical Support Contract is current, you can go ahead and download the update now from your My Software page.

Hope you have as much fun with the spatial mapping as I did!