1 / 29

ExSched

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)

benoit
Download Presentation

ExSched

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related