Scenario configuration overview

In this topic, learn about the Scenario Config workbook, which serves as a central point for configuring scenarios in Price IQ.

Use the Scenario Config workbook to:

• create new columns in scenarios
• configure strategy worksheets
• define data sources
• set target destinations for published prices
• define target analytics entity
• create scenario scopes
• configure the objective function
• create optimization constraints
• define KPI cards
• define worksheet actions

The Scenario Config workbook includes the following worksheets:

• Fields
• Source Data
• Scope
• KPIs
• Param Tabs
• Param Tabs Scope
• Jobs
• Scenario Params
• Global Params
• Objective Setup
• Constraint Setup
• Spread Mapping

Fields

Use the Fields worksheet to define columns that appear on the Price Details worksheet in scenarios.

• Scenario Type—Name of the scenario template.

• Field—System name of the column. Must be unique within the Scenario Type. Spaces or special characters are not allowed.

• Order—Order of the column on the Price Details worksheet, from left to right.

• Input Source—System name of the field in the source entity, data from which populates the column when you build or refresh a scenario. SQL-compliant formulas, including constants, are supported. Define the synonym of the source data entity on the Source Data worksheet.

• Publish Target—Target column of the Publish action. Define the target publish entity on the Scenario Params worksheet.

• Analytic Target—Target column for when values are pushed to the combined analytics entity. Define the target analytics entity on the Scenario Params worksheet.

• Display Name—Display name of the column. Spaces are allowed.

• Group Label—Group on the Price Details worksheet where the column will be placed in. Spaces are allowed. Specifying None leaves the field out of the group.

• Key—Defines whether the column is a unique business key on the Price Details worksheet. Typically, the Seg_Kee and the Senario_Id columns are business keys.

• Editable—Defines whether users can edit the column on the Price Details worksheet.

• Visible—Defines whether users can see the column on the Price Details worksheet. Users still have the ability to show hidden columns.

• Type—Column type. Values are case-sensitive. Supported values are:

  • Input
  • Calculated
  • DropDown
  • TypeAhead

• Data Type—Data type of the column. Supported values depend on the Type setting, as shown in the following table:

  Types	Values
  Input DropDown TypeAhead	FLOAT INTEGER STRING BOOLEAN DATETIME DATE
  Calculated	NUMBER BOOLEAN STRING

• Data Length—Length of the specified Data Type:

  • STRING data type—Defines number of characters allowed.
  • FLOAT data type—Defines number and decimal placement, such as (19,8).

• Display Format—Display format of numeric fields. Supported values defining the digits to the right of the decimal are:

  • CURRENCY(2)
  • PERCENTAGE(1)
  • FLOAT(2)
  • INTEGER(0)
  Note

  Leave the display format value blank for BOOLEAN, STRING, and DATETIME data types.

• Param Tab—Strategy worksheet on which the column is used as a parameter. Leave this field blank if the column shouldn't be used as a parameter.

• Default Value—Default value that populates the column on the strategy worksheet when users build or refresh scenarios. Leave this field blank if no default is needed.

• Solver Attribute—Defines whether you can use a string column to define the joins of spread constraints when running the Optimize Prices action. Such columns are typically the segmentation attributes.

  Note

  A column can be either a Solver Attribute or a Solver Parameter. Defining a column as a Solver Attribute includes it in the database indexing to speed up the Optimize Prices action.

• Solver Parameter—Defines whether you can use the column in the Objective Function and Constraint definitions. Such columns are typically numeric.

• Definition—Definition of the selected Type:

  • Calculated column—Defines a SQL-compliant formula. To reference other columns, use braces.
  • DropDown or TypeAhead column—Defines a system name of a field to be used as a source of the dropdown or type-ahead list values.

Source Data

Use the Source Data worksheet to define tables from which the application retrieves data when users build or refresh scenarios.

• Scenario Type—Name of the scenario template.
• Synonyms—Unique alias of the table with source data.
• Table Source—System name of the table with source data.
• Join—Join type. Supported values are:
  • INNER
  • LEFT
• Join Definition—SQL-compliant join definition using synonyms and logical operators (AND, OR, =, >, >=, <, <=).
• Order—Order of the table with source data in the SQL-compliant join.

