Executing Market Intelligence Interface Service for Import

Market Intelligence (MI) Interface Services are executed using the RunInterfaceService macro. As part of the Import process, the fields such as Item, Location, Start Date, End Date, Value are imported for the Market Intelligence selected on the Market Intelligence tab.

The Interface Service can also be run manually using the Import Scenarios option.

During the macro execution, this error message is displayed for errors in the import file(s):
Import [macro] completed with errors. See audit log for details.
Note: If multiple files are imported (for CSV or Excel), the files are considered as single file transaction.
If the Reject all on Error parameter is set to Off, a discard log is created and linked to the related audit log message. You can download the audit log file using the Log File option. The file contains all the rows that are discarded and an additional column with the error details. In case multiple files are imported, this comment is included to indicate the file in which the error occurred:
File Name 
Errors found on row: [row]; [error details]

The format of the log file: [date]-[user]-[interface service].CSV

If the Reject all on Error parameter is set to On and a row fails, no MI Event is imported.

The rows in the import files are processed sequentially.

The rows in the import file can be grouped to create a single MI Event if these rules are adhered to:

  • The rows must have same reference.
  • The rows must contain the same item and location.
  • The rows in the import file must be arranged in a contiguous order (does not have to be by Date).
  • When importing using Database, the data is imported based on the natural order of the table. To facilitate ordering within the imported table, the Source must be set to Query which allows the use of Order by option.
    Note: This can only be achieved by specifying the query as:
    SELECT A as ColumnA,B as ColumnB,C as ColumnC, ROW_NUMBER() OVER (ORDER BY A) as ColumnRowNumber FROM TABLE  
  • The Start date and End date on each row must match the Start date and End date of a single bucket and belong to the same level. For example, all Start date and End dates must define a month. A mix of months and weeks are not allowed.
  • The rows do not have to belong to contiguous buckets. For buckets that are not defined, a value of zero is considered during the creation of the single MI event.
  • For a bucket, two rows can have the same Start date and End date. In this scenario, the values are summed for that bucket.
  • If the rows contain Note information, a Note is created for the MI based on the Note in the first row. The remaining Notes for the event are not considered.
  • The rows are grouped and processed together as part of the import process until the next Reference.
    Note: Another instance of the same Reference can exist in the import file. If there is a break between the rows that have this Reference, separate MI events are created for these rows.
  • The MI event is created based on all the mapped fields, except:
    • Start Period: Obtained by matching the earliest Start date and End date in the linked rows of the imported file, with a bucket in SCP.
    • Number of Periods: Determined by the number of periods the linked rows span. This value can be greater than the number of rows, to accommodate a missed bucket.
    • Period Share Measure set to Import.

For rows with a unique Reference and having the Start date and End date that matches a single bucket (any level) in SCP, an MI event is created for this bucket. The MI event is created based on all the mapped fields, except:

  • Start Period: Obtained by matching the Start and End date in the imported file, with a bucket in SCP.
  • Number of periods set to 1
  • Period Share Measure set to Import.

For rows with a unique Reference and having Start date and End date that do not match a single bucket (any level) in SCP, an MI event is created. For this event:

  • The Start period is identified by the base bucket to which the Start date belongs.
  • The last bucket is identified by the base bucket to which the End date belongs.
  • If the Start date and End date are within one base bucket, the value is applicable to the single bucket.
  • If the Start date and End date belong to more than one base bucket, the values are prorated evenly across days. These values are later aggregated to the base calendar level based on the number of days the event exists in each bucket.
  • The Event is created based on all the mapped fields, except:
    • Start Period: Obtained by identifying the base bucket which matches the start date in the import file.
    • Number of periods is set as the number of buckets the start and end date span, at the base period level.
    • Period Share Measure set to Import.
Note: The rows without a Reference are considered as separate MI events.

In case the Start date and End date match more than 1 level, MI events are created against the lowest available level that matches with these dates. If the MI event is to be created for multiple rows, the MI event is added at a common level. For example, if the lowest level for a row is Pt Week and the lowest level for another row is Week, the MI event is added at the Week level.

Market Intelligence can be imported at the telescopic levels if:

  • The buckets exist within the cycle.
  • All the imported rows exist at the same telescopic level.

The MI is spread down to base Items, locations and periods (if applicable) based on the mapping values for Spread Measure and Use Spread Measure for Period Spread. Possible scenarios:

  • If the Spread Measure mapping is not specified, the default spread measure defined in the Market Intelligence definition is used to spread the values down to base items and locations.
  • If the Use Spread Measure for Period Spread mapping is populated with Yes, the Spread Measure mapping is also used to spread to periods.
    Note: The Spread Measure must exist at Item (I), Location (L) and Period (P).
  • If the Use Spread Measure for Period Spread mapping is populated with No, the Spread Measure specified for the Cycle is used to spread to periods.
    Note: 
    • The Interface Service Spread Measure must be defined at I and L level.
    • The Cycle Period spread measures must be defined at P level.
  • Spreading measure must exist for the dimension based on the selected spreading options. If the spreading measure does not exist for the same dimension, the record is rejected, and this error message is displayed:
    Errors found on row: [row]; [Spreading Measures must be based on the same dimension as the element to spread]
This table displays the valid Interface Service Spreading measure mappings and associated rules.
Spread Measure (Interface Services) Use Spread Measure for Period Spread (Interface Service) Rules
Populated Yes Use IS Spread Measure to spread to Items and Locations.

Use IS Spread Measure to spread to Periods.

IS Spread Measure must exist at I, L, P levels.

Populated No Use IS Spread Measure to spread to Items and Locations.

Use Cycle’s ‘Period Spread Measure’ to spread to Periods.

IS Spread Measure must exist only at I and L levels.

Cycle Period Spread Measure must exist only at P.

Populated Not Populated This is invalid, and an error message is displayed.

If ‘Use Spread Measure for Period Spread’ is not specified, the Spread Measure cannot be specified.

Not Populated Not Populated Use the MI definition to determine the spreading to both I, L and P levels.

If ‘Use Spread Measure for Period Spread’ (in MI definition) is set to On, the spread measure in MI definition must exist at I, L, P levels.

If ‘Use Spread Measure for Period Spread’ (in MI definition) is set to Off, the spread measure in MI definition must exist only at I and L levels.

The Period Spread Measure cycle must only exist at P.

Not Populated Populated This is invalid, and an error message is displayed.

Notes are mandatory if one of these conditions is met:

  • Note Type is specified. Consequently, Priority and Summary must be specified.
  • The Force Note option for the MI definition is set to On.
If the Note fields are not specified, or the Note type or priority is invalid, the record is rejected and this error message is displayed:
Errors found on row: [row]; [Notes are required, Note Type, Priority, Summary are required fields]
Note: 
  • Existing MI Events are not modified during the Import process.
  • The deletion of MI Events must be managed externally.