Harnessing OR through Decision Support Systems: Emerging Trends

Prof. T.P. Rama Rao Computer and Information Systems Group, Indian Institute of Management, Ahmedabad-380015 [email protected]

Evolution: DSS-ES-GDSS

Well Structured DP ES Problem Structure DSS Ill Structured GDSS Few Group Size Supported Many

TPR, IIMA

PC Anywhere

Current Computing Environments with internet access

Internet Service Provider

Often used for Powerful 32 bit processors Primitive User interfaces,

(Pentium 4, 2.0 GHz)

Large Main Memories

(256 MB – 512 MB)

Mundane applications,

Participating Organization

Large Disk Capacities

(40 GB – 80 GB)

Powerful OS with GUI, Multi-tasking and

Internet

Win XP, Excel, VBA, Access SPSS, SAS, CPLEX

Web server of Participating Organization

Internet Connectivity and Several collaborating Organizations (web services)

Web server of Participating Organization

TPR, IIMA

Decision Support Systems Take OR to Practitioners through DSS with Desktop tools

Terminal where workspace, Operations Training, Messages are Displayed by DSS And where operations are selected by the User

Control

Control Mechanisms

Representations Operations

Table Operations on Tables Library and Defaults Graph Operations on Graphs Library and Defaults Map Operations on Maps Library and Defaults Procedure Language

Procedure Construction Operations

Library and Defaults

Memory Aids

Views and Links Database

TPR, IIMA

DSS with Interactive Graphics & Optimization Model (DSS for Location of Service Centers)

TPR, IIMA

DSS with Interactive Graphics & Optimization Model (Location of Service Centers)

Now (2004) Problem:

Re-Developed with Optimal use of Resources MS EXCEL & VBA Rural Development context Using Solver, Enhance Coverage Graphic Class library Minimize Cost On Desktop PC <50 lines of VBA code Each Run: Instantaneous

Smooth interfacing of Models and Graphics Accomplished through Unconventional use of Chart Object for Spatial Planning Modeling with Spreadsheet as front-end

DSS Solution: Optimize - semi-structured Matrix of Distances Generation of constraints, Optimization Interactive Graphics

Then (1980s)

3 Computers (IIM, PRL) ~5000 lines of code Fortran, HPAL 3 Days for each Run

TPR, IIMA

DSS for Job scheduling

(Chart Object, Solver and Financial functions)

Optimization, Heuristics & Interactive Gantt Charting

TPR, IIMA

DSS for Retail Store Location

MAP scanned, Pasted on chart object, Coordinates obtained through Mouse clicks

TPR, IIMA

DSS for Simple Supply Chain

TPR, IIMA

Simple Executive Information System (EIS) with Pivot Tables and OLAP Cubes

TPR, IIMA

Distributed Optimization Tools: NEOS Solvers

TPR, IIMA

Using Distributed Resources for DSS

DSS Data File

Internet / Intranet

NEOS Servers Results 1. Current method is Asynchronous (Interactively send & Receive) 2. Desired – Through Program control (Use APIs - XML-RPC)

TPR, IIMA

NEOS Solvers

TPR, IIMA

Solving Traveling Salesman Problem (TSP) using NEOS Solvers

TPR, IIMA

Solving TSP using NEOS Solvers

TPR, IIMA

Using NEOS Solver results on DSS for Traveling Salesman Problem

Web-enabled DSS

Browser

HTML Interpreter Java Interpreter URL

User Display Internet / Intranet

Web Page process UI Application process

Web Server

DBMS Model Management System

DSS Server

TPR, IIMA

Individual Problem Solvers

G

DSS

Internet Intranet Other Group Members

Interactive computer-based system that facilitates solution of un-structured problems by decision makers working as group

DSS Software GDSS software Electronic questionnaires Electronic brainstorming tools Idea organizers Questionnaire tools Tools for voting or setting priorities Stakeholder identification and analysis tools Model base Database Improved preplanning Increased participation Open, collaborative meeting atmosphere Criticism-free idea generation Evaluation objectivity Idea organization and Evaluation

Meetingware (WW Uty) Decision Conferencing (LSE) Strategic Choice Approach (STRAD, UK) Strategic Options Development & Analysis (SODA )

TPR, IIMA

Requirements to Tap the Power of End-User Tools for DSS

• Modeling with Spreadsheets – Formula design, Table lookups, Data Tabulation, • VBA Programming – Interfacing with Excel (Controls, Forms) • Simple concepts of Object-oriented Programming – Creating Chart Classes • Features of Chart Object in VB • Interfacing Solver through VBA • Features of Pivot Table functions in VBA

TPR, IIMA