Scope

Use the Scope worksheet to define scopes, based on which users can limit the number of prices that their scenario will optimize. Scopes defined here appear in the Scope dropdown column on the Negotiated Prices > Scenario Management worksheet.

• Scenario Type—Name of the scenario template.
• Scope—Unique label of the scope for the selected scenario template.
• Filter—SQL WHERE clause which the application applies when users build or refresh a scenario. SQL-compliant formulas are supported. The synonyms and column names must match configurations on the Source Data worksheet.

KPIs

Use the KPIs worksheet to define KPI cards for scenarios.

• Scenario Type—Name of the scenario template.
• KPI Label—Display name of the KPI card.
• KPI Order—Display order of the KPI card, from left to right.
• KPI Definition—SQL-compliant aggregation formula for the KPI card. To reference columns from the Fieldsworksheet, use curly brackets.
• KPI Value Type—Data type of the KPI card value. Supported values are:
  • INTEGER
  • NUMBER
  • STRING
• KPI Display Format—Display format of numeric KPI cards. Values can include the desired decimal places, for example INTEGER(0) or PERCENTAGE(1).

Param Tabs

Use the Param Tabs to configure strategy worksheets in scenarios.

• Scenario Type—Name of the scenario template.
• Param Tab—Display name of the strategy worksheet in scenarios.
• Order—Display order of the strategy worksheet in scenarios, from left to right.

Param Tabs Scope

Use the Param Tabs Scope worksheet to define scope columns on strategy worksheets.

• Scenario Type—Name of the scenario template.
• Param Tab—Display name of the strategy worksheet in scenarios. Must match configurations on the Param Tabs worksheet.
• Field—String column you want to add to the strategy worksheet. Must match the string columns defined on the Fields worksheet.
• Type—Defines the type of the scope column. Values are case-sensitive. Supported values are:
  • DropDown
  • TypeAhead
  Tip

  Use the type-ahead column type when you have more than 50 list values.

• Order—Display order of the scope column on the strategy worksheet, from left to right.

Jobs

Use the Jobs worksheet to define worksheet actions available in scenarios and their associated job workflows.

• Scenario Type—Name of the scenario template.
• Action Name—Display name of the worksheet action. Must be unique within the Scenario Type.
• Job Id—Job automation ID associated with a valid job workflow.
• Order—Display order of the action.
• Price Sheet—Defines whether the action should appear on the Price Details worksheet in scenarios.
• Param Tab—Defines whether the action should appear on strategy worksheets in scenarios.

Scenario Params

Use the Scenario Params worksheet to define high level parameters associated with a scenario template, such as target entity of the Publish action, source of the Price Review analytics worksheet, or source of the linked analytics on the Price Details worksheet.

• Scenario Type—Name of the scenario template.
• Use Solver—Defines whether the supporting solver entities should be created when users build scenarios.
• Publish Target Entity—Target entity of the Publish action.
• Publish Prices Kee—Combination of fields that represent a unique price on the Negotiated Prices > Publish Price History worksheet. This setting is used to track the Publish Kee and to determine the active price.
  Note

  Usually, this combination is a list of segmentation attributes that define a microsegment in the segmentation manager.

• Archived Publishes—Number of publishes to keep for a Publish Price Kee. For example, a value of 4 means that only the most recent 4 prices will be kept.
• Analytic Target Entity—Target entity for the shared analytics entity.
• Embed Price Review—Defines whether to show the Price Review worksheet in scenarios.
• Price Review Tableau—Source workbook for the scenarios' Price Review worksheet that is published to the associated Tableau server site of the tenant.
• Embed Price Defense—Defines whether to show the embedded Price Defense on the scenarios' Price Details worksheet.
• Price Defense Tableau—Source workbook for the Price Defense that is published to the associated Tableau server site of the tenant.

Global Params

Use the Global Params worksheet to define global parameters for the solver, parallel resources, and timeouts. These parameters are held in key-value pairs.

• Parameter—Unique parameter key.
• Value—Associated value.

Objective Setup

Use the Objective Setup worksheet to define variables and their weight in the solver's objective function.

