ExSched

1. ExSched Solving Constraint Satisfaction Problems with the Spreadsheet Paradigm Gopal Gupta and Siddharth Chitnis The University of Texas at Dallas

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

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

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

5. CS Class Schedule

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

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

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

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

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

11. System Diagram Collects constraints and composes a clp(FD) program solution Spreadsheet with constraints Clp(FD) engine Display the solution in the spreadsheet

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

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

14. 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)

15. 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])

16. 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)

17. Solution: Employee Schedule (continued)

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

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

20. Course Scheduling (Map Table) • 16 time slots • Mapped into integer values 1 through 16

21. Course Scheduling (Query Table)

22. Course Scheduling (Query Table)

23. Solution: Course Scheduling

24. Solution: Course Scheduling (solving in parts) • Solve part of the problem • Freeze the solution for the solved part • Solve the whole problem now

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

26. 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 • Disadvantages: • Works only for problems which can be modeled as two dimensional tables

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

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

29. Questions