Exsched:Exsched:Solving Constraint Satisfaction Solving Constraint Satisfaction Problems with the Spreadsheet Problems with the Spreadsheet

ParadigmParadigm

Gopal GuptaGopal Gupta

UT Dallas & Logical Software SolutionsUT Dallas & Logical Software Solutions

Siddharth Chitnis, Madhu YennaminiSiddharth Chitnis, Madhu Yennamini

University of Texas at DallasUniversity of Texas at Dallas

The Spreadsheet The Spreadsheet ParadigmParadigm

Used for Manipulating Table(s) of DataUsed for Manipulating Table(s) of Data Data Centered: User is always looking at the data; Data Centered: User is always looking at the data;

programming is done around the data (data-programming is done around the data (data-oriented prog.)oriented prog.)

Data items in each row/column have similar Data items in each row/column have similar characteristicscharacteristics

Programming done by replicationProgramming done by replication Replication is parametrized: give one example of a Replication is parametrized: give one example of a

computation, then replicate it multiple times (with computation, then replicate it multiple times (with appropriate transformations applied).appropriate transformations applied).

No looping construct used: iterations replicated No looping construct used: iterations replicated explicitly with the index variables set appropriately.explicitly with the index variables set appropriately.

Spreadsheet ParadigmSpreadsheet Paradigm Man-machine interface for handling complex multi-Man-machine interface for handling complex multi-

dimensional data. dimensional data. Paper and pencilPaper and pencil approach to solving approach to solving

problemsproblems Spreadsheets: Popular for arithmetic computationsSpreadsheets: Popular for arithmetic computations Current spreadsheets: limited to arithmetic Current spreadsheets: limited to arithmetic

Arithmetic expressions are Arithmetic expressions are interactivelyinteractively entered and entered and evaluated until desired results are obtainedevaluated until desired results are obtained

Repetitive computations are performed by Repetitive computations are performed by copying copying expressions from one cell to a range of cellsexpressions from one cell to a range of cells, with , with appropriate transformation appliedappropriate transformation applied

Question: Can we generalize these arithmetic Question: Can we generalize these arithmetic (functional) computations (to relational)?(functional) computations (to relational)?

Spreadsheets and CSPs: Spreadsheets and CSPs: MotivationMotivation

Designing schedules is a problem that arises quite Designing schedules is a problem that arises quite frequently:frequently:

Class schedulesClass schedules Employee schedulesEmployee schedules Examination schedulesExamination schedules Job schedulesJob schedules Degree audits for studentsDegree audits for students

These schedules have a tabular, 2-D structureThese schedules have a tabular, 2-D structure The constraints to be met are similar across rows and The constraints to be met are similar across rows and

columnscolumns In general, many constraint satisfaction problems such as In general, many constraint satisfaction problems such as

timetabling and scheduling problems, recreational puzzles timetabling and scheduling problems, recreational puzzles can be modelled as tables of constraintscan be modelled as tables of constraints

Use of spreadsheet paradigm for this purposeUse of spreadsheet paradigm for this purpose Goal: Design an interface that facilitates the interactive Goal: Design an interface that facilitates the interactive

development of such tabular schedulesdevelopment of such tabular schedules

CS Class ScheduleCS Class Schedule

Spreadsheet and CSPsSpreadsheet and CSPs 2-D data-centered nature suggests use of of 2-D data-centered nature suggests use of of

the spreadsheet paradigm to interactively the spreadsheet paradigm to interactively develop these schedulesdevelop these schedules

Scheduling requires constraint solving: Scheduling requires constraint solving: generalize functional arithmetic expressions to generalize functional arithmetic expressions to relations (constraints/predicates).relations (constraints/predicates).

Obvious generalization: Use CLP(R) constraintsObvious generalization: Use CLP(R) constraints We go one step further: generalize We go one step further: generalize

spreadsheets so that finite domain constraints spreadsheets so that finite domain constraints can also be entered in the cellscan also be entered in the cells

