exsched n.
Skip this Video
Loading SlideShow in 5 Seconds..
ExSched PowerPoint Presentation
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 29

ExSched - PowerPoint PPT Presentation

  • Uploaded on

ExSched. Solving Constraint Satisfaction Problems with the Spreadsheet Paradigm Gopal Gupta and Siddharth Chitnis The University of Texas at Dallas. NFL Scheduling. 32 teams in 8 divisions within 2 conferences each team plays 6 divisional games (3 home and 3 away)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'ExSched' - benoit

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript


Solving Constraint Satisfaction Problems with the Spreadsheet Paradigm

Gopal Gupta and Siddharth Chitnis

The University of Texas at Dallas

nfl scheduling
NFL Scheduling
  • 32 teams in 8 divisions within 2 conferences
  • each team plays
    • 6 divisional games (3 home and 3 away)
    • 4 games against another division within the conference on a rotational basis
    • 4 games against another division in the other conference on a rotational basis
    • 2 games against teams in the same conference with the same previous season finish
  • 350 games to schedule
  • 32*6*4*4*2 = 32,256 combinations
  • fair schedule
  • schedule according to logistics available, TV coverage

NFL scheduling team

Paper-pencil based approach

constraint satisfaction problems
Constraint Satisfaction Problems
  • Designing schedules is a problem that arises quite frequently
    • Class schedules
    • Employee schedules
    • Examination schedules
    • Flight schedules
    • Degree audits for students
  • Resource Allocation
    • Job schedules
  • NP-hard problems
    • Time to solve these problems increases exponentially with increase in the size of the problem
current approach
Current Approach
  • Currently solved using
    • Paper Pencil-based Approach
    • Constraint based languages like CLP(FD)
  • Disadvantages
    • Manual computation is tedious, erroneous, time consuming
    • Constraint Languages are syntactically complex
csps and spreadsheets motivation
CSPs and Spreadsheets: Motivation
  • These schedules have a tabular, 2-D structure
  • In general, many constraint satisfaction problems such as timetabling and scheduling problems, recreational puzzles can be modeled as tables of constraints
  • Goal: Design an interface that facilitates the interactive development of such tabular schedules
    • Use of spreadsheet paradigm for this purpose
the spreadsheet paradigm
The Spreadsheet Paradigm
  • Used for Manipulating Table(s) of Data
    • Data Centered: User is always looking at the data; programming is done around the data (data-oriented prog.)
    • Data items in each row/column have similar characteristics
  • Programming done by replication
    • Replication is parametrized: give one example of a computation, then replicate it multiple times (with appropriate transformations applied)
    • No looping construct used: iterations replicated explicitly with the index variables set appropriately
spreadsheet paradigm as solution
Spreadsheet Paradigm as Solution
  • Man-machine interface for handling complex multi-dimensional data
  • Current spreadsheets: limited to arithmetic
    • Arithmetic expressions are interactively entered
    • Repetitive computations are performed by copying expressions from one cell to another, with appropriate transformation applied
  • Scheduling requires constraint solving: generalize functional arithmetic expressions to relations (constraints)
  • Our solution: We generalize spreadsheets so that finite domain constraints can also be entered in the cells
    • ExSched: plug-in for Microsoft Excel
exsched interface
ExSched Interface
  • Interface similar to regular spreadsheet
    • Extension of MS Excel
  • Each cell can be thought of as a variable or a place holder
  • A user can enter finite domain values in a cell. These finite domain values denote the finite domain of the variable corresponding to the cell

Example: [1..5]

  • Constraints can also be entered in the cell. Constraints contain variable names (cell coordinates) and constants

Example: B3 #= C4 + 1

interface continued
Interface (continued)
  • Constants can also be entered in the cell: the variable corresponding to that cell is set to the constant entered
  • Constraints/constants/finite domains can either be entered into the current cell or via dialog boxes
  • Constraints can be copied to a range of cells; appropriate transformations are applied while copying
  • Large number of of built-ins available as clickable buttons
    • alldifferent, count, cumulative, element, subset
  • Once constraints/constants/finite domains are entered
    • the system automatically collects them,
    • composes a clp(FD) program,
    • solves it using clp(FD) engine running in the background and
    • displays the solution
system diagram
System Diagram

Collects constraints and composes a clp(FD) program


Spreadsheet with constraints

Clp(FD) engine

Display the solution in the spreadsheet

interface continued1
Interface (continued)
  • The user must enter
    • at least one Query Table and
    • zero or more Auxiliary Tables
  • Query Table is used to compose the query
    • The query table could be as small as one cell
  • Auxiliary tables turn into facts: auxiliary tables useful in mapping non-integer domain values into integers
  • Computed results for the query are displayed in the query table
  • User can highlight a part of the query table, and only those cells are included in the query.
