General Rules

A problem description does not exist in isolation and is delivered along with a Model Logic responsible for providing the mappings, in the form of Model Tables, between this high-level description of the problem and the actual values coming from the partition’s transactional and master data.

Their names must consist of the keyword Problem, the name of the space, and the name of the scope, like this: Problem_TheSpaceName_TheScopeName. This naming is mandatory to be understood during the Optimization Engine instantiation.

Each table must contain:

• Actual values for the dimensions of the space and their hierarchical categories.

  • The name of the column is either directly the name of the category of the dimension (e.g. product) or the same name prefixed by the disambiguation if it applies (e.g. competitor_product).

• The parameters required to instantiate the variables and the criteria of the scope.

  • The name of the column is the one indicated by the field element in the description.

Example

As an example, let’s consider a scope named HighRevenues of a [customer, product_group] space named ByCustomerAndProductGroup, in a problem in which hierarchies are [customer, customer_family] and [product, product_group]. The table Problem_ByCustomerAndProductGroup_HighRevenues has to be created. It needs to contain at least the fields customer, customer_family, and product_group. The table will contain all the (customer, product_group) pairs with a high revenue as per the user requirements.

Moreover, this table can contain other fields, which are accessible from the model description. In the specific space (here: ByCustomerAndProductGroup) and the specific scope (here: HighRevenues), each declared parameter with a data type takes its value from the corresponding problem table. For instance, this init value parameter takes the corresponding value of the field historical_margin_rate in the table Problem_ByCustomerAndProductGroup_HighRevenues.

spaces:
  - name: ByCustomerAndProductGroup
    dimensions: [product_group, customer]
    scopes:
      - name: HighRevenues
        variables:
          - name: Margin
            type: value_finder
            init:
              type: data
              field: historical_margin_rate

For more details see Variables Description and Criteria Description.

Sample Logic

It is up to the configuration engineer to write the logics that create the model tables. Each scope needs the existence of the corresponding problem table. It is recommended to create them in specific logics elements named Store_Problem_<space_name>_<scope_name>. Here is an example how to create the table for the scope HighRevenues in the space ByCustomerAndProductGroup.

def dmCtx = api.getDatamartContext()
def query = dmCtx.newSqlQuery()

def highRevenueThreshold = out.lowerValue // this value provides from a previous step

query.addSource(dmCtx.newQuery(model.table("Current"), false),"current")

query.setQuery("""
SELECT 
  customer, customer_family, product_group, 
  AVG(margin_rate)::NUMERIC AS min_margin_rate,
  'medium' AS min_margin_priority
FROM current
GROUP BY customer, customer_family, product_group
HAVING SUM(invoice_price) > $highRevenueThreshold
"""
)

def table = model.addTable("Problem_ByCustomerAndProductGroup_HighRevenues",
        [
                label : "Problem ByCustomerAndProductGroup HighRevenues",
                fields: [
                        [name: "customer", label: "Customer id", type: FieldType.TEXT, key: true],
                        [name: "customer_family", label: "Customer group", type: FieldType.TEXT],
                        [name: "product_group", label: "Product group", type: FieldType.TEXT, key: true],
                        [name: "min_margin_rate", label: "Min margin rate", type: FieldType.NUMBER]
                        [name: "min_margin_priority", label: "Min margin priority", type: FieldType.TEXT],
                ]
        ]
)

model.loadTable(table, query, true)

return

There are min_margin_rate and min_margin_priority parameters that can be accessed in the HighRevenues scope. There could be a lower threshold criterion on margin rates for high revenues pairs of customers and product groups declared like this:

spaces:
  - name: ByCustomerAndProductGroup
    dimensions: [customer, product_group]
    scopes:
      - name: HighRevenues
        criteria:
          - name: MinMarginRate
            type: lower_threshold
            on: MinMargin            # variable definition not shown here
            threshold:
              type: data
              field: min_margin_rate
            acceptable_delta:
              type: inline
              value: 0.05
            priority:
              type: data
              field: min_margin_priority