Yesterday: KnowledgesheetYesterday: Knowledgesheet Today: Exsched, plug-in for Microsoft ExcelToday: Exsched, plug-in for Microsoft Excel

Exsched InterfaceExsched Interface Interface similar to regular spreadsheet Interface similar to regular spreadsheet

(extension of MS Excel)(extension of MS Excel) Each cell can be thought of as a variable or a Each cell can be thought of as a variable or a

place holderplace holder A user can enter finite domain values in a cell. A user can enter finite domain values in a cell.

These finite domain values denote the finite These finite domain values denote the finite domain of the variable corresponding to the celldomain of the variable corresponding to the cell

Example: [1..5]Example: [1..5] Constraints can also be entered in the cell. Constraints can also be entered in the cell.

Constraints contain variable names (cell Constraints contain variable names (cell coordinates) and constantscoordinates) and constants

Example: B3 #= C4 + 1Example: B3 #= C4 + 1

Interface (Interface (continued)continued) Constants can also be entered in the cell: the variable Constants can also be entered in the cell: the variable

corresponding to that cell is set to the constant enteredcorresponding to that cell is set to the constant entered Constraints/constants/finite domains can either be Constraints/constants/finite domains can either be

entered into the current cell or via dialog boxesentered into the current cell or via dialog boxes Constraints can be copied to a range of cells; Constraints can be copied to a range of cells;

appropriate transformations are applied while copyingappropriate transformations are applied while copying Large number of of built-ins available as clickable Large number of of built-ins available as clickable

buttonsbuttons alldifferent, count, cumulative, element, subsetalldifferent, count, cumulative, element, subset

Once constraints/constants/finite domains are enteredOnce constraints/constants/finite domains are entered the system automatically collects them, the system automatically collects them, composes a clp(FD) program, composes a clp(FD) program, solves it using clp(FD) engine running in the background and solves it using clp(FD) engine running in the background and displays the solution.displays the solution.

Interface (Interface (continued)continued) The user must enter The user must enter

at least one at least one Query TableQuery Table and and zero or more zero or more Auxiliary TablesAuxiliary Tables

Query Table is used to compose the queryQuery Table is used to compose the query The query table could be as small as one cellThe query table could be as small as one cell

Auxiliary tables turn into facts: auxiliary Auxiliary tables turn into facts: auxiliary tables useful in mapping non-integer tables useful in mapping non-integer domain values into integersdomain values into integers

Computed results for the query are Computed results for the query are displayed in the query tabledisplayed in the query table

User can highlight a part of the query table, User can highlight a part of the query table, and only those cells are included in the and only those cells are included in the query.query.

Example: Employee Example: Employee scheduleschedule

Scheduling managers at a store:Scheduling managers at a store: Store hours : 8 AM to 11 PM, 7 days / weekStore hours : 8 AM to 11 PM, 7 days / week Each manager must work 8.5 hrs / day (includes 0.5 Each manager must work 8.5 hrs / day (includes 0.5

hrs for lunch)hrs for lunch) Each manager must work 5 days / weekEach manager must work 5 days / week At least one manager must be present at any momentAt least one manager must be present at any moment Managers working night shifts should not be allocated Managers working night shifts should not be allocated

morning shift the following daymorning shift the following day Schedule must be fair to all managersSchedule must be fair to all managers

In most cases, this scheduling is done In most cases, this scheduling is done manuallymanually

Erroneous, leads to employee dissatisfactionErroneous, leads to employee dissatisfaction

Solution: Employee Solution: Employee ScheduleSchedule

An Empty TableAn Empty Table

Assume that there are 5 managersAssume that there are 5 managers Each manager works 8.5 hrs per day either inEach manager works 8.5 hrs per day either in