Some Issues

• Solver credibility – Instability with Large / non-linear Problems – Replace by credible math programming packages like CPLEX • Response times – With large problems - Due to Interpretive Processing – Move to compiler versions after prototype stage • Inadequate Documentation – Chart Object, Solver, Pivot Table, OLAP Cube •

Explore with user groups

TPR, IIMA

Using Events with Embedded Charts

Events are enabled for chart sheets by default. Before you can use events with a

Chart

object that represents an embedded chart, you must create a new class module and declare an object of type

Chart

with events. For example, assume that a new class module is created and named

EventClassModule

. The new class module contains the following code.

Public WithEvents myChartClass As Chart

After the new object has been declared with events, it appears in the

Object

drop-down list box in the class module, and you can write event procedures for this object. (When you select the new object in the

Object

box, the valid events for that object are listed in the

Procedure

drop-down list box.) Before your procedures will run, however, you must connect the declared object in the class module with the embedded chart. You can do this by using the following code from any module.

Dim myClassModule As New EventClassModule Sub InitializeChart() Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart End Sub ‘ Our Example Dim locate As IGraphicClass Option Explicit

IGraphicClass

Public WithEvents imap As Chart Private Sub ActivateButton_Click() Dim r As Integer, g As Integer, b As Integer, t As Integer Set locate = New IGraphicClass Set locate.imap = Sheet1.ChartObjects(1).Chart

End Sub Module Code: Subroutines for each Event TPR, IIMA

Using Chart Object in VBA Programs

• If you want use the Chart with Events – Create a Class Module:

For Example with Module Name: IGraphicClass

– Enter the Following statement as first statement of the Module

: Public WithEvents imap As Chart

– After this, code can be entered for several possible Events such as: MouseDown, Select, …..

• • • • • • • Code for imap_MouseDown Event: ' Private Sub imap_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) ' Used to select as well as un-select a Center (toggle) ' If not existing, draws a circle of specified radius around selected center ' If existing, Deletes the circle around the specified center Dim i As Integer, pts As Integer, cs As Integer .

.

End Sub

TPR, IIMA

Adding Constraints

• • •

SolverAdd(CellRef

,

Relation

,

FormulaText)

CellRef

Required

Variant

. A reference to a cell or a range of cells that forms the left side of a constraint.

Relation

Required

Integer

. The arithmetic relationship between the left and right sides of the constraint. If you choose 4 or 5,

CellRef

must refer to adjustable (changing) cells, and

FormulaText

shouldn’t be specified.

•

RelationArithmetic relationship

1 <= 2 = 3 >= 4 Cells referenced by

CellRef

must have final values that are integers.

5 Cells referenced by

CellRef

must have final values of either 0 (zero) or 1 •

FormulaText

Optional

Variant

. The right side of the constraint.

TPR, IIMA

•

Integrating Solver

SolverOk

(

SetCell

,

MaxMinVal

,

ValueOf

,

ByChange

)

SetCell

Optional

Variant

. Refers to a single cell on the active worksheet. Corresponds to the

Set Target Cell

box in the

Solver Parameters

dialog box.

MaxMinVal

Optional

Variant

. Corresponds to the

Max

,

Min

, and

Value

options in the

Solver Parameters

dialog box.

MaxMinVal Specifies

1 2 3 Maximize.

Minimize.

Match a specific value.

ValueOf

Optional

Variant

. If

MaxMinVal

is 3, you must specify the value to which the target cell is matched.

• • • •

ByChange

Optional

Variant

. The cell or range of cells that will be changed so that you’ll obtain the desired result in the target cell. Corresponds to the

By Changing Cells

box in the

Solver Parameters

dialog box

SolverSolve(UserFinish

,

ShowRef)

UserFinish

– –

True False

Optional

Variant

. to return the results without displaying the

Solver Results

dialog box. or omitted to return the results and display the

Solver Results

dialog box.

ShowRef

– Optional

Variant

. Used only if

True

is passed to the

StepThru

argument of the

SolverOptions

name of a macro (as a string) as the

ShowRef

an intermediate solution.

function. You can pass the argument. This macro is then called whenever Solver returns

TPR, IIMA

Integrating PIVOT Tables & Charts

• Passing the Row, Column, and Page fields • Choosing the output formats • Placing Pivot Table and Chart on Same Sheet • Choosing Chart location, size, and type • Drawing data from Access database • Creating OLAP cubes

TPR, IIMA

There is enormous power in end-user tools on desk top (PC) In addition, with connectivity and web services, you have access to resources in the world Tap them all you can develop exciting DSS solutions Best Wishes !!!

TPR, IIMA