Setup and configuration of Sequencer

As described in the overall Sequencer introduction, the value profile is one of the corner stones of the Sequencer. These value profiles are defined in the file <sch_client_dir>\custom\ValueProfiles.xml, where <sch_client_dir> is the root directory of the ROB-EX Scheduler client installation. This XML file is loaded once when Scheduler starts up. The figure below shows an overview of how value profiles are defined, loaded and used within the ROB-EX Scheduler client.

Value providers and profiles are defined in the ValueProfiles.xml file
When ROB-EX Scheduler starts it loads the value profiles in the xml file
Open an operation or resource and set the value profile.

There are two ways of assigning value profiles to operations:

When editing an operation, go to the “Advanced” tab and select the value profile under “Sequencer options”
Same as above, but put a checkmark in “Use value profile from resource” and choose the value profile for the resource under the “Planning” tab when editing an resource.

Defining value profiles

The concept of value profiles is shown below:

Each operation can have a specific valueProfile assigned – as described above either on each individual operation or the value profile can be set on the resource in order to apply the value profile to all operations assigned to that resource.

The ValueProfile will in some cases have a capacityProvider assigned. Whenever someone asks for the capacity on the operation the value profile will delegate to the capacityProvider which returns a value from the ValueProvider overriding the normal capacity field stored on the operation.

The overall XML structure of the ValueProfiles.xml file is as follows:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ValueProfileSetup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../config/schema/ValueProfiles.xsd">
     <ValueProviders>
         <!-- Add value provider definitions here -->
     </ValueProviders>
     <ValueProfiles>
         <!-- Define value profiles here using the value providers above -->
     </ValueProfiles>
 </ValueProfileSetup>

Syntax for defining a ValueProfile

The first step is to configure one or more ValueProviders (see syntax below) which in turn later in the file will be used when defining ValueProfiles. A ValueProvider define how the capacity or workload should be calculated or looked up.

When one or more value providers have been added they can be used in the definition of the value profile. An example definition of a ValueProfile with id=“p1” and name=“Profile 1” is shown below:

<ValueProfile id="p1" name="Profile 1">
    <Description></Description>
    <CapacityValueProviderID>id1</CapacityValueProviderID>
    <StepValueProviders>
        <StepValueProvider step="SETUP" valueProviderID="id2"/>
        <StepValueProvider step="SWITCHOVER" valueProviderID="id3"/>
    </StepValueProviders>
</ValueProfile>

As shown in the above, a value provider with id = id1 is used as capacity value provider. A value provider with id = id2 is used as work step value provider for the setup step and another work step value provider with id=_id3_ defines how the switchover step is calculated.

The possible keywords for the step parameter on the tag are:

PRE_QUEUE
SETUP
WORKLOAD
SWITCHOVER
POST_QUEUE

Syntax for defining a Value Provider

The job of a value providers is to return a single value and assign that value to an operation work step or an operation capacity. To define the values, ValueProviders are used. Values can be looked up in a table or calculated.

There are several ValueProvider types:

LookupTableValueProvider: This provider loads a table or matrix from an excel file or a database and provides values for operation worksteps or operation capacity
ExpandStepValueProvider: This provider adjust a specific part (workstep) of an operation to end when an operation further down in the route ends.
AggregatedValueProvider: Does the same as the LookupTableValueProvider but can aggregate values from several LookupTableValueProviders.

In the following sections the available value providers are explained.

Lookup table value provider

The lookup table value provider provides values based on a “base key” and a “condition key”. The base key could be the product id of the operation and the condition key could be the resource name as in the example below.

Below is an example of a Lookup table value provider:

<LookupTableValueProvider id="id1" name="Product Resource excel lookup">
   <Description>
    The lookup table is read from an Excel file and the
    lookup is made based on the product name of the order and
    the resource the operation is assigned to
   </Description>
   <DependenceType>OP_RES</DependenceType>
   <LayoutItemKeyMapper baseLayoutItem="order_product_id" condLayoutItem="resource_name"/>
   <DefaultValue layoutItem="opr_custom_double1">0.0</DefaultValue>
   <ExcelDataProvider>
      <Filename>testSheet.xlsx</Filename>
      <Sheetname>Sheet1</Sheetname>
      <TableLayout baseColumn="C" conditionColumn="D" startFromRow="4" valueColumn="E"/>
   </ExcelDataProvider>