The morning shift (8:00 AM to 4:30 PM), orThe morning shift (8:00 AM to 4:30 PM), or The midday shift (10:00 AM to 6:30 PM), orThe midday shift (10:00 AM to 6:30 PM), or The evening shift (2:30 PM to 11:00 PM)The evening shift (2:30 PM to 11:00 PM)

Solution: Employee Solution: Employee Schedule (Schedule (continued)continued)

Morning, midday and evening shifts are denoted by 5, 2 and Morning, midday and evening shifts are denoted by 5, 2 and 4 respectively4 respectively

0 is used to indicate a manager’s day off0 is used to indicate a manager’s day off Domain of each cell: [0,2,4,5]Domain of each cell: [0,2,4,5] User enters domain in one cell, copies it to the restUser enters domain in one cell, copies it to the rest For no morning after night restriction, we enter the For no morning after night restriction, we enter the

constraint:constraint:C2 != B2 + 1 (copied everywhere)C2 != B2 + 1 (copied everywhere)

At least one manager is present at any time during the day:At least one manager is present at any time during the day:member(4,[D2,D3,D4,D5,D6]), member(4,[D2,D3,D4,D5,D6]),

member(5,[D2,D3,D4,D5,D6])member(5,[D2,D3,D4,D5,D6]) No manager works for more than 5 days a week:No manager works for more than 5 days a week:

count(0,[B2,C2,D2,E2,F2,G2,H2],=,2)count(0,[B2,C2,D2,E2,F2,G2,H2],=,2) Every manager has more or less same proportion of morning, Every manager has more or less same proportion of morning,

midday and evening shifts:midday and evening shifts:sublist([2,4,5],[B2,C2,D2,E2,F2,G2,H2])sublist([2,4,5],[B2,C2,D2,E2,F2,G2,H2])

Solution: Employee Solution: Employee Schedule (Schedule (continued)continued)

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

[0,2,4,5], C2 != B2 + 1

(Cell Constraints)

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

sublist([2,4,5], [B2,C2,D2,E2,F2,G2,H2])

(Row Constraints)

member(4,[D2,D3,D4,D5,D6]),

member(5,[D2,D3,D4,D5,D6])

(Column Constraints)

Solution: Employee Solution: Employee Schedule (Schedule (continued)continued)

Displaying a solutionDisplaying a solution

Solution: Employee Solution: Employee Schedule (Schedule (continued)continued)

Displaying a solution along with a mapping of variable valuesDisplaying a solution along with a mapping of variable values

Example: The 3x3 Grid Example: The 3x3 Grid PuzzlePuzzle

Cell constraints:Cell constraints: B3: B3+C3+D3 #= 15, B3+B4+B5 #= B3: B3+C3+D3 #= 15, B3+B4+B5 #=

1515 C3: C3+C4+C5 #= 15C3: C3+C4+C5 #= 15 D3: D3+D4+D5 #= 15D3: D3+D4+D5 #= 15 B4: B4+C4+D4 #= 15B4: B4+C4+D4 #= 15 B5: B5+C5+D5 #= 15, B5+C4+D3 #= B5: B5+C5+D5 #= 15, B5+C4+D3 #=

1515 D5: B3+C4+D5 #= 15, D5: B3+C4+D5 #= 15,

alldiff([B3,B4,B5,C3,C4,C5,D3,D4,D5])alldiff([B3,B4,B5,C3,C4,C5,D3,D4,D5])

Solution: The 3x3 Grid Solution: The 3x3 Grid PuzzlePuzzle

Example: Cryptarithmetic Example: Cryptarithmetic PuzzlesPuzzles

• Most puzzles have such a graphical structure; for example, Zebra puzzleMost puzzles have such a graphical structure; for example, Zebra puzzle

• Exsched can be used for solving puzzles published in popular puzzle Exsched can be used for solving puzzles published in popular puzzle magazinesmagazines

D2 #= (E2+E3+E4) DIV 10

E5 #= (E2+E3+E4) MOD 10

Solving Large ProblemsSolving Large Problems Exsched is a man-machine interface for solving CSPsExsched is a man-machine interface for solving CSPs

