1. Implementation Guide
  2. Planning and Flex Fact Configuration

3 Planning and Flex Fact Configuration

Retail Insights supports Merchandise Financial Planning (MFP) data on four separate interfaces, referred to as PLAN1 through PLAN4 tables, Assortment Planning (AP) data on a fifth interface called PLAN5, as well as two IPO Cloud Service-Demand Forecasting forecast interfaces (PLANFC1 and PLANFC2) and four flexible fact interfaces (FLEXFACT1 to FLEXFACT4) for any other external data. All of these tables have configurable data levels to align with the hierarchy levels used in MFP, IPO Cloud Service-Demand Forecasting, or other source systems, but they must be configured prior to using them. The following section describes how to configure RI flexible interfaces for first-time use.

Setting Data Levels

Perform the following procedure to update the expected data levels for the planning interfaces. These levels determine which values the primary key columns are joined with inside Retail Insights (for example, is your plan at Department level or Subclass level). Each interface can be configured with a different set of levels.

  1. Access the Control & Tactical Center to make updates to the C_ODI_PARAM_VW table.

  2. Search for parameter names in the list below, depending on which interfaces you wish to use:

    Table 3-1 C_ODI_PARAM Planning and Flex Fact Parameters

    Planning Facts Flexible Facts Planning Forecast Facts

    RI_PLAN1_CAL_LEVEL

    RI_FLEXFACT1_CAL_LEVEL

    RI_PLANFC1_CAL_LEVEL

    RI_PLAN1_ORG_LEVEL

    RI_FLEXFACT1_ORG_LEVEL

    RI_PLANFC1_ORG_LEVEL

    RI_PLAN1_PROD_LEVEL

    RI_FLEXFACT1_PROD_LEVEL

    RI_PLANFC1_PROD_LEVEL

    RI_PLAN1_SUPP_LEVEL

    RI_FLEXFACT1_SUPP_LEVEL

    RI_PLANFC1_SUPP_LEVEL

    RI_PLAN1_ATTR_LEVEL

    RI_FLEXFACT1_ATTR_LEVEL

    RI_PLANFC1_ATTR_LEVEL

    RI_PLAN2_CAL_LEVEL

    RI_FLEXFACT2_CAL_LEVEL

    RI_PLANFC2_CAL_LEVEL

    RI_PLAN2_ORG_LEVEL

    RI_FLEXFACT2_ORG_LEVEL

    RI_PLANFC2_ORG_LEVEL

    RI_PLAN2_PROD_LEVEL

    RI_FLEXFACT2_PROD_LEVEL

    RI_PLANFC2_PROD_LEVEL

    RI_PLAN2_SUPP_LEVEL

    RI_FLEXFACT2_SUPP_LEVEL

    RI_PLANFC2_SUPP_LEVEL

    RI_PLAN2_ATTR_LEVEL

    RI_FLEXFACT2_ATTR_LEVEL

    RI_PLANFC2_ATTR_LEVEL

    RI_PLAN3_CAL_LEVEL

    RI_FLEXFACT3_CAL_LEVEL

    RI_PLAN3_ORG_LEVEL

    RI_FLEXFACT3_ORG_LEVEL

    RI_PLAN3_PROD_LEVEL

    RI_FLEXFACT3_PROD_LEVEL

    RI_PLAN3_SUPP_LEVEL

    RI_FLEXFACT3_SUPP_LEVEL

    RI_PLAN3_ATTR_LEVEL

    RI_FLEXFACT3_ATTR_LEVEL

    RI_PLAN4_CAL_LEVEL

    RI_FLEXFACT4_CAL_LEVEL

    RI_PLAN4_ORG_LEVEL

    RI_FLEXFACT4_ORG_LEVEL

    RI_PLAN4_PROD_LEVEL

    RI_FLEXFACT4_PROD_LEVEL

    RI_PLAN4_SUPP_LEVEL

    RI_FLEXFACT4_SUPP_LEVEL

    RI_PLAN4_ATTR_LEVEL

    RI_FLEXFACT4_ATTR_LEVEL

    RI_PLAN5_CAL_LEVEL

    RI_PLAN5_ORG_LEVEL

    RI_PLAN5_PROD_LEVEL

    RI_PLAN5_SUPP_LEVEL

    RI_PLAN5_ATTR_LEVEL

  3. Modify the parameters as needed using the following list of supported values. A value of ALL means the column should be set to a default value of -1 on the data file and it will not be joined with any other dimension in RI during ETL. A value of FLEX# indicates an alternate hierarchy level will be used, such as FLEX1 matching data on the W_PRODUCT_FLEX_D.FLEX1_CHAR_VALUE field. Usage of flex fields assumes that this is the same level also used in Planning applications for the plan data.

    Table 3-2 C_ODI_PARAM Planning and Flex Fact Values

    Product (PROD) Organization (ORG) Calendar (CAL) Supplier (SUPP) Attribute (ATTR)

    CMP

    COMPANY

    YEAR

    SUPPLIER

    C (for Color)

    DIV

    CHAIN

    HALFYEAR

    PARENT_SUPPLIER

    B (for Brand)

    GRP

    AREA

    QUARTER

    ALL

    FLAVOR

    DEPT

    REGION

    PERIOD

     

    FABRIC

    CLS

    DISTRICT

    WEEK

     

    SCENT

    SBC

    LOCATION

    DAY

     

    SIZE

    ITEM

    CHANNEL

    GREGORIANYEAR

     

    STYLE

    ITEM_LEVEL1

    CHANNEL_COUNTRY

    GREGORIANQUARTER

     

    ALL

    ALL

    PRICE_ZONE

    GREGORIANMONTH

     

     

    FLEX1 -FLEX20

    ALL

    GREGORIANDAY

     

     

     

    FLEX1 – FLEX20

     

     

     

  4. For RI customers, raise a Service Request with Oracle Support when all configurations are complete, stating that the Planning Configuration processes must be executed on your environment (provide the environment name and summarize the interfaces you plan to use). Part of the configuration process directly alters the application metadata to use your specified levels, so it currently must be managed by Oracle. This step only applies if you need to use the Reporting functionality of RI. It does not apply if you are only loading data for AI Foundation applications.

