Exsched:

1. Exsched: Solving Constraint Satisfaction Problems with the Spreadsheet Paradigm Gopal Gupta UT Dallas & Logical Software Solutions Siddharth Chitnis, Madhu Yennamini University of Texas at Dallas

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

3. Spreadsheet Paradigm • Man-machine interface for handling complex multi-dimensional data. • Paper and pencil approach to solving problems • Spreadsheets: Popular for arithmetic computations • Current spreadsheets: limited to arithmetic • Arithmetic expressions are interactively entered and evaluated until desired results are obtained • Repetitive computations are performed by copying expressions from one cell to a range of cells, with appropriate transformation applied • Question: Can we generalize these arithmetic (functional) computations (to relational)?

4. Spreadsheets and CSPs: Motivation • Designing schedules is a problem that arises quite frequently: • Class schedules • Employee schedules • Examination schedules • Job schedules • Degree audits for students • These schedules have a tabular, 2-D structure • The constraints to be met are similar across rows and columns • In general, many constraint satisfaction problems such as timetabling and scheduling problems, recreational puzzles can be modelled as tables of constraints • Use of spreadsheet paradigm for this purpose • Goal: Design an interface that facilitates the interactive development of such tabular schedules

5. CS Class Schedule

6. Spreadsheet and CSPs • 2-D data-centered nature suggests use of of the spreadsheet paradigm to interactively develop these schedules • Scheduling requires constraint solving: generalize functional arithmetic expressions to relations (constraints/predicates). • Obvious generalization: Use CLP(R) constraints • We go one step further: generalize spreadsheets so that finite domain constraints can also be entered in the cells • Yesterday: Knowledgesheet • Today: Exsched, plug-in for Microsoft Excel

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

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

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

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

11. 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) An Empty Table

12. Solution: Employee Schedule (continued) • Morning, midday and evening shifts are denoted by 5, 2 and 4 respectively • 0 is used to indicate a manager’s day off • Domain of each cell: [0,2,4,5] • User enters domain in one cell, copies it to the rest • For no morning after night restriction, we enter the constraint: C2 != B2 + 1 (copied everywhere) • At least one manager is present at any time during the day: member(4,[D2,D3,D4,D5,D6]), member(5,[D2,D3,D4,D5,D6]) • No manager works for more than 5 days a week: count(0,[B2,C2,D2,E2,F2,G2,H2],=,2) • Every manager has more or less same proportion of morning, midday and evening shifts: sublist([2,4,5],[B2,C2,D2,E2,F2,G2,H2])

13. 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(0,[B2,C2,D2,E2,F2,G2,H2],=,2), sublist([2,4,5], [B2,C2,D2,E2,F2,G2,H2]) (Row Constraints) [0,2,4,5], C2 != B2 + 1 (Cell Constraints) member(4,[D2,D3,D4,D5,D6]), member(5,[D2,D3,D4,D5,D6]) (Column Constraints)

14. Solution: Employee Schedule (continued) Displaying a solution

15. Solution: Employee Schedule (continued) Displaying a solution along with a mapping of variable values

16. Example: The 3x3 Grid Puzzle Cell constraints: • B3: B3+C3+D3 #= 15, B3+B4+B5 #= 15 • C3: C3+C4+C5 #= 15 • D3: D3+D4+D5 #= 15 • B4: B4+C4+D4 #= 15 • B5: B5+C5+D5 #= 15, B5+C4+D3 #= 15 • D5: B3+C4+D5 #= 15, alldiff([B3,B4,B5,C3,C4,C5,D3,D4,D5])

17. Solution: The 3x3 Grid Puzzle

18. Example: Cryptarithmetic Puzzles • Most puzzles have such a graphical structure; for example, Zebra puzzle • Exsched can be used for solving puzzles published in popular puzzle magazines D2 #= (E2+E3+E4) DIV 10 E5 #= (E2+E3+E4) MOD 10

19. 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 • The whole schedule cannot be generated in one shot • Obtain the schedule piecemeal, while manually adjusting the choices. • Or: set instructors first, set timings next, set classroom last. • If a solution is not found (or the system takes too long), relax constraints or reduce the size of the query table, until a solution is found, then gradually increase the query table • Another approach: divide the query table into N pieces, solve each piece individually, then enforce global consistency manually.

20. Current Work • Add auxiliary tables support • Develop conventions and add support for • Mapping tables • Reverse mapping tables • Add ability to hide constraints (constraint relaxation) • Add ability to hide rows/columns (constraint relaxation) • Add more function buttons • Allow individual cells to be named. • Support for automatic constraint relaxation?? • Support for Macros in CLP(FD)?? • Add CLP(R) support (already part of SICStus) • Spreadsheet for Engineering Design Our overarching philosophy is to provide all kinds of options to the user rather than providing problem solving strategies

21. Conclusion • Advantages of the Exsched Approach: • Flexibility • Interactivity • Non-experts can use it (Expert System Shell) • Managers are resource allocators!! • Standard (deficient) spreadsheets currently used (sorting fn used a lot) • Domain specific knowledge can be incorporated • User and clp(FD) system cooperate to produce solutions • User can give partial solutions, the rest can be computed using ExSched • Disadvantages: • Works only for tabular clp(FD) programs • No automatic help if the system is over-constrained • Challenges: Collecting data (e.g., preferences) • DEMO LATER

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