Large problems can be solved interactively.Large problems can be solved interactively. Note: never look for optimal solution; Note: never look for optimal solution; a a solution is enough.solution is enough.

Consider course scheduling at UT Dallas CS: 120+ Consider course scheduling at UT Dallas CS: 120+ courses with 50+ instructors in 9 classroomscourses with 50+ instructors in 9 classrooms The whole schedule cannot be generated in one shotThe whole schedule cannot be generated in one shot Obtain the schedule piecemeal, while manually adjusting the Obtain the schedule piecemeal, while manually adjusting the

choices.choices. Or: set instructors first, set timings next, set classroom Or: set instructors first, set timings next, set classroom

last.last. If a solution is not found (or the system takes too long), If a solution is not found (or the system takes too long),

relax constraints or reduce the size of the query table, relax constraints or reduce the size of the query table, until a solution is found, then gradually increase the until a solution is found, then gradually increase the query tablequery table

Another approach: divide the query table into N pieces, Another approach: divide the query table into N pieces, solve each piece individually, then enforce global solve each piece individually, then enforce global consistency manually.consistency manually.

Current WorkCurrent Work Add auxiliary tables supportAdd auxiliary tables support Develop conventions and add support for Develop conventions and add support for

Mapping tablesMapping tables Reverse mapping tablesReverse mapping tables

Add ability to hide constraints (constraint relaxation)Add ability to hide constraints (constraint relaxation) Add ability to hide rows/columns (constraint relaxation)Add ability to hide rows/columns (constraint relaxation) Add more function buttonsAdd more function buttons Allow individual cells to be named.Allow individual cells to be named. Support for automatic constraint relaxation??Support for automatic constraint relaxation?? Support for Macros in CLP(FD)??Support for Macros in CLP(FD)?? Add CLP(R) support (already part of SICStus)Add CLP(R) support (already part of SICStus) Spreadsheet for Engineering DesignSpreadsheet for Engineering Design

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

ConclusionConclusion Advantages of the Exsched Approach:Advantages of the Exsched Approach:

FlexibilityFlexibility InteractivityInteractivity Non-experts can use it (Expert System Shell)Non-experts can use it (Expert System Shell)

Managers are resource allocators!!Managers are resource allocators!! Standard (deficient) spreadsheets currently used (sorting fn Standard (deficient) spreadsheets currently used (sorting fn

used a lot)used a lot) Domain specific knowledge can be incorporatedDomain specific knowledge can be incorporated User and clp(FD) system cooperate to produce solutionsUser and clp(FD) system cooperate to produce solutions User can give partial solutions, the rest can be computed User can give partial solutions, the rest can be computed

using ExSchedusing ExSched

Disadvantages:Disadvantages: Works only for tabular clp(FD) programsWorks only for tabular clp(FD) programs No automatic help if the system is over-constrainedNo automatic help if the system is over-constrained

Challenges: Collecting data (e.g., Challenges: Collecting data (e.g., preferences)preferences)

DEMO LATERDEMO LATER

ReferencesReferences G. Gupta and S. Akhter. G. Gupta and S. Akhter.

Knowledgesheet: A Spreadsheet Knowledgesheet: A Spreadsheet Interface for Solving a Class of Interface for Solving a Class of Constraint Satisfaction Problems. Constraint Satisfaction Problems. PADL 2000. Springer LNCSPADL 2000. Springer LNCS

M. Yennamini. ExSched: Solving M. Yennamini. ExSched: Solving CSPs with Excel. M.S. Thesis. Dec. CSPs with Excel. M.S. Thesis. Dec. 2004. Univ. of Texas at Dallas2004. Univ. of Texas at Dallas

S. Chitnis. Next Generation S. Chitnis. Next Generation ExSched. M.S. Thesis. May 2006. ExSched. M.S. Thesis. May 2006. Forthcoming.Forthcoming.