</LookupTableValueProvider>

Always define an unique id and a unique name to the value provider. The description is optional.

<DependenceType>:

The dependence type describes on which values this lookup table should work. There are several types to choose from:

OP: The lookup table is based on the current operation only. Both the base and the conditional object is the current operation
OP_RES: The lookup table is based on the current operation and the current resource it’s placed on
OP_PREV_OP: The lookup table is based on the current operation and the operation scheduled*** just before on the same resource. This type is only recommended when scheduling using a forward scheduling strategy, as predictable results relies on the previous operation having already been scheduled.
OP_NEXT_OP: The lookup table is based on the current operation and the operation scheduled*** just after on the same resource. This type is only recommended when scheduling using a backward scheduling strategy, as predictable results relies on the following operation having already been scheduled.

The first part of the dependence type is the “base” and the second part is the “condition”. For OP_RES, OP stands for operation and is the base object used in the table lookup and RES stands for resource and is the conditional value in the lookup. In OP_NEXT_OP both the base and the condition is an operation.

* “Scheduled” operation means an operation which is either not selected for being scheduled at all (already planned operation) or an operation that has already been scheduled in this same run. As an example

the resource R1 has four operations O1, O2, O3 and O4 scheduled one after each other.
The R1 resource has a value provider enabled with setting “OP_NEXT_OP” modeling a variable switchover time.
If selecting O2 and O3 for re-scheduling forward from the end time of 01 (i.e. not re-scheduling O1 and O4), then the base operation is initially O2 and the conditional operation will be O4 – not O3 as maybe expected. This is because O3 is waiting to be scheduled, at the point in time where O2 scheduling is in progress. This means O2 switchover time will be based on a change from O2 to O4.
When O2 scheduling is complete, then scheduling of O3 starts up. The following operation in time will again be O4 – thus again the switchover time of O3 depends on the change to O4. When finally all scheduling is complete, all operations on resource R1 is re-validated. This means O2 is now validated this time with O3 as the next operation. This revalidation may now cause a change of switchover time on O2 and a possible overlap/empty space between O2 and O3.

Even though the example above explains how the base and conditional operations are selected for the “OP_NEXT_OP” and the problems this has with scheduling forward, then note that similar problems may also be able to happen even if the same example was executed using a backward strategy. I.e. the scheduling rules cannot currently guarantee that the “next” or “prev” operation is known, until all scheduling has run to the end. Also see the chapter on Sequencer limitations

<LayoutItemKeyMapper>:

The LayoutItemKeyMapper maps the base object and the condition object to strings used in the table lookup. The names of available layout items can be found here: Layout item list

If the base or condition object is an operation the valid layout items can be any of the variables under common, operation, production order or project in the list.

If the base or condition object is an resource the valid layout items can be found under the Resource variable list

Data providers

It is possible to get the table values from an excel spreadsheet, a database or a stored procedure in a database:

<DefaultValue>:
The DefaultValue is used as a fallback when a value is not found for the given base and condition objects.
If the ‘layoutItem’-attribute is a valid layoutitem and it returns a value that can be parsed as a double, then this value is used as the default, otherwise the fixed value is used.
A list of layoutitem can be found in the Layout item list.

<ExcelDataProvider>:

The excel data provider reads the values from an excel file. To use the data provider define a excel file to read from (Filename) and a sheet in the excel file(Sheetname):

<ExcelDataProvider>
  <Filename>testSheet.xlsx</Filename>
  <Sheetname>Sheet1</Sheetname>
  <TableLayout baseColumn="C" conditionColumn="D" startFromRow="4" valueColumn="E"/>
</ExcelDataProvider>

There are two ways of reading excel sheets, either read it as a table or as a matrix:

	Setup	Excel example