Preparing Data Files

After the interfaces are configured, you must prepare the data files for upload to Retail Insights. If you are using MFP, AP, or IPO Cloud Service-Demand Forecasting Cloud Services, the data can be automatically integrated between applications. This involves a one-time setup process during your Planning implementation to enable the batch processes that move data from PDS to RI, and then enable the RI jobs to consume that data. In that case the rest of this section does not apply. Proceed to the next section on partitioning the tables instead.

If you are uploading data files directly to RI, then there are some rules and guidelines to follow when creating the files:

  • All key columns on the interface must be populated, even if you have specified "ALL" as the data level. You should use a default value of -1 to populate these fields. This includes the fields PROD_DH_NUM, PROD_DH_ATTR, ORG_DH_NUM, SUPPLIER_NUM, as well as all other columns before the CAL_DATE on the flex interfaces.

  • The calendar (CAL_DATE) field must always be a date. If loading the data above day level, use the end-of-period date. The format must match the date mask specified on the context (CTX) file.

  • The PLANNING_TYPE_CODE field was originally used to specify if the plan type was COST or RETAIL, but this makes no functional difference in reporting at this time and can be set to any value.

  • The VERSION_NUM field specifies the plan version in numerical order, starting with 0. When integrating with MFP, the Original Plan is always version 0, and the Current Plan is version 1. These two versions correlate to OP and CP metrics in Retail Insights metadata. Versions greater than 1 may be used to capture version history in the RI database, and the highest available version will be shown in the CP set of metrics.

  • The DATASOURCE_NUM_ID field must be provided with a hard-coded value of 1, similar to all other RI interface specifications.

  • The INTEGRATION_ID field must be provided with a unique value that identifies the record, such as a concatenation of all primary key values.

  • The data file may use standard RI formatting, which uses pipes (|) as delimiters for the columns, or you may pick other delimiters as needed. You can change the file format options in the CTX file.

The accepted list of data files would align to the interfaces:

  • W_RTL_PLAN1_PROD1_LC1_T1_FS.dat

  • W_RTL_PLAN2_PROD2_LC2_T2_FS.dat

  • W_RTL_PLAN3_PROD3_LC3_T3_FS.dat

  • W_RTL_PLAN4_PROD4_LC4_T4_FS.dat

  • W_RTL_PLAN5_PROD5_LC5_T5_FS.dat

  • W_RTL_PLANFC_PROD1_LC1_T1_FS.dat

  • W_RTL_PLANFC_PROD2_LC2_T2_FS.dat

  • W_RTL_FLEXFACT1_FS.dat

  • W_RTL_FLEXFACT2_FS.dat

  • W_RTL_FLEXFACT3_FS.dat

  • W_RTL_FLEXFACT4_FS.dat

All of the files must be packaged into a ZIP file for upload, such as RI_MFP_DATA.zip or RI_EXT_DATA.zip. The zip file for these can be marked as optional (which means the nightly batch will not wait for them to arrive before starting) or required (meaning the batch must receive them every night to start processing). It is standard to mark these files as required, since you do not want the batch to run without your planning data, or the next day's reporting could be inaccurate.

