Home > Modeling Tips > Tip: Setting up imports for 2D Graphical Functions

Tip: Setting up imports for 2D Graphical Functions

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.

Let’s say you have a model set up to look at 3 teams’ performance on 3 different projects.  The model has an array of Projects and Teams. The Project dimension has 3 elements: Project 1, Project 2 and Project 3.  The Team dimension has 3 elements: Team 1,Team 2 and Team 3.

In this model we’ll put down a converter called “Performance rates” which we’ll later define as a 2D array of graphical functions over time.  The converter will contain historical data on the various teams performance rates for the various projects.

You can see how this sort of setup is pretty close to what someone would want to do – track performance over time per project per team.

The format of the Excel spreasheet would look like this:

Project 1   Project 2   Project 3
Team 1 Team 2 Team 3 Team 1 Team 2 Team 3 Team 1 Team 2 Team 3
                 
Performance rates *** ***
100 34 63 98 87 18 20 14 3
45 25 78 69 35 99 25 13 25
67 53 23 100 43 78 98 55 76

Notice that the Team 2 and Team 3 columns have an ellipsis (…) in the header that indicates the column is still part of the Project 1 array element.  Start the next array element, Project 2 with 3 asterisks  (***) at the top of the column.

This is important: The converter name is listed first, then then an ellipsis for each element of the team dimension, (since Team is our first dimension).  The Project dimensions are delimited by the *** and of course the time data goes down.

After setting up your spreadsheet, you  need to set the converter correctly.

Converter Dialog:

Converter

In the converter dialog, first check Array, change it from 1D to 2D, (Teams and Projects) and leave Apply to All checked.

Click on the “Become Graphical Function” button on the lower left.  This will switch to the Graphical Function dialog.

Graphical Function Dialog:

graphical_function

Uncheck Apply to All and be sure you have 3 data points set up for each Row/Column combination.  This corresponds to the three time data points we set up going down in the Excel sheet.

Import dialog

Data imported!

The data has been imported into the conveter

Open the Import Data dialog under the Edit Menu. Set up the link so that you are pointing to the spreadsheet you just created.

Select the Import Type you want and browse for the Excel file and worksheet you created.  The data orientation you pick should match the orientation of the spreadsheet.

Once you click OK, a message will let you know if data was successfully imported or not.

Now check to see that the data was imported by hovering over the arrayed variable.

It worked!! You’re good to go.

 >> Download the Sample Files

 

If you enjoyed this post, make sure you subscribe to my RSS feed!
  • Many thanks for the helpful tips. Now I can easily import data from Excel. good work and God bless!

    Prof. Juan
    Central Mindanao University
    University Town, Musuan
    8710 Bukidnon
    Philippines

  • Panand

    Really good demonstration –

  • Panand

    Really good demonstration –

  • Xiapeiya

    Thanks Sarah for the tip and the sample. But I am unable to make it work! Stella keeps saying that “…” are missing variables. Any idea?

  • Ch_sawvapark

    how can import data to be array(parity) initial if each parity has different value.

    • Sdavie

      I am afraid I don’t understand your question. Please e-mail support@iseeesystems.com so we can discuss further.  Thank you!

  • robert

    If i have a model that i want to run repetitively (like a 100 times) with a different set of random numbers, how can this be done? I was thinking of having a counter for the repetition i am in, I then I can look in the 10th or 12th coloumn for my set of random numbers. Really do not want to hard code this… Thanks so much for your help.

  • sigmund

    If there are more than 2000 projects, project 1, project 2,…, project 2000. I want to use 1001, 1002,…, 3000 instead of project 1, project 2, …. Is there any method to import them from excel, or is there any function to set them in stella? Thank you.