ExSched

1 / 29

# ExSched - PowerPoint PPT Presentation

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.

## PowerPoint Slideshow about 'ExSched' - benoit

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

### ExSched

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)
• 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
• 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
• Currently solved using
• Paper Pencil-based Approach
• Constraint based languages like CLP(FD)
• Manual computation is tedious, erroneous, time consuming
• Constraint Languages are syntactically complex
• 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
• 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
• 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
• 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)
• 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

Collects constraints and composes a clp(FD) program

solution

Clp(FD) engine

Display the solution in the spreadsheet

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
• 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
• 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)
• 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:

count(off,[B2,C2,D2,E2,F2,G2,H2],=,2)

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

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

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.

count(off,[B2,C2,D2,E2,F2,G2,H2],=,2),

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
• 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
• 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
• 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)
• 16 time slots
• Mapped into integer values 1 through 16
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
• Functionality to prioritize constraints
• Support for automatic constraint relaxation
• In case of clashing constraints, the system will drop some constraints to break the tie

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

Conclusion
• 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
• Works only for problems which can be modeled as two dimensional tables
So…
• ExSched: Extension of spreadsheet (MS Excel) to help users interactively model and solve complex scheduling, timetabling problems

Relax!!

Use ExSched

Large scheduling problem??

Wow!! Its easy

References
• 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