Lastly, you must also provide context (CTX) files for each interface, which specifies the exact columns you are populating with data. This allows you to provide only a subset of the many columns on the interface, as long as the key columns and at least one data column are populated. Each CTX file is named similarly to the interface (e.g. W_RTL_PLAN1_PROD1_LC1_T1_FS.dat.ctx) and contains the file format parameters such as column delimiter and header row counts, followed by a single column of values matching the interface field names. Refer to the Retail Insights Interfaces Guide for complete details on the available fields. Refer to the Retail Analytics and Planning Implementation Guide for complete details on CTX file contents.

Partition Tables

The first planning table (W_RTL_PLAN1_PROD1_LC1_T1_F) requires special partition structures due to its use in integrating data to the AI Foundation applications like Promotion and Markdown Optimization. The flex fact and forecasting tables also require similar partitioning before they can be used. The plan and flex tables have flexible partitions which can be either Day or Week level, depending on the data level you plan to use. You must perform this partition setup process before using the tables, or it will not function properly during data loads. The PLAN2 through PLAN5 tables do not have partitioning at this time as they are only used by AIF forecasting processes which don’t need partitioned data.

In order to configure the plan and flex fact partitions, you must update the table C_MODULE_EXACT_TABLE where MODULE_CODE in (PLAN1, PLANFC, FLEXFACT1, FLEXFACT2, FLEXFACT3, FLEXFACT4). Modify the columns PARTITION_COLUMN_TYPE and PARTITION_INTERVAL to be one of the following values:

  • If your input data will be at Day level, set both columns to ‘DY’

  • If your input data will be at Week level, set both columns to ‘WK’

You must also enable the partitioning process in C_MODULE_ARTIFACT by locating the rows for MODULE_CODE in (PLAN1, PLANFC, FLEXFACT1, FLEXFACT2, FLEXFACT3, FLEXFACT4) and setting ACTIVE_FLG=Y and PARTITION_FLG=Y. If your plan data will extend into the future, you must also change PARTITION_FUTURE_PERIOD to the number of future months that need partitions built beyond the specified business date (e.g. use a value of 6M to partition 6 months into the future).

Once the configuration steps are completed, you must run the adhoc POM process CREATE_PARTITION_ADHOC (this is run once as part of any standard implementation, but if you have already run it once before updating the plan configurations, then you must run it again). If you already have nightly batches running, then the ETL date is being managed there, and you must disable both the ETL_BUSINESS_DATE_JOB and W_RTL_CURR_MCAL_G_JOB in this process and only run the two partition jobs by themselves.

Example Postman message body to send to POM via Rest API call:

{
"cycleName": "Adhoc", 
"flowName":"Adhoc", 
"processName":"CREATE_PARTITION_ADHOC",
"requestParameters":"jobParams.CREATE_PARTITION_PRESETUP_
JOB=2018-12-30,jobParams.ETL_BUSINESS_DATE_JOB=2021-02-06"
}

If you disabled the business date jobs, then the process call would be like this instead:

{
"cycleName": "Adhoc", 
"flowName":"Adhoc", 
"processName":"CREATE_PARTITION_ADHOC",
"requestParameters":"jobParams.CREATE_PARTITION_PRESETUP_JOB=2018-12-30"
}

If you are entering the parameters from the POM UI instead of Postman, then you must specify each date as the input parameter on the jobs listed above. The first date is added as the parameter on CREATE_PARTITION_PRESETUP_JOB and the second date is added on ETL_BUSINESS_DATE_JOB (if using).

Loading Plan and Forecast Data

The plan and forecast data will be loaded either from flat files or directly from the planning systems. If you are loading from flat file, then you will run the COPY/STG jobs to import the file ata followed by the fact loads (*_F_JOB) and disable any other jobs. If you are loading from Planning directly, then you would use the *_SDE_JOB programs followed by the fact loads (*_F_JOB) and disable any other jobs. The forecast direct-loads are run outside the nightly batch as a recurring flow you can schedule, so that the weekly forecast load does not impact your nightly batch runtimes.

Table 3-3 Dataset Jobs

Job Ad Hoc Processes or Flows Usage

W_RTL_PLAN1_PROD1_LC1_T1_FS_COPY_JOB

W_RTL_PLAN2_PROD2_LC2_T2_FS_COPY_JOB

W_RTL_PLAN3_PROD3_LC3_T3_FS_COPY_JOB

W_RTL_PLAN4_PROD4_LC4_T4_FS_COPY_JOB

W_RTL_PLAN5_PROD5_LC5_T5_FS_COPY_JOB

W_RTL_PLAN1_PROD1_LC1_T1_FS_STG_JOB

W_RTL_PLAN2_PROD2_LC2_T2_FS_STG_JOB