TableLayout	`<TableLayout baseColumn="A" conditionColumn="B" startFromRow="3" valueColumn="C"/>` baseColumn: the column that holds the lookup string for the base value conditionColumn: The column that holds the condition key used in lookup. valueColumn: the column with the value of the lookup startFromRow: the row to start reading from
MatrixLayout	`<MatrixLayout headerColumn="B" headerRow="3" xAxis="COND" yAxis="BASE"/>` headerColumn: the column that holds the header values headerRow: the row that holds the header row xAxis: defines if the x-axis is the base value or the condition value (BASE or COND) yAxis: defines if the y-axis is the base value or the condition value (BASE or COND)

<DatabaseDataProvider>:

The database data provider reads the table values by executing an sql-query against an database. The JDBCDataProvider element is used to define the sql-query and connection:

<JDBCDataProvider>
   <Query>SELECT ProductName AS BASE, Res_Id AS COND, Capacity AS VALUE from dbo.ResourceProductCapacity</Query>
   <Connection>
       <Driver>MSSQL_SERVER_JTDS</Driver>
       <URL>jdbc:jtds:sqlserver://localhost:1433/GanttERP;instance=;useCursors=true</URL>
       <Login>sa</Login>
       <Password>1234</Password>
   </Connection>
</JDBCDataProvider>

The important part of the sql query is the AS BASE, AS COND and AS VALUE in the sql query. When the data is loaded these columns will be looked up in the resultset!

The Connection is defined by:

<Driver>
Name of the driver to use, the options are:

Driver name	Connection URL example
MSSQL_SERVER_JTDS	`jdbc:jtds:sqlserver://<pcName>:1433/GanttERP;instance=;useCursors=true`
JDBC_ODBC_BRIDGE	`jdbc:odbc:<dbName>`
SQL_SERVER_NATIVE	`jdbc:microsoft:sqlserver://<pcName>:1433;SelectMethod=cursor;DatabaseName=GanttERP`
SQL_SERVER_NATIVE_2005	`jdbc:sqlserver://<pcName>:1433;SelectMethod=cursor;DatabaseName=GanttERP`
ORACLE	`jdbc:oracle:thin:@<pcName>:1521:database_name`

<URL>
the connection string to the database, see examples above.

<Login>
The username to the database

<Password<
The password to the database

<MUDataProvider>:

The MUDataProvider gets its data from a call to a stored procedure in the multiuser server database. The stored procedure to call is given by the name defined in the StoredProcedure element.

The MUDataProvider will be loaded each time a user log in to the multiuser and only then, making it the preferred method of loading sequencer data when users are connected to a Multiuser environment.

The below example shows a MUDataProvider, setup to call a stored procedure named ‘spLocal_SetupTimeMatrixMaterial’.

<MUDataProvider>
   <StoredProcedure>spLocal_SetupTimeMatrixMaterial</StoredProcedure>
</MUDataProvider>

Sample XML and SQL queries creating and using this stored procedure ‘spLocal_SetupTimeMatrixMaterial’ can be found in a comment of <sch_client_dir>\data\ValueProfiles.xml and are as well provided below. The stored procedure serves as a template for the general structure of how a stored procedure should look like. It should be further edited to the actual needs.

The requirements for a stored procedure are as follows:

It must define three input varchar variables for username, siteId and valueProviderId. These are passed to the stored procedure when a user logs in and can be used in the stored procedure as necessary to filter out data to return.

The username is the username of the user logging in, the siteId is the id of the site the user is logged in to and the ValueProviderId is the name defined in the ValueProfile XML element (“area1” in the example below).

Also the stored procedure must select five strings, four integers and a double. The strings must be named strKey1..strKey4 and returnStr, the integers must be named intKey1..intKey4 and the double must be named returnDouble.

The srtKey1 represent the base key value, the srtKey2 represent the conditional key value and the returnDouble represent the corresponding lookup value. The other variables are for use in future versions.

The following is a full working combination of XML configuration and database scripts to setup an example

Example LookupTableValueProvider using an MUDataProvider