example employee schedule
Example: Employee schedule
  • Scheduling managers at a store:
    • Store hours : 8 AM to 11 PM, 7 days / week
    • Each manager must work 8.5 hrs / day (includes 0.5 hrs for lunch)
    • Each manager must work 5 days / week
    • At least one manager must be present at any moment
    • Managers working evening shifts should not be allocated morning shift the following day
    • Schedule must be fair to all managers
  • In most cases, this scheduling is done manually
    • Erroneous, leads to employee dissatisfaction
solution employee schedule
Solution: Employee Schedule
  • Assume that there are 5 managers
  • Each manager works 8.5 hrs per day either in
    • The morning shift (8:00 AM to 4:30 PM), or
    • The midday shift (10:00 AM to 6:30 PM), or
    • The evening shift (2:30 PM to 11:00 PM)
solution employee schedule continued
Solution: Employee Schedule (continued)
  • Morning, midday and evening shifts are denoted by 1, 2 and 3 (say) and 0 is used to indicate a manager’s day off using auxiliary table
  • Domain of each cell: [morning,midday,evening,off]
  • User enters domain in one cell, copies it to the rest
  • For no morning after night restriction, we enter the constraint:

IF (B2 = evening) THEN (C2 != morning)

  • At least one manager is present at any time during the day:

member(morning,[D2,D3,D4,D5,D6]), member(evening,[D2,D3,D4,D5,D6])

  • No manager works for more than 5 days a week:


  • Every manager has more or less same proportion of morning, midday and evening shifts:


solution employee schedule continued1
Solution: Employee Schedule (continued)

Note: Cell constraints are replicated in all 35 cells, column constraints in B7 through H7 and row constraints in I2 through I6.


sublist([miday,evening,morning], [B2,C2,D2,E2,F2,G2,H2])

(Row Constraints)

[morning,midday,evening,off], IF(B2=evening) THEN (C2!=morning)

(Cell Constraints)

member(morning,[D2,D3,D4,D5,D6]), member(evening,[D2,D3,D4,D5,D6])

(Column Constraints)

solving large problems
Solving Large Problems
  • Exsched is a man-machine interface for solving CSPs
    • Large problems can be solved interactively
    • Note: never look for optimal solution; a solution is enough
  • Consider course scheduling at UT Dallas CS: 120+ courses with 50+ instructors in 9 classrooms
    • Takes 40-50 man hours currently
    • With ExSched less than 5 man hours
  • If a solution is not found because of clashing constraints
    • Flexibility to relax constraints
    • Flexibility to reduce the size of the constraint table until a solution is found, then gradually increase the table
    • Divide the constraint table into N pieces, solve each piece individually, then enforce global consistency manually
example course scheduling
Example: Course Scheduling
  • Schedule day and time for 50 courses
  • Instructor for each course will have a list of preferred timings
  • Different sections of the same class should not overlap
  • Only 9 courses can be scheduled for the same day and time (only 9 class rooms)
  • An instructor cannot teach more than one course at the same time
course scheduling map table
Course Scheduling (Map Table)
  • 16 time slots
  • Mapped into integer values 1 through 16
solution course scheduling solving in parts
Solution: Course Scheduling (solving in parts)
  • Solve part of the problem
  • Freeze the solution for the solved part
  • Solve the whole problem now
current work
Current Work
  • Functionality to prioritize constraints
  • Support for automatic constraint relaxation
    • In case of clashing constraints, the system will drop some constraints to break the tie
  • Add more function buttons

Our overarching philosophy is to provide all kinds of options to the user rather than providing problem solving strategies

  • Advantages of the ExSched Approach:
    • Flexibility
    • Interactivity
    • Non-experts can use it
      • Managers are resource allocators
    • Man-machine interface - user can give partial solutions, the rest can be computed using ExSched
  • Disadvantages:
    • Works only for problems which can be modeled as two dimensional tables
  • ExSched: Extension of spreadsheet (MS Excel) to help users interactively model and solve complex scheduling, timetabling problems


Use ExSched

Large scheduling problem??

Wow!! Its easy

  • G. Gupta and S. Akhter. Knowledgesheet: A Spreadsheet Interface for Solving a Class of Constraint Satisfaction Problems. PADL 2000. Springer LNCS
  • S. Chitnis. Next Generation ExSched. M.S. Thesis. December 2006. Forthcoming.
  • M. Yennamini. ExSched: Solving CSPs with Excel. M.S. Thesis. Dec. 2004. Univ. of Texas at Dallas