W_RTL_PLAN3_PROD3_LC3_T3_FS_STG_JOB

W_RTL_PLAN4_PROD4_LC4_T4_FS_STG_JOB

W_RTL_PLAN5_PROD5_LC5_T5_FS_STG_JOB

N/A (Nightly Only)

Load the MFP and AP planning flat files from the server into the staging area in the database. Only used as part of nightly batch processing, flat files cannot be loaded in ad hoc processes. Tables are truncated at the beginning of each execution so new data can be inserted.

W_RTL_PLAN1_PROD1_LC1_T1_FS_SDE_JOB

W_RTL_PLAN2_PROD2_LC2_T2_FS_SDE_JOB

W_RTL_PLAN3_PROD3_LC3_T3_FS_SDE_JOB

W_RTL_PLAN4_PROD4_LC4_T4_FS_SDE_JOB

W_RTL_PLAN5_PROD5_LC5_T5_FS_SDE_JOB

LOAD_PLANNING1_DATA_ADHOC

LOAD_PLANNING2_DATA_ADHOC

LOAD_PLANNING3_DATA_ADHOC

LOAD_PLANNING4_DATA_ADHOC

LOAD_PLANNING5_DATA_ADHOC

Imports planning data directly from the MFP and AP exports from Planning Data Store (PDS) to Retail Insights. The data is inserted into the staging tables, and assumes you are not providing any data via flat file (each interface should only come from either MFP/AP or flat file but not both).

W_RTL_PLAN1_PROD1_LC1_T1_F_JOB

W_RTL_PLAN2_PROD2_LC2_T2_F_JOB

W_RTL_PLAN3_PROD3_LC3_T3_F_JOB

W_RTL_PLAN4_PROD4_LC4_T4_F_JOB

W_RTL_PLAN5_PROD5_LC5_T5_F_JOB

LOAD_PLANNING1_DATA_ADHOC

LOAD_PLANNING2_DATA_ADHOC

LOAD_PLANNING3_DATA_ADHOC

LOAD_PLANNING4_DATA_ADHOC

LOAD_PLANNING5_DATA_ADHOC

Transforms and loads planning data from staging areas into the final fact tables. Used both for the nightly processing and for ad hoc loads.

W_RTL_PLANFC_PROD1_LC1_T1_FS_COPY_JOB

W_RTL_PLANFC_PROD2_LC2_T2_FS_COPY_JOB

W_RTL_PLANFC_PROD1_LC1_T1_FS_STG_JOB

W_RTL_PLANFC_PROD2_LC2_T2_FS_STG_JOB

N/A (Nightly Only)

Load the forecast flat files from the server into the staging area in the database. Only used as part of nightly batch processing, flat files are not loaded in the ad hoc processes. Tables are truncated at the beginning of each execution so new data can be inserted.

W_RTL_PLANFC_PROD1_LC1_T1_FS_SDE_JOB

W_RTL_PLANFC_PROD2_LC2_T2_FS_SDE_JOB

RI_FORECAST_ADHOC

Imports forecast data directly from the AIF exports to Retail Insights. The data is inserted into the staging tables, and assumes you are not providing any data via flat file. Only the sku/store/week forecasts are pulled by default.

W_RTL_PLANFC_PROD1_LC1_T1_F_JOB

W_RTL_PLANFC_PROD2_LC2_T2_F_JOB

RI_FORECAST_ADHOC

Transforms and loads forecast data from staging areas into the final fact tables. Used both for the nightly processing and for ad hoc loads.

CLEANUP_C_LOAD_DATES_PLANNING_JOB

CLEANUP_C_LOAD_DATES_PLANNING_ADHOC

In order to run a planning load more than once in a single business date, you must clear the execution status from the prior run, otherwise the batch process will skip any subsequent loads (until the business date changes).

Loading Aggregate History

Special flex fact tables have also been provided to support loading of pre-aggregated history fact data for AI Foundation to use to generate forecasts for Planning. These tables should not be used unless there is no other way to provide history data except at a level above item/location. These tables will completely bypass the normal data flow for history in RI and AIF, directly populating history fact tables just for limited AIF use-cases.

These tables work identically to PLAN and FLEX tables described in the previous sections. They use FACT as the table name prefix, for example W_RTL_FACT1_PROD1_LC1_T1_F. Four such FACT tables are provided for the different data intersections that may exist for aggregate actuals. Just like PLAN tables, you must configure these in C_ODI_PARAM_VW in the Control Center using parameters like RI_FACT1_PROD_LEVEL and RI_FACT1_ORG_LEVEL.

Refer to the Retail Analytics and Planning Solutions Implementation Guide section on Loading Aggregate History Data for additional information on how to use these tables.