Archive

Posts Tagged ‘import’

Connecting iThink and STELLA to a Database

April 28th, 2011 5 comments

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.

Process overview

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.

Retail Supply Chain Model

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.

Read more…

Tip: Setting up imports for 2D Graphical Functions

January 29th, 2009 8 comments

iThink and STELLA version 9.0 introduced a powerful new feature: data import and export with Excel.  Here at isee, we were excited to hear from our customers about how much they appreciated the feature and how easy it was to use.  In fact, many customers told us that it was the major reason they upgraded to version 9.0
simple import 1

Setting up a data import from Excel is pretty straight forward:  type the name of the model variable in one cell, type the value you want to import in the cell below it, (or beside it if you are using horizontal orientation):

If you are importing a time series or graphical function, you just keep listing the data points in the cells below the variable name:

simple import 2

This kind of data formatting works well since an Excel sheet is made up of columns and rows.  But how do you format data that is harder to visualize?  Things get a little tricky when you start dealing with arrayed variables.  The most complex import is a 2 dimensional array of graphical functions.  It’s actually 3 dimensions!

I’ve put together a simple example to illustrate how to do this.

Read more…

Save Time Setting up Data Imports

January 27th, 2009 No comments

Setting up an Excel spreadsheet to import data into your model is easy when you’ve got just a few variables but it can get cumbersome when you’ve got lots of variables, especially if some of them are arrayed.

If you want to avoid lots of copying, pasting, and switching back and forth between your model and Excel, we suggest using the export data feature to set up your data import spreadsheet.

Below are a few simple steps that can save you a bunch of time and headache:

Click to expand

Click to expand

  1. In your model, create a Table with all of the variables that you want to import data to.
  2. From the Edit menu, choose Export Data. The Export Data dialog will appear.
  3. Under “Export Type”, select One Time.
  4. Under “Export Data Source”, select Export variables in table and select the table that you created in Step 1.
  5. Under “Interval”, select One set of values.
  6. Under “Export Destination”, Browse to an empty Excel file and Worksheet Name that you have created for your data import.
  7. Select the “Data Orientation” that you prefer.
  8. Click OK.

Voila! Your import sheet should be all set to enter data values for each of the variable names AND it will be in the correct format.

Categories: Modeling Tips Tags: , , ,