• Scenario Type—Name of the scenario template.
• Price Point—Price point label for the variable. Must be unique within the Scenario Type. For example, you can have a start, floor, and target points for a price. No spaces or special characters are allowed.
• Order—Order of the variable in relation to a unique numeric prefix generated by the system. For example, a value of 3 for a start price point means that a variable ID for this price point will begin with a number 3.
• Init_Value Field—Name of the source column for the variable. Must be a valid column from the Fields worksheet with the selected Solver Parameter checkbox.
• Weight Formula—Defines the weight of the variable in the solver's objective function of weighted least squares. SQL-compliant formulas are supported. Referenced columns must be valid columns from the Fields worksheet with the selected Solver Parameter checkbox.
• Weight Floor—Minimum weight assigned to the variable in the objective function.
• Weight Ceiling—Maximum weight assigned to the variable in the objective function.

Constraint Setup

Use the Constraint Setup worksheet to define the constraints applied during price optimization. Constraints are conditions that the variables in the optimization problem must satisfy.

• Scenario Type—Name of the scenario template.

• Constraint Code—Name of the constraint. Must be unique within the Scenario Type. No spaces or special characters are allowed.

• Active—Defines whether the constraint is applied when users run the Optimize Prices action.

• Preference—Relaxation order of the constraint in case the optimization problem is infeasible. The system will try to optimize prices in the order of preference groups, from the lowest to the highest preference number. If a constraint is infeasible, the system will remove it from the optimization.

• Constraint Type—Defines the behavior of the constraint. Available values are:

  • Bound—​Applies within a segment
  • Spread—​Applies across segments
  • GBB—Good Better Best, a specific type of a spread constraint

• Spread Field—For the Spread constraint, defines a column to which values and rankings are mapped. Must be a valid column from the Fields worksheet with the selected Solver Parameter checkbox.

• Price Point—Defines price points to which system applies the constraint. Available values are All or one of the price points defined on the Objective Setup worksheet.

• Join Attributes—Defines a set of columns to compare when applying a spread or GBB constraint. Must be valid columns from the Fields worksheet with the selected Solver Attribute checkbox.

• Coeff 1 Formula—Defines the coefficient formula associated with the first variable in a constraint. SQL-compliant formulas are supported. You can use only a valid column from the Fields worksheet with the selected Solver Parameter checkbox. Additional special references are allowed, such as Init_Value to reference the initial value from the Objective Setup worksheet.

• Coeff 2 Formula—Defines the coefficient formula associated with the second variable in a constraint. This value is only needed for spread and GBB constraints (the second variable corresponds to the lower rank spread value, rank 2 is lower than rank 3). SQL-compliant formulas are supported. You can use only a valid column from the Fields worksheet with the selected Solver Parameter checkbox. Additional special references are allowed, such as Init_Value to reference the initial value from the Objective Setup worksheet, or lowerRank.Spread to reference the mapped spread value for a spread or GBB constraint.

• Equality—Defines the direction of the inequality for the constraint. Supported values are:

  • >=
  • <=
  • =

• Bound Formula—Formula associated with the constraint bound constant. SQL-compliant formulas are supported. You can use only a valid column from the Fields worksheet with the selected Solver Parameter checkbox. Additional special references are allowed, such as Init_Value to reference the initial value from the Objective Setup worksheet, or lowerRank.Spread to reference the mapped spread value for a spread or GBB constraint.

Spread Mapping

Use the Spread Mapping worksheet to define mapping of the columns specified in the Spread Field field (on the Constraint Setup worksheet) to spread values and rankings.

• Scenario Type—Name of the scenario template.
• Constraint Code—Name of the constraint. Must match the values on the Constraint Setup worksheet.
• Value—Values from the Spread Field column on the Constraint Setup worksheet.
• Field—Column or formula mapped to the column specified in the Value field.
  • To map your Spread Field to a column, enter a name of a valid column from the Fields worksheet with the selected Solver Parameter checkbox.
  • To map your Spread Field to a hard-coded constant, enter a SQL-compliant formula.
  Note

  The spread is ignored for the highest rank.

• Rank—Rank mapped to the column in the Value field. Constraints are applied to adjacent ranks. Gaps in ranks are filled with the spread value of the lower rank.