Harnessing OR through Decision Support Systems: Emerging Trends

1. Harnessing OR through Decision Support Systems:Emerging Trends Prof. T.P. Rama Rao Computer and Information Systems Group, Indian Institute of Management, Ahmedabad-380015 ramarao@iimahd.ernet.in

2. Evolution: DSS-ES-GDSS Well Structured DP ES GDSS Problem Structure DSS Ill Structured Few Many Group Size Supported TPR, IIMA

3. Current Computing Environmentswith internet access Often used for Mundane applications, Primitive User interfaces, and weak model linkages Powerful 32 bit processors (Pentium 4, 2.0 GHz) Large Main Memories (256 MB – 512 MB) Large Disk Capacities (40 GB – 80 GB) Powerful OS with GUI, Multi-tasking and End-user Tools Win XP, Excel, VBA, Access SPSS, SAS, CPLEX Internet Connectivity and Several collaborating Organizations (web services) Internet Service Provider Web server of Participating Organization Internet Web server of Participating Organization PC Anywhere Web server of Participating Organization TPR, IIMA

4. Take OR to Practitioners through DSS with Desktop tools Decision Support Systems Terminal where workspace, Operations Training, Messages are Displayed by DSS And where operations are selected by the User Control Control Mechanisms Table Graph Map Procedure Language Representations Operations on Tables Operations on Graphs Operations on Maps Procedure Construction Operations Operations Library and Defaults Library and Defaults Library and Defaults Library and Defaults Memory Aids Views and Links Database TPR, IIMA

5. DSS with Interactive Graphics & Optimization Model(DSS for Location of Service Centers) TPR, IIMA

6. DSS with Interactive Graphics & Optimization Model(Location of Service Centers) Now (2004) Re-Developed with MS EXCEL & VBA Using Solver, Graphic Class library On Desktop PC <50 lines of VBA code Each Run: Instantaneous Smooth interfacing of Models and Graphics Problem: Optimal use of Resources Rural Development context Enhance Coverage Minimize Cost 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 Accomplished through Unconventional use of Chart Object for Spatial Planning Modeling with Spreadsheet as front-end TPR, IIMA

7. DSS for Job scheduling(Chart Object, Solver and Financial functions)Optimization, Heuristics & Interactive Gantt Charting TPR, IIMA

8. DSS for Retail Store Location MAP scanned, Pasted on chart object, Coordinates obtained through Mouse clicks TPR, IIMA

9. DSS for Simple Supply Chain TPR, IIMA

10. Simple Executive Information System (EIS) with Pivot Tables and OLAP Cubes TPR, IIMA

11. Distributed Optimization Tools: NEOS Solvers TPR, IIMA

12. Using Distributed Resources for DSS Internet / Intranet NEOS Servers DSS Results Data File • Current method is Asynchronous (Interactively send & Receive) • Desired – Through Program control (Use APIs - XML-RPC) TPR, IIMA

13. NEOS Solvers TPR, IIMA

14. Solving Traveling Salesman Problem (TSP)using NEOS Solvers TPR, IIMA

15. Solving TSP using NEOS Solvers TPR, IIMA

16. Using NEOS Solver results on DSS for Traveling Salesman Problem

17. Web-enabled DSS Browser HTML Interpreter Java Interpreter URL User Display Internet / Intranet DBMS Web server process UI Application process Web Page Model Management System Web Server DSS Server TPR, IIMA

18. GDSS DSS Interactive computer-based system that facilitates solution of un-structured problems by decision makers working as group Other Group Members Internet Intranet Individual Problem Solvers Electronic questionnaires Electronic brainstorming tools Idea organizers Questionnaire tools Tools for voting or setting priorities Stakeholder identification and analysis tools DSS Software GDSS software Model base Database Meetingware (WW Uty) Decision Conferencing (LSE) Strategic Choice Approach (STRAD, UK) Strategic Options Development & Analysis (SODA ) Improved preplanning Increased participation Open, collaborative meeting atmosphere Criticism-free idea generation Evaluation objectivity Idea organization and Evaluation TPR, IIMA

19. Requirements to Tap the Power ofEnd-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

20. 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

21. 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 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 IGraphicClass Public WithEvents imap As Chart Module Code: Subroutines for each Event TPR, IIMA

22. 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

23. 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

24. 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 Maximize. 2 Minimize. 3 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   Optional Variant. • True to return the results without displaying the Solver Results dialog box. • False 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 function. You can pass the name of a macro (as a string) as the ShowRef argument. This macro is then called whenever Solver returns an intermediate solution. TPR, IIMA

25. 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

26. 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 worldTap them allyou can develop exciting DSS solutionsBest Wishes !!! TPR, IIMA