SCHEDULE OpenBI
A job shop's schedule, solved with OR-Tools' CP-SAT: each task's start and end on its machine, and all of them done as early as possible.
Group: Operations research. OpenBI’s own: DAX has no function of this name.
Syntax
SCHEDULE ( <tasks>, <job>, <machine>, <duration> [, <order>] [, <seconds>] )| Parameter | Type | Description |
|---|---|---|
tasks | Table | The tasks: a row each, such as the Brewing table. |
job | Expression evaluated for each row | The row's job, such as a batch: its tasks run one after another. |
machine | Expression evaluated for each row | The row's machine, such as a kettle, which handles a single task at once. |
duration | Expression evaluated for each row | How long the task takes, 0 or more, in whole units such as hours (rounded). |
order | Expression optional, evaluated for each row | The task's place in its job, such as a step number. Left out, the tasks of a job run in the order of the rows. |
seconds | Number optional | How long the solver may search: 5 when left out, at most 120. |
Returns
Table. A row for each task, the tasks of a job together: Job, Order (1 for the job's first task), Machine, Start, End and Duration in the durations' units from 0, then Makespan (when the last task ends) and Optimal (TRUE when no schedule can be shorter).
Remarks
- Each job's tasks run in their order, no machine handles two tasks at once, and the makespan, the time when everything is done, is made as short as the solver can within the seconds given.
- Start and End are numbers of units from the start of the schedule, not dates. Add them to a start date in the units the durations are in (hours / 24 for days) to place them on a calendar.
- Optimal is TRUE when the solver proved that no shorter schedule exists; FALSE means the schedule is the best it found in time.
- When several schedules are equally short, which one comes back may change from one session to the next; the makespan does not. The same question asked again while the engine runs gets the same schedule.
- Rows with a blank job, machine or duration are left out; a table with no task left, a negative duration or more than 5,000 tasks is an error.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
Examples
Next week's brewing
Six batches of the Brewing table on one mash tun, one kettle, three fermenters, a press and a bottling line, in hours. Both IPA batches need fermenter B for 36 hours, after 9 hours of mashing and boiling and before 4 of bottling: no schedule takes less than 85 hours.
EVALUATE
SELECTCOLUMNS (
SCHEDULE ( Brewing, Brewing[Batch], Brewing[Equipment], Brewing[Hours], Brewing[Step] ),
"Batch", [Job],
"Step", [Order],
"Equipment", [Machine],
"From hour", [Start],
"To hour", [End]
)| Batch | Step | Equipment | From hour | To hour |
|---|---|---|---|---|
| Pils 41 | 1 | Mash tun | 10 | 14 |
| Pils 41 | 2 | Kettle | 14 | 17 |
| Pils 41 | 3 | Fermenter A | 30 | 60 |
| Pils 41 | 4 | Bottling line | 60 | 66 |
| IPA 17 | 1 | Mash tun | 0 | 5 |
| IPA 17 | 2 | Kettle | 5 | 9 |
| IPA 17 | 3 | Fermenter B | 9 | 45 |
| IPA 17 | 4 | Bottling line | 45 | 49 |
| Cider 9 | 1 | Press | 0 | 6 |
| Cider 9 | 2 | Fermenter A | 6 | 30 |
| Cider 9 | 3 | Bottling line | 30 | 35 |
| Lettøl 12 | 1 | Mash tun | 18 | 21 |
| Lettøl 12 | 2 | Kettle | 21 | 24 |
| Lettøl 12 | 3 | Fermenter C | 51 | 71 |
| Lettøl 12 | 4 | Bottling line | 71 | 76 |
| Pils 42 | 1 | Mash tun | 14 | 18 |
| Pils 42 | 2 | Kettle | 18 | 21 |
| Pils 42 | 3 | Fermenter C | 21 | 51 |
| Pils 42 | 4 | Bottling line | 51 | 57 |
| IPA 18 | 1 | Mash tun | 5 | 10 |
| IPA 18 | 2 | Kettle | 10 | 14 |
| IPA 18 | 3 | Fermenter B | 45 | 81 |
| IPA 18 | 4 | Bottling line | 81 | 85 |
Only the makespan is certain
EVALUATE
VAR Plan = SCHEDULE ( Brewing, Brewing[Batch], Brewing[Equipment], Brewing[Hours], Brewing[Step] )
RETURN
ROW ( "Hours in all", MAXX ( Plan, [Makespan] ), "Optimal", MAXX ( Plan, IF ( [Optimal], 1, 0 ) ) = 1 )| Hours in all | Optimal |
|---|---|
| 85 | TRUE |
Two jobs, two machines
Job A needs the cutter, then the welder; job B the other way round. Each machine serves one job while the other waits: 5 hours, and only one way to do it in 5.
EVALUATE
VAR Tasks =
DATATABLE (
"Job", STRING, "Step", INTEGER, "Machine", STRING, "Hours", INTEGER,
{
{ "A", 1, "Cutter", 3 },
{ "A", 2, "Welder", 2 },
{ "B", 1, "Welder", 3 },
{ "B", 2, "Cutter", 2 }
}
)
RETURN
SELECTCOLUMNS (
SCHEDULE ( Tasks, [Job], [Machine], [Hours], [Step] ),
"Job", [Job],
"Machine", [Machine],
"Start", [Start],
"End", [End],
"Makespan", [Makespan]
)| Job | Machine | Start | End | Makespan |
|---|---|---|---|---|
| A | Cutter | 0 | 3 | 5 |
| A | Welder | 3 | 5 | 5 |
| B | Welder | 0 | 3 | 5 |
| B | Cutter | 3 | 5 | 5 |