<!-- This is an example of how to configure a value provider where data is loaded
     via the Multiuser Server from a table of choice in the GanttMultiuser database
     The Stored Procedure specified in the StoredProcedure tag will be called whenever
     a user is logged into the Server -->
<LookupTableValueProvider id="area1" name="Product Product area1 lookup">
 <DependenceType>OP_PREV_OP</DependenceType>
 <DefaultValue>0</DefaultValue>
 <LayoutItemKeyMapper baseLayoutItem="order_product_id" condLayoutItem="order_product_id"/>
 <MUDataProvider>
  <StoredProcedure>spLocal_SetupTimeMatrixMaterial</StoredProcedure>
 </MUDataProvider>
</LookupTableValueProvider>

Example ValueProfile

<!-- Create value profile to be used with area1 -->
<ValueProfile id="area1" name="Area 1 Sequence dependent setup" enabled="true">
 <StepValueProviders>
  <StepValueProvider step="SETUP" valueProviderID="area1"/>
 </StepValueProviders>
</ValueProfile>

Example lookup table:

USE [GanttMultiuser]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Local_SetupTimeMatrix](
[siteId] [nvarchar](40) NOT NULL,
[materialNumberPrev] [nvarchar](50) NOT NULL,
[materialNumberNext] [nvarchar](50) NOT NULL,
[setupTimeHours] [float] NULL
) ON [PRIMARY]

GO

Example config data

This provide example data to insert into the table

USE [GanttMultiuser]
GO
INSERT [dbo].[Local_SetupTimeMatrix] ([siteId], [materialNumberPrev], [materialNumberNext], [setupTimeHours])
VALUES (N'stdSite', N'101', N'102', 3)
INSERT [dbo].[Local_SetupTimeMatrix] ([siteId], [materialNumberPrev], [materialNumberNext], [setupTimeHours])
VALUES (N'stdSite', N'101', N'103', 1)
INSERT [dbo].[Local_SetupTimeMatrix] ([siteId], [materialNumberPrev], [materialNumberNext], [setupTimeHours])
VALUES (N'stdSite', N'103', N'101', 2)

GO

Corresponding stored procedure

USE [GanttMultiuser]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE
PROCEDURE [dbo].[spLocal_SetupTimeMatrixMaterial]
@username varchar(30) = '',
@siteId varchar(40) = '',
@valueProviderId varchar(40) = ''
AS
BEGIN
SET NOCOUNT ON;

SELECT
stm.materialNumberNext as strKey1,
stm.materialNumberPrev as strKey2,
NULL as strKey3,
NULL as strKey4,
0 as intKey1,
0 as intKey2,
0 as intKey3,
0 as intKey4,
NULL as returnStr,
stm.setupTimeHours as returnDouble
FROM dbo.Local_SetupTimeMatrix stm
WHERE stm.siteId = @siteId
END

GO

Example test script of Stored Procedure

DECLARE @RC int
DECLARE @username varchar(30)
DECLARE @siteId varchar(40)
DECLARE @valueProviderId varchar(40)

-- Set parameter values here.
set @username='myuser'
set @siteId='stdSite'
set @valueProviderId='area1'

EXECUTE @RC = [GanttMultiuser].[dbo].[spLocal_SetupTimeMatrixMaterial]
@username
,@siteId
,@valueProviderId
GO

For more info it is recommended to look at the stored procedure ‘spLocal_SetupTimeMatrixMaterial’ in <sch_client_dir>\data\ValueProfiles.xml.

Also note that a script may trigger a load or reload of the stored procedure core values, using the gantt.plugin.PluginAPI.lookupDataprovider(MUValueProviderLookupArgs args) method. In fact this method may be used for custom loading of any stored procedure provided values from the Multiuser – also values not directly related to value providers. The only requirement of this method is that a user is logged into the multiuser server, at the point in time when the method is called.

Expand step value provider

This value provider ensures that the end of an operation follows the end of another operation further down in the route.

An example could be a tank operation where the tank must remain occupied until a laboratory operation is finished, or any setup where a resource is not released until another operation finishes further down in the route.

!When using Expand step value provider the ‘In anchor’ and ‘Out anchor’ of the operation MUST be set so that they are not affected by the step being expanded. E.g. if expanding switchover, the anchors MUST be before switchover!!!
Expand step value provider does NOT support resource efficiency being applied to the step being expanded! Resource efficiency can be disabled for e.g. switchover by setting WorktimeStep.SWITCHOVER.useResourceEfficiency=false in GanttSetup.properties. For more info, see GanttSetup.properties

The definition of an ExpandStepValueProvider is shown below:

<ExpandStepValueProvider id="id2" name="Switchover end at painting">
   <Description>Value provider that ensures that the switchover
     step of the operations lasts at least to the end
     of a painting operation further in the route</Description>
   <StepToExpand>SWITCHOVER</StepToExpand>
   <AlignWith>OPERATION_END</AlignWith>
   <RouteOperationCollector caseSensitive="false" exactMatch="false" layoutItem="operation_name" pattern="painting" stopAtFirst="true" />
</ExpandStepValueProvider>

Again it is defined with an id and name.

StepToExpand: This defines the step to adjust according to a operation further in the route. It can be set to any of:

SETUP: This will adjust the setup worktime step.
WORKLOAD: this will adjust the workload
SWITCHOVER: this will adjust the switchover worktime step.

RouteOperationCollector: This defines how the operation is found that defines the length of the step. This is done by setting up a pattern, and when an operation in the route matches that pattern is it added to the list of operations that define the step length. There are several options to define the match. The layoutitem defines which field from the operation is matched against the pattern.

In the above example the first operation in the route with a name matching “painting” is used to adjust the switchower time.

pattern	The pattern to match against the value returned by the “layoutItem”. Accepts a regular expression.
layoutItem	The layout item used to extract the string to match from the operations in the route. (see Layout item list)
caseSensitive	true or false – should the match be case sensitive.
exactMatch	true or false – should the pattern be an exact match or match partial
stopAtFirst	Should the collector stop at the first match or find all operations that match the pattern.
maxRouteLevels	If stopAtFirst = false this is the max number of routing levels to allow before stopping the search. Default value is -1, indicating an infinite number of levels. A route level of 2 indicates that the source operation is connected via two external links (material or sub-order links) over two routes. A value of 0 limits search within route of source operation.
requireDirectLink	true or false. True indicates that an operation target matching the specified pattern is only used in case source and target operations have a direct link between them (i.e. pegging or sub-order link).

AlignWith: This defines what part of an operation to expand the step to. When we know what step to expand and which operation to use as an endpoint for our expansion, we can define what part of the operation to expand to using the AlignWith tag.

AlignWith: can be any of:

OPERATION_START: Expand the step to the start of the collected operation
SETUP_END: Expand the step to the end of the setup step of the collected operation
SWITCHOVER_START: Expand the step to the start of the switchover step of the collected operation
OPERATION_END: Expand the step to the end of the collected operation

Aggregated value provider

This value provider can aggregate several value providers.

This could be useful if you have eg. have a setup that is the maximum or >This value provider can aggregate several value providers.

This could be useful if you have eg. have a setup that is the maximum or combined value of several lookup tables.

Below is an example of a AggregatedValueProvider

<AggregatedValueProvider id="id4" name="Product Resource aggregated lookup">
   <Description>Aggregated lookup where the sum from two value providers is used as the return value for the Product-Resource lookup</Description>
   <Aggregator>SUM</Aggregator>
   <ValueProviders>
     <Provider idref="id1"/>
     <Provider idref="id3"/>
   </ValueProviders>
</AggregatedValueProvider>

It is defined by an Aggregator and a list of value provider id’s:

Aggregator: can be any of:

SUM: Will add the values from the different value provider together
AVG: Will take the average of the values from the different value providers
MAX: Will take the maximum value from the different value providers
MIN: Will take the minimum value from the different value providers

Sequencer

Sequence Optimizer

Feedback

Post your comment on this topic.